Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
The title may not explain what exactly i'm looking for. myself can't even get to understand how to describe it in my head. so i thought the best way to ask for help is to display the input and the result i'm trying to achieve in excel.
here's what i currently have:
and here's what i'm trying to achieve:
I have over 30 thousands products, with over 50 attributes that are set as headers, and values that are set as values. so i can't basically do anything manually.
Thank you in advance.
Solved! Go to Solution.
Hi @bkazzaz
Simply select the Product column, use "Unpivot other columns" and then filter out the blanks. Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyyilNBVJA5JuYA2UpxeoAZY0gnAhdn8SidKiMCVzWGMgKSk2BagAj0wpTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Color = _t, Size = _t, Gender = _t, Number = _t, Dimension = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Color", type text}, {"Size", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Dimension", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> ""))
in
#"Filtered Rows"
|
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 @bkazzaz
Simply select the Product column, use "Unpivot other columns" and then filter out the blanks. Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyyilNBVJA5JuYA2UpxeoAZY0gnAhdn8SidKiMCVzWGMgKSk2BagAj0wpTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Color = _t, Size = _t, Gender = _t, Number = _t, Dimension = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Color", type text}, {"Size", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Dimension", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> ""))
in
#"Filtered Rows"
|
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. |