cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raymondpocher
Advocate II
Advocate II

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 II
Advocate II

@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 II
Advocate II

@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
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.