Excel Sage Power Query动态筛选问题：引用命名范围单元格失效

• 在Excel Sage Power Query中，通过工作簿内命名范围（对应单元格D3，科目表下拉选择）动态修改筛选条件
• 动态MonthNo筛选周期功能正常，但相同逻辑实现的AccountNo动态筛选虽能读取到对应值，却返回空表
• 将筛选条件改为固定账号（如"B20458"）时可正常返回结果
• 需求：当D3未选择账号（为空）时不应用筛选

原Power Query代码 #

let
MonthNo= Text.From(Excel.CurrentWorkbook(){[Name="MonthNo"]}[Content]{0}[Column1]),
AccountNo= Text.From(Excel.CurrentWorkbook(){[Name="AccountNo"]}[Content]{0}[Column1]),    
Source = Sql.Database("VM65\SAGE200", "UK_GamaAviationLtd", [Query="SELECT #(lf)NLNominalAccount.AccountNumber, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, NLNominalAccount.AccountName,#(lf)NLPostedNominalTran.TransactionDate, NLPostedNominalTran.PostedDate, NLPostedNominalTran.GoodsValueInBaseCurrency, NLPostedNominalTran.Reference,#(lf)NLPostedNominalTran.Narrative, NLPostedNominalTran.UniqueReferenceNumber, NLPostedNominalTran.UserName, NLPostedNominalTran.UserNumber,#(lf)SYSAccountingPeriod.PeriodNumber#(lf)FROM NLPostedNominalTran, NLNominalAccount, SYSAccountingPeriod, SYSFinancialYear#(lf)WHERE NLPostedNominalTran.NLNominalAccountID = NLNominalAccount.NLNominalAccountID#(lf)AND NLPostedNominalTran.SYSAccountingPeriodID = SYSAccountingPeriod.SYSAccountingPeriodID#(lf)AND SYSAccountingPeriod.SYSFinancialYearID = SYSFinancialYear.SYSFinancialYearID#(lf)AND SYSAccountingPeriod.PeriodNumber <="&MonthNo&"#(lf)ORDER BY NLPostedNominalTran.UniqueReferenceNumber DESC"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionDate", type date}, {"GoodsValueInBaseCurrency", Currency.Type}, {"PostedDate", type date}}),
Custom1 = Table.AddColumn(#"Changed Type", "Date Time Refresh", each DateTime.LocalNow() as datetime),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date Time Refresh", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"AccountNumber", "Account Number"}, {"AccountCostCentre", "Cost Centre"}, {"AccountDepartment", "Department"}, {"AccountName", "Account Name"}, {"TransactionDate", "Trans Date"}, {"PostedDate", "Posted Date"}, {"GoodsValueInBaseCurrency", "Posted Value"}, {"Reference", "Journal Ref"}, {"Narrative", "Journal Narrative"}, {"UniqueReferenceNumber", "URN"}, {"UserName", "User Name"}, {"UserNumber", "User Number"}, {"PeriodNumber", "Posted Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Posted Value", Currency.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Trans Date", "Trans Date - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Account Number", "Cost Centre", "Department", "Account Name", "Trans Date", "Posted Date", "Posted Value", "Journal Ref", "Journal Narrative", "URN", "User Name", "User Number", "Trans Date - Copy", "Posted Period", "Date Time Refresh"}),
#"Extracted Year" = Table.TransformColumns(#"Reordered Columns",{{"Trans Date - Copy", Date.Year, Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Year",{{"Trans Date - Copy", "Year"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Full Account No", each [Account Number]&[Cost Centre]&[Department]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Full Account No", "Account Number", "Cost Centre", "Department", "Account Name", "Trans Date", "Posted Date", "Posted Value", "Journal Ref", "Journal Narrative", "URN", "User Name", "User Number", "Year", "Posted Period", "Date Time Refresh"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Full Account No] = "&AccountNo&")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Year", Order.Ascending}, {"Posted Period", Order.Ascending}, {"Trans Date", Order.Ascending}})
in
#"Sorted Rows"

核心问题 #

筛选行步骤的代码错误：([Full Account No] = "&AccountNo&") 是把字符串&AccountNo&作为筛选值，而非引用变量AccountNo的实际值，导致无法匹配数据。

修复后的完整代码 #

let
    MonthNo= Text.From(Excel.CurrentWorkbook(){[Name="MonthNo"]}[Content]{0}[Column1]),
    AccountNo= Text.From(Excel.CurrentWorkbook(){[Name="AccountNo"]}[Content]{0}[Column1]),    
    Source = Sql.Database("VM65\SAGE200", "UK_GamaAviationLtd", [Query="SELECT #(lf)NLNominalAccount.AccountNumber, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, NLNominalAccount.AccountName,#(lf)NLPostedNominalTran.TransactionDate, NLPostedNominalTran.PostedDate, NLPostedNominalTran.GoodsValueInBaseCurrency, NLPostedNominalTran.Reference,#(lf)NLPostedNominalTran.Narrative, NLPostedNominalTran.UniqueReferenceNumber, NLPostedNominalTran.UserName, NLPostedNominalTran.UserNumber,#(lf)SYSAccountingPeriod.PeriodNumber#(lf)FROM NLPostedNominalTran, NLNominalAccount, SYSAccountingPeriod, SYSFinancialYear#(lf)WHERE NLPostedNominalTran.NLNominalAccountID = NLNominalAccount.NLNominalAccountID#(lf)AND NLPostedNominalTran.SYSAccountingPeriodID = SYSAccountingPeriod.SYSAccountingPeriodID#(lf)AND SYSAccountingPeriod.SYSFinancialYearID = SYSFinancialYear.SYSFinancialYearID#(lf)AND SYSAccountingPeriod.PeriodNumber <="&MonthNo&"#(lf)ORDER BY NLPostedNominalTran.UniqueReferenceNumber DESC"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionDate", type date}, {"GoodsValueInBaseCurrency", Currency.Type}, {"PostedDate", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Date Time Refresh", each DateTime.LocalNow() as datetime),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date Time Refresh", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"AccountNumber", "Account Number"}, {"AccountCostCentre", "Cost Centre"}, {"AccountDepartment", "Department"}, {"AccountName", "Account Name"}, {"TransactionDate", "Trans Date"}, {"PostedDate", "Posted Date"}, {"GoodsValueInBaseCurrency", "Posted Value"}, {"Reference", "Journal Ref"}, {"Narrative", "Journal Narrative"}, {"UniqueReferenceNumber", "URN"}, {"UserName", "User Name"}, {"UserNumber", "User Number"}, {"PeriodNumber", "Posted Period"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Posted Value", Currency.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Trans Date", "Trans Date - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Account Number", "Cost Centre", "Department", "Account Name", "Trans Date", "Posted Date", "Posted Value", "Journal Ref", "Journal Narrative", "URN", "User Name", "User Number", "Trans Date - Copy", "Posted Period", "Date Time Refresh"}),
    #"Extracted Year" = Table.TransformColumns(#"Reordered Columns",{{"Trans Date - Copy", Date.Year, Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Year",{{"Trans Date - Copy", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Full Account No", each [Account Number]&[Cost Centre]&[Department]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Full Account No", "Account Number", "Cost Centre", "Department", "Account Name", "Trans Date", "Posted Date", "Posted Value", "Journal Ref", "Journal Narrative", "URN", "User Name", "User Number", "Year", "Posted Period", "Date Time Refresh"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each if AccountNo = "" then true else [Full Account No] = AccountNo),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Year", Order.Ascending}, {"Posted Period", Order.Ascending}, {"Trans Date", Order.Ascending}})
in
    #"Sorted Rows"

关键修改点 #

1. 修正Filtered Rows步骤逻辑：使用if...else判断，当AccountNo为空时返回所有行，否则匹配Full Account No与变量AccountNo的值
2. 移除错误的字符串拼接写法"&AccountNo&"，直接引用变量AccountNo

内容的提问来源于stack exchange，提问作者Gary