Power BI Dashboard can't get refreshed automatically (Premium Workspace)

[modery]

Hi! We published the Power BI dashboard to a premium workspace, so that we can easily grant access to selected stakeholders (e.g. HR teams in different countries). However, it seems that it is currently not possible to schedule an automatic refresh, the following issue/warning is shown in the Dataset's settings page (where we could schedule the refresh):

"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. Learn more: https://aka.ms/dynamic-data-sources."

Is there any way to change/fix this?

[modery]

Found a workaround. The problem occurs due to the calls to webcal.fi, happens in 3 queries.

For the 3 queries “Requested date”, “Check in date”, and “Check out date”, I changed

    PHSource = Xml.Tables(Web.Contents("http://www.webcal.fi/cal.php?id=408&format=xml&start_year=" 
                    & Text.From(StartYear)
                    & "&end_year=" 
                    & Text.From(EndYear)
                    & "&tz=Europe%2FLondon")),

to

PHSource = Xml.Tables(Web.Contents("http://www.webcal.fi", [RelativePath="cal.php?id=408&format=xml&start_year=" 
                    & Text.From(StartYear)
                    & "&end_year=" 
                    & Text.From(EndYear)
                    & "&tz=Europe%2FLondon"])),

In the Advanced Editor

Reference: https://medium.com/datadriveninvestor/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-bi-409ccec7337b

Scheduled refresh became available, and I haven't seen any problems so far.

[shayne-x]

@modery Thanks for the workaround. The refresh capability becomes available but the refreshed data seems to be incorrect. For example, the calculation for pending check-ins and the number of people on-site does not calculate correctly after making the suggested update. When I undo the recommended changes on the 3 queries, the data gets populated correctly on PBI Desktop but then I lose the refresh capability online. Have you noticed a similar issue?

[modery]

That's interesting - besides the pending check-ins and number of people on-site, is anything else wrong afterwards? And in which way does it change for you? Just compared the data from before my change and after the change, and it looks the same. Though I also need to add that today was the first day that the app was to be officially used (announcement was made 2 weeks ago and people were asked to book for any request to go back to the office from 14 Dec onwards), and only a handful of people checked in/out. So there is a chance that it looks correct for me, but only due to limited data (163 requests for today and tomorrow; 7 checked in, 4 checked out). I also did a quick spotcheck on the underlying data in Power BI (Checked-In date, Requests), and couldn't see any differences

[shayne-x]

Thanks for the prompt feedback. I've been using it for about a month now :) I'll attach some screenshots and try to describe as much as I can.

1. In this image/chart, you'll notice that the 'onsite now' bar in the chart is blank.

2. In this image/table, notice Approved requests = 7, Check-ins today=6, meaning pending check-ins should be=1, but it remains as 7.

3. This is the overall snapshot A. This image is the showing the correct info as published from PBI desktop before editing the queries. The occupancy is getting calculated correctly and the total number of requests is a lot more:

B. After editing, the number of Check-ins remains the same which is good, however other numbers reduce and Current occupancy goes to 0%

Basically, it seems the change enables refresh but it limits the data collection which in turn produces incorrect data. Perhaps you should compare your data on PBI desktop (before editing queries) to the one published in your premium capacity and see if there's a difference

[modery]

Sorry for the very late reply - there was first some low usage due to public holidays, and then we didn't check those numbers that you mention above anymore. However, I can confirm that my "fix" actually broke the dashboard as mentioned by you. Haven't figured out why, will try to see if I can find the root cause

[jasonmcmillanpfp]

@modery I tried the webcal.fi edit but it fails due to auth issues with the web creds and scheduled refresh is greyed out (see below)

[Sripriyasp]

Hi, Has this data error with Power BI service refresh been resolved? I have followed Chris Webb's blog to use Relative path & Query options with Web.Contents() in Power Query. The refresh capability is achieved, but when refreshed all data resets to zero and doesn't show any records.

I have even tried to eliminate the dynamic data source by using a Sharepoint excel source for Public Holidays (which is the dynamic data that comes from www.webcal.fi), but still no luck.

[modery]

Note: Webcal.fi is no longer available now, I opened #57 for this