cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfschaefer
Helper IV
Helper IV

Expression error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.

After numerous hours of researching this issue for 2 days, I am really stumped.

 

I have tried to create a simple function of either If or Switch and both return the above mentioned error.

 

I even created a sample based on the example supplied on MSDN for the IF Function, trying to use it both in the Power BI Desktop and/or Power Query for Excel 2013 (32 bit) environments. Without any luck.

 

I am using the latest download of Power query and PBI,  I just set up my computer in the last week.  Urgently need of help.

 

Thanks,

Kfs

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

Hi Karen,

sorry, didn't read your question right. This will be the formula with the added condition:

 

if [TermDate] <> null then ''Termed" else if ([#"#YearsofService"]>=2) then "2 + Years" else "Less Than 2 Years")

 

or

 

if [TermDate] <> "" then ''Termed" else if ([#"#YearsofService"]>=2) then "2 + Years" else "Less Than 2 Years""

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

11 REPLIES 11
Korryk
Microsoft
Microsoft

Ok so I'm a little confused can I note formulate like the following?

 

=IF([SubmitFiscalMonth]="FM-01 (Jul)",1,IF([SubmitFiscalMonth]="FM-02 (Aug)",2,IF([SubmitFiscalMonth]="FM-03 (Sep)",3,IF([SubmitFiscalMonth]="FM-04 (Oct)",4,IF([SubmitFiscalMonth]="FM-05 (Nov)",5,IF([SubmitFiscalMonth]="FM-06 (Dec)",6,IF([SubmitFiscalMonth]="FM-07 (Jan)",7,IF([SubmitFiscalMonth]="FM-08 (Feb)",8,IF([SubmitFiscalMonth]="FM-09 (Mar)",9,IF([SubmitFiscalMonth]="FM-10 (Apr)",10,IF([SubmitFiscalMonth]="FM-11 (May)",11,IF([SubmitFiscalMonth]="FM-12 (Jun)",12,"N/A"))))))))))))

kfschaefer
Helper IV
Helper IV

Capture.PNG

B129
Continued Contributor
Continued Contributor

Hi @kfschaefer,

 

   is as @Sean and @ImkeF said. Take a look at this examples:

 

Excelguru

Technet

 

Just remember as @ImkeF said that M is very case sensitive so you have to write:   if (. . .) then (. . .) else (. . .)

 

 

#I'M Not An Expert#

Sean
Community Champion
Community Champion

I think in M you have to use => if .... then ... else...

How should I modify the if then else statement to include another layer:

 

if(hasonevalue([Term Date]),"Termed" if([#"#YearsofService"]>=2) then "2 + Years" else "Less Than 2 Years")

 

Could you help with the syntax?

 

Karen

try this:

 

= Table.AddColumn(#"Reordered Columns2", "YearsofSvce", each if [YrofSvc] < 2 then 1 else 2)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User II
Super User II

Hi Karen,

sorry, didn't read your question right. This will be the formula with the added condition:

 

if [TermDate] <> null then ''Termed" else if ([#"#YearsofService"]>=2) then "2 + Years" else "Less Than 2 Years")

 

or

 

if [TermDate] <> "" then ''Termed" else if ([#"#YearsofService"]>=2) then "2 + Years" else "Less Than 2 Years""

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Thanks for all the great input.

 

K

ImkeF
Super User II
Super User II

Exactly as @Sean said. And watch out: M is case sensitive!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Sean
Community Champion
Community Champion

@kfschaefer this is an issue with M

 

to me it looks like you are mixing up DAX and M the way you reference the column???

 

But @ImkeF is the M expert - maybe she can help you???

Sean
Community Champion
Community Champion

Post a screenshot...

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.