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

Buffer a table to improve performance

Hi, 

I read a few articles about how to buffer a table, but i am not sure of what i am doing, neither if it really helps. Here is what i have done so far, including the buffer query. Could you let me know if the buffers are doing anything good or wrong ? If wrong, how should i use the Buffer query to improve the performance of my query ? It takes 20min to refresh given it sucks all the "Iwi" lists (all the same) from a lots of sharepoint sites from a Project Online app.

Thanks

JL

 

let
Source = OData.Feed("https://xxxxxxx.sharepoint.com/sites/pwasandbox/_api/ProjectData"),
Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
#"Selected Columns" = Table.SelectColumns(Projects_table,{"ProjectId", "ProjectName", "ProjectWorkspaceInternalUrl"}),
#"TableBuffer1" = Table.Buffer(#"Selected Columns"),
#"AllListData"=Table.AddColumn(#"TableBuffer1","Iwi",each GetList([ProjectWorkspaceInternalUrl],"Iwi")),
#"RemovedErrors"=Table.RemoveRowsWithErrors(#"AllListData"),
#"TableBuffer2"=Table.Buffer(#"RemovedErrors"),
#"Expanded Iwi" = Table.ExpandTableColumn(#"TableBuffer2", "Iwi", {"IwiGroupName", "InterestValue", "DateOfAnswer", "Comments"}, {"Iwi.IwiGroupName", "Iwi.InterestValue", "Iwi.DateOfAnswer", "Iwi.Comments"})
in
#"Expanded Iwi"

1 ACCEPTED SOLUTION
Super User III
Super User III

You aren't using it wrong, but it only help performance in some cases. It doesn't buffer the table in memory for the next step. It techinally isolates it from external changes. So, a great use for this is to use it after sorting data. It is possible subsequent actions could cause the sort to change. It can be a huge performance booster for lists. I use List.Buffer() all of the time but lists are pretty small and are used differently.

 

If you aren't getting good performance gains, it means it isn't really helping in your scenario, and you certianly don't need to use it twice in a query - unless it is for a specific purpose, like protecting a sort.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Super User III
Super User III

You aren't using it wrong, but it only help performance in some cases. It doesn't buffer the table in memory for the next step. It techinally isolates it from external changes. So, a great use for this is to use it after sorting data. It is possible subsequent actions could cause the sort to change. It can be a huge performance booster for lists. I use List.Buffer() all of the time but lists are pretty small and are used differently.

 

If you aren't getting good performance gains, it means it isn't really helping in your scenario, and you certianly don't need to use it twice in a query - unless it is for a specific purpose, like protecting a sort.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thanks heaps.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors