workaround for SCHEDULED REFRESH on HTTP API with pages

[Anonymous]

I can load all pages using a script that cycles through all pages.

But now I found a problem and I need a workaround:

I cannot refresh data online (also scheduled refresh) because of the limitation of the PBI Service.
I am using a function inside the URL and this is not allowed for PBI Service.

In bold, the forbidden function inside URL:

each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]) & "&date_range=201001010000:201912310000")))

Can anyone please help me with another way to load all pages from API and solve this issue?

In another posts, people say that I need to use Query functions, but I am new to M language and looking for help.

[Anonymous]

Hi @Anonymous,

Current power bi service not support custom function, I'd like to suggest you to use static value to replace the bold part.
In addition, can you share some detail content of your issue?(e.g. screenshots, table structure...) It will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[Anonymous]

I know I need to use static values to replace the function. But how can I make a script to load 44 different links?

www.url.com/offset=100, www.url.com/offset=200, going up to www.url.com/offset=4400.

I dont want to load 44 distinct tables and perform append queries later...

my current code uses this, where text.from([column2]) is a number going up from 0, 100, 200.. till 4400,
each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]))))

I tried to put all line or parts of it inside a function, without success, where text.from([column2]) is the full line.
each Text.From([Column2])

Where text.from([column2]) is a big list with: Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0")), where offset keeps on going +100 on offset value.

[Anonymous]

Hi @Anonymous,

Maybe you can add a custom column and put your analysis formula in it.

Sample:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web.Contents("xxxxxxxx"&Text.From([Search Keyword])))

Regards,

Xiaoxin Sheng

[Anonymous]

this is exactly what I have. it works only on PBI Desktop. When I publish it to PBI Service, I cannot use the refresh options.

The issue is to use a function inside the URL address, as I read in another posts from another users.

I tried to create a function as below, but it also doesn´t work:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Search Keyword]))

 and the search keyword is a column in another source containing all addresses I need:

Json.Document(Web.Contents("url1"))

Json.Document(Web.Contents("url2"))

Json.Document(Web.Contents("url3"))

etc

[Anonymous]

Hi @Anonymous,

Yes, current customize function only works on desktop, perhaps you can submit an idea to ideas forum.

Regards,

Xiaoxin Sheng

[Anonymous]

I already voted for ideas like this.

But here, I am looking for any code that works correctly online. Any script that reads all my data base and is able to refresh via Power BI Service.

[Anonymous]

I was wondering... What if I convert a table into function, and then apply this function on a list with all the URL that I need?

url.com/1

url.com/2 and so on...

I really don´t know if this way is going to work. And even if it will work, I don´t know how to do it.

How will the table-function look? What commands should I use?

[Anonymous · Accepted solution]

I got the answer from outside the community, by another microsoft engineer.

I changed the code to something like this:
Json.Document(Web.Contents("https://url.com/token?offset=0", [Query=[offset=Text.From([Column2])]]))
Where my Column2 have all numbers that I need for paginating and cycling through the API.

Using the code above, all my pages are loaded correctly, and now I can refresh using Power BI Service because there is no function inside the URL address.

[Anonymous]

Can you share the entire query from advanced editor? Did you define "offset" as a variable before using it?

[Anonymous]

@Anonymous

you need to substitute MYAPI.COM for your own web API site.

you need to substitute OFFSET for your own API parameter.

you need to substitute TOKEN for your own API token.

if supported by your API, each extra parameter must be separated by "&". example: offset=0&date=01012017&local=bolivia

this code needs /token after the raw url. using only myapi.com?offset=0 or myapi.com/offset=0 does not work. (I tested in another similar API that doesn´t have /token)

let

Source = Json.Document(Web.Contents("https://myapi.com/yourtoken?offset=0")),
Source1 = {0..1000},
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Personalização Adicionada" = Table.AddColumn(#"Converted to Table", "Personalizar", each [Column1]*100),
#"Added Custom" = Table.AddColumn(#"Personalização Adicionada", "Custom", each Json.Document(Web.Contents("https://myapi.com/yourtoken?offset=0", [Query=[offset=Text.From([Personalizar])]])))
in
#"Added Custom"

[Anonymous]

NVM I had the url still setup with a parameter instead of just a regular string