Forum Discussion

jadewind's avatar
jadewind
Icon for Advocate I rankAdvocate I
10 years ago

Power BI cannot apply operator & to numbers?

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?

Need Help

4 Replies

  • jadewind's avatar
    jadewind
    Icon for Advocate I rankAdvocate I
    10 years ago

    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?

    • ankitpatira's avatar
      ankitpatira
      Icon for Community Champion rankCommunity Champion
      10 years ago

      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's avatar
        jadewind
        Icon for Advocate I rankAdvocate I
        10 years ago

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

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      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