cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KonstantinosEro
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

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

21 REPLIES 21
Amgad
Frequent Visitor

if[Country] = null then [Country] ="USA" else [Country]

keensoft
New Member

if [Currency Code]="" then "GBP" else [Currency Code])

Steven Keen
Keensoft Limited
www.keensoft.co.uk
keensoft
New Member

if [Currency Code]="" then "GBP" else [Currency Code])

Steven Keen
Keensoft Limited
www.keensoft.co.uk
Vasudha
New Member

= if [Country]=null then "USA" else [Country]     .............................is the syntax, case-sensitive

bajimmy1983
Advocate III
Advocate III

Guys,

 

Taking this opportunity, How Can I perform a kind of IF (AND(... Power Query formula that will evaluate something like this:

 

Excel language to understand what I need: IF(AND(A2=0;B2=0);0;1).

 

Logical 1 = A2=0;

Logical 2 = B2=0;

 

If both are true, then the result will be 0 otherwise result will be 1

 

Thanks a lot.

Jaderson Almeida
Business Coordinator
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi Greg! 

As soon as I type then,I see the token expected error.  Do you happen to know how to fix it?  Thanks!

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"

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

 

Not.PNGYes.PNG

 

@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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@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

@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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@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

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

Seems you found a way out. What worked for you? This has not worked:

 

if [Country]="" then "USA" else [Country] 

 

Could this have to do with this task "

  • Filter the rows that come from the header of the CSV files. (Hint: One way to do this is to filter out the Country column from records containing “Country”)." ?

In lab 1 I also have a problem with the if statement.

 

On a Windows 10 laptop the formula is working. Because of issues with regional settings (Dutch) I switched to new installed WIndows 2012 R2 server with regional settings USA.

 

lab 1 error if.png

Cool, thanks for checking that out @Fabiola_K, nice investigative troubleshooting.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Fabiola_K
Advocate IV
Advocate IV

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

Not totally related to the original question, but this comment pointing out that the whole sentence is case-sensitive solved my problem. The error message even has a capital 'T' in  "Then"!!

 

 

 image.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.