Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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).
Any advice or hint is much appreciated. Thank you.
Solved! Go to Solution.
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. |
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. |