Trying to use dynamic REST API code with scheduled refresh

[Anonymous]

Hi, 

I am working on a project to pull data from a rest API. This API uses headers for authentication and it also uses parameters. 

I have managed to get this working 100% on the Power BI Desktop but having the usual problem when I try to schedule automated refreshes on the Power BI service, it says that the URL is invalid because it's a dynamic URL. The error message says, "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed."

My original code (which works in Power BI Desktop) is as follows: (with relevant sensitive information changed)

(PageNo as number) as table =>
let
Source = Json.Document(Web.Contents("https://myapisite.com/v1/records?paginate=true&page_size=50&data_structure=keyval&
exclude_repeater_data=true&form_id=abcd&min_datetime=2021-03-19T00:00:01Z&page=" & Number.ToText(PageNo),
[Headers=[#"X-Integration-ID"="xxxxxxx", #"X-API-Token"="yyyyyyy"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_id", "user_id", "form_id", "record_id", "title",
"received", "gps", "data"}, {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data1",{"current_page", "page_size", "total_pages"}),
#"Expanded data.1" = Table.ExpandRecordColumn(#"Removed Columns", "data.1", {"requestor", "facility", "area", "line", "specify_type",
"maintenance_request", "photo", "email_address", "request_date"}, {"requestor", "facility", "area", "line", "specify_type",
"maintenance_request", "photo", "email_address", "request_date"}),
#"Expanded photo" = Table.ExpandListColumn(#"Expanded data.1", "photo"),
#"Expanded photo1" = Table.ExpandRecordColumn(#"Expanded photo", "photo", {"photo"}, {"photo.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded photo1", "Image", each "data:image/jpeg;base64," & [photo.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"photo.1"})
in
#"Removed Columns1"

Using hints from various sites (especially a few blog posts from Chris Webb), I learned about a method which could provide a valid URL to "fool" the Power BI service to accept the code and allow for refreshes to happen but still dynamically extract all the available data. But I am battling with the syntax. I keep getting errors. I have spent hours changing things without success.  My code currently reads as follows:

(PageNo as number) as table =>
let
Source = Json.Document(Web.Contents("https://myapisite.com/v1/records?paginate=true&page_size=50&data_structure=keyval&
exclude_repeater_data=true&form_id=abcd&min_datetime=2021-03-19T00:00:01Z&page=1",
[Headers=[#"X-Integration-ID"="xxxxxx", #"X-API-Token"="yyyyyy"]],
[Query=["paginate=true&page_size=50&data_structure=keyval&exclude_repeater_data=true&form_id=abcd&
min_datetime=2021-03-19T00:00:01Z&page=" & Number.ToText(PageNo)]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data"}, {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data1",{"current_page", "page_size", "total_pages"}),
#"Expanded data.1" = Table.ExpandRecordColumn(#"Removed Columns", "data.1", {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}, {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}),
#"Expanded photo" = Table.ExpandListColumn(#"Expanded data.1", "photo"),
#"Expanded photo1" = Table.ExpandRecordColumn(#"Expanded photo", "photo", {"photo"}, {"photo.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded photo1", "Image", each "data:image/jpeg;base64," & [photo.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"photo.1"})
in
#"Removed Columns1"

If anyone has any suggestions, I'd be very happy to hear them!

Thanks in advance. 

[v-eqin-msft]

Hi @Anonymous ,

Thanks for your feedback! Please kindly Accept it as the solution to make this thread closed. More people will benefit from it. 

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

I have made some progress with this problem. I reworked my code and it now reads as follows:

(PageNo as number) as table =>
let
    headerstring = [#"X-Integration-ID"="xxxxxx", #"X-API-Token"="yyyyyy"],
    querystring = [page_size="50", paginate="true",data_structure="keyval", exclude_repeater_data="true",form_id="MyFormID",min_datetime="2021-03-19T00:00:01Z",page= Number.ToText(PageNo)],
    Source = Json.Document(Web.Contents("https://myapisite.com/v1/records?paginate=true&page_size=50&data_structure=keyval&exclude_repeater_data=true&form_id=MyFormID&min_datetime=2021-03-19T00:00:01Z&page=1", 
        [
                Headers=headerstring, 
                Query=querystring
        ])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data"}, {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded data1",{"current_page", "page_size", "total_pages"}),
    #"Expanded data.1" = Table.ExpandRecordColumn(#"Removed Columns", "data.1", {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}, {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}),
    #"Expanded photo" = Table.ExpandListColumn(#"Expanded data.1", "photo"),
    #"Expanded photo1" = Table.ExpandRecordColumn(#"Expanded photo", "photo", {"photo"}, {"photo.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded photo1", "Image", each "data:image/jpeg;base64," & [photo.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"photo.1"})
in
    #"Removed Columns1"

This code allows me to set up a scheduled refresh! Success whoop whoop!

[Anonymous]

Hi, 

I am working on a project to pull data from a rest API. This API uses headers for authentication and it also uses parameters. 

I have managed to get this working 100% on the Power BI Desktop but having the usual problem when I try to schedule automated refreshes on the Power BI service, it says that the URL is invalid because it's a dynamic URL. The error message says, "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed."

My original code (which works in Power BI Desktop) is as follows: (with relevant sensitive information changed)

(PageNo as number) as table =>
let
Source = Json.Document(Web.Contents("https://myapisite.com/v1/records?paginate=true&page_size=50&data_structure=keyval&
exclude_repeater_data=true&form_id=abcd&min_datetime=2021-03-19T00:00:01Z&page=" & Number.ToText(PageNo),
[Headers=[#"X-Integration-ID"="xxxxxxx", #"X-API-Token"="yyyyyyy"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_id", "user_id", "form_id", "record_id", "title",
"received", "gps", "data"}, {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data1",{"current_page", "page_size", "total_pages"}),
#"Expanded data.1" = Table.ExpandRecordColumn(#"Removed Columns", "data.1", {"requestor", "facility", "area", "line", "specify_type",
"maintenance_request", "photo", "email_address", "request_date"}, {"requestor", "facility", "area", "line", "specify_type",
"maintenance_request", "photo", "email_address", "request_date"}),
#"Expanded photo" = Table.ExpandListColumn(#"Expanded data.1", "photo"),
#"Expanded photo1" = Table.ExpandRecordColumn(#"Expanded photo", "photo", {"photo"}, {"photo.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded photo1", "Image", each "data:image/jpeg;base64," & [photo.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"photo.1"})
in
#"Removed Columns1"

Using hints from various sites (especially a few blog posts from Chris Webb), I learned about a method which could provide a valid URL to "fool" the Power BI service to accept the code and allow for refreshes to happen but still dynamically extract all the available data. But I am battling with the syntax. I keep getting errors. I have spent hours changing things without success.  My code currently reads as follows:

(PageNo as number) as table =>
let
Source = Json.Document(Web.Contents("https://myapisite.com/v1/records?paginate=true&page_size=50&data_structure=keyval&
exclude_repeater_data=true&form_id=abcd&min_datetime=2021-03-19T00:00:01Z&page=1",
[Headers=[#"X-Integration-ID"="xxxxxx", #"X-API-Token"="yyyyyy"]],
[Query=["paginate=true&page_size=50&data_structure=keyval&exclude_repeater_data=true&form_id=abcd&
min_datetime=2021-03-19T00:00:01Z&page=" & Number.ToText(PageNo)]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data"}, {"account_id", "user_id", "form_id", "record_id", "title", "received", "gps", "data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data1",{"current_page", "page_size", "total_pages"}),
#"Expanded data.1" = Table.ExpandRecordColumn(#"Removed Columns", "data.1", {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}, {"requestor", "facility", "area", "line", "specify_type", "maintenance_request", "photo", "email_address", "request_date"}),
#"Expanded photo" = Table.ExpandListColumn(#"Expanded data.1", "photo"),
#"Expanded photo1" = Table.ExpandRecordColumn(#"Expanded photo", "photo", {"photo"}, {"photo.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded photo1", "Image", each "data:image/jpeg;base64," & [photo.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"photo.1"})
in
#"Removed Columns1"

This results in an error "Expression.SyntaxError: Invalid identifier."

If anyone has any suggestions, I'd be very happy to hear them!

Thanks in advance. 

[Anonymous]

By the way, this is the section of code that is causing the problem:

,
[Query=["paginate=true&page_size=50&data_structure=keyval&exclude_repeater_data=true&form_id=abcd&
min_datetime=2021-03-19T00:00:01Z&page=" & Number.ToText(PageNo)]]

If I remove this, the code runs 100% (but only returns page 1's data). I have tried putting quotes in, taking them out, putting them around the parameter values only, putting them around the numbers and dates only etc. None of these have helped. 

[v-eqin-msft]

Hi @Anonymous ,

Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. Thee only way to work around this is to use use the RelativePath and Query options with the Web. Contents M function. Queries that reference Power Query parameters can also be refreshed.

Refer to this:

https://blog.jongallant.com/2021/08/powerbi-dynamic-data-source-refresh/

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.