Turn api call into a query function

[GuillaumeB]

I have a call to a the Zoom API I need to turn into a query function to use another table's column as one of the parameters in the URL.

let

apiUrl = "https://api.zoom.us/v2/users/{Userid}/meetings",

token="Token1.token2",

options = [Headers=[Authorization="Bearer " & token ]],

result = Json.Document(Web.Contents(apiUrl , options)),
    meetings = result[meetings],
    #"Converted to Table" = Table.FromList(meetings, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"uuid", "id", "host_id", "topic", "type", "start_time", "duration", "timezone", "agenda", "created_at", "join_url"}, {"Column1.uuid", "Column1.id", "Column1.host_id", "Column1.topic", "Column1.type", "Column1.start_time", "Column1.duration", "Column1.timezone", "Column1.agenda", "Column1.created_at", "Column1.join_url"})
in
    #"Expanded Column1"

 In the M code above, the {userID} part of the URL needs to be every iteration of a colum in another table. I've done this before using :

(UserID as text) =>
Let
Webcontent(URL)
[RelativePath=]

 But in those cases I didn't have an authentication token and the parameter was simply the end of the url, not a middle part. 

Any help is appreciated.

[PhilipTreacy · Accepted solution]

Hi @GuillaumeB 

To use in PBI Service you have to provide a 'static' URL and add the rest of the path using RelativePath.

Change the function to this.

(UserID) => 

let
    apiUrl = "https://api.zoom.us/v2/users/",

    token="Token1.token2",

    options = [ Headers = [Authorization="Bearer " & token ] , RelativePath = Text.From(UserID) & "/meetings" ],

    result = Json.Document(Web.Contents(apiUrl , options)),

    meetings = result[meetings],
    #"Converted to Table" = Table.FromList(meetings, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"uuid", "id", "host_id", "topic", "type", "start_time", "duration", "timezone", "agenda", "created_at", "join_url"}, {"Column1.uuid", "Column1.id", "Column1.host_id", "Column1.topic", "Column1.type", "Column1.start_time", "Column1.duration", "Column1.timezone", "Column1.agenda", "Column1.created_at", "Column1.join_url"})
in
    #"Expanded Column1"

Regards

Phil

[GuillaumeB]

Absolute lifesaver, thank you for the help! @PhilipTreacy 

[PhilipTreacy]

@GuillaumeB 

No worries 🙂

[PhilipTreacy]

Hi @GuillaumeB 

Download sample PBIX file with this code

You can turn the API call into a function by putting this code into it's own query, I called the query fxZoomAPI 

(UserID) => 

let

    apiUrl = "https://api.zoom.us/v2/users/" & Text.From(UserID) & "/meetings",

    token="Token1.token2",

    options = [Headers=[Authorization="Bearer " & token ]],

    result = Json.Document(Web.Contents(apiUrl , options)),

    meetings = result[meetings],
    #"Converted to Table" = Table.FromList(meetings, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"uuid", "id", "host_id", "topic", "type", "start_time", "duration", "timezone", "agenda", "created_at", "join_url"}, {"Column1.uuid", "Column1.id", "Column1.host_id", "Column1.topic", "Column1.type", "Column1.start_time", "Column1.duration", "Column1.timezone", "Column1.agenda", "Column1.created_at", "Column1.join_url"})
in
    #"Expanded Column1"

If you wish you could take the steps from meetings onward and leave them in your main query so that you do all of that processing after all the API calls are made.

I created some dummy data to represent UserID's

In your main query you'd call the function by adding a Custom Column like this to get API data for each of these UserID's

Post back if you get stuck.  @ mention me or I'llmiss your reply.  Type @ then select my name

Regards

Phil

[GuillaumeB]

@PhilipTreacy Awesome the M code worked!
However when I try to refresh the data from the service it gives me an error about how Dynamic Queries can't be refreshed from the service.

Apologies because it's been a few years since I last worked with restAPI within PBI but I remember there being a workaround for this, are you familiar with it?