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

Re: IF - Formula in Add Custom Column

Well @smoupre 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

Super Contributor
Posts: 5,376
Registered: ‎07-11-2015

Re: IF - Formula in Add Custom Column

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

Frequent Visitor
Posts: 6
Registered: ‎03-22-2016

Re: IF - Formula in Add Custom Column

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”)." ?
Visitor
Posts: 1
Registered: ‎04-14-2016

Re: IF - Formula in Add Custom Column

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

Member
Posts: 60
Registered: ‎09-23-2016

Re: IF - Formula in Add Custom Column

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.

Frequent Visitor
Posts: 9
Registered: ‎09-21-2016

Re: IF - Formula in Add Custom Column

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
Posts: 1
Registered: ‎01-10-2017

Re: IF - Formula in Add Custom Column

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

Frequent Visitor
Posts: 2
Registered: ‎02-20-2017

Re: IF - Formula in Add Custom Column

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

Frequent Visitor
Posts: 2
Registered: ‎02-20-2017

Re: IF - Formula in Add Custom Column

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

Highlighted
Regular Visitor
Posts: 20
Registered: ‎05-29-2017

Re: IF - Formula in Add Custom Column

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