cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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, ...) 

JAGD_U_0-1594134478444.png

JAGD_U_2-1594134760109.png

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

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?

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

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.

Highlighted
Frequent Visitor

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

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

Highlighted
Community Champion
Community Champion

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

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?

View solution in original post

Highlighted

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

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

https://marqueeinsights.com

Highlighted
Frequent Visitor

Re: Avoid many "Functions & Helper Queries" from a SharePoint folder file with many ta

Hi @lbendlin @trebgatte , 

 

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. 

JAGD_U_0-1594297248230.png

JAGD_U_1-1594297279511.png

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 😞

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.