Power Query to Dynamically Remove Unwanted Header Rows

[legalhustler]

Hello PQ Gurus!

I need help in Power Query to dynamically remove unwanted row headers before I expand the 3 tables below and combine them (see below). The tables have the same columns once the unwanted rows above them are removed. I'm aware once this step is accomplished there will be duplicate headers from each of table somewhere below in each column, but I can easily filter them out in the next step. I'm guessing I need to add a custom column and somehow use some a function like Table.RemoveFirstN etc. To assist you, the unique first column header in each table should be "Company". So any data after the row "Company" is found in the first column the code should remove the rows above it for each table before I expand and combine the tables.

Data
Table1
Table2
Table3

 

[Matt Allington]

Are you sure you need to do this first? I would expand first, then edit the sample query, then come back and edit the combine query. More info here

Understanding Power Query Combine

Level: Intermediate Sometime late in 2016 Microsoft deployed a new “Combine” feature in both Power Query for Excel (Get and Transform) and also in Power BI Desktop (Get Data). The new capability makes it easier to combine multiple copies of similar workbooks into a single table without hand...

exceleratorbi.com.au

 

[legalhustler]

Are you sure you need to do this first? I would expand first, then edit the sample query, then come back and edit the combine query. More info here

Understanding Power Query Combine

Level: Intermediate Sometime late in 2016 Microsoft deployed a new “Combine” feature in both Power Query for Excel (Get and Transform) and also in Power BI Desktop (Get Data). The new capability makes it easier to combine multiple copies of similar workbooks into a single table without hand...

exceleratorbi.com.au

That may be a bit too complex for a simple transformation step for what I'm doing.

I added a custom column and used the code below before I expanded my data and it removed all the unnecessary headers dynamically, but it only shows the header ("Company") and data for column 1 from each table. I just need to know how to modify the code below so it shows all the columns and it should work when I expand & combine the tables.

=Table.FromList(List.RemoveFirstN(Table.Column([Data],"Column1"), each _ <>"Company"))

 

[GraH]

Could it be?

Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "Company")))

Without Excel, so sorry if I goof up some syntax.

 

[legalhustler]

Could it be?

Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "Company")))

Without Excel, so sorry if I goof up some syntax.

Bingo! That did it. Thank you very much!

 

[GraH]

Cool! Thanks for the feedback and mark.