Need help conveting Excel formula to Power Query (if statement and search/filter)

[bryanc78]

Greetings everyone.

Hoping I could get help converting an excel formula to M

I have a dataset that gives me a list of options.  I need to be able to tell what options are missing (the list of options will never change)

For example, I have a column called Apples.  This column will either list NA, Empty or a selection of apples.   If it's NA, then return NA.  If Empty, then return Empty.  If all the types of apples are listed, then return Full.  If some of the apples are missing, then return what's missing

Hope this example explains what I'm looking for

[mussaenda · Accepted solution]

Hi @bryanc78 ,

If you wanted in power query,

try this:

if List.Count(Text.PositionOf(Text.Upper([Apples]),"APPLES",Occurrence.All )) >1
then "Full"
else

if Text.Contains(
    Text.Upper([Apples]), "RED")
then "Not Full - Missing Green Apples"
else 

if Text.Contains(
    Text.Upper([Apples]), "GREEN")
then "Not Full - Missing Red Apples"
else 

if Text.Contains(
    Text.Upper([Apples]), "EMPTY") 
    then "Empty"
    else  

if Text.Contains(
    Text.Upper([Apples]), "N") 
    and Text.Contains(
    Text.Upper([Apples]), "A")
    then "NA"
    else 

null

I also attached a sample pbix for your reference.

Hope this helps

[Vijay_A_Verma]

Use this in a custom column

if [Apples]="[N/A]" then "N/A"
else if [Apples]="[Empty]" then "Empty"
else if Text.Contains([Apples],"Green Apples") and Text.Contains([Apples],"Red Apples") then "Full"
else if Text.Contains([Apples],"Green Apples") then "Not Full - Missing Red Apples"
else if Text.Contains([Apples],"Red Apples") then "Not Full - Missing Green Apples"
else null