Two heading rows - take whichever is not blank

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
My data source has two header rows, for which only one is ever populated. I want to take whichever is non-blank as my header.

My instant reaction was to transpose in Power Query, add a custom column that picks whichever is non-blank, and then retranspose. However, the transpose step took ages and I think attempting to add a custom column freezes my Power BI / Excel (still waiting maybe it will appear).

I guess it is because there are 100k rows which then become 100k columns, and it doesn't like adding the 100k+1th column? Any suggestions?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sigh. With the benefit of hindsight I can see my question was worded terribly.

The header rows are one for English and one for Spanish and it varies column by column which is populated. I feel like I need a 'conditional row', but that doesn't exist. I could transpose and then create the conditional column but that makes my excel hang.
 
Upvote 0
why don't you just promote the first row and manually change the ones that are wrong. I assume the columns are always the same
 
Upvote 0
There are a hundred or so columns so I was hoping there was a quicker way to do this.

What I ended up doing was splitting the header rows from the table, transposing (this seemed to work ok, presumably because there is so much less data), creating a conditional column that had the desired headers, deleting the original headers, retransposing and then appending back to the data.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top