Forum Discussion
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 ve...
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 TestIt 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