If Statement Alternatives

[Anonymous]

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.

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.

[m_dekorte · Accepted solution]

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:

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]

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