Forum Discussion
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 in the PowerBI Desktop Query Editor.
Already Tried :
But get an error "There were too many elements in the enumeration to complete the operation." - Assume its due to the same value of URI.
Could someone please guide me here.
Input Data :
Details about the data:
- Each UniqueID has 2 rows
- For each UniqueID, the value of the columns ST,ET and Seconds are always the same for the 2 rows
- For each UniqueID, the URI column might be the same/can differ for the 2 rows
UniqueID | URI | ST | ET | Seconds |
UniqueId1 | http:\\aa.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 5 |
UniqueId1 | http:\\aa.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 5 |
UniqueId2 | http:\\ggg.svc | 31-12-2016 13:00:00 | 31-12-2016 13:05:00 | 8 |
UniqueId2 | http:\\123.svc | 31-12-2016 13:00:00 | 31-12-2016 13:05:00 | 8 |
UniqueId3 | http:\\bb.svc | 31-12-2016 01:00:00 | 31-12-2016 11:01:00 | 2 |
UniqueId3 | http:\\wert.svc | 31-12-2016 01:00:00 | 31-12-2016 11:01:00 | 2 |
UniqueId4 | http:\\dd.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 4 |
UniqueId4 | http:\\dd.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 4 |
Expected Output :
UniqueID | URI1 | URI2 | ST | ET | Seconds |
UniqueId1 | http:\\aa.svc | http:\\aa.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 5 |
UniqueId2 | http:\\ggg.svc | http:\\123.svc | 31-12-2016 13:00:00 | 31-12-2016 13:05:00 | 8 |
UniqueId3 | http:\\bb.svc | http:\\wert.svc | 31-12-2016 01:00:00 | 31-12-2016 11:01:00 | 2 |
UniqueId4 | http:\\dd.svc | http:\\dd.svc | 31-12-2016 12:00:00 | 31-12-2016 12:01:00 | 4 |
Thanks in Advance.
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"
4 Replies
- Ashish_Mathur
Super User
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"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:)
