excel 检查表是否为空，如果为真则结束查询，否则继续查询

8yoxcaq7 于 2023-01-21 发布在其他

关注(0)|答案(1)|浏览(103)

我继承了一个相当复杂的excel工作簿，下面的查询向用户抛出了一个错误，因为在#“Filtered Rows”步骤中没有空值，它只返回了一个空表。因此，我正在尝试找出是否有一种方法可以让查询在表为空时停止，但如果它不继续。感谢您关于如何进行此操作的任何建议。
我正在尝试添加#“检查空表”步骤。

Source = Excel.CurrentWorkbook(){[Name="SAPCrosstab5"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Planning Reference", type text}, {"Column2", type text}, {"Project Definition", type text}, {"Column4", type text}, {"Distribution Channel", type text}, {"Distribution Channel_1", type text}, {"Contract Fee Type", type text}, {"Comp VS Sole Source", type text}, {"Outside/Assist (Rplan)", type text}, {"Booking Disposition (Rplan)", type text}, {"", type text}, {"Sold To Party", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Planning Reference"},#"AOP losses filter",{"Planning Reference"},"AOP losses filter",JoinKind.LeftOuter),
    #"Expanded AOP losses filter" = Table.ExpandTableColumn(#"Merged Queries", "AOP losses filter", {"Planning Reference"}, {"Planning Reference.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AOP losses filter", each ([Planning Reference.1] = null) and ([#"Booking Disposition (Rplan)"] = "Non-firm / Opportunity")),
    #"Check Empty Table" = if Table.IsEmpty(#"Filtered Rows") then Stop Query else #"Filtered Rows",
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Project Definition", "Column4", "Distribution Channel", "Planning Reference.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#""]), "", "_2", List.Sum),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"BBN", "Total Sales", "Total Profit", "Total Bookings"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Non-firm / Opportunity","Inactive/Delete",Replacer.ReplaceText,{"Booking Disposition (Rplan)"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Distribution Channel_1", "Distribution Channel"}})
in
    #"Renamed Columns"

excel

来源：https://stackoverflow.com/questions/75161623/check-if-table-is-empty-and-end-query-if-true-else-continue-query

1条答案

按热度按时间

vyswwuz21#

当您了解let ... in ...可以嵌套时，这实际上是微不足道的。
如果我没有犯语法错误，应该是这样的：

let
    Source = Excel.CurrentWorkbook(){[Name="SAPCrosstab5"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Planning Reference", type text}, {"Column2", type text}, {"Project Definition", type text}, {"Column4", type text}, {"Distribution Channel", type text}, {"Distribution Channel_1", type text}, {"Contract Fee Type", type text}, {"Comp VS Sole Source", type text}, {"Outside/Assist (Rplan)", type text}, {"Booking Disposition (Rplan)", type text}, {"", type text}, {"Sold To Party", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Planning Reference"},#"AOP losses filter",{"Planning Reference"},"AOP losses filter",JoinKind.LeftOuter),
    #"Expanded AOP losses filter" = Table.ExpandTableColumn(#"Merged Queries", "AOP losses filter", {"Planning Reference"}, {"Planning Reference.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AOP losses filter", each ([Planning Reference.1] = null) and ([#"Booking Disposition (Rplan)"] = "Non-firm / Opportunity")),
    result = if Table.IsEmpty(#"Filtered Rows") then #"Filtered Rows" else (
        let
            #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Project Definition", "Column4", "Distribution Channel", "Planning Reference.1"}),
            #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#""]), "", "_2", List.Sum),
            #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"BBN", "Total Sales", "Total Profit", "Total Bookings"}),
            #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Non-firm / Opportunity","Inactive/Delete",Replacer.ReplaceText,{"Booking Disposition (Rplan)"}),
            #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Distribution Channel_1", "Distribution Channel"}})
        in
            #"Renamed Columns"
     )
in
    result

微软在他们的页面here上暗示了这种处理方式（“跳过”一些计算而不是停止整个评估）：
if如果条件then真表达式else假表达式

*仅当if条件的计算结果为值true时，才会计算true表达式。
*仅当if条件的计算结果为值false时，才计算false表达式。

赞(0）回复(0）举报 2023-01-21

我来回答

excel 检查表是否为空，如果为真则结束查询，否则继续查询

1条答案

相关问题

热门标签

最新问答