|
Klassiska filtyper är inte längre tillgängliga för användning från och med januari 2021. Du kan överföra dina klassiska filer eller ladda ner en PDF. Läs mer

Script to Pull Report into PowerBI

Begär demo

Hi All - 

Wanted to share a script that I built to help with pulling in data directly into PowerBI.  Note - you do not need Python or Postman.  Just add to a blank query in PowerBI and update accordingly.

let

    // Get the bearer token

    auth_url = "https://api.app.wdesk.com/iam/v1/oauth2/token",

    client_id = "insert client_id here between quotes",

    client_secret = " insert client_secret here between quotes ",

    body = "grant_type=client_credentials&client_id=" & client_id & "&client_secret=" & client_secret,

    auth_headers = [#"Content-Type"= "application/x-www-form-urlencoded"],

    auth_response = Web.Contents(auth_url, [Headers=auth_headers, Content=Text.ToBinary(body)]),

    auth_json = Json.Document(auth_response),

    access_token = auth_json[access_token],

    // Set the parameters for the API request

    wdesk_base_url = "https://api.app.wdesk.com/platform/v1/graph/",

    report_id = "insert report_id here between quotes ",

    report_format = "/csv",

    url = wdesk_base_url & "reports/" & report_id & report_format,

    // Make the API request using the bearer token

    api_headers = [

        Authorization = "Bearer " & access_token,

        Accept = "text/csv"

    ],

    response = Web.Contents(url, [Headers=api_headers]),

    // Convert the response to a CSV table

    csv = Csv.Document(response,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(csv, [PromoteAllScalars=true])

in

    #"Promoted Headers"

2

Kommentarer

3 kommentarer

Datum Röster
  • Isabel Messore

    Amazing Danielle Okland, thank you for sharing this!

     

    0
  • Chris Omland

    This is great! Thank you for taking the time to share.

    0
  • Sander van Duinen
    • Redigerad

    I would like to echo the others and say: Amazing stuff Danielle! Thanks for taking the time to share :-)

    Second, I wanted to build upon Danielle's awesome work to make Workiva API's even more approachable. Specifically: the code snippet above assumes the API response is text/csv (through adding the header 'Accept = "text/csv"'). However, in a recent case of mine the response came over in JSON regardless. In these cases, the response can parsed in PowerBi as presented below. 

    response = Json.Document(Web.Contents(url, [Headers=api_headers])),
    data = response[data],

        // Convert the response to a table
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"responseProperty1", "responseProperty2"}, {"RenameResponseProperty1", "RenameResponseProperty2"})

        // Note that Workiva API's can return lists/records that require further expansion in PowerBi. This can be achieved through the Power Query Editor.

    in

        #"Expanded Column1"
    1

Du måste logga in om du vill lämna en kommentar.