Forum Discussion
Need help with comparing row
I need help with comparing two different rows using if else to make a custom colume in query
Logic : If "Rate" = EXPORT, E_EXPO, IMPORT or E_IMPO and if "Rate 2" = export and "Rate 1" of the same "Installation" = import then it results in a "y" which means they are in order (correct).
If "Rate" does not = EXPORT, E_EXPO, IMPORT or E_IMPO then N.A
In other words, Rate 1 has to be import and Rate 2 has to be export to be correct.
If Rate 1 and Rate 2 are the same or in reverse order then it is incorrect.
Anything else that does not fullfilled these conditions if applicable will result in a "n" which mean incorrect.
Here is a before and after image of what I would need
Before:
After:
Any help or guide will be very helpful, thank you.
Hi SIBEIRABZ ,
Here's my solution.
Sample data
1.Add two conditional columns to return 1 or 0.
2.Add the two columns.
3.Group by like the following.
Expand it.
4.Add a condintional to get the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi SIBEIRABZ ,
Here's my solution.
Sample data
1.Add two conditional columns to return 1 or 0.
2.Add the two columns.
3.Group by like the following.
Expand it.
4.Add a condintional to get the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This works just fine!!
=Table.Combine(Table.Group(PreviousStepName,"Installation",{"n",each let a=if not List.ContainsAll({"EXPORT",
E_EXPO","IMPORT","E_IMPO"},[Rate]) then "N.A." else if List.RemoveItems([Rate 2],{null,"export"}={} and List.RemoveItems([Rate 1],{null,"import"})={} then "y" else "n" in Table.AddColumn(_,"in order",each a)})[n])
