Power Query : Use R script to update Excel file then use values in PowerBI

[Grogu69]

Hi everyone,

Usually I find an answer to my issue in the forum, but this time it seems it's very uncommon. Can you help me please ?

I created some measures on PowerBI based on database data which are updated everyday. For multiple reasons, these data can't be saved or historicized directly on the database. So everyday, the data are replaced

I need to historicize some values to create an evolution curve. Actually, I do that with an Excel File, then import data in PowerBI. 

It works very well, but I would like to completely automatize the process.

I know PowerBI is not made for this, but I saw it seems possible with "R Script". I don't know this language at all

I found how create a new Excel File with R script, but not how to edit one.

Concretely, what i want to do : 

- Calculate measures of the day in PowerBI => Done

- Incorporate these values in an Excel File (in a new column of an unique table) ==> Need help please 🙂

- Actualize PowerBI to import automatically the table in the Excel File ==> Done

Do you think it's possible please ? If yes, can you help me with the R script please ?

Or maybe you have a better idea ?

At the end it needs to be fully automatized because it has to work without human intervention, on local powerbi gateway

Thanks for your help (and sorry for my english if there is any mistake)

[AlexisOlson]

The historical data needs to be saved somewhere. If you can't store it in a database, then you need some other approach to store your data automatically. I'd recommend asking out the Power Automate forums for this sort of thing. They can likely help you create an appropriate flow (may something similar to this one?).

[karen578]

I'm new to PowerQuery, but am pretty familiar with R. It seems weird to me that PowerQuery can't do this, and that you'll need to access R. But it's definitely do-able in R with the "openxlsx" package.

Do you have R installed? You'll have to install the openxlsx package too (once) with the install.packages("openxlsx") command. Here's an example that uses the openxlsx functions loadWorkbook, writeData, and saveWorkbook to change the value in cell A2 to 20. r - Change cell value in openxlsx workbook - Stack Overflow

I suspect that your R script run from PQ will look very similar to this. You'll need to add a line at the top to load the openxlsx package.

library(openxlsx)

path <- "C:\\file.xlsx" #Note, "\" is an escape character in R so you need to wrap it with another "\", use the whole path here

.wb <- loadWorkbook(path)

writeData(wb = .wb, sheet = 1, x = 20, xy = (2,1))

saveWorkbook(wb = .wb, path, overwrite = TRUE)

If you provide more info (eg. sample workbook) about the cells you want to update (is it a whole column in a named table? Is it just one cell in a named range?), I can help with the R code to reference the right range to update. The code to do so is a little messy if it can't be hard-coded to always be the same location.