Forum Discussion
Lookup which includes multiples, duplicates and null values
Thank you for your help, sorry I'm not good at posting stuff including links etc... I think the answer is
Column = IF(ISBLANK('Trial Balance'[SourceTransaction.UID]),FALSE(),IF(ISBLANK('Trial Balance'[UID]),FALSE(),CONTAINS('Trial Balance','Trial Balance'[UID],'Trial Balance'[SourceTransaction.UID])))... but i'm still testing it. thank you!
Community ChampionPlease paste a sample of the desired outcome as well.
Ideally post link to file, as your samples headers have lost connection to your data.
Headers are
UID
Category.DisplayID
Account.DisplayID
Job.Name
Amount
Total
Source
The Final Amount
Final Date
SourceTransaction.UID
in excel i would do a vlookup and get f792617f-c325-457a-be63-bce63819cc9d, which is finding that in UID based on SourceTransaction UID, when i try a lookup in Power BI i get blanks because of all the duplicates and nulls that also exist I presume.. I've also tried concatenex, rank, related, find, search...
Basically trying to get f792617f-c325-457a-be63-bce63819cc9d based on UID column so i can then filter it out. Trying to recreate a trial balance where I can report on categories and jobs using all the line items :) thanks for your help!!
- ImkeF
Hi Anonymous,
is this what you're after?: https://www.thebiccountant.com/2018/08/12/how-to-do-a-real-vlookup-false-in-power-query-or-power-bi/
Thanks I did find that one and try it, didn't work for me unfortunately. Your blogs are always excellent!!!
- ImkeF
Problem is, that I still don't understand your request.
So unless you're providing real sample data of your desired result (full table like described here for exammple: https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-bipower-querypower-pivot-question-to-get-a-prompt-accurate-and-helpful-response.aspx ) I cannot help you any further here.