Merge rows to 1 row based on UniqueID + Some Column Values can have duplicates

[New2BI]

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 :

http://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information/...

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:

1. Each UniqueID has 2 rows
2. For each UniqueID, the value of the columns ST,ET and Seconds are always the same for the 2 rows
3. 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.

[Ashish_Mathur · Accepted solution]

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"

[New2BI]

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

[New2BI]

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.