API Queries

[Ivancito111]

Good afternoon dear,

It is the first time that I work with API's in PBI and I do not handle correctly all the concepts/errors that they can carry out.

I have to connect to an API and extract the data of the workers.

API documentation: https://demo.buk.cl/apidocs#/

Employees: 

= (Page as number) =>
let
    Source = Json.Document(Web.Contents("INSERT URL?page="&Number.ToText(Page), [Headers=[token="xxxxxxxxxxxxxxx"]])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"full_name", "rut"}, {"full_name", "rut"})
in
    #"Expanded Column1"

Query:

= List.Generate(()=> 
[Result = try employees(1) otherwise null, Page = 1], 
each [Result] <> null, 
each [Result = try employees([Page]+1) otherwise null, Page = [Page]+1],
each [Result])

Tutorial I followed: https://www.youtube.com/watch?v=05yhwnuCjRw

There is no problem in the data I get, but the problem starts when I need to update it automatically in PBI Service, where I get the following error:

English translation:

This dataset includes a dynamic data source. Since these sources are not updated in the Power BI service, this dataset will not be updated. Learn more at https://aka.ms/dynamic-data-sources.
Query1 data source

I would like if someone could guide me what modification should I make to update automatically or if it is already impossible to update in this way.

Thank you very much for your time, have a nice day.

[v-kkf-msft · Accepted solution]

Hi @Ivancito111 ,

Please try use the Query option in Web.contents function:

(Page as number) =>
let
    Source = Json.Document(Web.Contents("https://xxxx/api/v1/chile/employees",[Query=[page=Number.ToText(Page)], Headers=[auth_token="xxxxxx"]])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"full_name", "rut"}, {"full_name", "rut"})
in
    #"Expanded Column1"

let
    Query1 = List.Generate(()=> 
[Result = try employees(1) otherwise null, Page = 1], 
each [Result] <> null, 
each [Result = try employees([Page]+1) otherwise null, Page = [Page]+1],
each [Result]),
    Query2 = Query1{0}
in
    Query2

Then publish report and select "Skip test connection" for this data source.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.