Forum Discussion

mvrdj's avatar
mvrdj
Copper Contributor
May 27, 2022

transposing double column headers

 I am facing an issue regarding double column headers. I am using Excel sheets as my data source. I have the following data (sample) with different data types.

 

 

 I need to show the data in different visuals , filters on the basis of month and category but I failed to do so as there are 2 column 

headers. I have used un pivoting columns option in power query but nothing worked. I need the data in the following manner. This is manually scripted data but I cannot do this to millions of rows. 

 

Any help would be much appreciated.

Thanks in advance.

 

 

excel
power bi
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 27, 2022

    mvrdj The trick is that you need to first transpose the table (without headers). Then merge the first to columns and transpose the table back. Now promote the first row to headers and an un-pivot. Split the column that contains the merged headers, re-pivot and sort. Example attached.

     

Resources