Forum Discussion
Thilbetr
4 years agoFrequent Visitor
Paginated, Throttled, Parameterized API Data Sourcing
Good afternoon, Pooling our awesome community here for thoughts on approach. I am currently sourcing data from an API endpoint using Power Query on PowerBI Desktop. The API isn't the most ide...
Anonymous
4 years agoNot applicable
Hi Thilbetr - here is a video explaining the process: Pagination and DO/WHILE in Power BI / Power Query - YouTube
Thilbetr
4 years agoFrequent Visitor
Here is where I have roughly found myself so far. It doesn't appear to quite be taking... Below is the custom code for the custom invoked function.
= (pst_id_param as number) =>
let
Source = List.Generate( ()=>
[Result = try Function.InvokeAfter(()=> Json.Document(Web.Contents("https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?per_page=500?page="&Number.ToText(pageno), [Headers=[Authorization="Bearer ***secret code***"]])),#duration(0,0,0,1)) otherwise null, pageno=1],
each [Result] <> null,
each [Result = try Function.InvokeAfter(()=> Json.Document(Web.Contents("https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?page="&Number.ToText(pageno+1), [Headers=[Authorization="Bearer ***secret code****"]])),#duration(0,0,0,1)) otherwise null, pageno=[pageno]+1],
each [Result]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"recipient_id", "pst_id", "user", "template", "scheduled_at", "delivered_at", "opened_at", "clicked_at", "replied_at", "attachment_opened_at", "macro_enabled_at", "data_entered_at", "qr_code_scanned", "reported_at", "bounced_at", "ip", "ip_location", "browser", "browser_version", "os"}, {"Column1.recipient_id", "Column1.pst_id", "Column1.user", "Column1.template", "Column1.scheduled_at", "Column1.delivered_at", "Column1.opened_at", "Column1.clicked_at", "Column1.replied_at", "Column1.attachment_opened_at", "Column1.macro_enabled_at", "Column1.data_entered_at", "Column1.qr_code_scanned", "Column1.reported_at", "Column1.bounced_at", "Column1.ip", "Column1.ip_location", "Column1.browser", "Column1.browser_version", "Column1.os"}),
#"Expanded Column1.user" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.user", {"id", "provisioning_guid", "first_name", "last_name", "email"}, {"Column1.user.id", "Column1.user.provisioning_guid", "Column1.user.first_name", "Column1.user.last_name", "Column1.user.email"}),
#"Expanded Column1.template" = Table.ExpandRecordColumn(#"Expanded Column1.user", "Column1.template", {"id", "name", "difficulty", "type"}, {"Column1.template.id", "Column1.template.name", "Column1.template.difficulty", "Column1.template.type"})
in
#"Expanded Column1.template"
- Anonymous4 years agoNot applicable
Thilbetr - wow this looks complex. The only thing that I a surpised by is the way that you have used Web.Contents. It is better to specify the query path separately like the way the header is included.
Web.Contents(
"https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?per_page=500?page="&Number.ToText(pageno),
[Headers=[Authorization="Bearer ***secret code***"]]
)this should look more like this:
Web.Contents(
"https://us.api.knowbe4.com/v1/phishing/security_tests",
[
RelativePath = Number.ToText(pst_id_param) & "/recipients "
Query = [ per_page = 500 , page = Number.ToText(pageno)] ,
Headers = [ Authorization="Bearer ***secret code***"]
]
)