Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

workaround for SCHEDULED REFRESH on HTTP API with pages

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

11 REPLIES 11
v-shex-msft
Community Support
Community Support

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

 

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.

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])))

6.PNG7.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Hi @Anonymous,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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.

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

Anonymous
Not applicable

@ATL_Adam

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"

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors