Accueil – Le CFO masqué › Forums › Power Query › Langage M – Dupliquer des lignes en fonction d’un critère numérique
Mots-clés : Langage M; Power Query; Boucle; VBA
- Ce sujet contient 5 réponses, 2 participants et a été mis à jour pour la dernière fois par
olivier.muscat, le il y a 3 années et 1 mois.
-
AuteurArticles
-
17 mars 2021 à 2 h 52 min #88443
olivier.muscatParticipantBonjour,
J’ai une table constituée de plusieurs millier de lignes.
Chacune de ces lignes possède une date de début et une date de fin.
Pour pouvoir répartir la durée totale en fonction des mois écoulés, j’ai besoin de dupliquer chaque ligne autant de fois que de mois écoulé.
Je saurais le faire facilement en Visual basique à l’aide de boucle récursive.Mon besoin un peu plus détaillé est de :
1- pour chaque ligne j’ai une date de début et une date de fin (si pas de date il faut prendre la date du jour)
2- Il faut que je vérifie sur combien de mois dure cette ligne
3- Créer le nombre de ligne correspondant au nombre de mois
4- Créer 2 colonnes permettant sur chaque ligne d’inscrire :
a- Le début du mois : correspond au début de la première ligne et au 1er jour du mois sur les lignes suivantes
b- La fin du mois : correspond à la fin de l’immob sur la dernière ligne et au dernier jour du mois sur toutes les lignes précédentes.Ma question est : Ce genre de traitement est-il réalisable en langage M ? Si oui auriez-vous des pistes de réalisation ?
Merci d’avance du temps passés pour me répondre.
Attachments:
You must be logged in to view attached files.17 mars 2021 à 5 h 19 min #88447Stéphane LorinParticipantBonjour
Par principe je vous dirai que (presque) toutes les manipulations de données sont réalisables en M.
Vous pouvez trouver un exemple différent, mais assez similaire malgré tout, dans le post “Calculer le délai de travail entre deux dates Power BI (DAX)” (mon fichier en date du 15 janvier)Pour obtenir les étapes que vous listez ci-dessus :
– remplacement des “null” par la date/heure avec DateTime.LocalNow()
– ajout d’une colonne début de mois via le menu ajouter une colonne/ Date/ Mois (avec fonction Date.StrartOfMonth)
– ajout d’une colonne fin de mois sur le même principe (avec fonction Date.EndOfMonth)
– il n’y a pas de fonction de différence de mois comme DATEDIFF(“m”,…) de VBA. Il faut donc passer par la différence des années * 12 + la différence des mois
– puis créer une liste {0…nb_mois} et la développer
– utiliser la fonction Date.AddMonths pour obtenir la liste des mois
– calcul les min des dates de fin et les max des dates de début et faire la différence
Je trouve ça lourd et complexe…Comme régulièrement avec Power Query, il faut raisonner différemment qu’avec Excel ou VBA.
Donc pour ma part je commence par créer 2 paramètres : début et fin (en datetime) et je crée une requête sur vos données en filtrant [date de création] <= fin et [date de clôture]>= début
je transforme cette requête en fonction (par clic droit)Ensuite je crée une liste des mois dans Excel (de janvier 2019 à mars 2021 dans votre cas)
je calcule la fin de mois et j’applique ma fonction
pour chaque mois j’obtiens la table filtrée des éléments présents durant le mois
il ne me reste plus qu’à insérer une colonne ‘la plus récente’ entre le début de mois et la date de création (donc un List.Max)
puis une colonne ‘la plus ancienne’ entre la fin de mois et la clôture (donc un List.Min)
et enfin la différence entre les dates heures obtenues via Duration.TotalDays([Fin retenu] – [Début retenu])j’obtiens ainsi la durée de chaque référence par mois que je synthétise dans 2 TCD.
cordialement
StéphaneAttachments:
You must be logged in to view attached files.18 mars 2021 à 2 h 18 min #88480olivier.muscatParticipantBonjour,
Merci pour cette réponse qui semble relativement complète.
Maintenant il faut que je prenne le temps de l’assimiler pour tenter de l mettre en oeuvre.Merci
Bonne fin de journée.18 mars 2021 à 8 h 45 min #88481olivier.muscatParticipantBonjour,
Je vais encore avoir besoin d’un peu d’aide….
J’ai suivi le process proposé :
1-Créer les paramètres : Ok
2-Filtrer et créer une fonction : Ok
3-Créer une table de mois avec début et fin de mois : Ok
4-Appliquer la fonction –> C’est là que je but un peu : Comment cette opération se réalise-t-elle ?Pour la suite je verrais une fois la fonction appliqué car j’ai encore du mal à voir la table de résultat une fois la fonction appliquée.
Merci d’avance.
18 mars 2021 à 10 h 42 min #88490Stéphane LorinParticipantBonjour
bravo pour les premières étapes 🙂
pour la suite, vous faites appel à la fonction personnalisée dans le menu ‘ajouter une colonne’ puis vous définissez la valeur de vos paramètres.La fonction filtre la table initiale en ne retenant que les lignes dont la plage début->fin est incluse entre les paramètres.
Ainsi en appliquant cette fonction à toutes les période de janvier 2019 à mars 2021 et en développant les colonnes, vous obtiendrez le résultat que vous souhaitiez à votre étape :
3- Créer le nombre de ligne correspondant au nombre de moisles dernières étapes
vous sélectionnez les colonnes début mois et début de période de la référence : menu ajout de colonne / Date / la plus récente
puis vous recommencez avec fin de mois et fin de période en choisissant ‘la plus ancienne’enfin vous faites la différence entre les deux valeurs obtenues via ajout de colonne/date/soustraire des jours qui génère un Duration.Days (nb de jour entier) que j’ai modifié enfin par Duration.TotalDays car je voulais avoir aussi la fraction de jour (les heures)
bon courage pour la fin
Stéphane
19 mars 2021 à 2 h 14 min #88524olivier.muscatParticipantBonjour,
Super et merci.
Grace à vos explications claires et précises, j’ai réussi à aller au bout de mon besoin.Bonne fin de journée.
-
AuteurArticles
- Vous devez être connecté pour répondre à ce sujet.
