none
Calling Functions into Conditional Custom Column RRS feed

  • Question

  • Hi Community,

    I am attempting to create one custom column that concatenates other column values (and some hard text) in Power Query:

    • Column contains conditional logic that references other column names
    • Which column names the logic references changes depends on a "business rule" number that is selected by the end user
    • Thus, business rule 1 may concatenate columns 1, 3, and 5, but business rule 2 may concatenate columns 4, 2, and 3
    • The conditional logic also uses hard-coded variables that the user will input, which could be at the beginning, middle, or end of the final concatenated string of text

    My question is more theoretical in nature - what is the most efficient way to design this process?  My current (and only approach) I have been considering is thus:

    • Create a big lookup table
    • With different rows for different rules
    • Columns would be Position 1, Position 1 Type (because some are columns, some are hard-text), Position 2, Position 2 Type, etc. etc.
    • And then, to create a large number of lookup functions in Power Query that will call the Position Variables based on the business rule input

    Is there a better way?  The lookup table will likely be a little more complex because the hard-text values change based on some keyword search functionality returning TRUE or FALSE.

    If this is confusing please let me know and I will strive for visual example.

    Jake

    Tuesday, September 26, 2017 7:40 PM

Answers

  • Hi Jake. Although I don't entirely grok your scenario and proposed solution, using a lookup table for the dynamic values sounds (on the surface) like a reasonable approach.

    Performance could be an issue, but it depends on how large your data and lookup tables are.

    Ehren


    Wednesday, September 27, 2017 8:58 PM
    Owner
  • Agree with Ehren.

    Regarding performance: If you can manage to create a single composite key (like certain numbers or letters at specific positions) instead doing the lookup with conditions, performance should be fine as well. 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, October 1, 2017 6:55 AM
    Moderator

All replies

  • Hi Jake. Although I don't entirely grok your scenario and proposed solution, using a lookup table for the dynamic values sounds (on the surface) like a reasonable approach.

    Performance could be an issue, but it depends on how large your data and lookup tables are.

    Ehren


    Wednesday, September 27, 2017 8:58 PM
    Owner
  • Agree with Ehren.

    Regarding performance: If you can manage to create a single composite key (like certain numbers or letters at specific positions) instead doing the lookup with conditions, performance should be fine as well. 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, October 1, 2017 6:55 AM
    Moderator
  • Hi Imke, Ehren,

    Understood on both accounts.  Thanks Imke - I think I have been way overusing lookups.  I am starting to dump them and to call values via Table.Column(Table, "Column"){0}, etc.

    Thank you both!

    Friday, October 27, 2017 10:53 PM