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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hunterduck
Regular Visitor

Unpivoting columns and then combing rows in PowerQuery

I have a set of raw data that I am trying to transform using PowerQuery.  I need to unpivot the columns then combine rows based on the column headers which are dates.  

 

Here is an example of the raw data I am recieving:

 

Customer Number1/1/20172/1/20173/1/20174/1/20171/1/20172/1/20173/1/20174/1/2017
1Company1Company1Company1Company20.500.50.5
2Company1Company1Company2Company20.20.20.50.5
3Company2Company2Company2Company10.20.20.20.2

 

I need PowerQuery to transform it into this:

 

CustomerDateCompanyValue
11/1/2017Company10.5
12/1/2017Company10
13/1/2017Company10.5
14/1/2017Company20.5
21/1/2017Company10.2
22/1/2017Company10.2
23/1/2017Company20.5
24/1/2017Company20.5
31/1/2017Company20.2
32/1/2017Company20.2
33/1/2017Company20.2
34/1/2017Company10.2

 

I am getting hung up with seperating the columns after unpivoting and then merging the rows based on the dates.  Thanks for the help!

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

@hunterduck

 

Based on your sample data, it seems like that one same column will have two different types of values. Right?

 

To unpivot this kind of data, I would suggest you to separate your source table into two. One table only contains unique columns. Then do the unpivot. After doing the unpivot, it is easy to merge the two queries into one.

 

Please refer following steps:

 

1. Separate your source table into two tables (Sourcetable1 and SourceTable2) and import them into Power BI Desktop. Go to Edit Queries => Choose Customer Number column and In Transform Menu there’s Unpivot Columns => choose Unpivot other columns. And you can change the columns name inside the expression.

1.PNG

 

2. Please be remember to Parse the Date column in Transform -> Date. Then create a Custom Column with Power Query expression: =Number.ToText([Customer Number])&"-"&Date.ToText([Date]) to create a unique valued column.

2.PNG

 

3. Same to SourceTable2.

3.PNG

 

4. Go back to SourceTable1, in Home menu, choose Merge Queries and make the two tables be merged based on the Primary Key column.

4.PNG

 

5. Expand the column you want. Then Close & Apply. The Result shows like this:

5.PNG

Thanks,
Xi Jin.

View solution in original post

1 REPLY 1
v-xjiin-msft
Solution Sage
Solution Sage

@hunterduck

 

Based on your sample data, it seems like that one same column will have two different types of values. Right?

 

To unpivot this kind of data, I would suggest you to separate your source table into two. One table only contains unique columns. Then do the unpivot. After doing the unpivot, it is easy to merge the two queries into one.

 

Please refer following steps:

 

1. Separate your source table into two tables (Sourcetable1 and SourceTable2) and import them into Power BI Desktop. Go to Edit Queries => Choose Customer Number column and In Transform Menu there’s Unpivot Columns => choose Unpivot other columns. And you can change the columns name inside the expression.

1.PNG

 

2. Please be remember to Parse the Date column in Transform -> Date. Then create a Custom Column with Power Query expression: =Number.ToText([Customer Number])&"-"&Date.ToText([Date]) to create a unique valued column.

2.PNG

 

3. Same to SourceTable2.

3.PNG

 

4. Go back to SourceTable1, in Home menu, choose Merge Queries and make the two tables be merged based on the Primary Key column.

4.PNG

 

5. Expand the column you want. Then Close & Apply. The Result shows like this:

5.PNG

Thanks,
Xi Jin.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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