Power Query M-动态使用Table.ColumnAdd的方法，使用值文字代替列名

Question

以下是我根据生成的步骤编写的脚本：

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"

我想更新的代码部分是

#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [#"Jan 15, 2021"] - [#"Mar 5, 2021"], type number),

其中我有两个文本变量(ScenarioFrom，ScenarioTo(，我想在定义中动态替换为

#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [Scenariofrom] - [Scenarioto] , type number),

我知道我试图将一个文字强行插入表列，这导致了问题，但我想知道是否有一个函数/简单的解决方案，而不需要完全转换数据。

Answer 1

定义自定义列时，写入each [ColName]是each _[ColName]的缩写，其中_表示当前行，是Record类型。

考虑到这一点，我们可以定义这样的代码

each Record.Field(_, ScenarioFrom) - Record.Field(_, ScenarioTo)

而不是

each [#"Jan 15, 2021"] - [#"Mar 5, 2021"]

---

这个问题类似，但使用不同列中的值，而不是参数/变量：

PowerQuery根据关键列选择值