Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.