Forum Discussion
Convert Excel Formula To Power Query
Hello all,
Can someone please help me convert this to power query? I keep getting the token literal expected error. I have googled everywhere and cannot figure the result out. I really appreciate any help! Thanks in advance!
IF((OR([Evaluation Status]='Reviewed',[Evaluation Status]='Finalized')*AND([Days Past POP End Date]>=121))'Delinquent',IF(AND([Evaluation Status]='Rated',[Days Past POP End Date]>=91),'Very Late',IF((OR([Evaluation Status]='Initiated',[Evaluation Status]='Drafted'))*AND[Days Past POP End Date]>=31)),'Late','NA')))
Hi ca-traveler - when you add a column Power Query, you need to use the following syntax for nest if with or + and conditions:
if ( [ColumnName1] = "text1" or [ColumnName1] = "text" ) and [ColumnName2] >= 121 then "Result1" else if [ColumnName1] = "text3" and [ColumnName2] >= 91 then "Result2" else "N/A"Here is a good resource to learn more: IF Statements in Power Query M (Incl Nested IFs, OR, AND) (gorilla.bi)
Hi ca-traveler ,
Does this seem to be about right? 🙂
Here the M code for the query:
if ( [Evaluation Status] = "Reviewed" or [Evaluation Status] = "Finalized" ) and [Days Past POP End Date] >= 121 then "Delinquent"
else if [Evaluation Status] = "Rated" and [Days Past POP End Date] >= 91 then "Very Late" else if ( [Evaluation Status] = "Initiated" or [Evaluation Status] = "Drafted" ) and [Days Past POP End Date] >= 31 then "Late" else "NA"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Awesome, thanks Tom! 🙂
Awesome, thanks Daryl!
Trent
4 Replies
Hi ca-traveler - when you add a column Power Query, you need to use the following syntax for nest if with or + and conditions:
if ( [ColumnName1] = "text1" or [ColumnName1] = "text" ) and [ColumnName2] >= 121 then "Result1" else if [ColumnName1] = "text3" and [ColumnName2] >= 91 then "Result2" else "N/A"Here is a good resource to learn more: IF Statements in Power Query M (Incl Nested IFs, OR, AND) (gorilla.bi)
Awesome, thanks Daryl!
Trent
- tackytechtom
Most Valuable Professional
Hi ca-traveler ,
Does this seem to be about right? 🙂
Here the M code for the query:
if ( [Evaluation Status] = "Reviewed" or [Evaluation Status] = "Finalized" ) and [Days Past POP End Date] >= 121 then "Delinquent"
else if [Evaluation Status] = "Rated" and [Days Past POP End Date] >= 91 then "Very Late" else if ( [Evaluation Status] = "Initiated" or [Evaluation Status] = "Drafted" ) and [Days Past POP End Date] >= 31 then "Late" else "NA"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Awesome, thanks Tom! 🙂