Exiting Query Steps

[rodf1021]

Wondering what other methods are available to exit the processing of applied steps in a query. I often use a logic statement first to test a parameter or function to see if the query should waste time fetching data. I will usually return a table like {"Error" = "Parameter is blank. Could not run query"}. The irritation with that stems from any following steps that require specific columns or data to successfully process the next steps. First, they waste time processing, then they produce an error that may or may not pop up to the users on the sheet.

Anyone have better ideas to ellegantly exit the query or stop the following steps from processing?

Thanks in advance,

Rod 

[edhans · Accepted solution]

Query applied steps must be followed top to bottom, but they can be skipped. So you cannot use the 3rd step to reference the result of the 8th step and that will get you a circular reference.

But you can use the results in the 3rd step to see if the 8th step needs to be done.

For example

1. Say step 3 is called RecordsWithNoErrors (NOTE: This will be easier if your steps have no spaces or special characters) and it has a filter that removes all errors.
2. Say step 8 only needs to process if Step 3 has 1+ records. You could use this as step 8 in the formula bar:
   
   1. if Table.RecordCount(RecordsWithNoErrors) > 0 then Table.AddColumn(Step7, "Another Field", each [Some Field] + 1, Int64.Type) else Step7
3. So Step 3 has records, step 8 adds a column to step 7. If Step 3 has no records, then it just returns Step 7 wihtout the added column.

Make sense? You can do the same with error checking logic, or the try/otherwise construct. For example, if step 7 ends in an error status, then step 8 could be:

= try Table.AddColumn(Step7, "Another Field", each [Some Field] + 1, Int64.Type) otherwise Step6

So if step 7 has an error, this will just return step 6.

However, I wouldn't use this too much. The more data you pull the worse this gets to manage. I would try to build the queries in a way that doesn't require too much of this logic. I have been using PQ heavily for 6+ years, and I only recall doing this once, and I suspect today I'd find another way to make the queries more robust to not return errors.

[rodf1021]

Thank you edhans.

This is definitely one way of solving the problem, but I see it more of a workaround. It requires adding Mcode to all following steps to get them to give the same result as the step where you would prefer to stop based on a logical decision. If I can determine at step 1 that all following steps do not need to process, it's unfortunate that it doesn't appear to be an option.