General data transformation guidance

Power Query allows users to build relatively complex data transformation pipelines through a point-and-click interface. Each step of the query is defined by a line of M script that has been autogenerated by the UI. It’s quite easy to load data from multiple sources and perform a wide range of transformations in a somewhat arbitrary order. Suboptimal step ordering and configuration can use the necessary resources and slow down the data refresh.

Note

Using Power Query to do more than connect to a data source and add/remove column transformations should be used for small data sources. If you find yourself having to transform large tables and data sources, the data source structure should be examined with dimensional modeling techniques in mind. This will push the transformations to the ETL level instead of Power Query. Semantic models are meant for analytical reporting and adding other types of reporting to Power BI Desktop can cause lots...