Forum Discussion
Manage duplicates with different values
- 8 years ago
I actually managed to perform the above using MarcelBeug proposed code on another topic! (See here)
The logic behind that is that I remove all duplicates based on "Service", "Fin réelle" and "Code Implantation" therefore I can only have 0, 1 or 2 Services TM04/TM4A/TM4E by "Code Implantation"/"Fin réelle"
Then I group by "Code Implantation"/"Fin réelle" and count the 3 different types of services and finally keep only the ones I want with a filter.
I guess I asked too quickly but maybe this can help someone else (or someone can give me a better/quicker solution to this one).
let Source = Excel.Workbook(File.Contents("C:\Work\Réorganisation STMC\PowerBI\Gestion doublons.xlsx"), null, true), Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code intervention", type text}, {"Code implantation", Int64.Type}, {"Service", type text}, {"Clients", type text}, {"Code postal", Int64.Type}, {"Ville", type text}, {"Catégorie (Matériel)", type text}, {"Statut", type text}, {"Raison de statut", type text}, {"Code canton", Int64.Type}, {"Echéance max.", type datetime}, {"Référence matériel", type text}, {"Ressources", type text}, {"Fin réelle", type date}, {"Durée réelle", Int64.Type}, {"Description", type text}, {"Date création intervention", type datetime}, {"Division", type text}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Service", "Code Implantation", "Fin réelle"}), #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Code Implantation", "Fin réelle"}, {{"New Column", each List.Count(List.Select([Service], each Text.StartsWith(_,"TM4") or Text.StartsWith(_,"TM04") )), Int64.Type}, {"AllData", each _, Value.Type(#"Removed Duplicates")}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Service"}), #"Filtered Rows" = Table.SelectRows(#"Expanded AllData", each [New Column] <> 2 or [Service] <> "TM04") in #"Filtered Rows"
I actually managed to perform the above using MarcelBeug proposed code on another topic! (See here)
The logic behind that is that I remove all duplicates based on "Service", "Fin réelle" and "Code Implantation" therefore I can only have 0, 1 or 2 Services TM04/TM4A/TM4E by "Code Implantation"/"Fin réelle"
Then I group by "Code Implantation"/"Fin réelle" and count the 3 different types of services and finally keep only the ones I want with a filter.
I guess I asked too quickly but maybe this can help someone else (or someone can give me a better/quicker solution to this one).
let
Source = Excel.Workbook(File.Contents("C:\Work\Réorganisation STMC\PowerBI\Gestion doublons.xlsx"), null, true),
Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code intervention", type text}, {"Code implantation", Int64.Type}, {"Service", type text}, {"Clients", type text}, {"Code postal", Int64.Type}, {"Ville", type text}, {"Catégorie (Matériel)", type text}, {"Statut", type text}, {"Raison de statut", type text}, {"Code canton", Int64.Type}, {"Echéance max.", type datetime}, {"Référence matériel", type text}, {"Ressources", type text}, {"Fin réelle", type date}, {"Durée réelle", Int64.Type}, {"Description", type text}, {"Date création intervention", type datetime}, {"Division", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Service", "Code Implantation", "Fin réelle"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Code Implantation", "Fin réelle"},
{{"New Column", each List.Count(List.Select([Service], each Text.StartsWith(_,"TM4") or Text.StartsWith(_,"TM04") )), Int64.Type},
{"AllData", each _, Value.Type(#"Removed Duplicates")}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Service"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded AllData", each [New Column] <> 2 or [Service] <> "TM04")
in
#"Filtered Rows"