Efficient Lookup

[Salfii]

Hello everyone,

I´m currently loading Data from Microsoft Dynamics 365 CRM ONLINE with PowerBI over OData-Feed.

For every option-Set (like a Drop Down Value/List in CRM) OData gives back the corresponding value of the OptionSet-Entry (e.g. 602370000) insteand of the label "Skyteam", which is not useable for the enduser.

Therefore, I´m loading all Option set Value- and Label-Combinations in PowerBI (which is already a workaround, as stated here, because the Table "StringMap" is not visible over OData at the Moment: Link, which looks) like the following:

gap_powerbioptionsetrefid	versionnumber	Value	Label	EntityTechName	EntityName	Language	OptionSetEntityTechName
	6752553	6,02E+08	keine	_airline	Airline	1031	_alliance_opt
	6752554	6,02E+08	oneworld	_airline	Airline	1031	_alliance_opt
	6752555	6,02E+08	Skyteam	_airline	Airline	1031	_alliance_opt
	6752556	6,02E+08	Star Alliance	_airline	Airline	1031	_alliance_opt
	6752557	1	Ja	_iso_country_code	Country Code	1031	_phone_mask_default_bit
	6752558	0	Nein	_iso_country_code	Country Code	1031	_phone_mask_default_bit
	6752559	6,02E+08	Business	_aviation_market_data	Flugverkehr Marktdaten	1031	_cabin_class_opt
	6752560	6,02E+08	Discount Coach	_aviation_market_data	Flugverkehr Marktdaten	1031	_cabin_class_opt
	6752561	6,02E+08	First	_aviation_market_data	Flugverkehr Marktdaten	1031	_cabin_class_opt
	6752562	6,02E+08	Premium Coach	_aviation_market_data	Flugverkehr Marktdaten	1031	_cabin_class_opt
	6752563	6,02E+08	Montag	_airport_direct_flight	Airport Schedule	1031	_day_of_flight_opt

As a generic approach, I wrote a custom function to match every value with the corresponding label in the table above and use "Invoke Custom Function" on each of those value columns.

Custom Function:

let
    Source = (OptionSetTable as nullable table, OptionSet as text, OptionSetValue as nullable number) => let
        OptionSetValueCleaned = if OptionSetValue is null then 0 else OptionSetValue,
        Source = if OptionSetTable is null then OptionSetValues else OptionSetTable,
        #"Filtered Rows" = Table.SelectRows(Source, each [OptionSetEntityTechName] = OptionSet),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Value] = OptionSetValueCleaned),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"gap_powerbioptionsetrefid", "versionnumber", "Value", "EntityTechName", "EntityName", "Language", "OptionSetEntityTechName"})  
    in
        #"Removed Columns"
in
    Source

Invoking this function for x columns in a few tables is a huge performance drain. It seems like the lookup table with the OptionSetValues in it is loaded for each call or something else. I also tried to use Table.Buffer(), but this only worsened the performance.

Does anyone have a better Idea - performance wise - how to match those values in  a generic approach and can give me a hint how to make the custom function better?

Thanks in advance.

[v-ljerr-msft]

Hi @Salfii,

Instead of do it with Power Query(M) in Query Editor, have you tried doing it in Data View by creating a relationship between the "StringMap" table and the fact table, and then use RELATED function(DAX) to create a new calculate column the fact table to get the related EntityName, or just using LOOKUPVALUE fucntion(DAX) without creating any relationships to see if there is any performance improvement in your scenario? 

Regards

[Salfii · Accepted solution]

Hi @v-ljerr-msft,

thanks for the ideas but I want to keep this in Query Editor and do it with M because it´s a modelling-problem.

LOOKUPVALUE looks tempting here, but at least for me, this is only some kind of workaround and might worsen the GUI/DAX-Performance for the enduser, which is more important than the query performance in my eyes. The bad query performance is only anoying at developement time because there is not a high frequecy reload schedule in production.

I´m now using another way which involves "unpivot columns" and "Merge Querys" (Left Join) in Query Editor. This solves the Problem and is much faster but not as comfortable als the custom function.

Best Regards.