Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

Need Help
  • mussaenda's avatar
    mussaenda
    4 years ago

    Hi Anonymous ,

     

    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

2 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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