Centralize the management of power query transformations??

[raymondpocher]

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 🙂

[raymondpocher]

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

[DataInsights]

@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 

[Thejeswar]

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]

@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?

[DataInsights]

@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... 

[DataInsights · Accepted solution]

@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...