Forum Discussion
Catch-all when no reference
I have a data table that has various info on it, all linked to location codes. I have a separate reference table that links those location codes to the common names of the locations. Sometimes there are instances where there is info on the data table that is linked to a code that is not truly a location, so not included on the reference table. What I would like to do is somehow include a label, i.e. 'misc', that is used for every instance when there is no location found, but I'm stuggling to do so. There isn't one or even several particular codes that I can put on the refernce table to address this as there are hundreds (maybe thousands) of potential codes that could show up on the data table.....
Any assistance is appreciated
5 Replies
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Okay here's a sample of specific data to help with my issue. The first table is the location reference table. The second table shows the actual data in our system, the third column being what I'm trying to figure out. The relationship between the two tables is the Code. In this sample, the charge codes 'QM456' and 'CA456' do not have an associated location, so what I'd like to have shown (as I've manually entered in the second table to show what I'd like) is 'Misc' for the location. The issue is that QM456 and CA456 are just made up examples, I have no idea what the Codes will be that aren't associated with locations, but I do know that Codes will come throug that don't have a location, so I'm looking for a way to have any code that doesn't exist in the reference table default to a location of 'Misc'.
Code Location TX123 Austin, TX CO123 Denver, CO CA123 Sacramento, CA FL123 Miami, FL Code Amount Location TX123 $ 100.00 Austin, TX QM456 $ 100.00 Misc CA123 $ 100.00 Sacramento, CA CA456 $ 100.00 Misc
