Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hikmer
Employee
Employee

Still struggling with PowerBI and Scheduled Data refresh using Excel and OneDrive via gateway

I've been trying to make this work for months, I was told there is a bug and it doesn't work.  Is this true? 

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-onedrive/

 

I am farily up to date with PowerBI and use it daily witht he Enterprise Gateway (newly installed) and I still get meaningless error messages that do nto help me figure out why this isn't working.  I expect the data in the model to refresh through the gateway....does it work?  If not wat is the secrect to making this function as advertised?

18 REPLIES 18
v-yuezhe-msft
Employee
Employee

@Hikmer,

Could you please describe more details about your dataset? We need to know that if your dataset only contains the Excel data source and if you connect to external data source in the Excel file. Also we need to know that what error message you get during the refresh.

If you don't connect to external data source in the Excel file and only connect to the Excel file stored in OneDrive in Power BI, gateway is not required to refresh your dataset.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Im trying to conenct to a live data source on premise via the gateway.  I've never been able to make it work as advertised in the documentation and was told there was a bug that was being fixed.  However I started a new job and no longer have an open ticket to track the effrot.  The error is meaningless to me, it just says the data refresh failed.

@Hikmer,

What live data source do you refer to? Does your dataset contain Excel file? What is the relationship between your data source and Excel and OneDrive?

Please post a screenshot about the data sources in your dataset, you can go to File -> Options and settings ->Data source settings to check data sources.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am connecting to an on premise SSAS Tabular model through Power Query as specified in the previous article I posted.  Does this work?  If so, under what circumstances?  I have not been able to make a data connection in PowerQuery or PowerPivot refresh in PowerBI using OneDrive or SharePoint as specificed in the articel I noted.

@Hikmer,

Firstly, make sure you select Add this data to the Data Model when connecting to SSAS in Power Query.

Secondly, directly connect to the Excel file in Power BI Service. After that, install personal gateway and use personal gateway to refresh your dataset.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I've followed the direction very carefully but it doesn't work (at two comapnies now).  I just want to confirm, does it actually work?  Can anyone else say with authority that they do this on a constant basis?  I had a very long tikcet open with Microsoft but never got to know if it was resolved as I left my previous company.  This feature does not work as advertised (at least in my experience). 

@Hikmer

I make a test using the steps below, everything works well. The steps also apply to your scenario.

1. Connect to SQL Server database in Excel.
2. Upload the Excel to OneDrive for business.
3. Connect to the Excel file via “Get Data-> Files->OneDrive - Business” entry in Power BI Service and choose the “Import Excel data into Power BI” option to upload data.
4. Create visuals in Power BI Service.
5. Use on-premises gateway(personal mode) to refresh the dataset and set schedule refresh.
1.PNG
6. I make changes to data in my SQL Server database, the dataset is updated based on the refresh schedule in Power BI Service.

In addition, I use same account to sign in OneDrive for Business and Power BI Service. Could you please describe the steps you take to connect to data source and refresh dataset?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am actually trying to view an Excel file not create a PowerBI report from the data connection.  I am following the posted link in my original thread but cannot get the refresh to work.   I need the file to be Excel not PowerBI. 

@Hikmer,

I am not quite sure about your requirement, could you please describe your steps and post expected result?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Pretty straight forward, create an Excel file as specified in the documentation provided by Microsoft then schedule a refresh using the data gateway.  It never works.  Does anyone else do this?  I can't image I am the only person trying to share an Excel file in PowerBI that automatically refreshes?

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-excel-workbook-files/

@Hikmer,

I have tested the scenario as yours, and refresh works well. Could you please describe detailed steps(1,2,3,4...) like my previous reply? We will appreciate it if you can post screenshots about your scenario.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm follwoing the directions as posted.

 

1) Connect to a SQL data source using PowerQuery.  This data source is actively being used in another PowerBI report so I know that it works.

2) Load the table making sure to click the "load to" option.

3) Add the table to the worksheet making sure to select the "add to model" option.

4) Save the file to my OneDrive for Business

5) Open my Workspace in PowerBI and then use the Get Data > Files > OneDrive for Business > Select File option

6) Connect with the file using the Conenct, Manage and View in PowerBI option.

7) Wait for the file to become ready then click the elipse buttons to enable the scheduled refresh.

😎 Enable the gateway and then sechedule a refresh.

9) To verify it works, I then Refresh the data manually.

10) Guess what...it works!  Lordy, sorry for all the compalints.  Whatever was a miss is either fixed on the server side or between the chair and the keyboard.

 

I spoke too soon, I get this message when uploading files with real world data.

 

We couldn't connect to your workbook from OneDrive - Business
 
Power BI couldn't connect to this workbook. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.
Activity IDa198f4ea-5d5c-468d-c6e4-c9f78e8dc202
Request IDc06a9e2f-e4e0-f581-587a-685b7355d232
Correlation ID9e026fa2-5d88-2abf-c825-6f5ab1d72039
Status code400
TimeFri Aug 04 2017 14:25:46 GMT+1000 (E. Australia Standard Time)
Version13.0.1973.185

@Hikmer,

At step 6, please choose the “Import Excel data into Power BI” option to upload data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thakns for the feedback, but I do nto want to import a data connection.  I woudl liek to work in native Excel for financial type reports.  PowerBI still does not offer a good soltuion for people who need Excel type reports.

@Hikmer,

I am not able to reproduce your issue. Please refer to the following similar thread to check your Excel file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Like I previously stated, I had tickets open with Microsoft support which confirmed there was a bug.  What I really wanted to find out is if that bug was fixed.  I was at a previous company and unfortunately Microsoft will not release bug numbers to the public.  I'll open a new ticket on this issue.

I'm follwoing the directions as posted.

 

1) Connect to a SQL data source using PowerQuery.  This data source is actively being used in another PowerBI report so I know that it works.

2) Load the table making sure to click the "load to" option.

3) Add the table to the worksheet making sure to select the "add to model" option.

4) Save the file to my OneDrive for Business

5) Open my Workspace in PowerBI and then use the Get Data > Files > OneDrive for Business > Select File option

6) Connect with the file using the Conenct, Manage and View in PowerBI option.

7) Wait for the file to become ready then click the elipse buttons to enable the scheduled refresh.

😎 Enable the gateway and then sechedule a refresh.

9) To verify it works, I then Refresh the data manually.

10) Guess what...it works!  Lordy, sorry for all the compalints.  Whatever was a miss is either fixed on the server side or between the chair and the keyboard.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors