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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
asarhu7926
New Member

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 @asarhu7926,

 

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 @asarhu7926,

 

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors