Switch statement in power query

Hi,

I know this is an older thread, but I have a personal solution that I have been preferring to use in several situations.

I use a "Record.Field" function around a custom record of my possible cases:

each Record.Field([Case1="Output1", Case2="Output2", Case3="Output3"], [INPUT])

If the input exactly matches any of the record's field names, then it will give that field's value.

This allows you to only need to have the input in the code once, and not need to have all the "Else If" and "Then" statements within the code.

There are some limitations to this approach (some of which I may be forgetting at this time), but here are a few extra tips with this approach which can be combined as needed:

• You can put this within a "try otherwise" statement to allow a default value when there is no exact field match:

  each try Record.Field([Case1="Output1", Case2="Output2", Case3="Output3"], [INPUT]) otherwise "Default Output"

• To allow case-insensitivity, you can use a "Text.Upper" or "Text.Lower" function on your input, then have all Case fields match that casing:

  each Record.Field([case1="Output1", case2="Output2", case3="Output3"], Text.Lower([INPUT]))

• If you might have number values instead of text for any/all of your input, you can use the "Text.From" function on your input:

  each Record.Field([101="Output1", 102="Output2", 103b="Output3"], Text.From([INPUT]))

• Note: You can have spaces in the record's field names; only the spaces between characters will be considered part of the field name:

  each Record.Field([Case Test 1="Output1", Case Test 2="Output2", Case Test 3="Output3"],[INPUT])

I hope this is helpful to someone! (or my future self 😁)

EDIT:

I noticed after posting this, that you had posted a reply with your specific use case, so here is my solution with your specific details:

each Record.Field([1="ABC", 54="FGH", 56="JKL", 101="BNM"],Text.From([#"Discoverer-Receivables"][Segment1]))

 (This example assumes "Discover-Receivables" is a record column where each record has a "Segment1" field.)

Hi Anonymous ,
I've created a function for this a while ago here: A generic SWITCH-function for the query editor in Power BI and Power Query – The BIccountant

Just be aware, that it doesn't cover Switch-true-statements.

waiting for you to better clarify what you expect, I propose this scheme.

let
/*    switch( c )
{
    case 'A':
        capital_a++;
    case 'a':
        letter_a++;
    default :
        total++;
}
*/

switch= (c) => 
     if c= "A" then "add_One_A" else 
     if c="a" then "add_One_a" else 
     "add_nOne_a"

in 

switch

You need to provide more information in order for us to help.