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
Anonymous
Not applicable

Load Static Excel Data into Power BI report without refresh on the service

Hello Experts,

 

We have a requirement of developing a Power BI report which will take some data from an excel sheet and some from an MDX cube. Basically the excel data is the historic data and will not change, so we want it to be imported only once and never to refresh in the dataset. But the data from SQL server should be refreshed every 4 hours.

 

Below are few things that we have tried.

 

Approach 1:

Tried Copy-Pasting from the excel sheet to Power BI table using the enter data option. Data is around 13000 rows. It does not allow that much amount of data to be pasted. Also tried uploading in buckets of 100, still facing issues. This is not favoured as copy-pasting in irregular sized buckets(as per data) is a time-taking and error prone activity.

 

Approach 2:

Import from excel in PBI desktop. But here it is considered for refresh in the Power BI service, which we dont want.

Even if the include in Report refresh option is disabled, it still tries to connect from the service.

Tried the Enable load option as well. It makes the data unusable in the report.

 

Approach 3:

Created a dataset in PBI service. It allows the data to be fetched from the service and also handles the no refresh requirement, but the limitation is that we have to connect to the dataset as a live query. So it basically does not allow us to bring in other tables from SQL Server, which will not cater to our requirement.

 

Please advise if there is a solution to this.

 

Regards,

Chetan

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

There are some limitations to put imported data and data with live connection together in the same dataset in Power BI.

When connecting live in the Power BI, it is disable to connect other tables outside.

How about importing excel data and SSAS MDX data in the same dataset with Power BI Desktop, then configure schedule refresh only for SSAS MDX data?

 

Best Regards

Maggie

 

 

Hi Maggie

 

That won't work though for the reasons outlined above. The service will still lokk for the file and won't permit refresh as the gatewway will not be properly configured even if load is disabled in desktop for the file.

 

 

Hi,

 

I'm facing the exact same issue... have you managed to find a solution?

 

Thanks

Anonymous
Not applicable

Hi @ricardomadaleno ,

 

Unfortunately, i was unable to find a solution to this using Power BI.

Eventually, i had to import the data in SQL Server table and then use the same in Power BI.

 

Regards,

Chetan

 

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.