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?
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?