Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 Number | 1/1/2017 | 2/1/2017 | 3/1/2017 | 4/1/2017 | 1/1/2017 | 2/1/2017 | 3/1/2017 | 4/1/2017 |
1 | Company1 | Company1 | Company1 | Company2 | 0.5 | 0 | 0.5 | 0.5 |
2 | Company1 | Company1 | Company2 | Company2 | 0.2 | 0.2 | 0.5 | 0.5 |
3 | Company2 | Company2 | Company2 | Company1 | 0.2 | 0.2 | 0.2 | 0.2 |
I need PowerQuery to transform it into this:
Customer | Date | Company | Value |
1 | 1/1/2017 | Company1 | 0.5 |
1 | 2/1/2017 | Company1 | 0 |
1 | 3/1/2017 | Company1 | 0.5 |
1 | 4/1/2017 | Company2 | 0.5 |
2 | 1/1/2017 | Company1 | 0.2 |
2 | 2/1/2017 | Company1 | 0.2 |
2 | 3/1/2017 | Company2 | 0.5 |
2 | 4/1/2017 | Company2 | 0.5 |
3 | 1/1/2017 | Company2 | 0.2 |
3 | 2/1/2017 | Company2 | 0.2 |
3 | 3/1/2017 | Company2 | 0.2 |
3 | 4/1/2017 | Company1 | 0.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!
Solved! Go to Solution.
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.
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.
3. Same to SourceTable2.
4. Go back to SourceTable1, in Home menu, choose Merge Queries and make the two tables be merged based on the Primary Key column.
5. Expand the column you want. Then Close & Apply. The Result shows like this:
Thanks,
Xi Jin.
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.
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.
3. Same to SourceTable2.
4. Go back to SourceTable1, in Home menu, choose Merge Queries and make the two tables be merged based on the Primary Key column.
5. Expand the column you want. Then Close & Apply. The Result shows like this:
Thanks,
Xi Jin.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
38 | |
31 | |
26 |