Check if Table is empty and end query if true else continue query-CodePudding

I inherited a fairly complex excel workbook and the following query is throwing an error for the users because there are no null values in the #"Filtered Rows" step, it just returns an empty table. So, I'm trying to figure out if there's a way to have the query stop there if the table is empty but if it's not continue on. Thanks for any advice on how to go about this.

I'm trying to add the #"Check Empty Table" step.

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"

CodePudding user response：

It is actually trivial when you understand the let ... in ... can be nested.

If I am making no syntax mistake, this should be it:

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

This way of proceeding ("skipping" some calculation rather than stopping the whole evaluation) is hinted by Microsoft on their page here:
if if-condition then true-expression else false-expression

The true-expression is only evaluated if the if-condition evaluates to the value true.
The false-expression is only evaluated if the if-condition evaluates to the value false.