Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
Solved! Go to Solution.
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.
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.
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)
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 Id | Work Item Id |
7024 | |
7024 | 7025 |
7027 | |
7380 | |
7380 | 7381 |
7380 | 7383 |
7380 | 7385 |
7387 | |
7387 | 7388 |
7389 | |
7389 | 7390 |
7393 | |
7393 | 7394 |
7393 | 7395 |
7393 | 7396 |
7393 | 7397 |
7775 | |
7776 | |
7777 | |
7778 | |
7778 | 7787 |
7778 | 7788 |
7777 | 7808 |
7777 | 7815 |
7777 | 7816 |
7775 | 7817 |
7775 | 7818 |
7775 | 7823 |
7777 | 7824 |
7777 | 8691 |
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:
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.
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.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |