Pivot data to be used in Power BI

[jeesan1234]

What is the best way to pivot the following data in excel or Power BI , so I can use it to creat visuals:

	January	February	March
	2nd - 4th	5th - 11th	12th - 18th	19th - 25th	26th - 1st	2nd - 8th	9th -15th	16th - 22nd	23rd - 1st	2nd - 8th	9th - 15th	16th - 22nd	23rd - 28th	29th - 2nd
Cargo	211	251	555	443	251	259	259	259	259	266	266	554	266	107
Energy	16	40	40	40	40	67	37	37	37	38	38	38	38	65
Casualty Treaty	179	448	448	448	448	432	432	432	432	429	429	429	429	172
Casualty Direct	85	212	212	212	212	215	215	215	215	36	36	36	36	15
Healthcare	49	121	111	121	545	134	134	134	134	322	138	138	138	55

As the months are split by different weeks. I need to sum the data for each month for few visuals in Power BI, and also show visuals with data split by weeks. I am not sure how to upload this data as there are essentially 2 different column headers when I get this data.

[audreygerred · Accepted solution]

Hi! In Power Query, I would suggest just using one of the headers to start out. For the purposes of this exercise, I will use the weeks as the header. 

Now, we need to unpivot the data. I clicked on Category, then went to the Transform ribbon, then selected the down arrow on Unpivot Columns and selected Unpivot other columns. Now, the data looks like this:

To get your month names back you can either add in a table with the week buckets in one column and the name in another, then merge that together with the above, or you can add in a conditional column and type out what you want:

Then, load your data, make your measures and build your visuals 🙂