Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Power BI: Date column not processed correctly using R script

Tags:

r

powerbi

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

enter image description here

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

enter image description here

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

like image 714
Varun Avatar asked Sep 04 '25 01:09

Varun


1 Answers

This answer has been edited after a discussion in the comments


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

enter image description here

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:

enter image description here

2. Go to Edit Queries and take a look:

enter image description here

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:

enter image description here

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:

enter image description here

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

like image 181
vestland Avatar answered Sep 07 '25 09:09

vestland