Forum Discussion

gittelena's avatar
gittelena
Microsoft Employee
10 years ago

Question about adding a new column and adding formulas

I am in the process of doing an intro Power BI course.

 

In the training materials it says:

 

"In the Sales query, add a custom column named Country Name which takes the value of the Country column when it is not null and the value of "USA" when the Country column is null"

 

I *think* this is done with formulas? But how?

 

Thanks!

GL

3 Replies

  • GilesWalker's avatar
    GilesWalker
    Skilled Sharer
    10 years ago

    The following could work for you.

     

    Country = IF(ISBLANK(Sales Query[Column]),"USA",Sales Query[Column])

     

    If the cell is blank put in USA, if it is not then put in the value that is there. Need a bit more information though as not sure where the other country names are coming from.

     

    Thanks,

     

    Giles

  • nwzomer's avatar
    nwzomer
    New Member
    10 years ago

    I am also trying to work on the same instruction of the course. What you provided did not work, PowerBI gives a "Token Eof Expected" error and won't allow me to use the formula.  These were the full instructions, perhaps this will help give more context. Stuck on #6.

     

    1. Download the zip file containing VanArsdel's international sales data and extract it to a folder. You should see 4 CSV files in the folder.
    2. Import the data from the file folder by using the Get Data. To do this, click the Get Data / More option, selectFolder and click Connect. Select the folder where you saved the 4 CSV files containing VanArsdel’s international sales data.
    3. Perform the following steps:
      • Name the query International Sales.
      • Select to combine (combined binaries) the content of those 4 files.
      • 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”).
      • Filter the rows that are after December 31st, 1999.
    4. Edit the Sales Query from the US Sales.
    5. Append the International Sales Query to the Sales query from the US Sales.
    6. In the Sales query, add a custom column named Country Name which takes the value of the Countrycolumn when it is not null and the value of "USA" when the Country column is null.
    7. Remove the Country column.
    8. Load the data into Power BI Desktop. This might take a few minutes.
    9. Explore the imported data in the Data View. 
    10. Hide the International Sales table from report view.