Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  API call request using Bearer Token from Power BI M Query

    Posted Jan 13, 2022 10:39 PM
    Hi Gurus,

    I am looking for help to create Power BI M Query to pull API data using Bearer Token(Dynamic with user name password). I was able to generate the Token but unable able complete to pull the data from the application.

    I am able to pull this from Python, but am unable to complete it M Query. Please help to complete this. I have to match below python code with M Query.

    M-Query :

    let
    GetJson = Web.Contents("https://xxxxxx-cloud.com/api/token",
    [
    Headers = [#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
    Content = Text.ToBinary("username=XXXXXX&password=XXXX&grant_type=password")
    ]
    ),
    FormatAsJson = Json.Document(GetJson),
    #"Converted to Table" = Record.ToTable(FormatAsJson),
    access_token = #"Converted to Table"{1}[Value],
    AccessToken = TokenResponse[access_token],
    AccessTokenHeader = "Bearer " & AccessToken,
    data_url = "https://xxxxxx-cloud.com/api/v2/objects",
    data_body = "{
    ""authorization"": """& AccessTokenHeader & """,
    ""content-type"": ""application/json""
    }",
    GetGroups = Web.Contents(data_url, [Headers=[#"Content-Type"="application/json"]])

    in
    GetGroups
    -----------
    Python : --> Python is working fine. -->
    import requests
    import json
    token_url = "https://xxxxxxxcloud.com/api/token"
    access_token_payload={'grant_type': 'password',
    'username': 'XXXXX',
    'password': 'XXXXXXXXX'}
    files=[
    ]
    access_token_headers = {
    'Content-Type': 'application/x-www-form-urlencoded'}
    get_access_token = requests.request("POST", token_url, headers=access_token_headers, data=access_token_payload, files=files)
    access_token = get_access_token.json()['access_token']
    query_report_url = "https://xxxxxxxcloud.com/api/v2/objects"
    query_report_payload=json.dumps({
    "EmptyValues": True,
    "Report": "prod_heir_1",
    "ReportResult": "DataSet",
    "Limit": 4
    })
    query_report_headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer ' + access_token
    }
    report_response = requests.request("POST", query_report_url, headers=query_report_headers, data=query_report_payload)
    print(report_response.text)
    ---------------
    Pulendar vadde

    ------------------------------
    Pulendar vadde
    IT consultant
    ------------------------------


  • 2.  RE: API call request using Bearer Token from Power BI M Query

    Bronze Contributor
    Posted Jan 14, 2022 02:42 AM
    This is what I've written to do this.  First make a call to get the token, convert the result into something usable, then use it to query the data.  I've generalised the website, username, password.

    let
    GetJson = Json.Document(Web.Contents("https://remote.website.com/api/auth/token",
    [Headers=[Accept="application/json", #"Content-Type"="application/json-patch+json"], Content=Text.ToBinary("{ ""EmailAddress"": ""user@xxx.com"", ""Password"": ""p4ssword"", ""RememberMe"": true}")])),
    #"Converted to Table" = Record.ToTable(GetJson),
    #"Filtered rows" = Table.SelectRows(#"Converted to Table", each [Name] = "accessToken"),
    #"Expanded Value" = Table.Last(#"Filtered rows"),
    access_token = List.Last(Record.FieldValues(#"Expanded Value")),
    FormatToken = "Bearer " & access_token,
    AuthCall = Html.Table(Web.Contents("https://remote.website.com/",
    [Headers=[#"Authorization"=FormatToken]]), {}),
    #"Changed Type" = Table.TransformColumnTypes(AuthCall, {}),
    GetData = Json.Document(Web.Contents("https://remote.website.com/api/investigation", [Headers=[Accept="text/plain", Authorization=FormatToken]]))

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 3.  RE: API call request using Bearer Token from Power BI M Query

    Posted Jan 14, 2022 12:35 PM
    Thank you James for the response, somehow still needs the matching part of M query for the below.

    query_report_payload=json.dumps({
    "EmptyValues": True,
    "Report": "prod_heir_1",
    "ReportResult": "DataSet",
    "Limit": 4
    })

    ------------------------------
    Pulendar vadde
    IT consultant
    ------------------------------



  • 4.  RE: API call request using Bearer Token from Power BI M Query

    Posted Jan 16, 2022 08:26 PM
    Hi Everyone,

    Finally, I was able to get this work done. But now the issue is, this solution is only working in  Power BI Desktop, facing an error while refreshing from Power BI Service.

    Solution :

    let
    GetJson = Web.Contents(
    "https://xxxxxxx/api/token",
    [
    Headers = [#"Content-Type" = "application/x-www-form-urlencoded;charset=UTF-8"],
    Content = Text.ToBinary("username=xxxxx&password=xxxxxxxx&grant_type=password")
    ]
    ),
    FormatAsJson = Json.Document(GetJson),
    #"Converted to Table" = Record.ToTable(FormatAsJson),
    access_token = #"Converted to Table"{1}[Value],
    AccessTokenHeader = "Bearer " & access_token,
    data_url = "https://xxxxxxx/api/v2/objects",
    data_body = "{
    ""authorization"": """
    & AccessTokenHeader
    & """,
    ""content-type"": ""application/json""
    }",
    body
    = "{
    ""EmptyValues"": ""true"",
    ""Report"": ""xxx_Capability_Hierarchy_1"",
    ""ReportResult"": ""DataSet"",
    ""Limit"": ""40""
    }",
    Source = Json.Document(
    Web.Contents(
    data_url,
    [
    Headers = [#"Authorization" = AccessTokenHeader, #"Content-Type" = "application/json"],
    Content = Text.ToBinary(body)
    ]
    )
    ),
    #"Converted to Table1" = Record.ToTable(Source),
    Value = #"Converted to Table1"{0}[Value],
    #"Converted to Table2" = Table.FromList(
    Value,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
    ),
    #"Expanded Column1" = Table.ExpandRecordColumn(
    #"Converted to Table2",
    "Column1",
    {"ClassName", "RefStr", "Values", "NestedObjects"},
    {"ClassName", "RefStr", "Values", "NestedObjects"}
    ),
    #"Expanded Values" = Table.ExpandRecordColumn(
    #"Expanded Column1",
    "Values",
    {"c1.L1Name", "c1.L1Description"},
    {"c1.L1Name", "c1.L1Description"}
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Values", {"NestedObjects"}),
    #"Renamed Columns" = Table.RenameColumns(
    #"Removed Columns",
    {{"c1.L1Name", "L1Name"}, {"c1.L1Description", "L1Description"}}
    )
    in
    #"Renamed Columns"
    ------------
    ERROR in Power BI Service --> Scheduling Error:
    _____________


    ------------------------------
    Pulendar vadde
    IT consultant
    ------------------------------