How to make PBI use an Excel with merged column headers

[Kilm]

Hi folks,

I've spent four hours trying various solutions and guides but just cannot figure this out.

I have an Excel which has four column headers, let's say they are One, Two, Three and Four. These are merged over two columns each.

Beneath each merged column header is the two columns, each with their own header.

So:

[One]

First | Second

And so on

When I load this in and try to put it in a bar chart, it doesn't work and PBI has difficulty reading it. I've tried unpivoting and merging in query but I just can't figure it out

I want PBI to show me the four merged headers and then sub headers so I can put them in a bar chart.

Any advice is greatly appreciated.

[jgeddes]

If I understand correctly you have data somewhat like...

And you want something like...

Assuming the merged first row loads in with null in every other column (as below) you could do the following...

Transpose the whole table...
Fill down the first column to remove the null values...

 

merge the first two columns together using a delimiter that does not exist in either column...

transpose the table back and promote the first row as headers...

unpivot the whole table and then split the first column by the delimiter you chose in the merge step...

[Kilm0]

Thank you for your help and time with this. I've managed to get on via the desktop so I can share a screenshot of the example dataset.

I apologise for explaining it poorly!

I've tried your method as outlined, but with the query looking like this:

when I transpose the whole table it looks like this:

and if I do the fill down, then it replaces the nulls with numbers, which I guess would skew the data?

I follow the rest of your steps but it all goes off the rails a bit and ends up nothing like yours.

Again I apologise for the poor description at the start and the time you're giving to this!

[jgeddes]

No worries. Here is a modified solution for you to try...
Load in your Excel file but make sure the headers are not promoted so you end up with something like...

Transpose the whole table... 

Fill down the first column and merge it with the second column. (Again, use a delimiter that is not in your string values.) Then promote the headers as first row.

Select the first column and then choose 'Unpivot Other Columns'...

Split the first column by delimiter, rename the columns to what you want and set the correct data types and you should be good to go...

[Kilm0]

Thank you again, I'm very conscious of the fact you've turned into my one-to-one PBI mentor on this!!

So I start with my data, and I go to 'User Headers as First Row' to ensure they're not promoted. I end up with this:

I transpose to this:

However I saw that you have nulls and I don't, but I did some further reading and figured that out, so I replaced the values to null to mirror you:

I then fill down successfully (as it wasn't before, and I read that it was because of the null thing) and have this:

Then I promote first row to headers and get:

and from there on everything you've said works fine.

I've got the data into PBI on a bar chart now, but I can't seem to get it looking like yours, where it has the merged header then the two columns for pass and fail showing. It either shows one or the other depending on what's on top on the Axis field.

Thank you so much again for your time with this. I'm sure there's an obvious answer staring me in the face with this!

[jgeddes]

Glad you were able to get the results you needed. Power Query is incredibly useful in transforming 'lemon' spreadsheets into 'lemonade'. 
For your chart, I set up the fields as follows...

which gets...

click on the 'Expand all down one level...' icon to get...

Cheers.