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?
thank you both very much for your suggestions.
I review my code and eliminate each duplicate sample file that was created by default in Power BI.
There are still several transform files but it was already an improvement, by calling the same parameter and sample file for everyone.
My Excel sheets do not have the same structure, nor my tables. They have different information, columns, etc.
Unfortunately, the query refresh is still delaying and I cannot take out the "Allow data preview in the background" because otherwise, my queries do not work 😞