Repeat rows of column and display available values in other columns

[bkazzaz]

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. 

[AlB · Accepted 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.

[bkazzaz]

Thank you so much @AlB . This was very quick solution. i really appreciate.