Forum Discussion
Avoid many "Functions & Helper Queries" from a SharePoint folder file with many tables in it - Desk
This part here
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
is the only weak spot. It basically looks at the imported tab again (loading it a second time) to figure out what the column names are. If you know that your excel sheets always have fixed column names then you can use list variables instead for that (and get rid of all the Sample files).
Do your excel sheets have the same structure?
Super UserThis part here
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
is the only weak spot. It basically looks at the imported tab again (loading it a second time) to figure out what the column names are. If you know that your excel sheets always have fixed column names then you can use list variables instead for that (and get rid of all the Sample files).
Do your excel sheets have the same structure?
Most Valuable ProfessionalThe Functions and Helper queries are added automatically by Power BI.
I have a video on working with multiple files in a SharePoint folder that may help so that you see what the happy path looks like. These can be found at https://getstartedwithpowerbi.com and scroll to the bottom.
Hope this helps,
--Treb, MVP