R script as data source fails when sending or recieving too many JSON messages

[BenjaminG]

Hi Gurus,

I've come across a strange issue when using an R Script as a data source.

Within the R script I'm using a REST API to request data from a URL. The JSON message that is received is converted into an R data frame. When using the script as a datasource in PowerBi, this only works if I set the verbose settings to FALSE i.e. if I was using R Studio, no messages (in particular data in) are sent to the console. However, I do not have the option to switch off the incoming/outgoing JSON header messages (which is going to come back to bite!).

Switching the verbose settings to FALSE works for a single request, however, I need to put the request into a loop and keep requesting  more data until the API gateway indicates there is no more data to be received. PowerBi appears to fail when in the script five or more request/replies are sent/received.

Just from observation, I assume this is to do with the JSON Header messages piling up. I can run the loops four times and it works, if I run it five times, I get a permanent spinning wheel. Note the script runs fine in R Studio. 

I understand in the short term I may need to find a solution in R (I've been trying!) and I'm opening a request on an R forum too. However, I do believe this is fundamentally an issue with PowerBi (Power Query) and R.Execuate command not handling the "console" messages.

Any help would be appreciated.

Thanks,

Ben.

[v-juanli-msft]

Hi @BenjaminG 

Basic guide how to loop url data in power query:

with Power query itself: https://community.powerbi.com/t5/Desktop/script-running-looping-URLs-and-get-data/td-p/135980

using python script: https://stackoverflow.com/questions/53558837/python-loop-to-pull-api-data-for-iterating-urls

R script: https://stackoverflow.com/questions/40956601/how-to-iterate-through-url-in-r

If it is convenient of you to share your error message or your R script.

Best Regards

Maggie

[BenjaminG]

Hi Maggie,

Thank you for the reply.

In fact the R script from stack overflow was posted by me!! (comedy 😊) . I rephrased the heading to reflect the post being a question not a tip or statement.

I could switch to Python or M, though I'm not so familiar with those languages, so in the short term I'm going to run the R script from R Studio and build a single CSV file I can load into PowerBi.

Regarding the error message. There isn't an error message, when it tries to connect to R, it simply freezes (spinning wheel) and I have to reset the script to the simpler version and refresh. Though I suspect the previous R.Execute command is still running as it doesn't seem to relinquish the memory.

Regards,

Ben.