Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mickeydjw
Frequent Visitor

Alternative for LOOKUPVALUE in M

Scenario:  I have a dataset with Work Item ID and Parent Work Item ID.  I'm trying to create a hierarchy within the 2 to build some reports.  When I try to set it up, I receive the error that the Parent Work Item ID doesn't have itself listed in the Work Item ID, to the tune of about 120k items.  I can create a column, Lookupvalue that will give me the work item info, but that doesn't allow me to remove them from the query, so I won't be able to build my Path.

 

How can I remove the cases that don't exist from the query using M?

 

References:

 

New Parent ID = LOOKUPVALUE('Work Items - Today'[Work Item Id],'Work Items - Today'[Work Item Id],'Work Items - Today'[Parent Work Item Id])

 

image.png

1 ACCEPTED SOLUTION
mickeydjw
Frequent Visitor

Thanks to those that helped.  I facepalmed when I figured it out.  Instead of setting the PATH parent as Parent of Work Item, I set it to New Parent ID.  Problem solved.

View solution in original post

10 REPLIES 10
mickeydjw
Frequent Visitor

Thanks to those that helped.  I facepalmed when I figured it out.  Instead of setting the PATH parent as Parent of Work Item, I set it to New Parent ID.  Problem solved.

MarkS
Resolver IV
Resolver IV

HI @mickeydjw,

If I understood what you wanted, I think that in the query editor you can merge the table with itself and select the Parent ID from the top table and the Work Item ID from the bottom table and select an inner join.  The resulting table should only have rows of data where the Parent ID is in the Work Item ID column (This will reduce the records in you dataset)

 

 

mickeydjw
Frequent Visitor

Here's a sampling.   What I'm trying to eliminate are the cases where the Parent item doesn't also exist as a work item, but from the query versus creating a column via dax.

 

Parent Work Item IdWork Item Id
 7024
70247025
 7027
 7380
73807381
73807383
73807385
 7387
73877388
 7389
73897390
 7393
73937394
73937395
73937396
73937397
 7775
 7776
 7777
 7778
77787787
77787788
77777808
77777815
77777816
77757817
77757818
77757823
77777824
77778691
 8702
 8746

not sure  what the ask is? from your sample, all parent work item ids and in work item id. What is expected output from this sample.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I'll take it back to square 1, because I doubt I was clear in the ask.  The 2 columns that matter are the Parent Work Item ID and Work Item ID.

 

I need to create a Heirarchy so the first step would be to create a new column:

 

Heirarchy Path = PATH('Work Items - Today'[Work Item Id],'Work Items - Today'[Parent Work Item Id])

 

That gives me this error:

 

  image.png

So to create the list of all the work items that will throw this error,  I ran the command:

 

new ParentID = LOOKUPVALUE('Work Items - Today'[Work Item Id],'Work Items - Today'[Work Item Id],'Work Items - Today'[Parent Work Item Id])

So what I want to do is to remove the blank values from the query, since those blanks are the ones that will throw the error in the path.

 

Does that make sense?

 

 

I'm unable to produce the erorr whcih you are getting using PATH function, I just used used your data set and all worked fine.  Not sure why you are getting on that specific parent work item, if there is no child records (work item id), it will still work.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mickeydjw
Frequent Visitor

Another option I though of would be to use DAX to create a table that's equal to my data and adding the column, then removing blank values from the 2nd table as it's created.

parry2k
Super User
Super User

Can you share the sample data?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

In which format?  Excel table?  I'm not certain what specific data I can and can't share.  The columns I'm concerned aboutt are Work Item ID and Parent Work Item ID.

You can put data in excel, only relative column or share  sample data here in forum



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.