Dynamic data refresh

[Joho70]

I have this  strings to get holidays in my calendar that is the only query I see:

(Year as number) as table =>

let
Source = Web.BrowserContents("https://www.kalender.se/helgdagar/"&Number.ToText(Year)&""),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.table.table-striped > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table.table-striped > * > TR > :nth-child(2)"}, {"Column3", "TABLE.table.table-striped > * > TR > :nth-child(3)"}, {"Column4", "TABLE.table.table-striped > * > TR > :nth-child(4)"}, {"Column5", "TABLE.table.table-striped > * > TR > :nth-child(5)"}}, [RowSelector="TABLE.table.table-striped > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Datum", type date}, {"Namn", type text}, {"Vecka", Int64.Type}, {"Veckodag", type text}, {"Dag på året", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Namn", "Holiday"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Veckodag] <> "Lördag" and [Veckodag] <> "Söndag")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Datum", "Holiday"})
in
#"Removed Other Columns"

and this is causing a problem to update the report by schedule and refresh now function.

I need help fixit the query correct, I have looked in Chris Webbs BI blog but didn´t see/get the solutin to use.

Hope someone can give me a hint on hos to solve it

[ibarrau · Accepted solution]

Hi. Chris Web does have something for this 🙂

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

When you are running a request for different arguments or rows. You need to split your URL until it's the same for all. You can't have a different URL for each year. That way the engine will consider it as one source and it can identify. You might be thinking "but I really need the year to get that". Yes, but you can make it as an argument of the url. The url parameters or the changes on url can be define by "RelativePath" and "Query". Those two will help you to make a single url that matches for all years url every time so the engine can identify.

The change would be in the Source step. Change it for this one:

Source = Web.Contents("https://www.kalender.se/helgdagar/", [RelativePath=Number.ToText(Year)] ),

As you can see, now the year will be part of the URL but as a different part. Letting the engine to think that the source is only the first part.

I hope that helps,

[Joho70]

Work after some minor incident and retrail, tnx
Unfortunately a new fault occured:

Data source error:            Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh.

Im not sure what to do

[ibarrau]

Hi. The error itself is telling you what to do 🙂
After you change the "Source". You need to specify de credentials for this new cloud source.

"go to this dataset's settings page and enter credentials for all data sources."

It's just editing credentials and pick anonymus (because as far as I remember this was a public data link)

This might be helpfull to show a picture of where is it (don't read all that on the link) https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh#data-source-credent...

I hope you find it