cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoshB_
New Member

Merge Tables based on first non-blank condition

Hi there,

I have two tables, each table contains employees and contains an ID, a personal Email, and a work email

 

Unfortunately, they don't match very well, and there will be no way of cleaning the data.

 

What I want is to merge the cols from table 1 and table 2, then the challenge I'm facing is matching them. From the samples below, I want to pull the colums A, B C....etc into table 2 where a match can be found on either, ID, Personal Email or Work Email. In the example, ID 2 matches and pulls the result, whereas Steven entered his ID wrong in table 1, so it doesn't find him, but can find a match by email.

 

My plan is to create a column in table 1, then using lookup checks the 3 conditions, then using coalesce, returns the first non-blank and finds the correct ID from table 2, I can then join the tables.

 

Heres my attempt:

 

Matching_ID =

VAR Lookupid = LOOKUPVALUE(Table2[ID], Table2[ID], Table1[ID])
VAR LookupPersonalEmail = LOOKUPVALUE(Table2[ID], Table2[Personal Email], Table1[Personal Email])
VAR LookupWorkEmail = LOOKUPVALUE(Table2[ID], Table2[Work Email], Table1[Work Email])
 
Return
COALESCE(Lookupid, LookupPersonalEmail, LookupWorkEmail)

 

and I get the error: A table of multiple values was supplied where a single value was expected.

 

I've tried doing a filter, or using FirstNonBlankValue, but every which way, I get some error. 

 

Any help with this function, or approach would be appriciated

 

 Table 1:

IDPersonal EmailWork EmailCol A
2Josh@emailJosh1@emailA
3Amy@emailAmy1@emailB
7james@email james1@emailB

 

Table 2:

IDPersonal EmailWork EmailCol A from Table 1
2Josh@email JoshB@emailA
4james@email james1@emailB
3Amy@emailAmy22@emailB
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@JoshB_ 

I did it in a different way, please check:

Col A = 
VAR _ID = [ID]
VAR _PMAIL = [Personal Email]
VAR _WMAIL = [Work Email]
RETURN
MAXX(
    FILTER(
        'Table-1',
        'Table-1'[ID] = _ID || 'Table-1'[Personal Email] = _PMAIL || 'Table-1'[Work Email] = _WMAIL
    ),
    'Table-1'[Col A]
)

Fowmy_0-1634468768744.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@JoshB_ 

I did it in a different way, please check:

Col A = 
VAR _ID = [ID]
VAR _PMAIL = [Personal Email]
VAR _WMAIL = [Work Email]
RETURN
MAXX(
    FILTER(
        'Table-1',
        'Table-1'[ID] = _ID || 'Table-1'[Personal Email] = _PMAIL || 'Table-1'[Work Email] = _WMAIL
    ),
    'Table-1'[Col A]
)

Fowmy_0-1634468768744.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Perfect, thank you.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.