LOOKVALUE does not return all fields

[OPS-MLTSD]

I have a table called ‘Employer-use this’, where I am trying use LOOKUPVALUE to return names of Region (Central East, Eastern etc), which is named as ‘Column’ right now (the last column on the picture). However, the problem is not all fields are populated, I highlighted in yellow to show the blanks that are not being shown. I checked my reference excel sheet (which is Sheet 2) and it shows the name of the region for that postal code, so I am not sure why these fields are not being populated. You can see that I used the formula:

Column = LOOKUPVALUE(Sheet2[Region], Sheet2[Postal Code], ‘Employer-use this’[Postal Code])

I also trie this formula:

Column = CALCULATE (

FIRSTNONBLANK ( Sheet2[Region], 1 ),

FILTER (

'Sheet2',

'Sheet2'[Postal Code] = 'Employers-use this'[Postal Code]

&& 'Sheet2'[RPS Registration Number] = 'Employers-use this'[RPS Registration Number]

)

)

I still get about 1624 blanks out of about 4500 entries 

I used the same Sheet2 table to create the Risk level Column you see on the picture below and Risk level populated all fields properly, so not sure why it is not work for the column with region names.

If someone could please help me solve this issue, I would really appreciate it!

[harshnathani]

Hi @OPS-MLTSD ,

Try to check the if these values come up using Power Query.

A good way to trouble shoot is using Merge queries option in Power Query 

Also have a look at the blogs below. 

https://exceloffthegrid.com/power-query-lookup-values-using-merge/

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

Regards,

Harsh Nathani