Source File has Some Different Column Names On Refresh

Power Query Data Setup
1

Hi
I have a Named Source range on a Excel Sheet.
The Names of the first 4 cols do not change.
I have used a range for input as I don’t want the Data on the Sheet converted into a table [my users don’t work with tables yet].
The number and names of cols from col 5 onwards do change. There will always be at least one column after Col 4.
All cols from col 5 onwards need the same transformations applied to them.
I have been trying to write a Query that in respect to col5 onwards works with an unknown number of columns and also with unknown names for those columns.
I have been trying to use Table.ColumnNames but with no sucess.
Changing Columns.xlsx (19.5 KB)

Can anyone help?

Thank You

2

Hi @AllisterB,

Give this custom function a go.

(t as table) as table =>
let
    Source = t,
    PromoteHeader = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ReplaceNull =  Table.ReplaceValue(PromoteHeader,null,0,Replacer.ReplaceValue, List.Skip( Table.ColumnNames(PromoteHeader), 4) ),
    UnpivotOthers = Table.UnpivotOtherColumns(ReplaceNull, List.FirstN( Table.ColumnNames(PromoteHeader), 4), "Attribute", "Value")
in
    UnpivotOthers

Note 1.
your first query had a “changed type” step with hard coded col names, if you require that move it all the way to the end by that I mean after the unpivot step.

Note 2.
your second named range has no values beyond col 4 this means the Unpivot will remove these nulls and no data is returned. I’ve included a ReplaceNull step. You’ll have to decide if you require that with your actual data and this was only caused by the sample provided…

I hope this is helpful

1 Like
3

Hello @AllisterB

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

@Melissa Thank You for your suggestion. I think this will work for me :slight_smile: