Forum Discussion
Power Query crazy format
Hi,
Human kind will always surprise me…
Here is the data input i got :
Client AmountType1 Amount1 AmountType2 Amount2 AmountType3 Amount3
e1 Bike 100 Car 200 Boat 30
e2 Car 50 Boat 2
e3 Bike 80 Boat 4
e4 Boat 40
(!!!!)
And what i want (quite obvious i know) :
Client AmountType Amount
e1 Bike 100
e1 Car 200
e1 Boat 30
e2 Car 50
e2 Boat 2
e3 Bike 80
e3 Boat 4
e4 Boat 40
How would you do that ?
In real life, i have got many different types (not only 3) and so, a LOT of columns in the input file.
About me :
I played a bit (for months) with Power Query but only used basic features.
Played once with functions, and i’m not a ‘M’ozart.
THANK YOU !!!
Steph
Anonymous,
In power query, you may refer to steps below(Unfortunately simply "Unpivot then pivot" can't achieve the result you want):
Firstly duplicate the original table, then you have two tables 'table1' and 'table2', in table1, click on columns [AmountType1], [AmountType2] and [AmountType3] then click Unpivot, remove all the useless columns like below:
Then filter the blank values in Value column and add an index column:
In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:
Finally, merge table1 and table2 based on index column.
You may refer to the appendix.
Regards,
Jimmy Tao
5 Replies
Anonymous
You can create a New Table using DAX formula, in case you dont want to use Power Query:
New Table = UNION(SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 1], "Amount", 'Table'[Amount 1]), SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 2], "Amount", 'Table'[Amount 2]), SELECTCOLUMNS('Table', "Client", 'Table'[Client], "Type", 'Table'[Type 3], "Amount", 'Table'[Amount 3]) )Workspace on this link.
Hi themistoklis,
thanks for the reply, i'll try this way.
I'd prefer to do it in MQuery though (if possible).
Thanks again
Anonymous,
In power query, you may refer to steps below(Unfortunately simply "Unpivot then pivot" can't achieve the result you want):
Firstly duplicate the original table, then you have two tables 'table1' and 'table2', in table1, click on columns [AmountType1], [AmountType2] and [AmountType3] then click Unpivot, remove all the useless columns like below:
Then filter the blank values in Value column and add an index column:
In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:
Finally, merge table1 and table2 based on index column.
You may refer to the appendix.
Regards,
Jimmy Tao
