Forum Discussion

Moonlight987's avatar
Moonlight987
Frequent Visitor
3 years ago
Solved

Transpose Table - duplicate rows in 1 column (label column), several columns with unique values

Hello Community, 

 

I am trying to find a solution for below situation  - there is a label column with duplicates values, then other columns with responses (amount of responses might differ).

(tried to use solution from the chain "Transpose Table - duplicate rows in 1 column (label column), several columns with unique values", but it was not sufficient in this case due to several response columns). 

 

 

Any advice or hint is much appreciated. Thank you. 

Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    3 years ago

    Hi Moonlight987 

    Next time please provide the sample data in text-tabular format instead of (or in addition to) screen caps so that data can be copied.

    Place the following M code in a blank query to see the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyklMSs0xVNJRSgTiJCBOVorVgQobgYTB4mAJJBljsAxECiKHJAk2DUQkgQh048ASYJnkZEMME8E6QJpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [label = _t, Response = _t, Response1 = _t, Response2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"label", type text}, {"Response", type text}, {"Response1", type text}, {"Response2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"label"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"label"}, {{"Result", each [Value], type table [label=nullable text, Value=text]}}),
    res_ = Table.FromColumns(#"Grouped Rows"[Result], #"Grouped Rows"[label])
in
    res_

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    Hi Moonlight987 

    Next time please provide the sample data in text-tabular format instead of (or in addition to) screen caps so that data can be copied.

    Place the following M code in a blank query to see the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyklMSs0xVNJRSgTiJCBOVorVgQobgYTB4mAJJBljsAxECiKHJAk2DUQkgQh048ASYJnkZEMME8E6QJpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [label = _t, Response = _t, Response1 = _t, Response2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"label", type text}, {"Response", type text}, {"Response1", type text}, {"Response2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"label"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"label"}, {{"Result", each [Value], type table [label=nullable text, Value=text]}}),
    res_ = Table.FromColumns(#"Grouped Rows"[Result], #"Grouped Rows"[label])
in
    res_

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.