Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingQuery 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |