March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
32 | |
24 | |
12 | |
11 | |
9 |
User | Count |
---|---|
47 | |
46 | |
23 | |
12 | |
9 |