Why doesn't Power BI recognize my R output as an excel document?

[gbAct10]

I use the following function from the openxlsx package to export my dataframe to a local folder as an xlsx file:

write.xlsx(data frame, path, overwrite = TRUE)

I want to pull it into a Power BI project, but when I do, I get the following error from Power BI:

"The input couldn't be recognized as a valid Excel document."

But when I go into the file, save it and close, it then works fine.

Thank you

[nphoeppner]

I ran into the same issue, and it appears to be an issue with the openxlsx package:

https://github.com/ycphs/openxlsx/issues/231

The discussion focuses on SPSS, but it appears to be equally relevant to Power BI. Hopefully it is fixed soon!

[lbendlin]

wouldn't that be more of a question for the maintainers of the openxlsx package? Maybe they save in .xls format and only slap a .xlsx extension on in the hope nobody notices.

Another question would be why you involve that package in the first place. It would likely be more performant if you dump the dataframe into a CSV file and then consume that in Power BI.

[brendenM0264]

An R script can do more than just prepare data to be loaded into PBI.  It isn't only another way of cleaning code like M.  It can also write the data out.  So in an example where this can be very beneficial is to write an R script to pull data in, clean it, and save a portion to a folder as an excel file.  Then continue cleaning data and preparing it for PBI.  Either more data sets or still working with the one that was saved out.   
This R script then can be ran within PBI, and scheduled out. 
This can be done now, but can NOT save out as Parquet or Excel since both the Arrow Package or the Openxlsx Package at this time are NOT supported by R packages within PBI.