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

[Moonlight987]

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. 

[AlB · Accepted solution]

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.

[Moonlight987]

Hi @AlB,

thanks a lot for prompt response, case solved✌️