Trying to use LOOKUP function to perform LEFT OUTER JOIN between 2 Dataverse tables

[themistoklis]

Hello All,

I have 2 Dataverse tables and I would like to perform a left join.

The main table is 'ANPR - LiveFeeds' (left table) and I would like to join 'ANPR - Vehicle Registration Plate Mappings' to it.

The formula that I use is the following:

ClearCollect(ANPR_joined_dataset, 
   AddColumns('ANPR - LiveFeeds',
       "Full_Name_or_Service", LookUp('ANPR - Vehicle Registration Plate Mappings', cre34_carplate = 'ANPR - Vehicle Registration Plate Mappings'[@Car_Plate_No_No_Spaces],Full_Name_or_Service)
   ))
;

There are missing records in the returned collection using the formula above.

It seems that it performs an inner or possibly other 'weird' join.

What am I doing wrong with the formula?

[timl · Accepted solution]

@themistoklis 

This syntax looks correct to me.

The possible problem here is that ClearCollect collects up to a maximum of 2000 rows (or whatever your data row limit setting is.

To verify this, you can modify your call to AddColumns and filter 'ANPR - LiveFeeds' to see if you can retrieve the rows that are missing in your initial formula.

[themistoklis]

@timl 

Indeed that was the issue. It was only retrieving the 2000 rows.

Thank you