cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodf1021
New Member

Exiting Query Steps

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 

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors