Reply
Highlighted
New Member
Posts: 1
Registered: ‎01-15-2016
Accepted Solution

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

 

 

 


Accepted Solutions
Regular Visitor
Posts: 21
Registered: ‎07-28-2015

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


All Replies
Regular Visitor
Posts: 21
Registered: ‎07-28-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

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

Super User
Posts: 8,191
Registered: ‎07-11-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

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.


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

Proud to be a Datanaut!


Regular Visitor
Posts: 21
Registered: ‎07-28-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

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 Smiley Happy

 

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

Super User
Posts: 8,191
Registered: ‎07-11-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

@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.


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

Proud to be a Datanaut!


Regular Visitor
Posts: 21
Registered: ‎07-28-2015

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

 

Super User
Posts: 8,191
Registered: ‎07-11-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

@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


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

Proud to be a Datanaut!


Regular Visitor
Posts: 21
Registered: ‎07-28-2015

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

Super User
Posts: 8,191
Registered: ‎07-11-2015

Re: IF - Formula in Add Custom Column

[ Edited ]

@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


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

Proud to be a Datanaut!


Regular Visitor
Posts: 21
Registered: ‎07-28-2015

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