M Query Editor - Trouble with Conditional IF statement

[CoreyP]

Hey guys,

I have a dataset that is a transaction log of pallet movements within a warehouse. It has the following fields: Date, Pallet No., From Location, To Location. I am adding a conditional column to the dataset to identify what activity was taking place. Rules set on From Location and To Location determine the activity. All of my rules are working correctly except for one, and I can't figure out why. I'm hoping extra sets of eyes might see something I'm missing. 

Here is my entire column code: 

if 
[FROM_LOCATION] = "SPRCV"
and [TO_LOCATION] <> "SG-PRO-CESS"
and [TO_LOCATION] <> "OS-PUT-AWAY"
and [TO_LOCATION] <> "PUT-AWA-50"
then "Data Entry"
else


if 
[FROM_LOCATION] = "SPRCV"
and [TO_LOCATION] = "OS-PUT-AWAY" 
then "OS Dimming"
else


if 
[FROM_LOCATION] = "SG-PRO-CESS"
then "SG Data Entry"
else

if 
[FROM_LOCATION] = "DANGR-GOODS" 
or [TO_LOCATION] = "PUT-AWA-50"
then "Complex Data Entry"
else


if 
[FROM_LOCATION] = "PUT-AWA-40"
then "Declarations"
else


if 
[TO_LOCATION] = "REPCK-ENTRY"
then "Repack"
else


if 
Text.StartsWith([TO_LOCATION] , "A0")
or Text.StartsWith([TO_LOCATION] , "B0") 
then "Putaway" 
else


if 
[TO_LOCATION] = "PICK-ED1"
then "Loose Pick"
else


if 
[TO_LOCATION] = "SM-CON-SOL"
or [TO_LOCATION] = "NZ-CON-SOL"
then "Complex Pick"
else 

if 
Text.StartsWith([FROM_LOCATION], "REP")
and Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT")
then "Repack Entry"

else
"Other"

The specific activity I'm having trouble with is Repack Entry:

if 
Text.StartsWith([FROM_LOCATION], "REP")
and Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT")
then "Repack Entry"

else

The logic should read: If From Location starts with REP and To Location starts with PAS or PUT, then Repack Entry. 

The From Location beginning with REP should return only two values, REPCK-ENTRY and REP-ACK-ING ( If From Location equals REPCK-ENTRY or REP-ACK-ING and To Location starts with PAS or PUT, then Repack Entry ), however, when I validate I'm getting a lot of values in From Location that do not start with REP. 

Does anyone have any thoughts? 

Any help is MUCHO appreciated!

Thanks!

[v-lionel-msft · Accepted solution]

Hi @CoreyP ,

"The logic should read: If From Location starts with REP and To Location starts with PAS or PUT, then Repack Entry. "

Try this code:

if 
        Text.StartsWith([FROM_LOCATION], "REP") 
        and 
        (
        Text.StartsWith([TO_LOCATION], "PAS")
        or Text.StartsWith([TO_LOCATION], "PUT")
        )
        then "Repack Entry" else null)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MartynRamsden]

Hi @CoreyP 

Looks like your 'Repack' IF statement is simply missing a set of parentheses.

Try this:

if 
Text.StartsWith([FROM_LOCATION], "REP")
and (Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT"))
then "Repack Entry"

else

Best regards,

Martyn