cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

IF - Formula in Add Custom Column

Hello everybody. 

When I tried to add a custom column power BI indicated an error (Token Eof  expected) so I changed the null values using the function "replace values". What was the problem, I think that I displayed the right type.

Any suggestions? Thanks. 

 

custom.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

Well @Greg_Deckler i'm very curious, i tried and this it's happened. (I think):

 

-  When the source is a CSV file and the field is empty the query show it as "empty string" for this reason the value "" is true and the preview appear it as blank. (like your test)

 

-  But, when the source is other (XLS, XLSX, ACCDB, etc) and the field is empty, the query show it as "null value", aaaaannnnd the value "" is false. Reason for this value on the preview appear it as null (like my test)

 

I didn't change the regional settings, and i'm very very curious Smiley Very Happy

View solution in original post

20 REPLIES 20
Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

Hi, i recommend you to use lowercase for the sentence "if", PowerQuery is very sensible with this. And i think you have write the complete sentence: if ..... then..... else...... Smiley Wink

Highlighted
Super User IV
Super User IV

Re: IF - Formula in Add Custom Column

Here are some good walk-throughs on if statements in "M":

 

http://www.excelguru.ca/blog/2014/08/27/the-if-function-in-power-query/

http://blog.crossjoin.co.uk/2014/03/10/conditional-logic-in-power-query/

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/03/10/conditional-logic-in-power...

 

I wouldn't think the null would cause you problems, but perhaps. Perhaps use "Null", no quotes. Or, you could fix it in DAX using ISBLANK function.

 

This "worked" for me:

if [Status] = null then "123" else "321"

 

Of couse, make sure that you really have a null in those fields that shows up as "null" in italics and without quotes. If it is just blank, then you need to use "" instead of null.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

Hi @Greg_Deckler i think both ways seems correct, but the language "M" don't recognizes the value "" and it's necessary to write the value null without quotes.  

 

I tried 🙂

 

PS: DAX it's different as you say, but i think what the course ask us work on "M"

Highlighted
Super User IV
Super User IV

Re: IF - Formula in Add Custom Column

@Fabiola_K, I did try the "" before posting, it is recognized and works. Honestly, I was a little surprised as well.

 

doublequotes.png

 

BTW, you need the "" instead of the null when your values in preview show up as just blank versus the null in italics.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

Smiley Frustrated  This is my test, in my case doesn't work only ""  Smiley Sad

 

Not.PNGYes.PNG

 

Highlighted
Super User IV
Super User IV

Re: IF - Formula in Add Custom Column

@Fabiola_K, that's wild man! Check it out, I wonder if it is a different version of the Desktop maybe? Language pack/version? Bit version?

 

doublequotes1.png

 

 

doublequotes2.png


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

@Greg_Deckler Smiley LOL Smiley LOL Smiley LOL Nop, it's the same version. One question, why your value appear as blank? i thought that when the value is blank in the source the query must show as null.

 

 

version.PNG

Highlighted
Super User IV
Super User IV

Re: IF - Formula in Add Custom Column

@Fabiola_K - This is the CSV file I used to import, was testings something else out and just happened to use this as an input. I'm guessing at this point that it is something to do with the language pack? Bizarre.

 

Status,Count
DIP,1
APP,2
OFF,0
SET,1
,2


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate IV
Advocate IV

Re: IF - Formula in Add Custom Column

@Greg_Deckler  I agree, it's bizarre.  But i did learn something new and i'm going to change the language settings for to try this theory Smiley Very HappySmiley Very HappySmiley Very Happy   I keep you informed

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.