cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

I've seen a number of posts that talk about adding table.buffer to queries to speed things up. I'm pretty new to PowerBi and I'd like to try this, but I'm unclear exactly how to add table.buffer? I know I need to add custom M code, but how do I exactly do that?

 

Below is my m code where I'd like to add table.buffer (it's not the entire code...just the first few steps)

 

let
Source = SapBusinessWarehouse.Cubes("vsapb90", "90", "010", [Implementation="2.0"]),
ZSD_O02 = Source{[Name="ZSD_O02"]}[Data],
#"ZSD_O02/Y_ZSD_O02_DELIVER_PBI_TRAX_JUD" = ZSD_O02{[Id="ZSD_O02/Y_ZSD_O02_DELIVER_PBI_TRAX_JUD"]}[Data],
#"Added Items (Pull by Delivery by Sales - weight calc at Matr level)" = Cube.Transform(#"ZSD_O02/Y_ZSD_O02_DELIVER_PBI_TRAX_JUD",
{
{Cube.ApplyParameter, "[0I_FPER]", {"001/2019", "003/2020"}},
{Cube.AddAndExpandDimensionColumn, "[0ACT_GI_DTE]", {"[0ACT_GI_DTE].[LEVEL01]"}, {"Act.goods issue date.Act.goods issue date Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0DELIV_NUMB]", {"[0DELIV_NUMB].[LEVEL01]"}, {"Delivery.Delivery Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0DOC_NUMBER]", {"[0DOC_NUMBER].[LEVEL01]"}, {"Sales document.Sales document Level 01"}},
{Cube.AddMeasureColumn, "Delivery quantity", "[Measures].[2M3WEA8ZK0QFKU517D8CGZLEJ]"},
{Cube.AddMeasureColumn, "Weight Formula (Matr Wgt X Del Qty)", "[Measures].[2M3WEA8ZK0QFKUN2RU84U8AOE]"},
{Cube.AddAndExpandDimensionColumn, "[0MATERIAL]", {"[0MATERIAL].[LEVEL01]"}, {"Material.Material Level 01"}},
{Table.AddColumn, "Material.Material Level 01.Key", each Cube.AttributeMemberProperty([Material.Material Level 01], "[20MATERIAL]")},
{Cube.AddAndExpandDimensionColumn, "[0MATERIAL__ZPHCAT]", {"[0MATERIAL__ZPHCAT].[LEVEL01]"}, {"PH Category*.PH Category* Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0PRODH3]", {"[0PRODH3].[LEVEL01]"}, {"Prod. hier. level 3.Prod. hier. level 3 Level 01"}},
{Cube.AddMeasureColumn, "Delivery USD net val", "[Measures].[2M3WEA8ZK0QDULVK77UTBG079]"},
{Cube.AddAndExpandDimensionColumn, "[0DOC_TYPE]", {"[0DOC_TYPE].[LEVEL01]"}, {"Sales doc. type.Sales doc. type Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0S_ORD_ITEM]", {"[0S_ORD_ITEM].[LEVEL01]"}, {"Item.Item Level 01"}},
{Cube.AddAndExpandDimensionColumn, "[0DOC_CURRCY]", {"[0DOC_CURRCY].[LEVEL01]"}, {"Document currency.Document currency Level 01"}},
{Cube.ApplyParameter, "[0I_DAYS]", {"01/01/2020", "03/31/2020"}}
}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Items (Pull by Delivery by Sales - weight calc at Matr level)",{{"Act.goods issue date.Act.goods issue date Level 01", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Delivery.Delivery Level 01"}, {{"Sumif weight of Delivery", each List.Sum([#"Weight Formula (Matr Wgt X Del Qty)"]), type number}, {"All row", each _, type table [Act.goods issue date.Act.goods issue date Level 01=date, Delivery.Delivery Level 01=number, Sales document.Sales document Level 01=text, Delivery quantity=number, #"Weight Formula (Matr Wgt X Del Qty)"=number, Material.Material Level 01=text, Material.Material Level 01.Key=text, #"PH Category*.PH Category* Level 01"=text, Prod. hier. level 3.Prod. hier. level 3 Level 01=text, Delivery USD net val=number, Sales doc. type.Sales doc. type Level 01=text, Item.Item Level 01=text, Document currency.Document currency Level 01=text]}}),
#"Expanded All row" = Table.ExpandTableColumn(#"Grouped Rows", "All row", {"Act.goods issue date.Act.goods issue date Level 01", "Sales document.Sales document Level 01", "Delivery quantity", "Weight Formula (Matr Wgt X Del Qty)", "Material.Material Level 01", "Material.Material Level 01.Key", "PH Category*.PH Category* Level 01", "Prod. hier. level 3.Prod. hier. level 3 Level 01", "Delivery USD net val", "Sales doc. type.Sales doc. type Level 01", "Item.Item Level 01", "Document currency.Document currency Level 01"}, {"Act.goods issue date.Act.goods issue date Level 01", "Sales document.Sales document Level 01", "Delivery quantity", "Weight Formula (Matr Wgt X Del Qty)", "Material.Material Level 01", "Material.Material Level 01.Key", "PH Category*.PH Category* Level 01", "Prod. hier. level 3.Prod. hier. level 3 Level 01", "Delivery USD net val", "Sales doc. type.Sales doc. type Level 01", "Item.Item Level 01", "Document currency.Document currency Level 01"}),
#"Merged Queries: Tie Trax data to BW Deliery" = Table.NestedJoin(#"Expanded All row", {"Delivery.Delivery Level 01"}, Trax, {"Delivery Document"}, "Trax data ship date 7 19-3 20 fbid", JoinKind.LeftOuter),
#"Expanded Trax data" = Table.ExpandTableColumn(#"Merged Queries: Tie Trax data to BW Deliery", "Trax data ship date 7 19-3 20 fbid", {"Master Fb Id", "%T019", "GL", "Cost Center", "Day of Ship Date", "Weight Actual Lb", "Weight Fncl Lb", "Orig State Prov", "Dest State Prov", "CA Amount USD", "Number of Freight Bills", "Mode", "Orig Post Code", "Dest Post Code", "Source", "Delivery Document"}, {"Trax data ship date 7 19-3 20 fbid.Master Fb Id", "Trax data ship date 7 19-3 20 fbid.%T019", "Trax data ship date 7 19-3 20 fbid.GL", "Trax data ship date 7 19-3 20 fbid.Cost Center", "Trax data ship date 7 19-3 20 fbid.Day of Ship Date", "Trax data ship date 7 19-3 20 fbid.Weight Actual Lb", "Trax data ship date 7 19-3 20 fbid.Weight Fncl Lb", "Trax data ship date 7 19-3 20 fbid.Orig State Prov", "Trax data ship date 7 19-3 20 fbid.Dest State Prov", "Trax data ship date 7 19-3 20 fbid.CA Amount USD", "Trax data ship date 7 19-3 20 fbid.Number of Freight Bills", "Trax data ship date 7 19-3 20 fbid.Mode", "Trax data ship date 7 19-3 20 fbid.Orig Post Code", "Trax data ship date 7 19-3 20 fbid.Dest Post Code", "Trax data ship date 7 19-3 20 fbid.Source", "Trax data ship date 7 19-3 20 fbid.Delivery Document"}),

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

Hi @jcueland 

 

Table Buffer is not a magical function that will make every code run faster, it is beneficial in situations where you have small tables that you have to reiterate over and over again, the way it works is: it loads the specified table into the memory so it can be accessed there through the execution.

You can add an extra step in your code like: 

step = Table.Buffer( previous step or table )

  and later refer to this "step" in the following steps.

 

If this table is too big or you not iterating it multiple times, then it might result in worse performance.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

Hi @jcueland 

 

Table Buffer is not a magical function that will make every code run faster, it is beneficial in situations where you have small tables that you have to reiterate over and over again, the way it works is: it loads the specified table into the memory so it can be accessed there through the execution.

You can add an extra step in your code like: 

step = Table.Buffer( previous step or table )

  and later refer to this "step" in the following steps.

 

If this table is too big or you not iterating it multiple times, then it might result in worse performance.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Highlighted
Helper V
Helper V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

Thanks, how do I determine if my table is small or big? E.g. I have a table that is 1.2 million rows with out 25 columns. Would that be too large for table.buffer?

Highlighted
Super User V
Super User V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

no, that's still ok.  The bigger question is - how many times are you referencing that table in the same query?

Highlighted
Helper V
Helper V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

My table is very linear. I do step #1, then step #2, then step #3, then step #4...... Each step refers to the previous step only (e.g. I don't at step #4 go back to step #1). With that said would table.buffer not help my query from a speed perspective?

Highlighted
Super User V
Super User V

Re: How do I use table.buffer to speed up a query? (How do I create M code for table.buffer?)

No, it would not help.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors