Power BI cannot apply operator & to numbers?

[jadewind]

I was following this article but when it comes to this part adding a new column: "YearQuarter = IF([Quarter] = 1,[Year]-1 & "4",[Year] & [Quarter]-1)" I get errors: "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.".

Then I tried to change "IF" to "if" in the "Add Custom Column" it shows error "Token RightParen expected".

Then I tried to change the formula to "if [Quarter] = 1 then [Year]-1 & "4" else [Year] & [Quarter]-1" the formula seemed ok, but the new column shows all the values as "Error". When I click on "Error" it took me to the error details which states

I wonder why the formula in the original article doesn't work on my computer (I use Power BI August 2016 version), and how I can concatenate two numbers in Power BI?

[jadewind]

I just realized that I tried to add the formula in Query Editor while I guess I should have added them in the Data tables. They now work. But I just wonder how I can do it in Query Editor?

[v-ljerr-msft]

@jadewind

But I just wonder how I can do it in Query Editor?

---

You can follow steps below to add this new column in Query Editor.

1. In Query Editor, Add Column tab, click Add Custom Column to add a custom column.

2. Then enter the column name and use the formula below to create the column.

if [Quarter] = 1 then Number.ToText([Year]-1) & "4" else Number.ToText([Year]) & Number.ToText([Quarter]-1)

Following is the result.

Regards

[ankitpatira]

@jadewind If you want to do via query editor then similar output of can be achieved by using Conditional column. So in query editor under Add Column tab add conditional column.

[jadewind]

Thanks ankitmatira for your reply. I did try the conditional column but I can not perform the calculation. The "Output" will have to be either an entered value (text or number) or "Select a column" where I am not able to add "+1".