Best Practices For Transforming Data In The Query Editor

by | Power BI

When you transform your data inside the Query Editor, there are important practices that you need to implement to get the best experience.

These are simple tips that can have a big impact on your reports and visualizations. It includes the things that people often take for granted but are essential in getting high-quality analysis from Power BI.

Changing The Table And Column Names 

Once you start transforming your data in Power BI, you should always use the correct naming conventions. This includes using intuitive names for your tables, columns, and measures. As much as possible, follow the proper format. For example, don’t name it using all capital letters or all lowercase letters. 

Check all the column names and make sure they all start with capital letters. Just take some time to check all your columns from left to right and change the names if needed.

In addition to that, don’t use underscores or abbreviations. Lastly, you should avoid using prefixes or suffixes for the column names. 

The goal is to simplify everyone’s Power BI experience. You need simple names so any user can understand your report or model easily. You can also create DAX measures easily if you have simple column names.

To give you an example, I’m changing the name of this table to Sales.

You can change it in the Properties section. You can also change the table name simply by double-clicking it on the left side of the screen.

Removing Useless Columns

Another best practice tip when you transform your data in Power BI is to remove any useless columns. 

If you noticed that a certain column is not necessary to your model, remove it. You can do this by just right-clicking the column title and then selecting either Remove or Remove Other Columns.

When you remove useless columns in advance, you can decrease the memory needed to load the data in Power BI. Moreover, clean data will save you more time in the future.

Making Sure The Date Types Are Correct

Another thing that I want to highlight is the importance of correct data types. When you transform your data in the Query Editor, it is important that you have your columns in the correct data type.

Generally, there are various data types available in Power BI. When you load data, Power BI automatically detects the data type of your columns. However, there are times when there is an error. Therefore, you need to check each column manually. You can check what your column’s data type is when you right-click the column. 

Data types can be decimal numbers, whole numbers, percentages, dates, times, text, and more. You have to check the individual columns and make sure they have the correct data type.

For example, the Order Date and Ship Date columns should be date-type columns. Meanwhile, Order Number and Customer Name Index should be number-type columns.

When Power BI incorrectly identifies a column, you just have to change it manually. For instance, the Delivery Region Index has been identified as a text column instead of a number column.

Just right-click the column, and then change it to a whole number.

Some things can go wrong during your DAX calculation when your columns have incorrect data types. For instance, a particular column won’t show up in your visualization or you’ll get an error in your DAX calculations.

To make sure everything runs smoothly in your future calculations, make sure to set things up correctly. 

***** Related Links *****
My Practice Tips When Using The Power BI Advanced Editor
Unpivot And Pivot Basics In Power BI – Query Editor Review
Power BI Query Parameters: Optimizing Tables

Conclusion

Whether you’re a new user or not, I hope you apply all the best tips that I have discussed in this tutorial. It will not only help you transform your data more quickly, but it can also help you create a strong and clean model for your Power BI.

In the next tutorials, you’ll learn more data transformation techniques for your rows, tables, columns, queries, and more.

Thanks!

Related Posts