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

How and When to use List & Table Buffer?

Hi all, I have the below query that uses a 15.2MB excel document to provide Fuel Costs. The query also contains two parameters that allow end-users to provide a custom fuel cost per gallon if desired or to apply the appropriate rate of inflation if desired.

 

Because of all the pivoting, merging and re-merging that occurs with the source excel document it seems that PBI actual loads 45.6MB to the data model each time a parameter is changed and the changes are applied to the report. Is Table.Buffer my friend here? If so, how do I use it and is there a nice and easy layman's understanding of when List.Buffer or Table.Buffer is appropriate to use?


Moreover, 5 other queries use this source excel document to query various types of costs..... so as you can imaging having to load somewhere between 15.2MB - 45.6MB per query is taking a little bit of time when something like the inflation parameter effects all queries/tables.

 

let
    Source = #"REF_CPFH_AirOperationsTable (Indexed)",
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column Header Name] = "Fuel Gallons - Commercial" or [Column Header Name] = "Fuel Gallons - DoD")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Column Header Name"]), "Column Header Name", "Value", List.Sum),
    #"Merged Queries2" = Table.NestedJoin(#"Pivoted Column",{"FY", "FHPTAircraftType", "Segment Mission Base of Operation Code", "Mission Base of Operation Branch Code", "Mission Base of Operation Region Code"},#"REF_CPFH_AirOperationsTable (Indexed)",{"FY", "FHPTAircraftType", "Segment Mission Base of Operation Code", "Mission Base of Operation Branch Code", "Mission Base of Operation Region Code"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"Index", "Column Header Name", "Value"}, {"Index.1", "Column Header Name", "Value"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded NewColumn2", each ([Column Header Name] = "Fuel Cost per Gallon - Commercial" or [Column Header Name] = "Fuel Cost per Gallon - DoD")),
    #"CC: Fuel Cost" = Table.AddColumn(#"Filtered Rows1", "Fuel Cost", each if(Fuel_CostPerGallon="Actuals") 
and [Column Header Name]="Fuel Cost per Gallon - Commercial" 
then [#"Fuel Gallons - Commercial"]*[#"Value"]

else if(Fuel_CostPerGallon="Actuals") 
and [Column Header Name]="Fuel Cost per Gallon - DoD" 
then [#"Fuel Gallons - DoD"]*[#"Value"]

else if(Fuel_CostPerGallon<>"Actuals") 
and [Column Header Name]="Fuel Cost per Gallon - Commercial" 
then [#"Fuel Gallons - Commercial"]*Fuel_CostPerGallon

else if(Fuel_CostPerGallon<>"Actuals")
and [Column Header Name]="Fuel Cost per Gallon - DoD" 
then [#"Fuel Gallons - DoD"]*Fuel_CostPerGallon

else "Not Possible"),
    #"Changed Type1" = Table.TransformColumnTypes(#"CC: Fuel Cost",{{"Fuel Cost", type number}}),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type1",{"Column Header Name"},SRC_Fuel_Hierarchy,{"Column Header Name"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"AGB Cost Element", "Cost Category"}, {"AGB Cost Element", "Cost Category"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded NewColumn1", each ([Fuel Cost] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Fuel Cost", "Fuel Cost2"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"FY"},SRC_InflationRate_CPI_BLS,{"FY"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Cumulative Inflation by FY to Date"}, {"Cumulative Inflation by FY to Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Fuel Cost", each if(Inflation_BLS_CPI="Yes")
then [Fuel Cost2]+([Fuel Cost2]*([Cumulative Inflation by FY to Date]/100))

else 
[Fuel Cost2]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Fuel Cost", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Fuel Cost2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index","Index.1", "FY", "FHPTAircraftType", "Segment Mission Base of Operation Code", "Mission Base of Operation Branch Code", "Mission Base of Operation Region Code", "Flight Hours", "Fuel Gallons - Commercial", "Fuel Gallons - DoD", "Column Header Name", "Value", "Fuel Cost", "Cumulative Inflation by FY to Date", "AGB Cost Element", "Cost Category"})
in
    #"Reordered Columns"

 

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: How and When to use List & Table Buffer?

@MarkDGaal

 

I’m not an advanced Power Query user but I think you can take a look at following two threads which related to Table.Buffer. Maybe it can help you.

http://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/...

http://community.powerbi.com/t5/Desktop/Queries-over-large-data-tables/m-p/26783/highlight/true#M852...

 

Best Regards,

Herbert

Helpful resources

Announcements
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.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors