For a certain key (in this case Name)Take the Code Value on List B for the Key unless the Key doesn’t occur on List B in which case you take the value for teh Key on List A If it also doesn’t appear on List A then return a blank.
I think this might be some special type of xlookup - can someone help me.
It is likely too that the spelling of the names might differ eg Sally in list A and sallie on list B. Can I use fuzzy logic in this case - if so how?
let
Source = Table.FuzzyGroup( Table.Combine( { TableB, TableA } ), "Contract", {"AllRows", each _, type table [Contract=nullable text, Code=nullable number]}, [IgnoreCase = true, IgnoreSpace = true] ),
AddCode = Table.RemoveColumns( Table.AddColumn(Source, "Code", each Table.Sort([AllRows],{{"Prio", Order.Ascending}}){0}[Code]?, type number), "AllRows" )
in
AddCode
.
It does a FuzzyGroup on TableB and TableA. Here’s a link to the documentation .
Note that the documentation states that the aggregation function cannot guarantee to return a fixed order of rows. So I added an additional key to ensure TableB values before TableA.
It’s great to know that you are making progress with your query @AllisterB. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hi @AllisterB, did the response provided by @Melissa and @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @AllisterB, we’ve noticed that no response has been received from you since the 8th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
