Vlookups across two tables

[Andrewutter12]

Hello all,

I have two tables. Project table and debtors table. Both tables have matching unique transaction ID's. I'm looking to do some sort of vlookup to populate a project number on my debtors table.

Here's what I'm looking to achieve:

Project Table                                  Debtors Table

Trans ID          Project #              Trans ID   Project #

1                   20-24050               1                Blank

2                   20-24050               2                Blank

I want to populate a project # on my debtors table based on the matching trans ID's from each table.

Hope this makes sense.

Thanks!

[AlB]

Hi @Andrewutter12 

You can do this in PQ (probably better) or in DAX. In DAX the code for the Project# column in the debtors table would be:

Project# =
LOOKUPVALUE ( ProjectT[Project#], ProjectT[TransID], DebtorsT[TransID] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Andrewutter12]

Hello,

This is the error message I'm receiving when using that DAX.

Any thoughts?

[CNENFRNL · Accepted solution]

@Andrewutter12 , an error arises when more than one result correspond to Debtors[Tranns ID New]. You may try this formula in a calculated column,

Column =
MAXX (
    FILTER (
        z_ISG_InFocusProjectQuery,
        z_ISG_InFocusProjectQuery[Trans ID New] = Debtors[Tranns ID New]
    ),
    z_ISG_InFocusProjectQuery[ProjectNumber]
)

[Andrewutter12]

This seemed to do the trick. Thank you so much for your help.