Forum Discussion
Avoid many "Functions & Helper Queries" from a SharePoint folder file with many tables in it - Desk
Hi, how can I avoid many "Functions & Helper Queries" when I upload many tables from just 1 file in a SharePoint folder - using PowerBI Desktop?
I change the data source of my report from a local location to a SharePoint online location.
I used to have 14 queries (one for each of my tables in the file). However, to do not lose the work made, I made a query with the instructions to upload the las file, copy the instructions of the advance editor to my existing relation query and I duplicate these process for the other 14 queries (duplicating the 1st one that I made). Then after all the hard work, the queries increased to 70 queries (most of them Helper Queries, transform sample files, parameters, ...)
I have a function set of queries for every table that I want to extract (store in power bi desktop).
The refreshes are taking so long now.
thank you for your time and help in this matter.
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?
5 Replies
- lbendlin
Super User
Have 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?
