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

 

 

 

20 REPLIES 20

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

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


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

@ me in replies or I'll lose your thread!!!

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!




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

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

Thank you. That solved the problem. I would love it if the online function reference for M was better... that if/then statement is not showing up on my searches. argh. thanks again.

New Member

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

New Member

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

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

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

Steven Keen
Keensoft Limited
www.keensoft.co.uk

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.