Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
123is
New Member

Power Query from folder, headers in different rowd

Hello, 

 

I have 200 Excel Sheets in a folder which I have to extract data from. Most of the times my header is in row 74, but not always. Sometimes it can be in Row 77 for example. The headers are always the same name (for example: Category, Date and Amount). Can I somehow set the headers so that it searches for it in all the Excel Sheets and gives me all the data that is below my header? 

 

Thanks in advance. 

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

Hi @123is ,
I would create a sample table with the valid column names like so:

ImkeF_0-1674591229190.png

Then, you can dynamically fetch the correct data like so:

let
    Source = SourceTable,
    HeaderPosition = Table.PositionOf( SourceTable, ColumnHeaderTable{0}),
    Custom1 = Table.RemoveFirstN(Source, HeaderPosition),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1)
in
    #"Promoted Headers"

Please also check the file enclosed.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Community Champion
Community Champion

Hi @123is ,
I would create a sample table with the valid column names like so:

ImkeF_0-1674591229190.png

Then, you can dynamically fetch the correct data like so:

let
    Source = SourceTable,
    HeaderPosition = Table.PositionOf( SourceTable, ColumnHeaderTable{0}),
    Custom1 = Table.RemoveFirstN(Source, HeaderPosition),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1)
in
    #"Promoted Headers"

Please also check the file enclosed.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.