API and Retrieving data.

[DPCCGF]

Hello, wondering if there is a way to get information via an API URL that inserts data into the URL.  Here is what I have:

I have an API connection in the Desktop that retrieves "training sessions".  One of the fields is the Session ID.  Let's call it ABC3.  Each row is a Session and each row has a unique Session ID.  To get the details of a session I need to configure a API call that looks like the below:

https://xxxxx.cnf.io/api/v2/sessions/ABC3/data - with the ABC3 data being the variable.  I can do this in the desktop by doing an API call with the URL as noted below.  However.......

Is there a way to have this variable passed within Power Query to create a table via inserting these unique keys.  Or is there a way within a report to click the Session ID in the row of data, then have it do a GET to the backend database?  I would prefer to get it all into the data model as it is not a very big set of data.  

Just looking for options.

TIA

[DPCCGF]

This is working great. So I might as well as you secondary question which has nothing to do with my original question but it does with this file.  Now that I have it expanded, do you know how to get the column (1) to be able to be expanded like I can do with column (2).  

[NaveenGandhi]

@DPCCGF 
This is a tricky one beacuse list and records expand in different ways. I have attached a file where i tried enacting your problem and if you try to expand the two tables that i have split from a single table(split a big table with list and record in consective row into 2 different tables.).

This data expansion will be uneven. It would be helpful if you can share sample data to understand the situation better.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[DPCCGF]

Thank you.........so I have a question.  If I have M code like below with the Item in RED being the Session ID, do I put your API Code in this same query but right after Expanded data.data1 step?

let
Source = Json.Document(Web.Contents("https://XXXX.cnf.io/api/v2/sessions", [Headers=[#"API-Key"="XXXXXXXXXXXXXXXX"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandRecordColumn(#"Converted to Table", "data", {"count", "data"}, {"data.count", "data.data"}),
#"Expanded data.data" = Table.ExpandListColumn(#"Expanded data", "data.data"),
#"Expanded data.data1" = Table.ExpandRecordColumn(#"Expanded data.data", "data.data", {"id", "external_id", "name", "url", "location", "date", "start_time", "end_time", "access_code", "moderator_password", "checkin_required", "checkin_code", "checkout_required", "checkout_code", "delivery_format", "presenters", "learning_objectives", "fields_of_study"}, {"data.data.id", "data.data.external_id", "data.data.name", "data.data.url", "data.data.location", "data.data.date", "data.data.start_time", "data.data.end_time", "data.data.access_code", "data.data.moderator_password", "data.data.checkin_required", "data.data.checkin_code", "data.data.checkout_required", "data.data.checkout_code", "data.data.delivery_format", "data.data.presenters", "data.data.learning_objectives", "data.data.fields_of_study"})  

RIGHT HERE?
in
#"Expanded data.data1"

[NaveenGandhi]

@DPCCGF 

Yes, it should look something like below.

let
Source = Json.Document(Web.Contents("https://XXXX.cnf.io/api/v2/sessions", [Headers=[#"API-Key"="XXXXXXXXXXXXXXXX"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandRecordColumn(#"Converted to Table", "data", {"count", "data"}, {"data.count", "data.data"}),
#"Expanded data.data" = Table.ExpandListColumn(#"Expanded data", "data.data"),
#"Expanded data.data1" = Table.ExpandRecordColumn(#"Expanded data.data", "data.data", {"id", "external_id", "name", "url", "location", "date", "start_time", "end_time", "access_code", "moderator_password", "checkin_required", "checkin_code", "checkout_required", "checkout_code", "delivery_format", "presenters", "learning_objectives", "fields_of_study"}, {"data.data.id", "data.data.external_id", "data.data.name", "data.data.url", "data.data.location", "data.data.date", "data.data.start_time", "data.data.end_time", "data.data.access_code", "data.data.moderator_password", "data.data.checkin_required", "data.data.checkin_code", "data.data.checkout_required", "data.data.checkout_code", "data.data.delivery_format", "data.data.presenters", "data.data.learning_objectives", "data.data.fields_of_study"}),

Api = my code (instead of session in my code replace with your column name inside the [ ] )
in
Api

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn

[NaveenGandhi]

Hi @DPCCGF 

Below PBIX has a solution for this, Casically we can use m code to pass each session id from a table into the API format. 

Mine will throw error as the API is dummy, Feel free to test wiht yours, might need additional functions depending on how the data looks for each session.

Let me know if this helps.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!