Hello! I have 2 unrelated tables where I need to pull some information across into the second table. Table 2 contains all "capabilities", but table one only has a few of those capabilities listed. Utilizing LOOKUPVALUE, I was able to match the available capabilities into table 2 from table 1. However, in Power BI, the cells that do not match naturally come back as blank. That being said, I am trying to figure out how to return the "alternate value" from Table 1 (in this case, it is called "All Other"). This alternate value needs to be the value listed in the first table and not just free text. To simplify, I need to have all non-matching values in table 2 return the unmatched value from table 1. If LOOKUPVALUE will not work here, I'm open to other suggestions! Here's a screenshot:
Thank you in advance!
Solved! Go to Solution.
@Marik if you have following two tables, respectively _t1 and _t2
You can achieve the desired result by this
Column = VAR _0 = MAXX (FILTER(_t1,_t1[Val]=EARLIER(_t2[Column1])),_t1[Val]) VAR _1 = IF(_0=BLANK(),MAXX (FILTER(_t1,_t1[Val]="Others"),_t1[Val]),_0) RETURN _1
Thank you for that but something is still off. It's clearly on my end because it worked well for you. One thing to note, however, is that the "other" option in the formula (i.e, if the value is not found in table 1), needs to return the other value from Table 1 and not be free text. For certain subsets of data, the unmatched values could be listed as "All", or "All Other", but it all depends on the data. That's why I can't use just free text in the "IF" statement argument. So anyway, here's what I'm seeing in my full dataset. That column with the green highlights is a simple LOOKUPVALUE formula. The green highlighted cells should say "All Other" because the values in those rows do not match anything from Table 1. As we can see here, the column with the blue highlights is indicating which cells should say "All Other". You can see that SOME are correct, but not all.
Again, I appreciate the efforts here.
@JLambs20 not clear whay you are after. It is evident from the screenshot that my formula returns what you are after. I am not sure what else do you need.
Also, "One thing to note, however, is that the "other" option in the formula (i.e, if the value is not found in table 1), needs to return the other value from Table 1" is itself contracdictory - if
t2 [Capability] not found in t1[Capability], needs to "other" value from t1????? I have no idea what it means. A value is no not found, hence the overwriting with "All Other".
Hello and thank you for the information! Unfortunately your solution is showing me data where it isn't expected. I exported the data table into Excel and hid some columns but it still shows what's going on. For some more context, I used "Lab Sciences Lead Time" as Table #1 and "SA Capabilities" as Table #2. So did I do something wrong here?
Since the red cells do not match up to what's in Table 1, they should say "All Other" as opposed to the values present.
right, but how do I pull in the "alternate result" value from the table 1 column and not just use free text? Free text won't work in the context of the dataset (this is just a small snippet of a much larger dataset).
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.