Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DBirdmanAR
Regular Visitor

Connecting PowerBI to REDCap via API with token

Hi All,

 

I am trying to connect PowerBI to REDCap (see https://projectredcap.org/) which is a PHP/MySQL application that has an API which uses HTTP POST to allow users to pull data with a valid token.

 

I have been having a lot trouble formatting the PowerBI call.  I know I have the proper permissions because I can use R to query my API.  This is a valid R call to the API

 

 

 

 

library(RCurl)
 result <- postForm(
     uri='MY APIs URL',
     token='MY VALID TOKEN',
     content='project',
     format='json',
     returnFormat='json'
 )
 print(result)

 

 

 

 

and I get a good response.  However I cannot figure out how to translate this to PowerBI.  

 

What is interesting is that when I run this code I get an error which states I get an error "Expression.Error: Access to the resource is forbidden." 

 

 

 

let
     url = "MY APIs URL",
     body = "{ 
     ""token"":""MY VALID TOKEN HERE"", 
     ""content"":""project"", 
     ""format"":""json"", 
     ""returnFormat"";""json""
     }",

     Source = Json.Document(Web.Contents(
          url,
          [Headers = [#"Content-Type"="application/json"],
          Content=Text.ToBinary(body)]))
in
     Source

 

 

 

 

 When I click on "Edit Settings" I get this popup window which I accept as "OK" but I still am getting and error  "Web.Contents failed to get contents from 'My APIs URL' (501): Not Implemented".    I know I have permissions because I can get R to run the code and I get a response.  I think I am not formatting the M code correctly but I am not sure how.

Error1.png

 

9 REPLIES 9
spardasa
Regular Visitor

@DBirdmanAR @PradeepTh 

Please refer to this video for detailed steps: https://www.youtube.com/watch?v=S3yNJWfFo8A

You can export the manual connector from here: https://github.com/mandevuMan/REDCap_PowerBI_Connector

 

I was able to connect successfully. Please feel free to let me know if you have any issues.

Anonymous
Not applicable

Can anyone outline the steps took to get this to work?

 

I currently use the GitHub connector that allows for local refresh but does not allow for auto refresh online. I'm not sure which connector you guys were using for the code listed below.

DBirdmanAR
Regular Visitor

Hi All, I found a public REDCap API (see https://cran.r-project.org/web/packages/REDCapR/vignettes/TroubleshootingApiCalls.html which midway down says 

 

If it helps to start with a different REDCap server, you can use this dummy project containing fake data hosted by the OUHSC BBMC. The url is https://bbmc.ouhsc.edu/redcap/api/. There are three key-value pairs: (1) the ‘token’ is 9A81268476645C4E5F03428B8AC3AA7B, (2) the ‘content’ is record, and (3) the ‘format’ should be CSV. When checking your own server, the token value should change, but the content and format should not. It should return five records in a CSV format. The ‘status’ should be 200 OK

 

I installed Postman in Chrome and tried the dummy API at bbmc and both it, and my API work within Postman.  It is clearly an error that I am having in trying to set up PowerBI.

 

I tried to set up a very simple connection like this but I still get an error Details: "Web.Contents failed to get contents from 'https://bbmc.ouhsc.edu/redcap/api/' (501): Not Implemented"

 

Error2.png

Hi, 

I am facing similar issue. How this was solved? Pleas share steps.

 

Regards,

Pradeep

It was two issues:

  1.   I need to change to x-www-form-urlencoded
  2.  There were serious formating errors with both the Headers and the Content
let
    actualUrl = "https://bbmc.ouhsc.edu/redcap/api/",
    record  =[token="9A81268476645C4E5F03428B8AC3AA7B",
    content="record",
    format="csv"
    ],
    body = Text.ToBinary(Uri.BuildQueryString(record)),
    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"], Content=body],
    result = Web.Contents(actualUrl, options)
    
in
    result

 

Hi @DBirdmanAR ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @DBirdmanAR ,

 

Your query works well on my side, we tried to convert the result with csv format and get the dummy data as the document. If you have any other questions , please kindly ask here and we will try to resolve it.

 

4.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Perfect for my needs - a year later but THANKS!! Life Saver.

Is there a limit on the number of fields I can connect to?  I have 250!

 

Cheers,

John

Hi guys,

I'm new in power BI and I wanna make the same connexion RedCap => power BI above  with token

Can you please show me what I must set   in parameters header :

Url :...

token:..

content:record

format:csv

I download the file example but i didn't found the way 😞

Thank very much for your help

Diana

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.