4 Ways to Transform Data in Power BI Desktop
While making transformations in Power BI Desktop there often arrives a question which is the best method/place to do the transformations?
Transformation method you choose becomes critical if you are dealing with large data sets (million+ rows) and can impact performance of your model and overall user experience.
Below are some of the methods you can adopt.
1. Relational Database:
If you are connected to any of relational databases like Microsoft SQL Server, Oracle DB, etc and you have the access to make transformation within the database then it is the best place to do the transformation before loading that data in Power BI environment.
2. Native Query (within Power Query):
Another method which is less talked about is using native query.
• Using this method you can make the necessary transformations using SQL script.
• The native query fetches the transformed data directly from the database(like SQL Server, Oracle) hence avoids/minimizes your transformations in Power Query.
3. Power Query:
The most widely used a.k.a the lazy way :D of getting the things done is Power Query.
In this method complete data is loaded into the Power Query model and transformation are made over it.
• Transformations like adding custom columns, concatenation , counting rows etc can be doing using option shown in below image (#1)
• The transformations made are automatically recorded in ‘Applied Steps’ section(as shown in #2) using ‘M’ Language.
4. Power BI Desktop:
In this method you can transform data by using DAX and perform combining of columns, summarized tables, grouping etc in Power BI Desktop. However this is really not the place where you want to do transformations instead the focus here should be on data modelling & visualizations only.
Note:
• Transformation method preference:
Relational Database>Native Query> Power Query> Power BI Desktop
• Loading/Refreshing of data in case of relational database and Native query method is 40–50% better in comparison to other two methods.
• In case of native query method note that the SQL code used is dependent on database.
For example if you want to connect to SSMS then use Microsoft SQL server script and for Oracle Database use Oracle SQL script.
• Using relational database method and native query method avoids the hassle of learning another language (i.e. M language) used in Power Query.
Do like this post if it helped you in some ways :)
