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 an...
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.

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.