Power BI or Tableau Integration

Miguel - thanks for an excellent guide to querying an object! I have 10 objects to query, and I switch between an archive app (to test on old data) and a live app (which is cleaned out once every year), so I've set up a function that lets me change the code references in one place only - thought I'd share it here.

First, I set up a function (just create a blank query and paste the code over the code that's shown there, then replace the text in bold with your own application id/API key):

let
fnObj = (ObjectNum, Records as any) => let
Address="https://api.knack.com/v1/objects/object_"&Number.ToText(ObjectNum)&"/records?rows_per_page="&Number.ToText(Records),
Source = Web.Contents(Address, [ Headers = [#"X-Knack-Application-Id" = "xxxxx", #"X-Knack-REST-API-Key" = "yyyyyy"]])
in
Source
in
fnObj

Then, to create the connection to my object, I enter the object number and the number of records in the function window and click "invoke". After that, we're back in Miguel's guide ("Click “List” to expand ....).

Beware, though: during testing and development in Power Query, I've managed to reach the max limit for API calls, so be thoughtful about how often you run that refresh..

Miguel, this is perfect!
The Remove Errors was the missing link for me! Thank you heaps.

Stephen - I still have to polish this article, but I explain here how to do it for querying an object: https://medium.com/@mgr/querying-a-knack-object-from-excel-8f2b7163d81b

No worries Steven, glad to be of help!

Miguel, would be awesome to get a similar step-by-step for handling more than 1000 records, and that way I can merge into one single tutorial. I had trouble getting Matt Masson's tutorial on "iterating over an unknown number of pages" to work, so would be great to get one in relation to Knack data.

This covers: Linking Knack to Power BI and Handling objects/views with more than 1000 records, however I was wondering if you had an easy solution for editing existing Power BI queries to include an extra fields of data that may have been added.
From what I understand, this means either editing the query manually by code, or creating a query from scratch. Any suggestions for an easier approach would be awesome.

I successfully was able to query over 1000 records by using functions. I tested it on Power BI, excel power query, python... So I'll be happy to help if someone is having trouble with it. What's described in this link is most of what needs to be done: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

Tremendous! Thanks for updating so quickly. I see you handled the import much more elegantly than I. The extra detail in organizing the data using query editor helped me a ton!

 

Sorry Steven, I edited the original comment to incorporate the part about 1000 records rather than 25 and upon submitting, it disappeared! Luckily I had saved an offline version. I've reproduced it below. What this tutorial doesn't do is deal with views or objects that have more than 1000 records, which I'm still trying to solve myself. Brad Stevens mentions the following tutorial above which deals with pagination which I still haven't got working for myself: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ 

Linking Knack to Power BI

1. Reference your Application ID and API key in the API & Code section in Knack.

2. Reference your Page (Scene) number and View number in the Pages section by editing the View you wish to extract data from. You can see the scene and view number in your browser URL (outlined in red below).

3. In Power BI, click on the Get Data > Web from the top toolbar.

4. Select the Advanced radio button to expand the input. In URL parts type: https://api.knack.com/v1/pages/scene_1/views/view_1/records?page=1&rows_per_page=1000, replacing scene_1 and view_1 with your desired scene and view numbers respectively. In the HTTP request header parameters (optional) section, type:
X-Knack-Application-ID [Your Application ID]
X-Knack-REST-API-Key [Your API Key]
Once this is done, click OK. See image below for what the input will look like.

5. In the table in the center of the screen, click the List link (outlined in red) to expand the records.

6. At the top-left of the screen, click the To Table button. When the To Table dialog appears, keep the defaults and click OK to proceed.

7. At the top-right of the table in the center of the screen, click the Split button (outlined in red). Keep all the columns ticked in the next window and click OK to proceed. This will now expand to all column data in the View, albeit a little incomprehensible.

8. Any column that references another Object will display a List link. To show the values, click on the Split button (same as before) in that particular column header and select Expand to New Rows. This will now change the rows to display a Record link instead of List.

9. Repeat the same step above in clicking the Split button and when prompted, only keep identifier ticked in the list. Click OK to proceed. This will now display the proper values as stored in the View.

10. Repeat Step 7 through to Step 9 for all columns containing List values to get the stored values.

11. Remove any column you don't require by right-clicking the column header and clicking Remove.

12. Rename any column by double-clicking the column header, entering a name, and pressing Enter.

13. Once you are happy with your data, click the Close and Apply button at the top-left of the screen.

14. Your data records can now be accessed in the list at the right of the page.

Let me know if you run into any problems. Happy reporting!

1 Like

Just want to keep the thread tidy. Stephen Chapman, did you delete your original tutorial? It's no longer part of this thread.

I'm returning to this after some time away - and I can't figure for the life of me how to push table info into Power BI using the web connector of Power BI.

I've read through the API documentation of knack, but my background isn't in coding, so I'm struggling.

Hi Alex, forgot to mention one essential part in my tutorial originally. Make sure you add the following to the end of the URL part field:
https://api.knack.com/v1/pages/scene_1/views/view_1/records?page=1&rows_per_page=1000

This will only retrieve 1000 records, which is the maximum Knack allows. Any more will need some code written to grab individual pages.
I'll update the original post now.

Hello,

I make the connection and I can extract the columns that I require, however I could only get 25 records. I change the view settings to 100 records and it was possible to extract 100, but could not synchronise anymore.

Then I change the address directly to the master table, but again only extract the first 25 records.

How can I synchronize the entire table?

I finally was able to refresh Knack data from Power BI Web. I can add more information if someone still have problems making it work, but this is essentially the key:

On Power BI Desktop, following the steps described above, you'll end up with code similar to this one:

Source = Json.Document(Web.Contents("https://api.knackhq.com/v1/objects/object_2/records",
[Headers=[#"X-Knack-Application-ID"="YOUR KNACK ID", #"X-Knack-REST-API-Key"="YOUR KNACK API"]]))

But when you publish this into Power BI Web, you'll get the errors that I also described above. The solution is to manually modify this query, and use a relative path, splitting the URL in two parts, which would look like this:

Source = Json.Document(Web.Contents("https://api.knackhq.com",
[Headers=[#"X-Knack-Application-ID"="YOUR KNACK ID", #"X-Knack-REST-API-Key"="YOUR KNACK API"]
, RelativePath="/v1/objects/object_2/records"]))

I have no idea why, but the first way only works on Power BI Desktop, and the second way works in both Power BI Desktop and Power BI Web. I've been testing it for a couple days and it works like a charm!

Just get sure, after publishing into Power BI Web, to set anonymous authentication for your Knack dataset, here:

The online web refresh is essential! I'm very tempted to migrate my company from our Excel dummy database to a proper online solution with Knack but we rely heavily on PowerBI to connect to other data sources as well.

The problem I noticed is that Power BI Web doesn't seem to be able to sync with Knack on the same way that Power BI Desktop and Excel do. Which this means is that when you publish a report from Power BI Desktop, the Knack datasources won't be able to refresh. Here is the error you get: 

And here is the reason, Power BI Desktop and Excel are compatible with HTTP header requests:

 

But Power BI Web is not: 

So Power BI Web doesn't have a way to authenticate into Knack. Hopefully Microsoft add support for HTTP header requests... Or is there an alternative way of authenticating into Knack?

The workaround is to have a computer that takes care of refreshing Knack datasources, and having Power BI Web sync with that computer. But of course this is not ideal.

This is the last piece of the puzzle for having Power BI web based reports that sync with Knack datasources!

Awesome stuff thanks - I was just starting to look at this.

This page covers handling multiple pages of data: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ 

@Stephen, it's actually the exact same process that you described. You go here, and then follow the same steps than on Power BI. 

 

If you have any links regarding querying to Excel, would be much appreciated!
Thanks for the input Miguel.

Great explanation, Stephen!

Worth it to mention that this method also work to query data from Knack into Excel, and that it's also possible to import data straight from an object (instead of a page), by using the following link:

https://api.knackhq.com/v1/objects/object_1/records

Great work! I will give it a try myself and report back

Hi Steven,
Had you found a solution? I am in need of the same.