Get data from API with token that expires

[edubcardoso]

Hey there. I hope you are all ok.

I want to get data from an API. I was able to connect to Postman, but I can't connect to Power BI.

First i have to use the POST method, with no Authorization and a Body written in JSON.

url: https://api.prismart.pt/api/Auth/login
body: 

{

  "username": "aaa@aaaa.pt",

  "password": "bbbbb"

}

Sorry i cant show you the credentials

After that, i use a GET method with the token i received from the POST fase as a Bearer Token

url https://api.prismart.pt/api/1040/InferenceDocuments/filter?taxonomy=Fatura&start=2022-09-05&end=2022...

The token expires in 4 hours

How can i put this in a table with Power Query ?

[v-kkf-msft]

Hi @edubcardoso ,

Using the Power BI Rest API as an example, I can use the following code to get access token and then use it to get workspace information in Power Query.

https://community.powerbi.com/t5/Service/Power-Bi-workspace-content-overview/m-p/2348069 

1. Obtain dynamic access token.

() =>
let 
    body = "grant_type=password&&resource=https://analysis.windows.net/powerbi/api&&response_type=code&&client_id=xxxxxxxxxxx&&client_secret=xxxxxxxxx&&username=xxxxxxxx&&password=xxxxxx",
    Data=Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/",
        [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
    access_token = Data[access_token]
in 
    access_token

2. Call Dashboards - Get Dashboards In Group api to return the list of dashboards.

let 
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/{your group id}/dashboards", [Headers=[Authorization="Bearer "& GetAccessToken() ]])),
    value = Source[value]
in
    value

Best Regards,
Winniz

[edubcardoso]

Thank you for your reply.

I followed the first two steps. but I cant invoke the function.

I used the the application cliend if from the app registragions to cliend_id

The secret value from the app registragions to client_secret

And the Username and password from the API credentials

Changed the  

https://login.microsoftonline.com/common/oauth2/token/

to the api url https://api.prismart.pt/api/Auth/login

When i invoke the fuction appears and error:

Ocorreu um erro na consulta ‘’. DataSource.Error: Não foi possível Web.Contents obter conteúdo de 'https://api.prismart.pt/api/Auth/login/' (415): Unsupported Media Type
Detalhes:
DataSourceKind=Web
DataSourcePath=https://api.prismart.pt/api/Auth/login
Url=https://api.prismart.pt/api/Auth/login/

Translated means something like 

An error occurred in query ''. DataSource.Error: Web.Contents could not get content from 'https://api.prismart.pt/api/Auth/login/' (415): Unsupported Media Type
Details: (...)

[edubcardoso · Accepted solution]

I just solved it.

Funciton GetAcessToken

() =>
let
Url = "https://api.prismart.pt/api/Auth/login",
Body ="{ ""username"": ""xxxx"", ""password"": ""xxxx""}",
// Define the POST Request.
Source = Json.Document(
Web.Contents(
Url,
[
Headers=[#"Content-Type"="application/json"],
Content= Text.ToBinary(Body)
]


)
),
JsonOutput = Json.FromValue(Source),
Text = Text.FromBinary(JsonOutput)


in


Text

And

let
Source = Json.Document(Web.Contents("https://api.prismart.pt/api/1040/InferenceDocuments/filter?taxonomy=Fatura&start=2022-08-01&end="&end, [Headers=[Authorization="Bearer "& GetAcessToken() ]])),
    #"Convertido em Tabela1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 Expandida" = Table.ExpandRecordColumn(#"Convertido em Tabela1", "Column1", {"id", "document", "type", "name", "state", "date", "documentId", "resultJson", "successPercentage", "taxonomy", "createdBy", "createdByName", "createdOn", "companyId", "errorMessage"})
in
    #"Column1 Expandida"

Thanks for the help @v-kkf-msft 
I hope this page can help someone in the future.