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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
poweredsoul
New Member

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

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?

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
poweredsoul
New Member

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?

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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:

MFelix_0-1728551738676.png

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.