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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

If Statement Alternatives

Hello guys,

 

Apologies if I might seem unaware that there's already an existing thread regarding my inquiry, I would love it if you'll guide me to it. I'm still new to power query and I'm quite very interested in it.

 

For my inquiry, I'm wondering if there's a better alternative for nested if statements that might take multiple conditions and arguments.

 

1.PNG

This is not the actual data table I'm working with but only the representation of what I want to learn.

 

We can easily use if statements to categorize column B, but is there an easier and more efficient method using power query? That actual categories I'm working with are around 20 and changes from time to time. The if statement currently is very long and hard to maintain.

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @Anonymous,

 

Give this query a go, you can copy it in full into a new blank query (replacing what is there)

let
    Source = Table.FromColumns(
        {
            {"a".."e"},
            {20, 5, 50, 90, 55}
        }, type table[A=text, B=number]
    ),
    Conditions = Table.FromRows(
        {
            {1, 30, "Cat-A"},
            {31, 60, "Cat-B"},
            {61, 100, "Cat-C"}
        }, type table[Greater than=number, Less than=number, Category=text]
    ),
    fxGetCat = (value as number) as text =>
        List.Last( List.First(
            List.Select( Table.ToRows(Conditions),
                each value >= _{0} and value <= _{1} )
        )),
    Test = Table.AddColumn( Source, "Cat", each fxGetCat([B]), type text )
in
    Test

 

It returns this result:

m_dekorte_0-1716481166810.png

 

fxGetCat  is a custom function, which takes the Conditions table and a value (B) to look up the Category

I hope this is helpful

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

Hi @Anonymous,

 

Give this query a go, you can copy it in full into a new blank query (replacing what is there)

let
    Source = Table.FromColumns(
        {
            {"a".."e"},
            {20, 5, 50, 90, 55}
        }, type table[A=text, B=number]
    ),
    Conditions = Table.FromRows(
        {
            {1, 30, "Cat-A"},
            {31, 60, "Cat-B"},
            {61, 100, "Cat-C"}
        }, type table[Greater than=number, Less than=number, Category=text]
    ),
    fxGetCat = (value as number) as text =>
        List.Last( List.First(
            List.Select( Table.ToRows(Conditions),
                each value >= _{0} and value <= _{1} )
        )),
    Test = Table.AddColumn( Source, "Cat", each fxGetCat([B]), type text )
in
    Test

 

It returns this result:

m_dekorte_0-1716481166810.png

 

fxGetCat  is a custom function, which takes the Conditions table and a value (B) to look up the Category

I hope this is helpful

Anonymous
Not applicable

hello @m_dekorte , thank you very much for this. Your command lines are easier to maintain. This is surely a better alternative than manually doing if statements. I will use this method. Thanks again

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors