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 NULL
s
Anybody experiencing the same, with a possible solution?
Thanks in advance
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With