Two heading rows - take whichever is not blank

[gazpage]

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?

 

[Matt Allington]

Put a filter on the column to filter out blanks. Then promote the first row - the one that this left.

 

[gazpage]

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.

 

[Matt Allington]

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

 

[gazpage]

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.