Split values from different rows of a same column into a single row

[Cado_one]

Hi,

my problem is simple but I don't find any good solution on internet.

Below is what I have :

column1.id	column2.id	Column3
2850	718012	Date1
2850	718012	Value1
2852	880592	Date2
2852	880592	Value2

And here is what I want :

Column1.id	Column2.id	Dates	Values
2850	718012	Date1	Value1
2852	880592	Date2	Value2

Any suggestion will be appreciated.

Thanks in advance,

Cado

[Fowmy · Accepted solution]

@Cado_one 

Paste below code in a blank query in the advanced editor.

The idea is summarize by the 1st two columns and sum the 3rd column then replace the List.Sum() with Text.Combine([Column3],",")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIwNVDSUTI3tDAwNAIyXBJLUg2VYnUwZcISc0oRUiARCwsDU0uYJiOsMmBNQKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1.id = _t, column2.id = _t, Column3 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"column1.id", "column2.id"}, {{"Count", each Text.Combine([Column3],","), type nullable text}})
in
    #"Grouped Rows"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Cado_one]

Hi @ziying35 @Fowmy 

Thanks you for your responses !

@ziying35 your code give me an error : The column 'Column1.id' of the table wasn't found.

@Fowmy your code give me an error : 4 keys were specified, but 3 values were provided.

In order to understand the first operation and to be able to do it again by myself, could you please show/tell me of to do the trick manually ?

Cado

[Fowmy]

@Cado_one 


Check this out: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Cado_one]

Exactly what I needed !

Thank you very much both of you for the help @Fowmy @ziying35 

[Fowmy]

@Cado_one 

You are welcome!

[ziying35]

Hi, @Cado_one 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WSs7PKc3NM9TLTFGyMrIwNdCBihiBRcwNLQwMjXSUnMFixkpWSi6JJamGSrU6pOsMS8wpxa7VCFWrhYWBqSWGpUZk6QRbCtQaCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    group = Table.Group(Source, {"column1.id", "column2.id"}, {"Foo", each Record.FromList([Column3],{"Dates", "Values"})  }),
    expd = Table.ExpandRecordColumn(group, "Foo", {"Dates", "Values"})
in
    expd

 If my code solves your problem, mark it as a solution