Migrate Data Transformation Process from Power BI's Power Query to Excel's Power Query

[Anonymous]

Hi all,

I have a problem where my Power BI report is currently pulling and transforming data from so many excel files. I have configured the data transformation processes in PBI's Power Query which produces one large data table. Moving forward, the database will grow and i fear the PBI file will get bogged down due to the connections to so many source files. Therefore, I would like to introduce an Excel file which acts as an intermediary that pulls and transforms the data (using Excel's Power Query) before it gets into Power BI. I am not sure if this addresses my concern, but if it does, how would i go about doing it without having to configure the data transformation again?

Thank you in advance.

[Anonymous · Accepted solution]

Hi @Anonymous 

If your purpose is to separate the transformation process from the report-create process in Power BI Desktop, you can consider using the dataflow in Power BI Service. Dataflow is like Power Query online. It can connect to various data sources and transform data. After transformation, it can keep the data in Power BI Service. You can then use data from it to create multiple reports. As this uses the resource in Power BI Service, you need to have at least a paid Power BI Pro license. If a dataflow connects to Excel files in the on-premises computer, you need to configure an on-premises data gateway for refreshing the dataflow. 

Introduction to dataflows and self-service data prep - Power BI | Microsoft Learn

What licenses do you need to use dataflows - Power Query | Microsoft Learn

If you want to use the Excel Power Query and store data in an Excel file, you can copy queries from Power BI to Excel easily. In Power Query Editor of Power BI Desktop, select the queries, right click on them and select Copy. Then go to Power Query Editor in Excel, Paste them into Queries pane directly. You can also copy the M code from Advanced Editor directly. Share a query - Power Query | Microsoft Learn

If the data may grow very large in the future and the Excel might have a poor performance on storing it, you might need to store data in a database or somewhere else to improve the performance. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

Have you considered using a database for your storage needs?