Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Script to Pull Report into PowerBI

Request 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

Comments

3 comments

Date Votes
  • 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
    • Edited

    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

Please sign in to leave a comment.