Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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.
Best Regards,
Herbert
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |