Remove Rows & Use First Row as Headers for Specific Columns

[TiZi08]

Hi.

I want to Remove Rows & then Use First Row as Headers for specific columns. Specifically, I want to apply this to all except two columns.

I am working with messy data; at least the data is consistently messy so I know the data I am reading will always be weirdly structured like this.

For e.g. below, Col 1 and Col 6 I want to leave as-is but I want to remove the first 6 rows & then apply the Use First Row as Headers to columns 2 to 5. Preferably, I would not want to manually specify each column to apply the actions to (as the data can have up to 150+ columns) & instead be able to specify the columns I don’t want the actions applied to.

Col 1	Col 2	Col 3	Col 4	Col 5	Col 6
Do NOT apply remove rows & use first row as header	APPLY remove rows & use first row as header	APPLY remove rows & use first row as header	APPLY remove rows & use first row as header	APPLY remove rows & use first row as header	Do NOT apply remove rows & use first row as header

[wdx223_Daniel]

NewStep=let ColumnNamesYouWantKeep={"Col 1","Col6"},Rows=Table.ToRows(PreviousStepName) in #table(List.Transform(List.Zip({Table.ColumnNames(PreviousStepName),Rows{5},List.Positions(Rows{0})}),each if List.Contains(ColumnNamesYouWantKeep,_{0}) then _{0} else Text.From(_{1}??Number.ToText(_{2}+1,"Column_0")),List.Skip(Rows,6))