Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
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