library ssh in R

[stchln]

Hello all,

I have a simplified R script below that works fine in Power BI Desktop but no more in Power BI Service, the reason is that the library ssh I am using is not supported in Power BI Service ("Error in library("ssh") : there is no package called 'ssh'")

For some reason, despite ssh library  CRAN - Package ssh (r-project.org) is an official package it is not part of the list of supported ones Learn which R packages are supported - Power BI | Microsoft Docs

Any idea for another solution? I am thinking about RCurl library but I don't see why we need to provide both public and private keys in scp_upload and I don't see how to build a temporary pkey file (containing private key) and to pass it to scp_upload...

I think I need help to port the below lines from ssh to RCurl (if RCurl is a good B-plan candidate)

# pkey: Cloud private key .pem format
pkey<-tempfile()
write("-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEA5Vl3nEh3NZRE1p2ek3Xb81GtOrwgqwCtFsS9oUeT5pjmkYmh
../..
-----END RSA PRIVATE KEY-----",pkey)

# csv generated file, dummy case
csv1<-tempfile(pattern="nodes",fileext=".csv")
write.csv(1:10,csv1)

# upload file using ssh private key
library("ssh")
session<-ssh_connect("root@10.157.211.173",keyfile=pkey)
scp_upload(session,csv1,to="/var/lib/neo4j/import")

[stchln · Accepted solution]

I think I found a way, based on One Drive + Power Automate to upload on Sharepoint or Task Scheduler to upload a server using curl or pscp

Example : source is writetonedrive.csv file 2 columns F and Rem

"Fid","Rem"
"FA",14
"FB",19

1/ in Power BI report, open source located in your OneDrive. Take care of path format that should not contain the /:x/r/ added by default for browser compatibility matrix

If your path has the /:x/r/ you don't have OAuth2 as credential in Power BI Service

let
Source = Csv.Document(Web.Contents("https://nokia-my.sharepoint.com/personal/stephane.../writetoonedrive.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fid", type text}, {"Rem", Int64.Type}})
in
#"Changed Type"

2/ in R script, here simple code is just substracting 1 to Rem field

# basic code
dataset$Rem<-dataset$Rem-1

# saving in same writetoonedrive.csv (update)
write.table(dataset,file='C:/Users/chalon1/OneDrive.../writetoonedrive.csv', sep=",", append=F, row.names=F)

library(gridExtra)
gridExtra::grid.table(dataset)

3/ in Power BI Service, you can edit the credentials using OAuth2 and you can schedule a refresh.

[lbendlin]

Power BI is a sink, not a source. Use different tools to push your data into other systems.