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
shera276
Advocate I
Advocate I

External data fresh failed error when I upload my excel workbook (based on an **bleep** serv model)

Hi - I've seem similar posts but they are old with no resolution.  I have an excel file with a few power pivots in them based on an analysis service model.  I'm trying to get the workbook in PowerBI Online Service but I receive an External Data Fresh Error.  Steps:

 

1. Get Data from the online service and select 'Upload Excel file from PowerBI'

2. First message I receive is 'Query and Refresh Data - Be careful. This workbook contains one or more queries that might be unsafe.  Do you want to enable these queries?'  I select yes.

3. Next message I recieve 'External Data Refresh Failed - We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: [connectionname]'.  I press OK.

4. The workbook still loads but when I select a filter in the power pivot, I get the same message in step 3.

 

Any advice on how to resolve this? I'm not having this issue when I upload a pbix file from the same analysis service model.  It's only excel based on the model.  I ensured my gateway connection to the underlying model is working too.

8 REPLIES 8
v-huizhn-msft
Employee
Employee

Hi @shera276,

I am still confusing, I get you try to get excel data in Power BI service like this article said. While in your fist step, "Get Data from the online service" means you get data from BI service to excel? Could you please share more details for further analysis?

Best Regards,
Angelia

Hi Angelia - I'm trying to get an excel file that is connected to an analysis service model from my desktop to the online service.  I do that using #1 from my original description.  After that, then the events in 2-4 happen.  Does that help?

Hi @shera276,

For your requirement, you get date from SSAS to excel fine. In desktop, you just need to inport data from the excel file, then publish the data to Power BI service. Then you add the excel file as the data source of gateway. Why do you need use select 'Upload Excel file from PowerBI'?

Best Regards,
Angelia

Hi Angelia - That's correct. I connect fine from SSAS to excel and can upload the file to apps.powerbi.com.  However, I get the errors outlined in the description when I try to change a filter:

 

1. First message I receive is 'Query and Refresh Data - Be careful. This workbook contains one or more queries that might be unsafe.  Do you want to enable these queries?'  I select yes.

2. Next message I recieve 'External Data Refresh Failed - We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: [connectionname]'.  I press OK.

 

To answer your question, I select 'Upload Excel file from PowerBI' b/c it seemed like what I needed it to do to connect to SSAS in the excel file from apps.powerbi.com.  I tried 'Import Excel data in to PowerBI' but it requires me to convert my pivot tables to data so it can upload it's own data set and independent of the data from the SQL Server data at my compnay.  From the excel file that I load in apps.powerbi.com, I want the file to connect to the data stored from SQL Server, so when I filter and refresh data, I'm getting the the latest data.  In think with the import excel data option, it won't connect to the SQL server and the excel data will be standalone or flat.  I hope that makes sense.

 

Thanks,

 

Sheila

Hi @shera276

If you want to refresh dateset including multiple data source, each data source must be added within the gateway. So did you both SSAS and Excel as the data source?

Best Regards,
Angelia

Hi Angelina - I didn't but I'll try.  Thank you.

Hi @shera276,

Got it, please respond and share your workaround if you have resolved your issue. So that more people will benefit from here.

Best Regards,
Angelia

Hi @shera276,

Got it, please respond and share your workaround if you have resolved your issue. So that more people will benefit from here.

Best Regards,
Angelia

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