Transpose table based on criteria

Hi Anonymous ,

According to your description, I download your sample, but I can't do M transformation because can't connet to the data source. Based on that, I create a demo.

Here's my solution.

1.Group rows based on PRODUCTNUMBER, and transform the list to record.

= Table.Group(Source, {"PRODUCTNUMBER"}, {{"New", each Record.FromList(_[ATTRIBUTETYPENAME],{"Dexter-ecommerce","Jerarquia principal","Jerarquia secundaria marcas"})}})

Result:

2.Expand the new column. Get the result:

Here's the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRx9DK1NDBR0gEyPV0cg5VidVBEfV1dPB2DFTz8fZ2CXLFLIouaYjXIFJ9BphgGBRqaGxgCRf29/ENwiSqE+nkGu0agS8LsjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODUCTNUMBER = _t, ATTRIBUTETYPENAME = _t]),
    #"Grouped Rows" = Table.Group(Source, {"PRODUCTNUMBER"}, {{"New", each Record.FromList(_[ATTRIBUTETYPENAME],{"Dexter-ecommerce","Jerarquia principal","Jerarquia secundaria marcas"})}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "New", {"Dexter-ecommerce", "Jerarquia principal", "Jerarquia secundaria marcas"}, {"Count.Dexter-ecommerce", "Count.Jerarquia principal", "Count.Jerarquia secundaria marcas"})
in
    #"Expanded Count"

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.