Power BI [résolu] Multiples opérations via Power Query

[ilyes4205]

Bonjour à tous, je souhaite réaliser des opérations via Power Query. Je suis un gros débutant et j'ai réussi tant bien que mal à me rapprocher du résultat final mais là je bloque.

J’ai un ensemble de 5 colonnes qui, pour chaques techniciens, donnent le type d’heure dépensé (travail, congé, …), le nombre d’heure dépensé, la date où ce type et ce nombre d’heure ont été dépensé, le numéro de semaine de cette date. Voici plus de details sur mes données actuelles, on a donc la 1ère colonne avec les noms, prenoms des techniciens (nommée “Technician name”), la seconde colonne (nommée “Pay Code”) avec le type d’heure, c’est à dire :

• les heures travaillées (“Regular”, “Regular - Shift 2, “Regular – Shift 3”),
• les heures supplémentaires (“Overtime”, “Overtime - Shift 2”, “Overtime - Shift 3”, “Overtime - Weekend", “Overtime - Weekend -Shift2”, “Overtime - Weekend -Shift3”, “Holiday Worked”, “Holiday Worked - Shift 2”, “Holiday Worked - Shift 3”),
• les heures de congés (“PTO”, “PTO - Shift 2”, “PTO -Shift 3”),
• les heures correspondant aux jours fériés (“Holiday”, “Holiday - Shift 2”, “Holiday - Shift 3”).

La 3ème colonne (nommée “Work Date”) correspond à la date lié aux heures, la 4ème colonne (“Hours”) donne le nombre d’heure dépensé, le 5ème colonne (“Week No”) donne le numéro de la semaine lié à la date.

Mon besoin : comparer les heures "Holiday" (ou "Holiday - Shift 2" ou "Holiday - Shift 3") avec les heures "Holiday Worked" ("Holiday Worked -Shift2" ou "Holiday Worked -Shift3")
Dans les 2 exemples que je vais détaillé ci-après, la journée de travail du technicien est de 10h (certains autres techniciens ont une journée de 8h).

En orange on a le cas ou "Holiday" est inferieur à "Holiday Worked", en français cela veut dire que le technicien a travaillé 11h un jour considéré férié oú il était censé etre au repos 10h. Dans ce cas là, je veux simplement supprimer la ligne qui correspond à "Holiday".
DONC si heures "Holiday" (ou "Holiday - Shift 2" ou "Holiday - Shift 3") inferieur à heures "Holiday Worked" (ou "Holiday Worked -Shift2" ou "Holiday Worked -Shift3") pour le meme jour et meme technicien alors supprimer la ligne entiere qui correspond à "Holiday" pour le meme jour et meme technicien.
En bleu on a le cas oú "Holiday" est superieur à "Holiday Worked", en français cela veut dire que le technicien a travaillé 7.75h un jour considéré férié et que sur cette journée de 10h, il lui reste 2.25h de congés rémunérés (10-7.75 = 2.25). Dans ce cas je veux 2 choses, premièrement calculer la difference "Holiday" MOINS "Holiday Worked". Le résultat (2.25 ici) va venir remplacer la valeur de "Holiday" (ici remplacer 10 par 2.25) pour enfaite dire que ce jour là le technicien, au lieu d'avoir 10h de congés, n'en a eu que 2.25. Et deuxiemement supprimer la ligne qui correspond à "Holiday Worked" car plus besoin du temps travaillé qui a été absorbé.

Resultat :

J'éspère avoir été clair, si non, n'hésitez pas à me demander plus de précisions,

Une copie des fichiers sources Excel et du fichier PowerBI ci-joints.

Merci !

 

[ilyes4205]

Je marque la discussion comme résolue pour poster ailleurs

 

[alexga78]

Bonsoir @ilyes4205, le forum,

j'arrive un peu tard mais une proposition à transformer en fonction pour gérer les différents fichiers, à regrouper dans un même dossier.
très probablement perfectible...

let
    Source = Excel.Workbook(File.Contents(" ADD YOUR FILE TARGET"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Added Index" = Table.AddIndexColumn(Sheet, "Index", 1, 1, Int64.Type),
    Position = List.PositionOf(#"Added Index"[Column2], "Assignment Group"),
    Header = Table.RemoveColumns(Table.Combine(Table.AddColumn(Table.SelectRows(#"Added Index", each [Index] > Position and [Index] < Position + 5), "Header", each Table.FromRecords({_}))[Header]), "Index"),
    #"Listed ColName" = List.Transform (List.Transform(Table.ToColumns(Table.DemoteHeaders(Header)), List.RemoveNulls),  each if List.Count(_) = 2 then _ else  List.InsertRange(_, 1, {"Empty " & _{0}})  ),
    #"Promoted Header" = Table.RenameColumns(Table.SelectRows(#"Added Index", each [Index] > Position + 7), #"Listed ColName"),
    #"ColName to keep" = {"Employee Name", "Pay Code", "Work Date" , "Hours"},
    #"Selected Columns" = Table.SelectColumns(#"Promoted Header" , #"ColName to keep"),
    #"Filled Down" = Table.ReplaceValue(Table.SelectRows(Table.FillDown(#"Selected Columns",{"Employee Name"}), each [Pay Code] <> null and [Hours] <> null and [Hours] <> 0)," (cont.)","",Replacer.ReplaceText,{"Employee Name"}),
    #"Removed Meal" = Table.SelectRows(#"Filled Down", each ([Pay Code] <> "Meal")),
    #"Sum per Pay Code" = Table.Group(#"Removed Meal", {"Employee Name", "Work Date", "Pay Code"}, {{"Hours", each List.Sum(_[Hours])}}),
    #"Table w/o Holiday" = Table.SelectRows(#"Sum per Pay Code",  each not Text.StartsWith([Pay Code], "Holiday")),
    #"Table w/ Holiday" = Table.ExpandRecordColumn(Table.SelectColumns(Table.AddColumn(Table.Group(Table.SelectRows(#"Sum per Pay Code", each Text.StartsWith([Pay Code], "Holiday")), {"Employee Name", "Work Date"}, {{"All data", each Table.AddIndexColumn(_, "Index",0)}}), "Diff",
each let
Alldata = [All data],
PreRows = Table.AddColumn(Alldata, "Previous", each  try if Alldata[Hours] {[Index] - 1 } - [Hours] > 0 then Alldata[Hours] {[Index] - 1 } - [Hours]  else [Hours] otherwise [Hours] ),
Select = Table.AddColumn(PreRows, "Custom", each try if PreRows[PreRows]{1} = PreRows[Hours]{1}  then PreRows[Pay Code]{1} else PreRows[Pay Code]{0} otherwise [Pay Code] ),
FinalStep = Table.SelectColumns(Table.RenameColumns(Table.SelectColumns(Select, {"Employee Name", "Work Date", "Previous", "Custom"}), {{"Previous", "Hours"},{"Custom", "Pay Code"}}), Table.ColumnNames(#"Table w/o Holiday")),
Result = try FinalStep{1} otherwise FinalStep{0}
in Result),"Diff"), "Diff", Table.ColumnNames(#"Table w/o Holiday")),
    #"Combine Tables" = Table.TransformColumnTypes(Table.Sort(Table.Combine({#"Table w/o Holiday", #"Table w/ Holiday"}),{{"Employee Name", Order.Ascending}, {"Work Date", Order.Ascending}, {"Pay Code", Order.Ascending}}),{{"Work Date", type date}}),
    #"Added Week N°" = Table.AddColumn(#"Combine Tables", "Week N°", each Date.WeekOfYear([Work Date], Day.Monday))
in
    #"Added Week N°"

Bonne soirée

 

[ilyes4205]

Bonsoir merci. Alors je n'ai pas bien compris, c'est une solution pour mon probleme ou pour gérer les fichiers ?