Excel Power Query条件过滤需求：空表时取消过滤返回全量数据

关键修改点 #

1. 单独提取账号单元格值，简化空值判断逻辑
2. 新增账号为空时直接返回全量数据的处理
3. 调整排序步骤的数据源为判断后的结果集
4. 保留原有的无匹配数据时返回全量的逻辑

修改后的完整代码 #

let
    MonthNo= Text.From(Excel.CurrentWorkbook(){[Name="MonthNo"]}[Content]{0}[Column1]),
    // 单独获取账号单元格的值，方便后续判断
    AccountNoValue = Excel.CurrentWorkbook(){[Name="AccountNo"]}[Content]{0}[Column1],
    Source = Sql.Database("XXXX\SAGE200", "UKXXXXXXXLtd", [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" = if AccountNoValue = null or AccountNoValue = "" then #"Reordered Columns1" else Table.SelectRows(#"Reordered Columns1", each Text.From([Full Account No]) = Text.From(AccountNoValue)),
    // 保留判断逻辑：过滤后无数据则返回全量
    checkstep = if Table.RowCount(#"Filtered Rows")=0 then #"Reordered Columns1" else #"Filtered Rows",
    // 排序步骤基于checkstep的结果
    #"Sorted Rows" = Table.Sort(checkstep,{{"Year", Order.Ascending}, {"Posted Period", Order.Ascending}, {"Trans Date", Order.Ascending}})
in
    #"Sorted Rows"

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