Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Table transformation

I have a table that looks like this - 

 

IDFinancial Code
1123
2456
3789
3102
2223
1413

 

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

 

IDFinancial Code
1123, 413
2456, 223
3

789, 102

 

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

Need Help
Show and Tell

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    You can create a measure like below:-

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

     

    Output:-

     

    Thanks,

    Samarth

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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