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)
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!