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 UserHave a look at the M code produced by the helper queries. There's no magic behind that, and you can replace them all with a single function.
In Power Query load your raw table once. Then let the other queries point to the first query. Also make sure that "parallel loading of data sources" is disabled.
Hi lbendlin, thank you for your answer.
The problem is that for the report I am not using just a single raw table. The excel file has several tables that I use to create my graphs and that I needed to import one by one. Thus, I can't just let the other queries point to the first query because they are different tables that I need to preview for this file.
Nevertheless, how can I replace the helper queries with a single function? taking into account what I mentioned before.
The code of one of my queries is the following:
Source = SharePoint.Files("https://uniper.sharepoint.com/sites/OGrp_BTM", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = "https://uniper.sharepoint.com/sites/OGrp_BTM/Shared Documents/Performance Board/PowerBI_BTM_PerformanceBoard/" or Text.Contains([Folder Path], "PowerBI_BTM_PerformanceBoard")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each [Extension] = ".xlsm"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date created", Order.Descending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each not Text.StartsWith([Name], "~$")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows1",1),
#"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"DATE", type date}, {"WEEKNUM", Int64.Type}, {"Very Happy", Int64.Type}, {"Happy", Int64.Type}, {"Slightly Happy", Int64.Type}, {"Slightly Unhappy", Int64.Type}, {"Unhappy", Int64.Type}, {"Very Unhappy", Int64.Type}, {"Average", type number}, {"Target", type number}, {"Deviation from Target", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
The other queries are the same (similar), they just changed because they called a different "Transform File" (e.g. Transform File, Transform File (1), Transform File (2), Transform File (3) ...) from the query helpers.
Probably my problem is too specific 😞
Again, thank you
- lbendlin
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?
- trebgatte
Most Valuable Professional
The 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
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 😞
