Forum Discussion
Solved
Merge rows to 1 row based on UniqueID + Some Column Values can have duplicates
Question : I am trying to merge two rows in to 1 based on a UniqueID. Note that the URI Column is the differing one but not always.Sometimes it can be the same/different. I am trying to achieve this ...
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"URI", type text}, {"ST", type datetime}, {"ET", type datetime}, {"Seconds", Int64.Type}}), Partition = Table.Group(#"Changed Type", {"UniqueID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"URI", "ST", "ET", "Seconds", "Index"}, {"URI", "ST", "ET", "Seconds", "Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each "URI"&[Index]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "URI"), #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"ST", type datetime}, {"ET", type datetime}}) in #"Changed Type2"
Ashish_Mathur
Super User
Super UserHi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"URI", type text}, {"ST", type datetime}, {"ET", type datetime}, {"Seconds", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"UniqueID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"URI", "ST", "ET", "Seconds", "Index"}, {"URI", "ST", "ET", "Seconds", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each "URI"&[Index]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "URI"),
#"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"ST", type datetime}, {"ET", type datetime}})
in
#"Changed Type2"Hey Ashish,
Thanks for the reply, I am going to try this and respond.
But is it possible to achieve this with the QueryEditor and performing some powerBI in-built actions? Thanks.EditQueries
Ashish, the M Query worked perfectly.Also, I understood that the PowerBI designer is using M query behind the scenes.
For anyone new , to edit or view the M Query of the PowerBI: "EditQueries>>View>>AdvancedEditor"
Thank you for the help:)
- Ashish_Mathur
You are welcome.
