Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@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
Your solution worked like a charm !
Thanks a lot (to both of you guys, DAX solution was interesting as well)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |