Vlookup to identify non matching values between two tables

[Ramendra]

Dear Team, 

Could you please help me to know, 

How to apply Vlookup to identify non matching values between two tables in power bi. 

Ex. 

I have a table with 100 asset serial numbers and thier locations. 

I have another table with 49 serial number

And thier locations with active status. 

Now I want to get and show 51 non matching serial numbers with thier location from first table to show the remaining assets in stock. 

I would like to use matrix and table view. 

[Ashish_Mathur]

Hi,

My suggestion would be to use the Merge feature of the Query Editor.  To get further help, share data in a form that can be pasted in an MS Excel workbook. 

[Anonymous · Accepted solution]

Hi @Ramendra,

In my opinion, I'd like to suggest you write a measure formula with 'except', 'intersect' functions to extract not matched values then you can use this list to compare with the current value and return tag.
After these steps, you can use this measure in the visual level filter to filter not matched records.

Measure =
VAR list =
    EXCEPT (
        UNION ( ALL ( Table1[serial number] ), ALL ( Table2[serial number] ) ),
        INTERSECT ( ALL ( Table1[serial number] ), ALL ( Table2[serial number] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( Table1[serial number] ) IN list, "Y", "N" )

Regards,

Xiaoxin Sheng

[amitchandak]

@Ramendra ,

Found example how to get a value in new column from another table

example of trying to get a value in the sales table from date table -Blank means values not found. prefer on 1-M

Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date])

example, getting City data from geography to sales -Blank means values not found.

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

or city count from sales to geography -Blank means values not found.

City count= countx(FILTER(Sales,geography[City Id]=Sales[City Id]),Sales[City])

[Ramendra]

@amitchandak , I am not sure how to do that.
I am trying to attach exel with raw data but not able to find option here.