Unpivoting columns and then combing rows in PowerQuery

[hunterduck]

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!

[v-xjiin-msft · Accepted solution]

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

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.