The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
Hi @123is ,
I would create a sample table with the valid column names like so:
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
Hi @123is ,
I would create a sample table with the valid column names like so:
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