Power Query API source de données WEB-token

[djam28]

Bonjour,
Je souhaiterais récupérer des données dans excel en exécutant une requête à partir du web. Je dispose d'une URL et un token :
url: https://bbmc.ouhsc.edu/redcap/api/
token : 9A81268476645C4E5F03428B8AC3AA7B
content: record
Format: csv

Seriez-vous comment s'y prendre svp ?
J'ai essayé mais j'ai une erreur :
Connexion impossible : Détails : « Web.Contents n'a pas réussi à obtenir le contenu de « https://bbmc.ouhsc.edu/redcap/api/ » (501) : Not Implemented »

Merci de votre aide
Bonne fin de journée
DE

 

[Dudu2]

Bonjour,
Elle est où la description de l'API ?

 

[djam28]

Bonjour,
La voici:
Description
This method allows you to export a set of records for a project.

Note about export rights: Please be aware that Data Export user rights will be applied to this API request. For example, if you have 'No Access' data export rights in the project, then the API data export will fail and return an error. And if you have 'De-Identified' or 'Remove all tagged Identifier fields' data export rights, then some data fields *might* be removed and filtered out of the data set returned from the API. To make sure that no data is unnecessarily filtered out of your API request, you should have 'Full Data Set' export rights in the project.

URL
https://bbmc.ouhsc.edu/redcap/api/
Supported Request Method
POST
Permissions Required
To use this method, you must have API Export privileges in the project.
Parameters (case sensitive)
Required
token
The API token specific to your REDCap project and username (each token is unique to each user for each project). See the section on the left-hand menu for obtaining a token for a given project.
content
record
format
csv, json, xml [default], odm ('odm' refers to CDISC ODM XML format, specifically ODM version 1.3.1)
type

• flat - output as one record per row [default]
• eav - output as one data point per row
  • Non-longitudinal: Will have the fields - record*, field_name, value
  • Longitudinal: Will have the fields - record*, field_name, value, redcap_event_name

* 'record' refers to the record ID for the project

Merci+++
Bonne soirée

 

[djam28]

Si cela peut vous aider dans votre réflexion, un tuto Power BI avec ce même exemple utilisait power Query en 7 requêtes et ça a fonctionnait : Pourriez-vous me créer un template ?
1-AcuelURL = https://bbmc.ouhsc.edu/redcap/api/
2-record = [token="9A81268476645C4E5F03428B8AC3AA7B",
content="record",
format="csv"
]
3- body = Text.ToBinary(Uri.BuildQueryString(record))
4-Options= [Headers =[#"Content-type"="application/x-www-form-urlencoded"], Content=body]
5- Result = Web.Contents(actualUrl, options)
6- Imported CSV= Csv.Document(result,[Delimiter=",", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.Csv])
7-Promoted headers= Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])

Merci++
Bonne soirée

 

[djam28]

il manquait une dernière requête :
8- Changed type = Table.TransformColumnTypes(#"Promoted Headers",{{"record_id", Int64.Type}, {"name_first", type text}, {"name_last", type text}, {"address", type text}, {"telephone", type text}, {"email", type text}, {"dob", type date}, {"age", Int64.Type}, {"sex", Int64.Type}, {"demographics_complete", Int64.Type}, {"height", type number}, {"weight", Int64.Type}, {"bmi", type number}, {"comments", type text}, {"mugshot", type text}, {"health_complete", Int64.Type}, {"race___1", Int64.Type}, {"race___2", Int64.Type}, {"race___3", Int64.Type}, {"race___4", Int64.Type}, {"race___5", Int64.Type}, {"race___6", Int64.Type}, {"ethnicity", Int64.Type}, {"race_and_ethnicity_complete", Int64.Type}})

 

[Dudu2]

J'ai essayé ça mais ça ne marche pas, je ne sais pas comment paramétrer les bidules.
Response:
<?xml version="1.0" encoding="UTF-8" ?><hash><error>You do not have permissions to use the API</error></hash>

 

[djam28]

Bonjour Dudu,
Merci en tout cas, peut-être en Power Query fonctionnerait.
DE