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"
