15

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?

Improve this question
3
  • 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. Commented Jan 24, 2017 at 8:58
  • 2
    Post it as an answer and mark it correct. Maybe it will attract an upvote :) Commented Jan 24, 2017 at 20:15
  • 1
    Glad you didnt delete this question. I needed this. Commented May 5, 2022 at 3:33

2 Answers 2

Reset to default
22

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

6

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.)

Improve this answer

1 Comment

That's Interesting! And yeah, very likely a version issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.