Forum Discussion
Alternatives for transforming data (PBI desk) using a DataFlow with Import-instead of Direct Query
Thankyou, Akash_Varuna, for your response.
Hi EduBubicz,We appreciate your inquiry in the Microsoft Fabric Community Forum.
Based on my understanding, please find below an approach using Power BI Dataflows with Parameterized Queries in Power BI Desktop. This approach fully meets your requirements, is user-friendly for analysts, and ensures scalability without the need for SQL. It allows filtering before import, supports sample previews, and requires minimal maintenance.
Kindly follow the steps outlined below which may help resolve the issue:
-
In Power BI Service, create a dataflow for your service order items table (approximately 100 million rows) using the appropriate connector (e.g., SQL Server). Apply initial transformations (such as removing unnecessary columns) in Power Query Online and save the dataflow. If needed, enable Incremental Refresh to partition data by date (for example, yearly) to facilitate faster refreshes (this requires Premium capacity).
-
Set up parameters in Power BI Desktop. Connect to the dataflow by navigating to Home > Get Data > Power BI Dataflows. In the Power Query Editor, create two parameters: CustomerID (Text) to filter by customer, and DateRange (Date) to filter by date (or alternatively, create StartDate and EndDate parameters). Apply these filters in Power Query (for example, CustomerID = CustomerID parameter and OrderDate >= DateRange parameter) to reduce data volume before import.
-
Preview a sample for ETL. Temporarily limit the data to 5–10 rows using “Keep First Rows” in Power Query Editor to define the ETL transformations (such as pivots and merges). Once the transformations are finalised, remove the row limit and apply the parameterized filters to import the filtered dataset.
-
Analysts can open the .pbix file, update the CustomerID and DateRange parameters in Power Query Editor, and refresh to import only the filtered data. They can then perform custom ETL and build reports using the Power BI Desktop interface.
-
Publish the .pbix file to Power BI Service and share it with analysts. Set up a refresh schedule for the dataflow to keep the data up to date.
Additionally, please refer to the following link for more information:
Parameters - Power Query | Microsoft LearnIf you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Should you have any further questions, please feel free to contact the Microsoft Fabric community.
Thank you.
-
Hi EduBubicz,
Thank you for your follow-up. You are absolutely correct; the solution involves using a master .pbix file in Power BI Desktop, which is configured once with parameters such as CustomerID and DateRange, along with the required transformations. Analysts can then open this file, update the parameters to filter the data dynamically, and import only the filtered subset, thereby avoiding the need to reload the entire 100 million rows.
We are pleased to know that you find this approach feasible. It ensures a user-friendly experience for your non-technical analysts, eliminates the need for SQL queries, and reduces maintenance efforts by relying on a single dataflow.
If you find our response helpful, kindly consider marking it as the accepted solution and awarding kudos. This will assist other community members facing similar queries.
Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.
Thank you.
Thankyou, Akash_Varuna, for your response.
Hi EduBubicz,
We appreciate your inquiry in the Microsoft Fabric Community Forum.
Based on my understanding, please find below an approach using Power BI Dataflows with Parameterized Queries in Power BI Desktop. This approach fully meets your requirements, is user-friendly for analysts, and ensures scalability without the need for SQL. It allows filtering before import, supports sample previews, and requires minimal maintenance.
Kindly follow the steps outlined below which may help resolve the issue:
-
In Power BI Service, create a dataflow for your service order items table (approximately 100 million rows) using the appropriate connector (e.g., SQL Server). Apply initial transformations (such as removing unnecessary columns) in Power Query Online and save the dataflow. If needed, enable Incremental Refresh to partition data by date (for example, yearly) to facilitate faster refreshes (this requires Premium capacity).
-
Set up parameters in Power BI Desktop. Connect to the dataflow by navigating to Home > Get Data > Power BI Dataflows. In the Power Query Editor, create two parameters: CustomerID (Text) to filter by customer, and DateRange (Date) to filter by date (or alternatively, create StartDate and EndDate parameters). Apply these filters in Power Query (for example, CustomerID = CustomerID parameter and OrderDate >= DateRange parameter) to reduce data volume before import.
-
Preview a sample for ETL. Temporarily limit the data to 5–10 rows using “Keep First Rows” in Power Query Editor to define the ETL transformations (such as pivots and merges). Once the transformations are finalised, remove the row limit and apply the parameterized filters to import the filtered dataset.
-
Analysts can open the .pbix file, update the CustomerID and DateRange parameters in Power Query Editor, and refresh to import only the filtered data. They can then perform custom ETL and build reports using the Power BI Desktop interface.
-
Publish the .pbix file to Power BI Service and share it with analysts. Set up a refresh schedule for the dataflow to keep the data up to date.
Additionally, please refer to the following link for more information:
Parameters - Power Query | Microsoft Learn
If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Should you have any further questions, please feel free to contact the Microsoft Fabric community.
Thank you.
Thanks for the response v-pnaroju-msft.
Just for clarification,
The idea is to create like a master pbix where the responsible for the data load all data in pbi desktop (but in fact only once), configures the parameters as filters and the row limitations and shares for the analysts to open and change the parameters to filter at will, so they don't need to load all data over and over.
Is that right?
I think its a very feasible approach indeed. Thanks for the answer.
Hi EduBubicz,
Thank you for your follow-up. You are absolutely correct; the solution involves using a master .pbix file in Power BI Desktop, which is configured once with parameters such as CustomerID and DateRange, along with the required transformations. Analysts can then open this file, update the parameters to filter the data dynamically, and import only the filtered subset, thereby avoiding the need to reload the entire 100 million rows.
We are pleased to know that you find this approach feasible. It ensures a user-friendly experience for your non-technical analysts, eliminates the need for SQL queries, and reduces maintenance efforts by relying on a single dataflow.
If you find our response helpful, kindly consider marking it as the accepted solution and awarding kudos. This will assist other community members facing similar queries.
Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.
Thank you.