cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Salfii Regular Visitor
Regular Visitor

Efficient Lookup

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_powerbioptionsetrefidversionnumberValueLabelEntityTechNameEntityNameLanguageOptionSetEntityTechName
 67525536,02E+08keine_airlineAirline1031_alliance_opt
 67525546,02E+08oneworld_airlineAirline1031_alliance_opt
 67525556,02E+08Skyteam_airlineAirline1031_alliance_opt
 67525566,02E+08Star Alliance_airlineAirline1031_alliance_opt
 67525571Ja_iso_country_codeCountry Code1031_phone_mask_default_bit
 67525580Nein_iso_country_codeCountry Code1031_phone_mask_default_bit
 67525596,02E+08Business_aviation_market_dataFlugverkehr Marktdaten1031_cabin_class_opt
 67525606,02E+08Discount Coach_aviation_market_dataFlugverkehr Marktdaten1031_cabin_class_opt
 67525616,02E+08First_aviation_market_dataFlugverkehr Marktdaten1031_cabin_class_opt
 67525626,02E+08Premium Coach_aviation_market_dataFlugverkehr Marktdaten1031_cabin_class_opt
 67525636,02E+08Montag_airport_direct_flightAirport Schedule1031_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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Salfii Regular Visitor
Regular Visitor

Re: Efficient Lookup

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.

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Efficient Lookup

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? Smiley Happy

 

Regards

Salfii Regular Visitor
Regular Visitor

Re: Efficient Lookup

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.