LOOKUPVALUE column works partially

[jelleschuurman]

Hi,

We have a Azure SQL database with 200+ tables, and connected it via import to PowerBI.

This 3 tables are related:

Verrichtingen: 150.000 rows

Adres: 20.000 rows

Behandelregel: 150.000 rows

When I use the LOOKUPVALUE  _woonplaats in Verrichtingen I only get a result for 70.000 rows, the rest stays blank.

_woonplaats = LOOKUPVALUE(Adres[plaats],Adres[adresId],Verrichting[_dossier]) -> this gets the city based on _dossier

I get _dossier from: _dossier = LOOKUPVALUE(BehandelRegel[dossierId],BehandelRegel[behandelRegelId], Verrichting[behandelRegelId]) ->This get the result for all 150.000 rows.

Is there some kind of limitation in PowerBI that causes the problem with _woonplaats? Does anyone know what causes this problem and how to fix this?  Thanks in advance. Kind regards, Jelle

[HotChilli · Accepted solution]

"Is there some kind of limitation in PowerBI that causes the problem with _woonplaats?" No

Some ideas to troubleshoot this:-

Find some values which do not return a match (should be easy to do).  Inspect the fields by eye.  Go to the Data View in powerbi.  Use the search features (from the column header)  to see if you can return rows based on a value that doesn't match (even though you think it should).  Search on the first 3 characters (using the 'starts with' filter).

Try anything to identify why it doesn't match. 

Once you have identified a problem, use Power Query to clean and/or fix the data then try the Lookupvalue again.

-------

It's possible that there are spaces or hidden characters in the data so try a 'Clean' or 'Trim' in Power Query to eliminate this.

Good luck