Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Lookup which includes multiples, duplicates and null values

Hi all,   Hoping you can help me with this one as I am going around in circles! I've got multiple lines some with a category and some with a job and i'm trying to keep both for reporting. The probl...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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!
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
7 years ago

Please paste a sample of the desired outcome as well.

Ideally post link to file, as your samples headers have lost connection to your data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!!