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.
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.
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.
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.
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?
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 @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.