Need help with comparing row

[SIBEIRABZ]

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.

[wdx223_Daniel]

=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])

[Anonymous · Accepted solution]

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.

[SIBEIRABZ]

Thank you! This works just fine!!