CODEWITHSUNDEEP
rexcelpowerbi
Joe Crozier
Joe Crozier

Reputation: 1036

Writing .xlsx in R, importing into PowerBi error

I'm experiencing an odd error. I have a large dataframe in R (75000 rows, 97 columns) and I need to save it out and then import it into Power Bi.

At first I just did the simple:

library(tidyverse)
write_csv(Visits,"Visits.csv")

and while it seems to export and looks fine in excel, the csv itself is all messed up when I look at the contents in Power Bi. Here's an example of what I mean:

The 'phase.x' column should only have "follow-up" or "treatment" in that column. In excel, looks great: enter image description here

but that exact same file gets screwed up in Power Bi:

enter image description here

I figured that being a 'comma separated variable' file, there must be some extra comma somewhere, and I saved it as an .xlsx instead.

So, while in excel, I saved that .csv as an .xlsx and it opened great in Power Bi!

Jump forward a moment and instead of write_csv() in R, I use write.xlsx(). But now I get this error:

enter image description here

If I simply go to that file, open it in excel, save it and hit close, that error goes away and it can load into Power Bi just fine. I figure it has something to do with this question on here.

Any ideas on what I might be screwing up as I save it out of R? Somehow I can fix it in R and not have to open and save it every time?

Upvotes: 0

Views: 252

Answers (1)

NickW
NickW

Reputation: 108

In power BI check that your source has ignore quoted line breaks enabled. I've found this is often an issue with .csv files in PowerBI. enter image description here

Upvotes: 1

Related Questions