Power BI: Date column not processed correctly using R script

Question

In Power BI, I am trying to transform my data table prior to visualization. I have chosen to do so using R script.

My data table contains a 'Date' column. Whenever I run my R script on the column, it returns NULL for all values in that column.

For example -

Before

R Script used to transform column

# 'dataset' holds the input data for this script
library(dplyr)
library(lubridate)
library(zoo)

# Extract month-year for each date
df=dataset %>%
   mutate(Date=as.yearmon(Date))

Result

I re-executed this script in R Studio, and the transformation works perfectly fine.

Seems to be an issue with Power BI, any date-related operation results in NULLs

Anybody experiencing the same, with a possible solution?

Thanks in advance

Answer 1

This answer has been edited after a discussion in the comments

---

I have a suggestion assuming your desired output looks like this:

The key to the solution lies in converting your dates to text before running the R script, and treat the input in your R script accordingly. Also, to make sure the Power Query Editor doesn't make any formatting choices for you (avoid dates as decimals), your output dates from your R script should be formatted as text / string.

---

My approach:

1. Manually insert a few dates using Enter Data:

2. Go to Edit Queries and take a look:

Notice that the column has been automaticaly changed to Date, and there's a step Changed Type under Applied Steps. Now, change the Date data type to text and replace the other data changing step:

3. Insert the following R script that only has a few changes compared to your setup:

# 'dataset' holds the input data for this script
library(dplyr)
library(lubridate)
library(zoo)

# Extract month-year for each date
df=dataset %>%
   mutate(Date=as.yearmon(as.Date(Date,format = "%d/%m/%Y")))

# Make sure Power BI picks up the date column as a string format
df['Date'] <- format(df['Date'], "%b %Y")

4. Run the script to get this:

I hope this is what you were looking for. Don't hesitate to let me know if not.