Power Query crazy format

[Anonymous]

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

[themistoklis]

@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.

[Anonymous]

Hi @themistoklis,

thanks for the reply, i'll try this way.

I'd prefer to do it in MQuery though (if possible).

Thanks again

[v-yuta-msft · Accepted 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]

Hi,

It seems to do the trick ! Can't wait to be at work to test (right now, I'm in my bed ^^). Thank you !!

[Anonymous]

Your solution worked like a charm !

Thanks a lot (to both of you guys, DAX solution was interesting as well)