For Power BI version 2.76.5678.782 (December 2019).

Working with dates with Power BI requires an extra steps, since R doesn’t recognize date data automatically. Let’s look at the following example. We have a table with columns Date and Price. Column Date is of type date.

We use R to add column Price2, which is column Price multiplied by 2. We take the following steps.

Open Transform > Run R script.

Enter the following:

newname = dataset

newname[‘Price2’] = newname[‘Price’]*2

First row creates a new variable newname, duplicate of variable dataset. We can’t change dataset variable, so we always create a duplicate.

Second row adds column Price2 to table newname, a product of column Price and 2.

Confirm with OK and newname table is created.

We see R doesn’t recognize date data. For advanced manipulation of data we would need to use one of many available packages. But for simple problems we can just change data type to number. Let’s look at both examples.

Example: change date column to text

We start the same as in the example above.

We take the following steps.

Change the date column into text.

We can recognize text by left justification.

Continue the same as in example above. Add column Price2 which is a product of column Price and 2. Select Transform > Run R script.

Enter following commands.

newname = dataset

newname[‘Price2’] = newname[‘Price’]*2

Confirm with OK. We get a new table, but the date is still treated as text.

Change data type of column Date to date.

Example: calculating monthly average

Example above is a quick solution that can work in simple queries. But often we want to actually calculate using date data. Let’s look at the following example.

We want to calculate monthly average using the same table.

We take the following steps.

Change date column to text.

We can recognize text by left justification.

Select Transform > Run R script.

Enter the following commands.

# ‘dataset’ holds the input data for this script

newname = dataset

newname$Date = as.Date(newname$Date, format = “%m/%d/%Y”)

newname$Month = format(newname$Date, format = “%m”)

newname$Year = format(newname$Date, format = “%Y”)

newname = aggregate(Price ~ Month + Year, newname, mean)

Confirm with OK. We get monthly averages of prices.

We can get the same result via multiple other ways, for example using one of various date packages.