Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.

 

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.

Need Help
Tips and Tricks
Tutorial Requests
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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