Schedule Data Refresh Issues

[Zu]

Hi there,

I'm having issues refreshing my dataset in PowerBI Service, it gives me an error message saying that my personal gateway could be reached. But my data in not on-premise, I created by report with PowerBI desktok, the pbix file and the datasource (an Excel workbook) are saved in my OneDrive for Busisness.

I think that the issue is related to the fact that in the Power BI desktop there is no way to import directly OneDrive, I imported from the OneDrive for business found in my laptop. Here are the steps that I follow to import the data, publish and update the data.

I imported data from an Excel workbook into Power BI Desktop where I created mesures and other data graphs required. Then I uploaded the Excel file and the .pbix file to OneDrive for Business.  I reopened the .pbix file stored in my laptop OneDrive folder (I didn't found how to import the .pbix file directly from OneDrive), in order to update the file path and have it point to OneDrive folder and not my local saved file. See image in attachment

Then I published the pbix file to the PowerBI service. We need to update the Excel data every three months and we would like to see the data updated in the powerBI service. The person who will be doing this is not myself. I'm setting up the reports and dashboards for the human ressources team who will update the data in the Excel data and want to be able to see the updated dashboards.  The issue is that after adding the data in the Excel file and we try to click on Refresh Now in the Service app, it says that we have connection on-premises, Could someone help me to setup the files and connections correctly so that I don't need to use gateway?

Thanks

[Zu]

[Greg_Deckler]

I do not see your image.

[Zu]

Hi Smoupre, Sorry about that. I have now added two images as a reply to my original post. Thanks!

[v-micsh-msft · Accepted solution]

Hi Zu,

I agree with smoupre here. Open the Advanced Editor to modify the file path with the OneDrive for business online path should make it work.

Regarding where to get the OneDrive online file path, please follow the article below:

Use OneDrive for Business links in Power BI Desktop

Steps should be the following:

1. Open Browser and login with OneDrive for Business with your account, then find the Excel;
2. Select it and then right click to choose Open in Excel;
3. In Excel, select File > Info and select the link above the Protect Workbook button. Select Copy link to clipboard.
4. Notice the ?web=1 string at the end of the link - you must remove that portion of the Web URL string before selecting OK, in order for Power BI Desktop to properly navigate to your file.
5. Under Advanced Editor, replace the file path with the edited URL, which would require you to login, choose Organization Account to login here;
6. After that, files should be mapped to OneDrive online service, publish it again to replace the original dataset;
7. Then under Power BI service, click Refresh, it will remind you to edit the credentials, choose Oauth2 and enter your credentials again, once done, it should be available for you to refresh without personal gateway.

If any further assistance needed, please feel free to post back.

Regards

[Zu]

@Greg_Deckler, @v-micsh-msft,

Thanks so much for your help both of you! The PBI Desktop file was still pointing to the file in my local OneDrive folder. Replacing the url in Advance Editor. I had to all 7 steps in @v-micsh-msft post to make the refresh work. It's working fine now.

Thanks,

Zu

[Greg_Deckler]

@Zu - OK, can you edit your query in the Query Editor and then go to View | Advanced Editor and the copy and post your Source line? First thing is that I want to make sure that it is really pointing to the OneDrive file and not the local file and this will make certain of where it is pointing.

[kcantor]

@Zu makeu sure you use the @ when tagging someone in the forum. That way they receive a message. While I cannot answer the question, I am just popping in to tag @Greg_Deckler for you.