Forum Discussion

edubcardoso's avatar
edubcardoso
Advocate I
3 years ago
Solved

Get data from API with token that expires

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": "[email protected]",
  "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
The token expires in 4 hours
 
How can i put this in a table with Power Query ?
Need Help
Tutorial Requests
  • edubcardoso's avatar
    edubcardoso
    3 years ago

    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.

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    3 years ago

    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's avatar
      edubcardoso
      Advocate I
      3 years ago

      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's avatar
        edubcardoso
        Advocate I
        3 years ago

        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.