Power BI Report Data Refresh Questions

[LijunChen]

Hello Guys,

I have a few questions about refreshing Power BI report and would like to seek your adivce. 

We are collecting data with a MS Forms survey, and the survey responses data is stored in our Sharepoint online project folder. I am using Power BI desktop to build a report based on the survey data. As suggested by the experts on Youtube eg.  https://youtu.be/YI6ey8EvIAI, I used the Web url of the Excel file and my Organizational credentials to connect and import the Excel data in Sharepoint. After processing the data in Query Editor of Power BI, I published the Report and set up the Refresh Schedule, As shown in the screenshot below.

  

As I checked the refresh status, the data / report seems to be refreshed on time. However, I find that the refreshed Report does not incorporate the new survey repsonse data. Event the manual "refresh now" does not do the job. To refresh the report, I have to go the Power BI desktop / query editor and refresh the data there and republish the the report. 

I just read from this recent MS doc  (https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data) that besides setting up power BI data refresh using the scheduler, "If you created your datasets and reports based on a Power BI Desktop file, Excel workbook, or comma separated value (.csv) file on OneDrive or SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh." Although according to this doc, the OneDrive refresh seems to be done automatically, but it is not working in my case and I have to refresh the data manually using the Power BI desktop. The "refresh history" screenshot shows no auto refresh of OneDrive.  

My first question is: Am I doing right to connect to the Sharepoint Online excel data through the Web url? 

My 2nd question is: If the web connection is the way to go, how can I import the Excel data from the the Sharepoint folder in order to have the automatic refresh of the OneDrive data? Do I use get data from Sharepoint? 

Any advice and suggestions are welcome. Thanks.

LC  

[parry2k]

@LijunChen are you using any parameters that you are using to change the location of the files in the service? 

[LijunChen]

I have not set or used any parameters. 

[parry2k]

@LijunChen your schedule refresh should pull the latest data and reports should get updated, not sure why it is not happening if refresh is executed without any error.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexisOlson]

This sounds strange to me too. I haven't had this problem with my connections from the Power BI Service to Excel spreadsheets saved in SharePoint.

[LijunChen]

@AlexisOlson @parry2k  Thanks for your response. I wonder how do you connect to the Excel file on SharePoint Online. Are you using the Web connection? Does the Refresh History show the the status of OneDrive refresh as "schedule"? 

In my case, I use the Web connection as recommend by others. I use the web connection to import the 2 Excel tables in 2 Excel workbooks saved in SharePoint project online folder. But somehow the scheduled autorefresh is not refreshing the data. I have to open Power BI desktop to refresh the data.

It is very frustrating. 

[AlexisOlson]

My connection looks like this:

let
    Source = Excel.Workbook(Web.Contents("https://companydomain.sharepoint.com/sites/SiteName/FileName.xlsx"), null, true),
    Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    [...]

I'm only seeing OneDrive refreshes from when I manually refreshed the dataset (no scheduled OneDrive refreshes show in the history).

Potentially useful links for future readers:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

https://community.powerbi.com/t5/Service/OneDrive-Refresh/m-p/1145650

I wonder if your issue is related to this part from the second link above:

Power BI performs refresh based on an item ID in OneDrive, so be thoughtful when considering updates versus replacement. When you set a OneDrive file as the data source, Power BI references the item ID of the file when it performs the refresh. Consider the following scenario: you have a master file A and a production copy of that file B, and you configure OneDrive refresh for file B. If you then copy file A over file B, the copy operation deletes the old file B and creates a new file B with a different item ID, which breaks OneDrive refresh. To avoid that situation, you can instead upload and replace file B, which keeps its same item ID.

You can move the file to another location (using drag and drop, for example) and refresh will continue to work because Power BI still knows the file ID. However, if you copy that file to another location, a new instance of the file and a new fileID is created. Therefore, your Power BI file reference is no longer valid and refresh will fail.

[LijunChen]

@AlexisOlson My connection is similar to yours, also through web connection.

I wonder if you revise the Excel spreadsheet, can the Power BI report refresh (either auto or mannually) reflect the new changed data? 

For me, either scheduled or mannual refresh won't show the latest data. I have to open the Power BI desktop app to refresh the data there.