Most significant value - SQL Outer Apply

[SvenAntonissen]

Hi Girls and Guys,

I have a challenge. I have 2 data sources, 1 from Sharepoint and another one from SQL. 

The tabel in Sharepoint is a list with phone prefixes and not full phone numbers containing location information. The tabel in SQL is containing destinations, which are full phone numbers.

I need to match the destination phone numbers against the phone prefixes, based upon the most significant values matching the phone prefix. 

I used to match these using an outer apply when both tabels are hosted in SQL. But I understood that this is not available in Powerbi. I have tried using lookupvalue and match, but this is not working.

You can download the powerbi file via the link down below to check this for yourself.

Powerbi file

[v-piga-msft]

Hi @SvenAntonissen,

For your requirement, we usually use lookupvalue function in Power BI.

Your data is large which is not convenient for us to test. (For data security, you'd better not share the sensitive data.)

If it is convenient, could you share the a dummy sample data which could reproduce the scenario and your desired output so that we can help further investigate on it?

Best  Regards,

Cherry

[SvenAntonissen]

Hi Cherry,

Thanks for your feedback, I have disabled the download link.

Please find herewith an overview of the CDRS tables.

CDRS Picture

Prefixes Picture

I need to have the title associated with the CDRS table by matching the phone number to the prefixes.

Kind regards,

Sven

 

[v-piga-msft]

Hi @SvenAntonissen,

I cannot see your shared image. 

You can upload it to OneDrive or Dropbox and post the link here.

Best Regards,

Cherry

[SvenAntonissen]

Hi @v-piga-msft

The onedrive links are added in the post above.

Kind regards,

Sven