Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 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:

1.PNG 

 

Then filter the blank values in Value column and add an index column:

2.PNG 

 

In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:

3.PNG 

 

Finally, merge table1 and table2 based on index column.

4.PNG 

 

You may refer to the appendix.

 

Regards,

Jimmy Tao

View solution in original post

5 REPLIES 5
themistoklis
Community Champion
Community Champion

@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
Not applicable

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:

1.PNG 

 

Then filter the blank values in Value column and add an index column:

2.PNG 

 

In table2, click on columns [Amount1], [Amount2] and [Amount3] then click Unpivot, remove all the useless columns and add an index column:

3.PNG 

 

Finally, merge table1 and table2 based on index column.

4.PNG 

 

You may refer to the appendix.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

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
Not applicable

Your solution worked like a charm !

 

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.