Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks heaps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.