Transpose PIVOT table with dynamic headings to a list

[L70F]

Hi,

I have searched for possible solution among other posts, but unfortunataly didn't find what can help my problem. 

We get price lists in pivot format where quantity values mentiones as the headings for different columns. These differ every time we get a new price list. For further analysis with these, I need to transform PIVOT matrix table into a list format. I tried to use the PQ function unpivot, but it makes the column headings to fixed values, which makes this not work when I use a new table as input.

Now I use a VBA macro for this purpose, but  I want to get a solution in Power Query. 

I really appreciate your help with PQ codes, thank you. 

Example input1:

Row Labels	1	2	3	4	5
53673701	76.65
870113580	8302.35	6108.9	5042.1	4281.9
870120980			1797.6	1493.1
870168680					971.25
870304080
870304801					273

Example output 1:

Row Labels	Qty	Price
53673701	1	76.65
870113580	1	8302.35
870113580	2	6108.9
870113580	3	5042.1
870113580	4	4281.9
870120980	3	1797.6
870120980	4	1493.1
870168680	5	971.25
870304801	5	273

Next time the input might be as exampe 2:

Row Labels	4	5	10	15	20	25	44	50	60	75
870306280							74.55
870315801	1697.85
99137427			376.95	330.75	303.45
99137455		486.15	379.05	343.35
99138432			199.5	153.3	130.2
99138441						58.8		44.1		39.9

Output example2:

Row Labels	Qty	Price
870306280	44	74.55
870315801	4	1697.85
99137427	10	376.95
99137427	15	330.75
99137427	20	303.45
99137455	5	486.15
99137455	10	379.05
99137455	15	343.35
99138432	10	199.5
99138432	15	153.3
99138432	20	130.2
99138441	25	58.8
99138441	50	44.1
99138441	75	39.9

[camargos88]

@L70F ,

You just need to use the Unpivot other columns:

Check the attached file.

[L70F]

Hi, thank you for your reply, but it doesn't work to simply do unpivot other columns. Because if I would enter my second example data instead of the first one in the same table the PQ will give the wrong message that headers do not match. My target is not to create a new PQ each time I need to update input data. Target is to have a PQ that I refresh after inserting new data to the table and it updates output table automatically. 

I hope it is more clear now. 

BR//Larissa

[camargos88]

@L70F ,

Have you tried switching the values.

There no reference to the headers beside "Row Labels", once you have "Row Labels" as column everything else will be unpivoted.

Try it switching the values on the 2 table of the pbix provided.

[L70F]

Hi,

Thank you for your reply. 

I don't work in Power BI PQ and don't know how to use your pbix file. 

Could you please share your codes so I can use it in  Excel Power Query. Sorry for additional trouble. 

BR//Larissa

[v-yingjl]

Hi @L70F ,

For example 1, here is the power query code in excel:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", Int64.Type}, {"1", type number}, {"2", type number}, {"3", type number}, {"4", type number}, {"5", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Row Labels"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Qty"}, {"Value", "Price"}})
in
    #"Renamed Columns"

 

example 2 is simliar with example 1.

Attached the sample excel file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi,

Please find the step below, I run the same in EXCEL POWER QUERY, same you can run in POWER BI POWER QUERY

1. Load data into power query

2. Make sure QTY in the header (as below image)

3. Select the first column

4. Go to UNPIVOT COLUMN > Unpivot Other Column

5. Done, change the Column Header as per your requirement

Now see the result

Also if you want to advance the crosstab matrix table to a simple data table, please find a tutorial for referance -myaccountingtricks.com/2020/06/convert-crosstab-matrix-excel-list-data-table-unpivot.html, may it will help you.

If you find this is the solution, please mark as the solution and give kudos

Thanks
Pijush

[L70F · Accepted solution]

Thank you Pijush for your link. I have watched it and solved my problem. 

BR//Larissa