How to refresh the data

[stribor45]

My report (PBIX file) is developed with Power BI Desktop and saved in my OneDrive 365.  I published this report in premium workspace in service. When I go to check the report in service it is there and works fine.  On the following day in the morning I want to refresh my data so this is what I do

1. Go to my PBIX file and do refresh
2. Then I have to republish and replace exisiting
3. I then go to service and do the refresh to see the changes in data

Is there a way to do this automatically daily so the user of my reports just refreshes the browser in order to get new data in?

[stribor45]

looks ok now

[stribor45]

I scheduled another one to run at 11 am and as of now looks like it is still running (see red arrow). when I manually refresh it it takes only like 20-30 seconds

[aj1973]

Refresh the whole page

[stribor45]

I was able to adjust the code and set up the refresh schedule. It did run this morning successfully according to the message however the report did not show fresh data only after I manually clicked here

[aj1973 · Accepted solution]

Good job,

That's normal it can take couple minutes to see the updates applied on the page or just refresh it and you will see it right after

[stribor45]

ok fair enough how would I adjust this. This is the data that i have to manually update every week or so in excel file which is use as a source for my blank query. This file is for one table in this report. All other tables are done via blank query/api calls

[stribor45]

When go here Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File i see this which is file I am using to get data for one of my tables. Is that why? This file is in my OneDrive

[stribor45]

I am using API calls in my power queries. Is that why I cant refresh?

[aj1973]

I think you are misusing the fonctionality of OneDrive integration

The purpose is clearly not for refreshing the model

Power BI Desktop OneDrive and SharePoint integration - Power BI | Microsoft Learn

You might want to use different approach if you want to refresh the model automatically.

[stribor45]

Where is "Query 1" coming from? I have 12 queries in my report. None of them are called "Query 1"

[aj1973]

Your issue is due to refreshing dynamic Datasources. I found this for you

Certainly! Refreshing dynamic data sources in Power BI Service can be tricky, especially when dealing with APIs and dynamic content. Let’s tackle this step by step:

1. Dynamic Data Sources in Power BI Service:

   • When you create a dataset in Power BI that includes dynamic data sources (such as APIs), you might encounter issues during refresh in the Power BI Service.
   • The error message you’ll see is: “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.”

2. The Challenge:

   • Unlike Power BI Desktop, where dynamic data sources work seamlessly, the Power BI Service doesn’t automatically refresh them.
   • The issue arises because certain features (like paging with nextLink) behave differently between Power BI Desktop and the Service.

3. Workaround:

   • To overcome this limitation, you can modify your query to handle paging correctly.
   • Here’s an example of how you can adjust your code to make it work in both Power BI Desktop and the Service:

let
    GetPages = (Path) =>
        let
            Host = "https://management.azure.com/subscriptions/",
            Source = Json.Document(Web.Contents(Host, [RelativePath = Path])),
            LL = @Source[value],
            Next = Text.Replace(Source[#"nextLink"], Host, ""),
            result = try @ll & @GetPages(Next) otherwise @ll
        in
            result,
    Fullset = GetPages(SubscriptionId & "/resources?api-version=2019-05-01")
in
    Fullset

1. Explanation:

   • In the modified code:
     • We use the GetPages function to handle paging correctly.
     • The Next variable extracts the next page URL from the response.
     • The try function ensures that the recursion stops when there are no more pages.
     • Finally, we get the complete dataset using Fullset.

2. Apply to Your Scenario:

   • Replace the URL and parameters in the Host variable with your specific API endpoint.
   • Adjust the query according to your dynamic data source.

Remember, this workaround ensures that your dynamic data sources are refreshed in both Power BI Desktop and the Service. If you encounter any issues, feel free to ask for further assistance! 🚀

also in the community many had faced the same issue, check them out

Search - Microsoft Fabric Community

[stribor45]

I was able to get to this line 

LL = @Source[value] but had to modify it to
LL = @Source,

which returns table

Can anyone help from here

[stribor45]

Spoiler

Would you be able to adjust this code a bit. I am confused by the part of the code where you have fullpath variable.   I have api key and couple of variables in post data

[aj1973]

Sorry, what is this spoiler?

[stribor45]

sorry it must have been something in my cut and paste.  Would you be able to adjust this code a bit. I am confused by the part of the code where you have fullpath variable.   I have api key and couple of variables in post data

[aj1973]

What code? My fullpath variable !

Honestly I have never worked with dynamic datasources but it doesn't hur to try

[stribor45]

This is good information but none of my queries containg paging. How would I narrow it down to see which one of my queries is preventing me of doing automatic refresh? Each of my queries is showing me this 

[aj1973]

Try them one by one after publishing it in the service.

[aj1973]

Hi @stribor45 

In the service go to semantic model settings and set up a scheduled refresh