Forum Discussion
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). (tri...
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.
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. |
