Here is the script I have based on the steps generated:
let
Source = Sql.Database("BITESTDBSVR1", "DW_FINANCE"),
CORPDB_BTT_SCENARIO_DETAILS_WITH_BACKLOG = Source{[Schema="CORPDB",Item="BTT_SCENARIO_DETAILS_WITH_BACKLOG"]}[Data],
#"Filtered Rows" = Table.SelectRows(CORPDB_BTT_SCENARIO_DETAILS_WITH_BACKLOG, each [ScenarioHeaderID] = FromScenario or [ScenarioHeaderID] = ToScenario),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [ValueTypeName] <> "Backlog"),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"ID", "ContractID", "LineNumber", "Ophours", "Prime", "ScenarioHeaderID", "Division", "ORN", "BidNumber", "CustomerContractNumber", "ProjectID", "CustomerOrderID", "CurrentStatus", "CustomerBuyer", "ProgramName", "ProgramNotes", "Notes", "MajorProgram", "ProductFamily", "ProductSubFamily", "ProductGroup", "SeriesGroup", "Turret", "ProgramSeriesID", "OrderQuantity", "ContractValueSource", "SourceCurrencyID", "BookingFXRate", "ContractValueUSDollars", "PODateIn", "AcceptanceDate", "ContractAwardDate", "ContractAwardYear", "Probability", "FactoredValue", "ProductAccountManager", "ProgramManager", "InterIntraDivision", "InterIntraExternal", "Territory", "IDIQ", "Platform", "CustomerBuyerCountry", "EndUser", "LHXRegion", "LHXName", "ProcurementLocation", "CustomerType1", "CustomerType2", "ExternalCustomer", "SalesType", "BidType", "PricingType", "ExportLicenceNumber", "USExportStatus", "CANExportStatus", "ExportComments", "RevenueRecognitionCriteria", "IncoTerms", "ShipToCountry", "PaymentTerms", "EOIRMaster", "EOIRChildID", "BidDivision", "SubProduct", "ProductLine", "ServiceAccountManager", "BusinessUnit", "OffsetCapture", "EndUserBusinessPartner", "GeographicRegion", "GeographicSubRegion", "ContractDuration", "RevRecTiming", "ForecastRevenueMonths", "InvoiceNumber", "WorkOrdernumber", "SerialNumber", "OrderLine", "PKLineIdentifier", "Year1", "Year2", "Year3", "BI1", "BI2", "BI3", "RI1", "RI2", "RI3", "VMProject", "ServiceContractNo", "MainProject", "LNProject", "ProjectContract", "SalesForceRef", "SalesRegion", "FiscalDate", "ScenarioCreated", "ScenarioUpdated", "ScenarioWeek", "Scenario", "ValueType"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[ScenarioName]), "ScenarioName", "Value", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{ScenarioFrom, ScenarioTo}),
#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [#"Jan 15, 2021"] - [#"Mar 5, 2021"], type number),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Subtraction",{{"Subtraction", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Subtraction", "Variance"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"InterIntra"}, #"CORPDB INTER_INTRA_MAPPING", {"INTER DIVISION"}, "CORPDB INTER_INTRA_MAPPING", JoinKind.LeftOuter),
#"Expanded CORPDB INTER_INTRA_MAPPING" = Table.ExpandTableColumn(#"Merged Queries", "CORPDB INTER_INTRA_MAPPING", {"INTER DIVISION NAME"}, {"INTER DIVISION NAME"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded CORPDB INTER_INTRA_MAPPING",{"Forecast", "ProgramGroupID", "InterIntra", "INTER DIVISION NAME", "EndUserCountry", "ValueTypeName", "Month", "Year", "Quarter", "MonthNumber", "FiscalYearMonth", "QuarterStart", "QuarterEnd", ScenarioFrom, ScenarioTo , "Variance"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"INTER DIVISION NAME", "InterIntraDivision"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"InterIntra"})
in
#"Removed Columns1"
The part of the code I want to update is
#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [#"Jan 15, 2021"] - [#"Mar 5, 2021"], type number),
Where I have 2 text variables (ScenarioFrom, ScenarioTo) that I want to dynamically substitute in the definition to say
#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [Scenariofrom] - [Scenarioto] , type number),
I get that I'm trying to force a literal into a table column which is causing the problem, but wondering if there is a function / easy work-around without transforming the data entirely.
When you are defining a custom column, writing each [ColName] is short for each _[ColName] where _ represents the current row, which is a Record type.
With this in mind, we can define the code like this
each Record.Field(_, ScenarioFrom) - Record.Field(_, ScenarioTo)
instead of
each [#"Jan 15, 2021"] - [#"Mar 5, 2021"]
This question is similar but uses the value in a different column rather than parameters/variables: