How much Power Query to Engineer-out at the source?

[ericOnline]

Hello,

I'm learning a lot about PowerBI and its components. I do like the power of Power Query as far as transforming data and cleaning it up. But this also adds complexity to things. 

• How much Power Query should I engineer-out of the data at the source if possible?
• Where do professionals draw the line?
• How do you surface all the Power Query that is applied against a table?
  • I'm thinking in terms of long-term maintainability
  • Handing a .pbix report over to a rookie and having them wade through the PowerQuery vs. taking care of that at the data source...

Very curious to hear what @ImkeF and @Greg_Deckler have to say on the matter. 

Thank you

[v-xicai]

Hi @ericOnline ,

You may refer to the links below learning how to transform data in Power BI.

Tutorial: Shape and combine data in Power BI Desktop

Transform, shape, and model data in Power BI - documentation

You may try to improve the performance for the data model, see Power BI performance best practices , Power BI Performance Tips and Techniques.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

As always, it depends.

But in most cases, if you can do the transformations at the source: Do it.

Also: Dataflows are a very good alternative to use M-language and manage those transformations centrally.

Many options...

[ericOnline]

Thank you @ImkeF for the response. I will research Dataflows as a way to simplify and centralize transformations. 

[ericOnline]

@amitchandak and @v-xicai ,

Thank you for the reference material. I'm more looking for experience from professional PowerBI devs on where they draw the line in regards to transformations done in PowerQuery and at the source. Also how to make Power BI code more maintainable for the long-term.

[amitchandak]

Refer: https://docs.microsoft.com/en-us/power-query/power-query-quickstart-using-power-bi

https://docs.microsoft.com/en-us/powerquery-m/

https://docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language

https://www.howtoexcel.org/power-query/m-code/