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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
raymondpocher
Advocate III
Advocate III

Centralize the management of power query transformations??

Hello people 🙂

 

I am not so sure how to name that topic but I have the following use case:

 

  • I am using a specific datasource table in many reports ( I cannot use datasets as each report requires other data)
  • The table is complex so I woul have to do many transformations in power query
  • If I must adjust anything in the transformation I would have to adjust the query in every single report
  • Since this is time consuming I'd appreciate a simple solution
  • I was thinking that it might be possible to (1) connect to a datasource, (2) do all transformations in power query just ONCE, and (3) then instead of connecting to the datasource just connect to the transformed power query output.

 

Please tell me that power bi offers anything like that 🙂

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@raymondpocher,

 

Power BI Service offers dataflows, which is Power Query online. You transform the data once, and then each report can import the dataflow.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-serv... 





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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
raymondpocher
Advocate III
Advocate III

@DataInsights is there however a more in expensive way to do that? 

@raymondpocher,

 

You could create reports that use a composite model, which is a combination of DirectQuery and Import data sources. The DirectQuery data source would be a published dataset consisting of the one table that you want to centralize. Then, other reports would be sourced from this published dataset, and in these reports you would add Import data sources.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models 





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

Proud to be a Super User!




Hi,

But this solution would require @raymondpocher , to do the transformation on that one table that he is trying to centralize in each of these reports.

 

Instead, I feel that he builds a view/ a dataset with this table with all the transformations done in it. This can be published as a standalone dataset. This then can be imported to all the reports by filtering the data while importing the dataset in a multi dataset setup as the only differing factor is the data that the report uses.

 

Regards,

 

raymondpocher
Advocate III
Advocate III

@DataInsights thank you.

If I understand correctly it only goes with a premium license. I believe the base price is something around 5000 USD a month. Do you know if there is a more inexpensive solution as well?

@raymondpocher,

 

Certain dataflow features require a premium license or embedded capacities. You could try creating a dataflow and see if the features you need are available via your paid Power BI Pro license.

 

https://docs.microsoft.com/en-us/power-query/dataflows/what-licenses-do-you-need-in-order-to-use-dat... 





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

Proud to be a Super User!




DataInsights
Super User
Super User

@raymondpocher,

 

Power BI Service offers dataflows, which is Power Query online. You transform the data once, and then each report can import the dataflow.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-serv... 





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

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors