Skip to main content

Power BI

Needs Votes

Error handling in the PATH () function for circular references

Vote (3) Share
Pär Adeen's profile image

Pär Adeen on 20 Mar 2018 19:41:37

Please add a third optional parameter to the Path () function to handle circular reference errors. I.E. instead of:
PATH(, )
implement:
PATH(, , [])
Where can be either null or the same type as

Rational:
If not having an advanced ETL process it's kind of hard to be immune for circular references between various levels in the parent child hierarchies. And the whole data import will stop in case of circular references

By it's nature, there's no possibility for us to act on error messages from the PATH-function. By this reason, any kind of error handling must be carried out by the function itself.

Example data:
ID Parent
President CFO
CFO President
Note: There could be many levels between the circular relations, so they are kind of hard to detect using standard PowerQuery

My suggestion is to add a third optional parameter to the PATH function. This third parameter shall contain a value (can also be null) that shall terminate the path (using null), or replace the items parent (by replacing the data when an error ocour

Using null as the third parameter, the result would be:
President
President|CFO

Using a custom replacement value (in this case: CirPathError) as the third parameter, the result would be:
CirPathError|President
President|CFO

Comments (1)
Pär Adeen's profile image Profile Picture

Ryan McCauley on 05 Jul 2020 23:28:00

RE: Error handling in the PATH () function for circular references

I'd like to see PATH enhanced with a general Value_if_error parameter, beyond what you're requesting here - in my dataset, not every parent value appears in the list (the data isn't perfect), but the PATH function refuses to evaluate anybody if even one value is missing. If I could provide a "Blank () " replacement value so those broken rows are obvious, that would be a huge help.

Voted!