Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aviral
Advocate III
Advocate III

In PBI Desktop Query editor, every applied step fetches the whole table again

I have some data stored in a SharePoint list (~10k records) which is fetched in the PBI Query Editor.

 

The fetched data is then transformed with several applied steps as shown below.

 

 

 

 

The problem is that at every applied step, the whole table is fetched from SharePoint online. Is there a way to prevent that from happening as it is taking a very long time to complete the fetching of data and really slowing down progress on transformation of this dataset. 

 

 

The data is historical and static and does not need to be loaded repeatedly. Is there a way in which this time-consuming reloading of data can be avoided? 

 

The only two options I see are "Enable Load" which makes the table available in the report view and "Include in report refresh" which causes the table to refresh when the dataset is refreshed by clicking "Refresh" in the report view. 

 

1 ACCEPTED SOLUTION

Haven't tried it myself, but maybe Table.Buffer helps here. Just "wrap" it around your table-expression or add a new step like this:

 

Buffer = Table.Buffer(<LastStepname>)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

Can you post the applied steps? How do you know the entire table is being fetched each time? That doesn't seem right.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for responding @Greg_Deckler for responding. I am pasting the part of the query from where it starts taking time.

 

 #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows", "Index.0", 1, 1),
    #"Added Index" = Table.AddIndexColumn(#"Added Index0", "Index.-1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index.0"},#"Added Index",{"Index.-1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous1" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Title", "RAG Status", "Report_Date"}, {"Previous.Title", "Previous.RAG Status", "Previous.Report_Date"}),
    #"Added Custom6" = Table.AddColumn(#"Expanded Previous1", "Retention", each if [Title]=[Previous.Title] and Date.From([Report_Date])=Date.From([Previous.Report_Date]) and Time.From([Report_Date]) < Time.From([Previous.Report_Date])then "Remove" else "Keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom6", each ([Retention] = "Keep")),
    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1)
in
    #"Added Index1"

 

The moment I hit the index steps, it starts showing the the progress message at the bottom right corner as shared in the screenshot in the original post. XXX MB from XXXX.SHAREPOINT.COM(CLICK HERE TO CANCEL) and the MB goes a few times the original size of the table. The table size is about 35 MB based on the highest counter shown by by that message at the bottom right in earlier steps. 

 

 

It looks like you are perhaps joining the table back with itself? Is that correct? In that case, I would expect it to retrieve the list twice.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes that is correct. I am joining the table with itself because I need to compare previous and next values. 

Haven't tried it myself, but maybe Table.Buffer helps here. Just "wrap" it around your table-expression or add a new step like this:

 

Buffer = Table.Buffer(<LastStepname>)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

 

This is how the query looks now.

 

I added Table.Buffer to the Navigaton step where it fetches the items from the selected list. It worked fine till #"Merged Queries" and then it slowed down again @ #"Expanded Previous1" so have wrapped #"Merged Queries" in Table.Buffer too.

 

 

let
    Source = SharePoint.Tables("https://mycompany.sharepoint.com/teams/mySiteCollection", [ApiVersion = 15]),
    #"e510bef6-masked-1bdd1c5b4c78" = Table.Buffer(Source{[Id="e510bef6-masked-1bdd1c5b4c78"]}[Items]),
    #"Renamed Columns" = Table.RenameColumns(#"e510bef6-masked-1bdd1c5b4c78",{{"ID", "ID.1"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FileSystemObjectType", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "Activities", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesForEdit", "File", "Folder", "ParentList", "Properties", "Versions", "Author", "Editor", "Change"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "ProgName", each if Text.Contains([Title], ==MASKED== else [Title] ),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column", "Report_Date", "Report_Date - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Report_Date - Copy", type text}}, "en-US"), "Report_Date - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Report_Date - Copy.1", "Report_Date - Copy.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Report_Date - Copy.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Report_Date - Copy.1.1", "Report_Date - Copy.1.2", "Report_Date - Copy.1.3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Report_Date - Copy.1.1", "Month"}, {"Report_Date - Copy.1.2", "Date"}, {"Report_Date - Copy.1.3", "Year"}, {"Report_Date - Copy.2", "Time"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Month-MM", each if Text.Length([Month])<2 then "0"&[Month] else [Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date-DD", each if Text.Length([Date])<2 then "0"&[Date] else [Date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Time", type time}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type", "Time-24hr", each let
hr=Text.From(Time.Hour([Time])),
mi=Text.From(Time.Minute([Time])),
se=Text.From(Time.Second([Time])),
result = Text.Combine({
if Text.Length(hr)<2 then "0"&hr else hr,
if Text.Length(mi)<2 then "0"&mi else mi,
if Text.Length(se)<2 then "0"&se else se},"")
in
result),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "SortOrder", each Text.Combine({
[Year],
[#"Month-MM"],
[#"Date-DD"],
[#"Time-24hr"]
},"")),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Check", each Text.Length([SortOrder])),
    #"Added Custom" = Table.AddColumn(#"Added Custom5", "customKey", each Text.Combine({[ProgName],[SortOrder],[RAG Status]},"~")),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"customKey"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"customKey", Order.Ascending}}),
    #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows", "Index.0", 1, 1),
    #"Added Index" = Table.AddIndexColumn(#"Added Index0", "Index.-1", 0, 1),
    #"Merged Queries" = Table.Buffer(Table.NestedJoin(#"Added Index",{"Index.0"},#"Added Index",{"Index.-1"},"Previous",JoinKind.LeftOuter)),
    #"Expanded Previous1" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"ProgName", "RAG Status", "Report_Date"}, {"Previous.ProgName", "Previous.RAG Status", "Previous.Report_Date"}),
    #"Added Custom6" = Table.AddColumn(#"Expanded Previous1", "Retention", each if [ProgName]=[Previous.ProgName] and Date.From([Report_Date])=Date.From([Previous.Report_Date]) and Time.From([Report_Date]) < Time.From([Previous.Report_Date])then "Remove" else "Keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom6", each ([Retention] = "Keep"))
in
    #"Filtered Rows"

  

So has the overall performance improved now?

 

Normally Power BI is supposed to be better than Excel in caching, but unfortunately it happens very often that the same calls are made multiple times. It is still much too much trial and error to find improvements here unfortunately.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Yes. The overall performance has definitely improved now.

 

I don't see the toast message at the right bottom corner which indicates that data is being fetched from SharePoint.

 

It seems however, that on performing a merge query, the result needs to be buffered again otherwise it starts fetching data from SharePoint again.

 

Thank you so much for this insight. Much appreciated.

Looking up this article to figure out the usage

 

https://social.technet.microsoft.com/Forums/en-US/12c51d1e-c236-4ff6-9c49-3c9b70a59f25/use-of-tableb...

 

In case you find any other relevant referece, please share.

 

Will keep this thread updated if something works out.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.