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

Path() Error

Hi all,

 

I'm running into problems with the PATH formula in PBI desktop.

 

I have a table of items [item_id] and parents [parent_id], and I'm trying to create a calculated column to show the path

=PATH([item_id],[parent_id]).

 

I keep getting the following error:

 

The value '' in Table1[parent_id] must also exist in Table1[item_id]. Please add the missing data and try again.

 

There is always going to be an item with no parent (top of the hierarchy) so I'm not sure why I am getting this error.

 

I have also tried this in powerpivot in excel and it works, so looks to be a PBI desktop issue.

 

Can anyone help? I've searched on the forum and found a couple of similar threads but no answers.

 

Regards

 

Mike

8 REPLIES 8
sdjensen Senior Member
Senior Member

Re: Path() Error

Can you verify that you don't have a value in parent_id that doesn't exists as an item_id besides the blanks.

 

Can you verify that item_id and parent_id is the same datatype?

/sdjensen
mbrough Member
Member

Re: Path() Error

Hi,

Yes parent_id only contains blanks and values that exisit in item_id, and both datatypes are text data types.

 

Mike

mbrough Member
Member

Re: Path() Error

Hi

 

Just wondering if anyone can help with this?

 

Thanks

 

Mike

jamesoleonard Frequent Visitor
Frequent Visitor

Re: Path() Error

Same exact thing is happening for me.  Did you resolve it?

kanadc Occasional Visitor
Occasional Visitor

Re: Path() Error

My 2 cents here; hope it helps someone.

I was facing the same error; my data is in .xlsx file. So you will possibly have to try a few options before this works.

 

So, my data looked like this (all Columns as "Text", please ignore the Dates):

  

Original Data.JPG

I had only 1 root node and power BI was throwing the error for the PARENTID for this node.

So, what I did is to just Delete the PARENTID field and made it look like this:

 

Modified Data.JPG

Once I did that, Power BI started interpreting the PARENTID as "NULL". And the error went away.

So my guess is, it's using the NULL in the Parent_Key as a termination criteria for the PATH( ) function. And I'm guessing if you replace the Parent_Key field for all your root nodes with NULLS, it will probably work for you.

 

The original source of my data is a SAP HANA view. Hopefully I will be able to figure out how to get it working directly from HANA and not off my Excel solution here Smiley Happy

jamesoleonard Frequent Visitor
Frequent Visitor

Re: Path() Error

In my case the records at the top of the hierarchy already have a NULL value for the parentID.  What is strange is that PowerBI seems to not be seeing them as blank.  When I do a new column as TEST = ISBLANK(parentID), I get false on all records even those that have no value in that column.  I suspect that is the root of the error bc. if PowerBI doesn't see it as a blank, then it would be complaining that it can't find that parentID in the recordID column when doing PATH = PATH(recordID, parentID)

jamesoleonard Frequent Visitor
Frequent Visitor

Re: Path() Error

I was able to fix the underlying issue by using the replace values function

2018-10-08_11-15-32.png

I_NeedMorePower Regular Visitor
Regular Visitor

Re: Path() Error

Thanks guys! I was having the same issue! using the replace values I replaced the top hierarchy "Blank" with "null" and the error has gone! 

 

Thanks again people! Smiley Very Happy

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors