A way to use REST API to Extract Data from Dataset

[duys]

I know this question has been asked, but I was wondering if the community here could possibly give me an alternative solution. My original hopes were to create a C# application that would connect to two different datasets. Where I could filter the data from table from a dataset and push that data into another dataset. However, according to this post back in Sept 2017, it seems this may not be possible: https://community.powerbi.com/t5/Developer/Get-data-using-REST-API/m-p/122055#M4200

Here is my problem. At our company we have been set up with SalesForce, and I can connect to SalesForce quite easily with PowerBI Desktop/Service. Here is my problem:

I am trying to capture, on a daily basis, a certain number of items in my SalesForce objects that have a certain Flag associated with it. So essentially I am after two things:

Date      Number of Tickets In Progress

I can easily set up a basic PBI visual to give me this information, but the problem is that all the objects are dynamic and changes as people throughout our company 'sync' everyday. So the "Number of Tickets In Progress" changes on a daily basis. My objective is to be able to track the "Number of Tickets In Progress" as the days go on. Because the objects are dynamic and always changing, my solution was to create a snapshot, programatically, by using REST API to essential grab the date and the number of tickets in progress, and push that data into a dataset. 

I am not sure if effort has been made to change the functionality of REST API to extract data from datasets, but if not, I would be eternally grateful if someone could help me out with this. I'll try to provide as much extra information should you ask for it.

Thanks.

[duys]

I wanted to clarify to remain unambiguous. The date field which I am after would essentially be the date everytime I query for the Number of Tickets in Progress. So the only data I am wanting to extract from the SalesForce object is "Number of Tickets In Progress"

Thanks.

[Greg_Deckler]

What I would suggest would be to possibly insert an R script step into your query. With that, you should be able to connect to Salesforce per usual in Power BI and then use the R script to call your REST API or load the data into a database somewhere. Not exactly sure of the specific requirements but I have used R script steps successfully to do things like call an Azure ML web service to return predictions on data being loaded through a query in Power BI for example.

[MikeWise1618]

That R-Script technique to call AML looks interesting. Do you have public code that we could look at?

[duys]

I snooped around online to find a REST API for R, but I wouldn't know how to go about putting the Dataset into a dataframe? Any ideas or websites you could point to for that? I am currently looking at https://github.com/olfrost/poweRbi but seems just as limited as the other REST API's out there.

[v-ljerr-msft]

Hi @Greg_Deckler,

Could you try using the R package RForcecom which enables R to query & analyze data from Salesforce.com to putting the SalesForce Data into a dataframe with Power BI? 

Reference:

https://hiratake55.wordpress.com/2013/03/28/rforcecom/

https://www.linkedin.com/pulse/salesforcecom-r-conducting-simple-logit-regression-your-matt-lunkes

Regards