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

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors