参考同一工作簿中的命名范围单元格(验证列表)以更改Excel Sage Sequel数据库Power Query中的帐号筛选器

我正试图从下图中的命名范围单个单元格D3动态更改Excel Sage Power Query中的筛选器。D3是从同一工作簿中的科目表中选择的列表。

Stackoverflow query

根据上面的图片,动态MonthNo在选择周期/s方面很有魅力,我对动态帐号使用了相同/相似的代码,但当它读取AccountNo(命名范围)时,它给出了正确的结果,但表显示为空,如下图所示。

Table Empty

我的代码如下:-

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"

我已经在插入第2行的地方尝试了上面的代码 AccountNo= Text.From(Excel.CurrentWorkbook(){[Name="AccountNo"]}[Content]{0}[Column1]), ,每个图像的结果是空表,因此它不是动态过滤的。此代码引用工作簿中D3(AccountNo)中的命名单元格范围。我尝试过各种交替迭代,但都没有成功。我也尝试过改变 AccountNo= Text.From 到 AccountNo= Number.From 出现错误,而 AccountNo=文本。从…起 当查看AccountNo步骤时,我可以看到Account Number的结果。

我还将倒数第二行代码从 #"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Full Account No] = "B20458")), 到 #"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Full Account No] = "&AccountNo&")), .插入时 "B20458" 代替 "&AccountNo&" ,我得到了账号B20458的结果和表格,但我想从单元格D3的下拉列表中动态更改账号。

我还希望增加的复杂性是,如果在D3(空白)中选择了“无账号”,那么如果可能的话,将应用“无过滤器”。

非常感谢你的麻烦。

加里