[Power Query] Création d'un compteur en fonction des dates
Bonjour à tous,
Je bloque complètement sur une transformation de données afin de générer une requête "compteur" en fonction de dates.
Je m'explique :
J'ai un base de données avec des véhicules, des régions et des dates (Affectation début, affectation fin, date de sortie).
Ci-dessous un exemple de ma base de données :
Mon but est d'obtenir un compteur de véhicule pour chaque dates (année-mois) et d'y rapatrier la région. J'aurai donc tant que le véhicule n'est pas sorti 12 lignes par années avec en face la région sur laquelle le véhicule est affecté (en fonction des différents mouvements)
Pour se faire j'aimerai transformer cette base de données dans power query afin d'obtenir une nouvelle base exploitable du style :
Je bloque rien que sur les règles à déterminer afin de d'obtenir une région par ligne.
En gros j'avais déjà pensé à rajouter une colonne avec une date de fin de période pour déterminer les intervalles.
Exemple pour le premier cas :
Véhicule XXXX : Affectation début : 03/01/2017 ; Affectation fin : 09/02/2017 = REGION 01
Date fin période du 31/01/2017
Date fin période est bien comprise (>=) entre mon affectation début (03/01/17) et ma date de fin (<=) (09/02/17) pour le véhicule XXXX donc résultat = "01"
Je ne sais pas comment faire pour le deuxième cas : (et les autres...)
Véhicule XXXX : Affectation début : 10/02/2017 ; Affectation fin : 09/03/2017 = REGION 04
Date fin période du 28/02/2017 => résultat attendu : "04"
Et ainsi de suite.
Je vous remercie d'avance pour votre aide, si quelqu'un à déjà travaillé sur une problématique similaire :)
Cdt,
Hello,
Une proposition
@+
Edit : une V2 pour l'étape "Group"
Hello,
Whaouu, je dois vraiment me mettre à Power Query ! Ce que tu fais rapidement me prends une plombe à faire via des formules et cela prends trop de ressources de calculs..
J'ai juste un petit bémol. C'est que si j'étends à ma base de données, j'ai des véhicules qui n'ont pas de date de sortie (car encore en exploitation). Donc l'étape ou tu remplaces les valeurs nuls dans "Affectation Fin" par la date de sortie ne fonctionne plus.
Donc si je n'ai pas de date de sortie il faut que mon compteur continu avec la date maximum actuelle.
Par exemple pour un véhicule encore en exploitation sans date de sortie, je dois avoir un ligne avec la date au 30/06/2024 et la région en fonction de l'affectation actuelle.
Je te remercie pour ton aide, mon niveau en langage "M" n'en ai qu'au début :=)
J'ai remis le fichier en PJ avec l'ajout d'un véhicule encore en activité
Hello,
Remplace l'étape "Replace" par ça :
= Table.ReplaceValue(Type,null,each if [Date Sortie]<> null then [Date Sortie] else Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Affectation Fin"})@+
Hello,
Merci à vous deux, vos solutions fonctionnent parfaitement, merci beaucoup !!
@JFL,
Pour ma culture, je veux bien une petite explication sur ton code ci-dessous. Impressionnant ce que vous arrivez à faire avec ce langage. J’espère le maitriser avec le temps :)
Table.AddColumn(TypeDate, "Rec", each let
dd = [Affectation Debut],
df = [Affectation Fin] ?? Date.AddMonths([Date Sortie],1) ?? Date.From(DateTime.LocalNow()),
dm = [Date MES],
lm = List.Accumulate({0..(Date.Year(df)-Date.Year(dd))*12+(Date.Month(df)-Date.Month(dd))-1},{},(s,c) => s & {Date.EndOfMonth(Date.AddMonths(dd,c))}),
nbM= List.Count(lm),
r= List.Generate(
()=>[c=0,DateFinPériode=lm{0},COMPTEUR=1,AGE=Number.Round((fnNbMois(dm,DateFinPériode))/12,1),ANNEE=Date.Year(DateFinPériode), MOIS=Date.Month(DateFinPériode)],
each [c]<nbM,
each [c=[c]+1,DateFinPériode=lm{c},COMPTEUR=1,AGE=Number.Round((fnNbMois(dm,DateFinPériode))/12,1),ANNEE=Date.Year(DateFinPériode), MOIS=Date.Month(DateFinPériode)],
each [[DateFinPériode],[COMPTEUR],[AGE],[ANNEE],[MOIS]]
)
in r)
Bonjour à tous de nouveau !
Cette partie du code affecte, dans une nouvelle colonne (Table.AddColumn....) et dans chacun des enregistrements, un record contenant les informations désirées.
- dd, df et dm : récupération les valeurs, de l'enregistrement concerné, nécessaires pour le calcul.
- lm : La fonction List.Accumulate est utilisée pour produire la liste des dates (fin de mois) de la période.
- nbM : Nombre de mois de la période concernée. Cette valeur est utile pour sortir de la boucle du List.Generate.
- r : Création d'un record via la fonction List.Generate, contenant les valeurs calculées ; Liste des mois (FinPériode), Compteur,Age, Année et Mois.
Impressionnant ce langage. J’espère le maitriser avec le temps
Votre gourmandise sera votre principale alliée...