Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Antmkjr
Post Patron
Post Patron

Switch statement in power query

Can some one help me build switch statement in power query

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

the code you indicate I believe is DAX (which I don't know). Try to see if this is okay for you. If the control conditions are different (not "simple" equalities) it is necessary to change the setting. But in case you should give specific examples of which situation you need to manage.

View solution in original post

Hi @Antmkjr 

There is no built-in Switch function in Power Query. In additon to the custom Switch function, you can also achieve this by adding a conditional column easily. It will generate an IF statement like below.

 

if [Segment1] = 1 then "ABC" else if [Segment1] = 54 then "FGH" else if [Segment1] = 56 then "JKL" else if [Segment1] = 101 then "BNM" else null

 

042102.jpg

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

12 REPLIES 12
PL-Jordells
Regular Visitor

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.)

Thank you, this helped me to keep my code simple in cases that the information is static.

FYI if you have leading or trailing spaces or special characters in the field names then wrap it in #" "

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

 

ImkeF
Super User
Super User

Hi @Antmkjr ,
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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

 

SWITCH (
TRUE (),
'Discoverer-Receivables'[Segment1] = 1, "ABC",
'Discoverer-Receivables'[Segment1] = 54, "FGH",
'Discoverer-Receivables'[Segment1] = 56, "JKL",
'Discoverer-Receivables'[Segment1] = 101, "BNM"
)
 
Looking  to implement this logic in power query

Hi @Antmkjr 

There is no built-in Switch function in Power Query. In additon to the custom Switch function, you can also achieve this by adding a conditional column easily. It will generate an IF statement like below.

 

if [Segment1] = 1 then "ABC" else if [Segment1] = 54 then "FGH" else if [Segment1] = 56 then "JKL" else if [Segment1] = 101 then "BNM" else null

 

042102.jpg

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Is there a way to do that without creating a column? Otherwise I will need more steps, creating a conditional column, removing the previous one

Well that just made my life a whole lot easier! Thank you!

https://gorilla.bi/power-query/switch/


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

the code you indicate I believe is DAX (which I don't know). Try to see if this is okay for you. If the control conditions are different (not "simple" equalities) it is necessary to change the setting. But in case you should give specific examples of which situation you need to manage.

Anonymous
Not applicable

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.