13

The essence:

Why does Powerbi show data of the form 2017-01-04 (yyyy-mm-dd) like this?

enter image description here

The details

I'm trying to transform a table in Power BI using the Run R Script functionality in Edit Query. The source of the table is a csv file with a column with dates of the format 2017-01-04 (yyyy-mm-dd):

2017-01-04
2017-01-03
2017-01-02
2017-01-01
2016-12-31
2016-12-30
2016-12-29
2016-12-28
2016-12-27
2016-12-26
2016-12-25
2016-12-24
2016-12-23
2016-12-22

Using Get Data, Power BI shows the same date column like this:

enter image description here

And after opening the Edit Query window, the very same date column still looks like this:

enter image description here

However, when trying to run an R sctript with the same data, the column only consists of the "values" Microsoft.OleDb.Date like this:

enter image description here

The R script I'm running is simply:

# 'dataset' holds the input data for this script
output <- head(dataset)

If I try to change the data type, en error is returned:

enter image description here

It all seems very strange to me, and I haven't found a reasonable explanation using Google.

Any suggestions?

vestland
  • 55,229
  • 37
  • 187
  • 305
  • 2
    For once, the Power BI forum actually had a good answer to an R related problem. Simply change the data type to string format before running the R script. Then you can change back to any date format afterwards. I'll delete the question in a day or two if it doesn't get any attention. – vestland Jan 24 '17 at 08:58
  • 2
    Post it as an answer and mark it correct. Maybe it will attract an upvote :) – Mike Wise Jan 24 '17 at 20:15
  • 1
    Glad you didnt delete this question. I needed this. – Jacob Nordstrom May 05 '22 at 03:33

2 Answers2

20

I already provided a solution in the comments, but I'll add a detailed suggestion here as well.

The applied steps in Power BI and the resulting date column should look like this: enter image description here

Here are the details:

  1. After loading the data from the csv file, go to Edit Queries and change the data type to text:

enter image description here

  1. Run the R script

  2. Change the datatype back to date once the script has provided an output.

vestland
  • 55,229
  • 37
  • 187
  • 305
5

I wandered upon this answer last week and found that it didn't work for me. I'd receive an error from the R script that "character string is not in a standard unambiguous format." I'm assuming this is due to the many updates that have happened with Power BI in the years since the original answer, because as far as I could tell, all dates were in the exact same format (this error did not occur if I ran the data separately in R/RStudio). I figured I'd leave my solution for those who happen upon this like I did.

I did the exact same thing as vestland's solution, except instead of changing the data type to text, I had to change it to a whole number:

1) Edit query. 2) Convert all date columns to "Whole Number." 3) Run R Script, and convert date columns from numbers to date:

In R, this requires that you use as.Date() or lubridate::as_date(), with the origin argument, origin = "1899-12-30" to get the correct date when you convert from whole number back to date. (This is the origin instead of "1900-01-01" because Excel/Power BI don't account for two leap years in early 20th century, I've heard.)

Adam
  • 63
  • 1
  • 4