Reporting from Excel file system using Excel Online in Power BI Service

[poweredsoul]

We do not have a data warehouse at present and I am trying to report on Excel files stored on premise in a shared drive. The total size of all the files would be nearly 40 GB. 

I am wondering if I can use the gateway to upload the files into service and use power Query online to do transformations and aggregations online without having another license other than the Pro licence and incuring further costs.

Is there another approach that I can take to enable performant transformations using Excel as data source?

[poweredsoul]

Hi Miguel Félix,
I do not have a gateway set up.

I would request for a gateway to be set up to the map to the central repository.

I tried doing the transformations in Power Query within Power BI desktop and it takes a lot of time to refresh the previews due to the size of the files and the number of merges involved. 
Would it be possible to enhance the performance of preview refresh by doing the same transformations in power query online using excel files on prem?

[MFelix]

Hi @poweredsoul ,

The preview online will also not be much more performant, being the case of your preview assuming you are working with several files that are beig appended based on a function on Power Query I would advise the following:

• Create a parameter with the path of your files
  • This parameter is to be used on your source of your data
• Create a folder where you get only one or two files and make all the transformations you need
  • This will reduce the preview size and allow you to work better on the power query or dataflow
• Publish the report and then on the service change the parameter to the final destination where you have all your files.

This approach allows you to manage and be more productive on developing your report and model since you will be working with a parcial dataset reducing loading times in development.

[poweredsoul]

Hi Miguel Félix,
Thanks a lot for the reponse. 
I will try doing it.
Changing the parameter on the service should help.
2 Quick questions on that
1. Once I have the full data by changing the parameter on the service, how can I do the validation on the full data in Power Query?
2. Does loading the full data by changing the parameter on service require a gateway to be set up? I think it does.

[MFelix · Accepted solution]

You will always need to setup the gateway since you have on prem files.

The parameter is to land on a different folder thart has less data and then when you connect on the service you change the folder to the actual one.

[MFelix]

Hi @poweredsoul ,

You can use the gateway to access directly the files on the Dataflows  (Power Query online) you just need to select the correct connection when creating the dataflow:

You can also do the transformation on the desktop and then when you do the publish to the web you connect to the gateway and the transformations will be assume on-prem