Forum Discussion
Remove Rows & Use First Row as Headers for Specific Columns
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 |
1 Reply
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))
