Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |