Forum Discussion

Cado_one's avatar
Cado_one
Resolver III
6 years ago
Solved

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

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...
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    6 years ago

    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

ziying35's avatar
ziying35
Impactful Individual
6 years ago

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