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

[Anonymous]

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"}),

[Mariusz · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

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?

[lbendlin]

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

[Anonymous]

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?

[lbendlin]

No, it would not help.