scheduled refresh web page, disabled (You have no personal gateways installed)

[Phsc1]

Hi 

I have looked far and wide on this issue, but in vain, hoping that someone here can shed some light on the issue.

I am trying to schedule a refresh of Web data from a webpage with dynamic pages (paging).

Refresh works manually in PBI Desktop, but Scheduling is disabled in Powerbi online.

the code: 

----------------

(varPageID as number) =>

let

    BaseUrl = "https://bruun-rasmussen.dk/m/auctions/1007934", Source = Web.Contents(BaseUrl, [Query = [#"page" = Number.ToText(varPageID)]]),

    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".catalogue-number"}, {"Column2", ".description"}, {"Column3", "CURRENCY-RANGE"}, {"Column4", ".lot-list-item-location"}, {"Column5", ".hammer-time SPAN"}, {"Column6", ".relative-time"}, {"Column7", ".no-grow.tw-br-transition"}, {"Column8", ".lot-list-status-text"}, {"Column9", ".next-bid-amount:nth-child(4) *"}, {"Column10", ".next-bid-amount + *"}, {"Column11", ".accepteret_tilbagekob_via_web CURRENCY-AMOUNT"}, {"Column12", ".accepteret_tilbagekob_via_web .bid-status"}}, [RowSelector=".large\:tw-p-2\.5"]),

    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each true)

in

    #"Filtered Rows"

------------------------

varPageID is the function calling the page numbers (8 in total)

 
Error:

Data Source settings:

Cloud Connection settings:

I am aware of Chris Webb's blog and used that for inspiration eventhough its about Json:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... 

and this post:

https://community.fabric.microsoft.com/t5/Power-Query/WORKAROUND-How-to-use-scheduled-refresh-of-a-w...

Any suggestions on how to fix this will be highly appreciated 🙂

Cheers

Philip

Cheers,

[Anonymous]

Hi @Phsc1 

According to the current attempts, how about trying this

BaseUrl = "https://bruun-rasmussen.dk", 
Source = Web.Contents(BaseUrl, [ RelativePath = "/m/auctions/1007934", Query = [#"page" = Number.ToText(varPageID)]]),

In addition, since this is a custom function to be called in a main query, does that main query call the same API or does it only have numbers that passed to "varPageID"?

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Phsc1]

Thank you for the suggestion

tried, but get this:

[lbendlin]

Use RelativePath and Query attributes to avoid that issue.

[Phsc1]

tried that, doesnt work either..   cannot Refresh or schedule refresh of data in powerbi online 

code:

varPageID as number) =>
let
Source = Web.Contents("https://bruun-rasmussen.dk/m/auctions/1007934?page=" & Number.ToText(varPageID)),

#"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".catalogue-number"}, {"Column2", ".description"}, {"Column3", "CURRENCY-RANGE"}, {"Column4", ".lot-list-item-location"}, {"Column5", ".hammer-time SPAN"}, {"Column6", ".relative-time"}, {"Column7", ".no-grow.tw-br-transition"}, {"Column8", ".lot-list-status-text"}, {"Column9", ".next-bid-amount:nth-child(4) *"}, {"Column10", ".next-bid-amount + *"}, {"Column11", ".accepteret_tilbagekob_via_web CURRENCY-AMOUNT"}, {"Column12", ".accepteret_tilbagekob_via_web .bid-status"}}, [RowSelector=".large\:tw-p-2\.5"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type date}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),

#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Lot no", "Item", "Estimate", "Auction Date", "Current bid", "Next bid"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each true)
in
#"Filtered Rows"

[lbendlin]

You are not using the Query parameter.

[Phsc1]

It calls this function:

is that not the Query Parameter you refer to?

[lbendlin · Accepted solution]

no, it is not.  Please refer to the documentation.  Web.Contents - PowerQuery M | Microsoft Learn

I would also strongly recommend to inline all functions.

[Phsc1]

hi
thank you for you input and patience:)
using Google Gemini I managed to build this code
but it fails in line 4 with "Eof Token expected".  
marking "=> " 

can you see why?

//(* Get data from a paginated website with error handling and transformation *)

// Function definitions for data processing tasks
getPageData = (pageNumber as number) => {
try
Web.Contents(
Text.Combine(source, "?page=", Text.FromNumber(pageNumber)),
[
RelativePath = "/", // Specify relative path within the website (if applicable)
Query = [ // Optional query parameters for filtering (if applicable)
$filter = Text.Combine("HasContent(Data)", "&", "$filter eq 'true'")
]
]
)
otherwise
null
}

errorHandling = (errorMessage as text) =>
Error.Renaming(errorMessage, "Data Extraction Error");

transformData = (data as table) =>
Table.TransformColumns(
data,
{ "Column1", Text.Clean } // Replace "Column1" with actual column names and transformation logic
)

// Variable declarations
let
source = "https://bruun-rasmussen.dk/m/auctions/1007934", // Replace with your base URL
totalPages = 8,

// Combine data from all pages with logging and error handling
allPagesData = List.Accumulate(
List.Range(0, totalPages - 1),
null,
(acc, pageNumber) =>
let
pageData = getPageData(pageNumber)
in
if acc is null then pageData
else Table.Combine({acc, pageData})
)
// Check if any errors occurred during data retrieval
, errorLog = List.Where(allPagesData, isBlank)
// Filter out any null pages resulting from errors
, filteredData = List.RemoveItems(allPagesData, errorLog)

in
// If errors occurred, display them
if List.IsEmpty(errorLog) then
transformData(filteredData)
else
errorHandling(
List.Combine(
"Errors occurred while fetching data from some pages:",
Text.NewLine(),
Text.Combine(" - Page(s): ", Text.Combine(errorLog, ", "))
)
)

[Phsc1]

sorry.. line 3 of course...

[lbendlin]

Gemini confused your API call with an ODATA call...  You don't have a $filters query parameter.

[Phsc1]

Succes!!

this revised snippet works! and it can refresh data (and schedule refresh)  from Powerbi online

let
// Base URL for data retrieval
baseUrl = "https://bruun-rasmussen.dk/m/auctions/1007934",
totalPages = 8, // Define the total number of pages you want to fetch

// Function to get data from a specific page using Query attributes
getPageData = (pageNumber as number) =>
let
// Utilize Query attribute for pagination
source = Web.Contents(baseUrl, [Query = [page = Text.From(pageNumber)]]),


thank you for all your help!:)

[lbendlin]

Have you considered using List.Generate and an inlined function instead?