Table transformation

[Anonymous]

I have a table that looks like this - 

ID	Financial Code
1	123
2	456
3	789
3	102
2	223
1	413

And, I'd like to transform it into the following table as it enables me to have a single record for each ID. 

ID	Financial Code
1	123, 413
2	456, 223
3	789, 102

Please can someone help me understand how I go about this? 

[v-shex-msft]

HI @Anonymous,

You can also do these operations on the query editor side to transfer your table structure: (Table.Group nested with Text.Combine function)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MlaK1YlWMgKyTUzNwGxjINvcwhLONjQwgqsxgqoH6TUxBLJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Financial Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Financial Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Merge", each Text.Combine(List.Transform(_[Financial Code], each Text.From(_)),","), type text }})
in
    #"Grouped Rows"

Regards,

Xiaoxin Sheng

[Samarth_18]

Hi @Anonymous ,

You can create a measure like below:-

Measure = 
    CONCATENATEX ( VALUES ('Table'[Financial Code] ), [Financial Code], "," )

Output:-

Thanks,

Samarth