Table.buffer causing weird large loading size

[ghe7549]

I currently have a table and the load refresh size is 946MB but when i add Table.buffer to the mergered query in the table to deal with an data issue that arose after i sorted and removed dublicates now my table load size goes to around 90GB.  Below is the code the i am usisng 

let
    Source = Assets,
    #"Filtered Rows" = Table.SelectRows(Source, each ([TypeGroup] = "AHU" or [TypeGroup] = "Chiller")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"TypeGroup"}, TaskList, {"TypeGroup"}, "TaskList", JoinKind.LeftOuter),
    #"Expanded TaskList" = Table.ExpandTableColumn(#"Merged Queries", "TaskList", {"Tasks", "GrnMin", "GrnMax", "YelMin", "YelMax"}, {"Tasks", "GrnMin", "GrnMax", "YelMin", "YelMax"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TaskList", "Unique-AssetTask", each [tag_number]&[Tasks]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Unique-AssetTask"}),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Duplicates", {"Unique-AssetTask"}, #"Chiller & AHU Tasks", {"AssetTaskUnique"}, "Chiller Tasks", JoinKind.LeftOuter),
    #"Expanded Chiller Tasks" = Table.ExpandTableColumn(Table.Buffer(#"Merged Queries1"), "Chiller Tasks", {"procedure_description", "value", "inserted_at"}, {"procedure_description", "value", "inserted_at"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Chiller Tasks", "Tasks", "Tasks - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Tasks - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Tasks - Copy.1", "Tasks - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tasks - Copy.1", type text}, {"Tasks - Copy.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Tasks - Copy.1", "Element"}, {"Tasks - Copy.2", "State"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "MeasureIndicator", each if [value] = 0 then "Unreported or No Data Available" else
if [value] = null then "Unreported or No Data Available" else
if [value] >=[GrnMin] and [value] <=[GrnMax] then "KPIs within optimal range" else
if [value] >= [YelMin] and [value] <= [YelMax] then "KPIs within meaningful range" else "KPIs outside of meaningful range"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Light", each if Text.Contains([MeasureIndicator], "Unreported") then "TrafficBlackRimmedLight" else if Text.Contains([MeasureIndicator], "within optimal") then "CircleHigh" else if Text.Contains([MeasureIndicator], "within meaningful") then "SignMedium" else "SignLow"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "LightSortOrder", each if Text.Contains([MeasureIndicator], "Unreported") then 4 else if Text.Contains([MeasureIndicator], "optimal") then 1 else if Text.Contains([MeasureIndicator], "within meaningful") then 2 else 3),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column1",{{"Economizer Damper?", "Economizer"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","false","No",Replacer.ReplaceText,{"Economizer"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","true","Yes",Replacer.ReplaceText,{"Economizer"}),
    #"Merged Queries2" = Table.NestedJoin(#"Replaced Value1", {"COID"}, FacilityHierarchy, {"COID"}, "FacilityHierarchy", JoinKind.LeftOuter),
    #"Expanded FacilityHierarchy" = Table.ExpandTableColumn(#"Merged Queries2", "FacilityHierarchy", {"Group"}, {"FacilityHierarchy.Group"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Expanded FacilityHierarchy", "Task Sort Order", each if Text.Contains([Tasks], "Airside") then 1 else if Text.Contains([Tasks], "Supply") then 2 else if Text.Contains([Tasks], "Return") then 3 else if Text.Contains([Tasks], "Efficiency") then 4 else if Text.Contains([Tasks], "Condenser-EWT") then 5 else if Text.Contains([Tasks], "Condenser-LWT") then 6 else if Text.Contains([Tasks], "Condenser") then 7 else if Text.Contains([Tasks], "Evaporator-EWT") then 8 else if Text.Contains([Tasks], "Evaporator-LWT") then 9 else if Text.Contains([Tasks], "Evaporator") then 10 else 11)
in
    #"Added Conditional Column2"

Does anyone know what would cause this?

[v-shex-msft]

HI @ghe7549,

AFAIK, buffer function is used to the looping or custom function which will prevent invoke/reference one table with multiple times and only spend the resource at the first time. And your scenario is a simple join multiple query tables operations and it seems not required to use buffers.

Since I not so clearly the query which you used in buffer step and named #"Chiller & AHU Tasks”, can you please share some more detail about this? (Join with huge amount of records obviously will spend large amount of resources)

How to Get Your Question Answered Quickly 

BTW, you can also take a look the following blog which told the improve performance methods if helps:

Speed/Performance aspects – The BIccountant

Regards,

Xiaoxin Sheng