Transpose table based on criteria

[Anonymous]

Hi, I have a table like this:

and need to transpose and get a table like this:

is that possible?
Thanks in advance!!!

[v-yanjiang-msft · Accepted solution]

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.

[Anonymous]

@v-yanjiang-msft  thanks a lot!!!!

[ImkeF]

Hi @Anonymous ,
as I don't have access to your data sources, the file in the link you've provided doesn't work for me.
Also: What does the error-message say?
Please provide sample data in a usable form.

[ImkeF]

Yes, that's what I expected.
Please post some sample data here so I can check the code:
How to provide sample data in the Power BI Forum - Microsoft Power BI Community

[Anonymous]

@ImkeF  I think I did it with dynamic column, but I can´t do the same with "172" table.  table 85  is the one of the post example.  https://drive.google.com/file/d/1ze0w5JXhiaNca8yd7YQwEV5qxgV_1x5o/view?usp=share_link
I need to transpose table 172, but I m getting error.

[ImkeF]

Hi @Anonymous ,
check the last column -> Transform: Unpivot -> select 2nd column as Value column -> Advanced Options: Don't aggregate

[Anonymous]

@ImkeF  thanks for the answer but I m not getting the output expected...I need 4 columns as result, and the 3 levels of last column as 3 columns...