Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

API calls with Pagination

I almost see the light at the end of the tunnel.   I am able to make a connection through an API call to my data source (Okta in this case) and was very excite until I looked at the data and notice...
Need Help
Show and Tell
Tips and Tricks
gabrielsroka's avatar
gabrielsroka
Frequent Visitor
7 years ago

Hi Anonymous

 

I spent some more time and I got a basic Custom Data Connector to work. It needs more work, but this should get you started.

 

This works with Power BI Desktop (as of now). Excel support may be added by Microsoft in the future.

 

1. In your Documents folder, create a folder called "Power BI Desktop\Custom Connectors" (mine already existed).

2. Download the code from https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq to Okta.pq and save it in the "Custom Connectors" folder.

3. In Power BI Desktop, go to File | Options and settings | Options.

4. Go the Security tab.

5. Under Data Extensions, select Allow any extension to load without validation or warning.

6. Restart Power BI Desktop

 

See https://github.com/Microsoft/DataConnectors for more info.

 

One thing I found by researching and experimenting (that I didn't see in the documentation) is you can use the Okta.pq file, you don't have to create a .mez file (which is a Zip file). You can even edit the Okta.pq file while it's loaded in Power BI Desktop -- this makes development much easier.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Your code works great for my purposes gabrielsroka, many thanks for sharing.

I have this working perfectly combining multiple pages for when the API response looks like this:

 

However some areas of the API give me a response in the following slightly awkward format:

 

 

Ultimately this looks like this:

{

"column_definitions":[

{"ColumnName1":{"type":"Numeric","isNullable":false,"identityColumn":true}},
{"ColumnName2":{"type":"Text","isNullable":true,"identityColumn":false}}],

"row_values":
[[2,"TEST"], [2,"TEST"]] etc

}

 

I can manipulate this format within PowerBI Desktop to extract the data I need but I cannot get the paging to work correctly for this format.  The problem appears to lie somewhere in your List.Generate function after getting the first page & assigning the link value as metadata. 

 

The error I'm getting is "“We cannot convert a value of type Record to type List”.  Any suggestions would be greatly appreciated.

 

  • gabrielsroka's avatar
    gabrielsroka
    Frequent Visitor
    7 years ago

    Hi Anonymous 

     

    Are you connecting to an Okta API? If so, which one? Can you provide a little more detail? Maybe a code sample...