Has anyone successfully integrated knack data (automated export) with data analytics platforms Tableau or Power BI?
1 Like
Hello everyone,
Here we are working with powerbi as told and shown possible in many answers.
But that method takes a lot of processing from powerbi, specially the pagination issue.
We have been also able to get data to powerbi via exporting the objects as .csv and using that link as the web link to powerbi.
Though it fixes the issue with pagination, it only works in an unsecure page, meaning if i export it from a page without login authentication. Which is not adequate to our project.
So we need to export it from a safe environment (credentials or api key) to csv in a way that powerbi can open it.
I've given many attempts to mix the the method described here with the csv export, as in:
- Picking the export into cvs:
https://us-api.knack.com/v1/scenes/scene_XXX/views/view_XXX/records/export/applications/(...)?type=csv&format=both&page=1&rows_per_page=10&sort_field=field_XXX&sort_order=asc&token=(...)
- And trying to combine it with using the Api keys
" [Headers=[#"X-Knack-Application-ID"="YOUR KNACK ID", #"X-Knack-REST-API-Key"="YOUR KNACK API"] "
Yet I've had no success on doing so,
Could someone try and help me "check" if this other method is possible?
Thanks!
Edit: It is possible, I'm currently experimenting with it.
Jade,
I could not get mine to schedule refresh using the "let loadJSON" method, but I did using info from this post:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
The import function I use looks like this:
(page as number) as table =>
let
Source = Json.Document(Web.Contents("https://api.knackhq.com/v1/objects/object_1/",
[RelativePath="records?rows_per_page=1000&page=" & Number.ToText(page), Headers=[#"X-Knack-Application-ID"="XXXXXXXXXX", #"X-Knack-REST-API-Key"="XXXXXXXXXX"]])),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
...
...
...
in
#"Removed Columns9"
Obviously you want to change "object_1" to your object, and the "XXXXXXXXXX" values to your own API keys, but that is how I got scheduled refreshes to work in Power BI with my Knack data.
Hi everyone,
I would like to schedule a refresh on Power Bi Web service but the problem is that the Power BI service wants to validate the URI before it commits to refreshing the data source. Because the URI isn’t static, there is no URI to validate. So I end up with this error:
Query contains unsupported function. Function name: Web.Contents
So I've tweaked my function using RelativePath like suggested 8812151427 on August 11, 2017 and get this:
let loadJSON= (Object as text, Page as text) =>
let
Source = Json.Document(Web.Contents("https://api.knack.com/V1",
[Headers=[#"X-Knack-Application-ID"="xxxxxxxxxxxxxx", #"X-Knack-REST-API-Key"="xxxxxxxxxxxxxxxxxxxxxx"],
RelativePath="/objects/object_" &Object& "/records?page=" &Page&"&rows_per_page=1000"])),
records = Source[records],
#"Converti en table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"
in
loadJSON
But it doesn't work and I don't know why. (It work perfectly well on Power BI desktop)
19853025308 It seems to have worked for you, do you see what I've done wrong?
Thanks!
Wont be easier to just use the advanced editor to insert into the code the new field that must be accessed by the api call? If yes, does anyone know which part of code should be changed for that work?
Also, I´m begging to use a lot of data with power bi and knack and noticed that it take like 40 min just to update the datasets. Did anyone find a solution to make this refresh faster?
Thanks Steven, unfortunately that would not work for what I need in my specific case, but that is a great idea for calling limited data sets. I have gotten around the large refresh times by scheduling Power BI to do a scheduled refresh of the data set over night. This way Power BI is using data through the end of the previous business day. Works fine for I need.
Guilhermebastian2, you would have to either back up in the Applied Steps section of the Query Editor and re-select which fields you want. Keep in mind that this may (and probably will) break any steps you took after that.
Jason,
I'd suggest rather than referencing the object, instead create a table view on a hidden page in your app, apply the 'SOMETHING' filter there, and reference that view in Power BI: https://api.knack.com/v1/pages/scene_key/views/view_key/records.
Guilhermebastian2,
Not quite sure what you mean?
Hi everyone,
After everything is converted as the pictures below, is it possible to add new columns calling back the api? If yes, how can I do that?
Thanks!
I have had a decent amount of success pulling records directly out of Knack and into Power BI thanks to this thread, but am wondering if anyone has any experience in filtering/limiting those records.
I have an object within Knack that had over 60,000 records. Each time the report is refreshed, Power BI seems to re-import all 60k+ records (which also looks like several API calls over that time). It is a simple API request to:
https://api.knackhq.com/v1/objects/object_##/records?rows_per_page=1000
What I am looking for is a way to basically add a filter to that link (something like “?rows_per_page=1000&field_98=”SOMETHING”).
I am also open to suggestion if someone has anything better for importing large record volumes into BI.
Thanks
Hi,
how did you guys deal with the connected objects in Power BI. I was able to retrieve data, but don´t know how to handle the connected and multiple values cells. Anyone know how to work with it?
It's definitely possible. But I believe only licensed Power BI users can actually view the embedded dashboards. You can find the option to get the embed HTML here:
From there, you create a rich text view in Knack and paste the HTML in code-view.
Hi guys, this is awesome. I will definitely try to link my knack to power bi. Is it possible to embed power bi dashboards to my knack app? If yes, can anyone explain me how to do that?
tks!
Thanks @Adam Fryer.
I now got it working as intended. The key turned out to be moving the dynamic part of my URL to the "RelativePath" section.
TheBug, I was having a similar issue with a different web-based api request. I ended up fixing it with that one by following these instructions: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
I haven't tried it with Knack yet, but I imagine it'll work similarly. My function ended up looking like this:
(page as text) =>
let
Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/projects?access_token=#####&account_id=#####",
[Query=[page=(page)]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in #"Converted to Table1"
Has anyone succesfully gotten this to work with automatic updates on PowerBi on the web?
I followed @Miguel Gutierrez guide from August 11th 2017, but I have made the query with parameters in order to query multiple pages (>1000 records) and with that, it seems I can't get PowerBi web to auto update.
Anyone?
Actually one more question. This is related more to the advanced editor in Power BI or Power Query. But does anyone know how to encrypt the API key in step 4 of the tutorial above?
I notice my API key is viewable in my Advanced Editor - and I plan to share my query with others.
RIght now I've come across this page, but haven't gotten down to testing it out. Anyone else know of a good way to encrpyt the API key?
https://seddryck.wordpress.com/2017/06/22/encoding-basic-authentication-in-an-url/
I got mine to work too ! (I just extracted data to Excel). Thank you so much.
On a side note - can we put in filters when extracting the data? Like if I wanted to refresh just data that was uploaded in the past week... ?
This is a fantastic thread, and I am beyond thrilled to have found a way to pull our Knack data into Excel and Power BI for some more advanced analysis.
I'm having the same problem some others have when they tried to add the query to get more than 1000 records.
Here's what my query looks like in the Advanced Editor:
let loadJSON= (Page as text, AmountOfRecords as number) =>
let
Source = Json.Document(Web.Contents("https://api.knack.com/v1/pages/scene_71/views/view_203/records?page="&Page&"&rows_per_page="&AmountOfRecords&"", [Headers=[#"X-Knack-Application-ID"="***************", #"X-Knack-REST-API-Key"="************************"]])),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"field_281_raw", "field_279_raw", "field_280_raw", "field_287.field_201_raw", "field_201_raw", "field_844.field_825_raw", "field_825_raw", "field_1363_raw", "field_283_raw", "field_1563_raw", "field_282_raw", "field_278_raw", "field_753_raw", "field_580_raw", "field_579_raw", "field_366.field_185_raw", "field_185_raw", "field_366.field_710_raw", "field_710_raw", "field_366.field_970_raw", "field_970_raw", "field_366.field_151_raw", "field_151_raw", "field_366.field_163_raw", "field_163_raw", "field_366.field_167_raw", "field_167_raw", "field_366.field_385_raw", "field_385_raw", "field_366.field_1502_raw", "field_1502_raw", "field_366.field_971_raw", "field_971_raw", "field_366.field_152_raw", "field_152_raw", "field_366.field_164_raw", "field_164_raw", "field_366.field_168_raw", "field_168_raw", "field_366.field_386_raw", "field_386_raw", "field_366.field_1503_raw", "field_1503_raw", "field_366.field_972_raw", "field_972_raw", "field_366.field_153_raw", "field_153_raw", "field_366.field_165_raw", "field_165_raw", "field_366.field_169_raw", "field_169_raw", "field_366.field_387_raw", "field_387_raw", "field_366.field_1504_raw", "field_1504_raw", "field_366.field_973_raw", "field_973_raw", "field_366.field_555_raw", "field_555_raw", "field_366.field_388_raw", "field_388_raw", "field_366.field_1493_raw", "field_1493_raw", "field_366.field_1494_raw", "field_1494_raw", "field_366.field_384_raw", "field_384_raw", "field_366.field_346_raw", "field_346_raw", "field_366.field_347_raw", "field_347_raw", "field_366.field_118_raw", "field_118_raw", "field_1331_raw", "field_769_raw", "field_740.field_927_raw", "field_927_raw", "field_740.field_928_raw", "field_928_raw", "field_1654_raw"}, {"Column1.field_281_raw", "Column1.field_279_raw", "Column1.field_280_raw", "Column1.field_287.field_201_raw", "Column1.field_201_raw", "Column1.field_844.field_825_raw", "Column1.field_825_raw", "Column1.field_1363_raw", "Column1.field_283_raw", "Column1.field_1563_raw", "Column1.field_282_raw", "Column1.field_278_raw", "Column1.field_753_raw", "Column1.field_580_raw", "Column1.field_579_raw", "Column1.field_366.field_185_raw", "Column1.field_185_raw", "Column1.field_366.field_710_raw", "Column1.field_710_raw", "Column1.field_366.field_970_raw", "Column1.field_970_raw", "Column1.field_366.field_151_raw", "Column1.field_151_raw", "Column1.field_366.field_163_raw", "Column1.field_163_raw", "Column1.field_366.field_167_raw", "Column1.field_167_raw", "Column1.field_366.field_385_raw", "Column1.field_385_raw", "Column1.field_366.field_1502_raw", "Column1.field_1502_raw", "Column1.field_366.field_971_raw", "Column1.field_971_raw", "Column1.field_366.field_152_raw", "Column1.field_152_raw", "Column1.field_366.field_164_raw", "Column1.field_164_raw", "Column1.field_366.field_168_raw", "Column1.field_168_raw", "Column1.field_366.field_386_raw", "Column1.field_386_raw", "Column1.field_366.field_1503_raw", "Column1.field_1503_raw", "Column1.field_366.field_972_raw", "Column1.field_972_raw", "Column1.field_366.field_153_raw", "Column1.field_153_raw", "Column1.field_366.field_165_raw", "Column1.field_165_raw", "Column1.field_366.field_169_raw", "Column1.field_169_raw", "Column1.field_366.field_387_raw", "Column1.field_387_raw", "Column1.field_366.field_1504_raw", "Column1.field_1504_raw", "Column1.field_366.field_973_raw", "Column1.field_973_raw", "Column1.field_366.field_555_raw", "Column1.field_555_raw", "Column1.field_366.field_388_raw", "Column1.field_388_raw", "Column1.field_366.field_1493_raw", "Column1.field_1493_raw", "Column1.field_366.field_1494_raw", "Column1.field_1494_raw", "Column1.field_366.field_384_raw", "Column1.field_384_raw", "Column1.field_366.field_346_raw", "Column1.field_346_raw", "Column1.field_366.field_347_raw", "Column1.field_347_raw", "Column1.field_366.field_118_raw", "Column1.field_118_raw", "Column1.field_1331_raw", "Column1.field_769_raw", "Column1.field_740.field_927_raw", "Column1.field_927_raw", "Column1.field_740.field_928_raw", "Column1.field_928_raw", "Column1.field_1654_raw"}),
#"Expanded Column1.field_279_raw" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.field_279_raw", {"date_formatted"}, {"Column1.field_279_raw.date_formatted"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.field_279_raw",{{"Column1.field_279_raw.date_formatted", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.field_281_raw", "T#"}, {"Column1.field_279_raw.date_formatted", "Date"}, {"Column1.field_280_raw", "Time"}}),
#"Expanded Column1.field_287.field_201_raw" = Table.ExpandRecordColumn(#"Renamed Columns", "Column1.field_287.field_201_raw", {"formatted_value"}, {"Column1.field_287.field_201_raw.formatted_value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1.field_287.field_201_raw",{{"Column1.field_287.field_201_raw.formatted_value", "Supervisor"}}),
#"Expanded Column1.field_201_raw" = Table.ExpandRecordColumn(#"Renamed Columns1", "Column1.field_201_raw", {"formatted_value"}, {"Column1.field_201_raw.formatted_value"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.field_201_raw",{"Column1.field_201_raw.formatted_value", "Column1.field_844.field_825_raw"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Column1.field_825_raw", "BA"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Column1.field_1363_raw"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Column1.field_283_raw", "Client"}}),
#"Expanded Column1.field_1563_raw" = Table.ExpandListColumn(#"Renamed Columns3", "Column1.field_1563_raw"),
#"Expanded Column1.field_1563_raw1" = Table.ExpandRecordColumn(#"Expanded Column1.field_1563_raw", "Column1.field_1563_raw", {"identifier"}, {"Column1.field_1563_raw.identifier"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded Column1.field_1563_raw1",{{"Column1.field_1563_raw.identifier", "Brand"}, {"Column1.field_282_raw", "Products"}, {"Column1.field_278_raw", "Store#"}, {"Column1.field_753_raw", "Store Type"}, {"Column1.field_580_raw", "City"}, {"Column1.field_579_raw", "Province"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns4",{"Column1.field_366.field_185_raw", "Column1.field_185_raw", "Column1.field_366.field_710_raw", "Column1.field_710_raw"}),
#"Expanded Column1.field_366.field_970_raw" = Table.ExpandListColumn(#"Removed Columns2", "Column1.field_366.field_970_raw"),
#"Expanded Column1.field_366.field_970_raw1" = Table.ExpandRecordColumn(#"Expanded Column1.field_366.field_970_raw", "Column1.field_366.field_970_raw", {"identifier"}, {"Column1.field_366.field_970_raw.identifier"}),
#"Expanded Column1.field_970_raw" = Table.ExpandListColumn(#"Expanded Column1.field_366.field_970_raw1", "Column1.field_970_raw"),
#"Expanded Column1.field_970_raw1" = Table.ExpandRecordColumn(#"Expanded Column1.field_970_raw", "Column1.field_970_raw", {"identifier"}, {"Column1.field_970_raw.identifier"}),
#"Removed Columns3" = Table.RemoveColumns(#"Expanded Column1.field_970_raw1",{"Column1.field_366.field_970_raw.identifier"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns3",{{"Column1.field_970_raw.identifier", "Sku A"}}),
#"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns5",{"Column1.field_366.field_151_raw", "Column1.field_366.field_163_raw", "Column1.field_366.field_167_raw", "Column1.field_366.field_385_raw", "Column1.field_366.field_1502_raw", "Column1.field_366.field_971_raw", "Column1.field_366.field_152_raw", "Column1.field_366.field_164_raw", "Column1.field_366.field_168_raw", "Column1.field_366.field_386_raw", "Column1.field_366.field_1503_raw", "Column1.field_366.field_972_raw", "Column1.field_366.field_153_raw", "Column1.field_366.field_165_raw", "Column1.field_366.field_169_raw", "Column1.field_366.field_387_raw", "Column1.field_366.field_1504_raw", "Column1.field_366.field_973_raw", "Column1.field_366.field_555_raw", "Column1.field_366.field_388_raw", "Column1.field_366.field_1493_raw", "Column1.field_366.field_1494_raw", "Column1.field_366.field_384_raw", "Column1.field_366.field_346_raw", "Column1.field_366.field_347_raw", "Column1.field_366.field_118_raw", "Column1.field_740.field_927_raw", "Column1.field_740.field_928_raw"}),
#"Renamed Columns6" = Table.RenameColumns(#"Removed Columns4",{{"Column1.field_151_raw", "Opening A"}, {"Column1.field_163_raw", "Sampled A"}, {"Column1.field_167_raw", "Sold A"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Opening A", type number}, {"Sampled A", type number}, {"Sold A", type number}}),
#"Renamed Columns7" = Table.RenameColumns(#"Changed Type1",{{"Column1.field_385_raw", "Conversion A"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns7",{{"Conversion A", Percentage.Type}}),
#"Expanded Column1.field_971_raw" = Table.ExpandListColumn(#"Changed Type2", "Column1.field_971_raw"),
#"Expanded Column1.field_971_raw1" = Table.ExpandRecordColumn(#"Expanded Column1.field_971_raw", "Column1.field_971_raw", {"identifier"}, {"Column1.field_971_raw.identifier"}),
#"Renamed Columns8" = Table.RenameColumns(#"Expanded Column1.field_971_raw1",{{"Column1.field_971_raw.identifier", "Sku B"}, {"Column1.field_152_raw", "Opening B"}, {"Column1.field_164_raw", "Sampled B"}, {"Column1.field_168_raw", "Sold B"}, {"Column1.field_386_raw", "Conversion B"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns8",{{"Conversion B", Percentage.Type}}),
#"Expanded Column1.field_972_raw" = Table.ExpandListColumn(#"Changed Type3", "Column1.field_972_raw"),
#"Expanded Column1.field_972_raw1" = Table.ExpandRecordColumn(#"Expanded Column1.field_972_raw", "Column1.field_972_raw", {"identifier"}, {"Column1.field_972_raw.identifier"}),
#"Renamed Columns9" = Table.RenameColumns(#"Expanded Column1.field_972_raw1",{{"Column1.field_972_raw.identifier", "Sku C"}, {"Column1.field_153_raw", "Opening C"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns9",{{"Opening B", type number}, {"Sampled B", type number}, {"Sold B", type number}}),
#"Renamed Columns10" = Table.RenameColumns(#"Changed Type4",{{"Column1.field_165_raw", "Sampled C"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns10",{{"Column1.field_169_raw", type number}}),
#"Renamed Columns11" = Table.RenameColumns(#"Changed Type5",{{"Column1.field_169_raw", "Sold C"}, {"Column1.field_387_raw", "Conversion C"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns11",{{"Conversion C", Percentage.Type}}),
#"Expanded Column1.field_973_raw" = Table.ExpandListColumn(#"Changed Type6", "Column1.field_973_raw"),
#"Renamed Columns12" = Table.RenameColumns(#"Expanded Column1.field_973_raw",{{"Column1.field_973_raw", "Sku D"}, {"Column1.field_1504_raw", "Price C"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns12",{{"Price C", type number}}),
#"Renamed Columns13" = Table.RenameColumns(#"Changed Type7",{{"Column1.field_1503_raw", "Price B"}}),
#"Changed Type8" = Table.TransformColumnTypes(#"Renamed Columns13",{{"Price B", type number}}),
#"Renamed Columns14" = Table.RenameColumns(#"Changed Type8",{{"Column1.field_1502_raw", "Price A"}}),
#"Changed Type9" = Table.TransformColumnTypes(#"Renamed Columns14",{{"Price A", type number}}),
#"Renamed Columns15" = Table.RenameColumns(#"Changed Type9",{{"Column1.field_346_raw", "Total Sales"}, {"Column1.field_384_raw", "Total Sampled"}, {"Column1.field_555_raw", "Opening D"}, {"Column1.field_388_raw", "Conversion D"}, {"Column1.field_347_raw", "Conversion"}, {"Column1.field_1331_raw", "Day"}, {"Column1.field_769_raw", "Month"}, {"Column1.field_927_raw", "Category"}, {"Column1.field_928_raw", "Sub-Category"}}),
#"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns15",{{"Conversion", Percentage.Type}, {"Total Sales", type number}, {"Total Sampled", type number}, {"Column1.field_1494_raw", type number}, {"Column1.field_1493_raw", type number}})
in
#"Changed Type10"
in
loadJSON
Many thanks, Miguel! I tried converting the pages column to text, but that didn't fix the problem.
I did get it fixed this morning though, by removing the number of records per page as a parameter in the function, and simply replacing it with 1000. I assume I must've messed up some syntax that this fixed.
I really really appreciate the support, and all your work in putting your article together; it's been an enormous help!
Hi all,
Thanks for all of your great help on this, everyone.
Miguel, I'm having a problem trying to query more than 1000 records. Specifically I'm getting this error after I invoke the function:
I've tried it from scratch multiple times, but can't figure out what's going on for the life of me! Any help folks might be able to provide would be greatly appreciated.
Adam