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 in 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 column.
If you noticed that a certain column is not necessary to your model, make sure to remove it. Just right-click the column title and then select 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, a 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 a decimal number, whole number, percentage, date, time, 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.
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.
***** Related Support Forum Posts *****
Query Editor-Financial Reporting-Data Transformation Issue
Advanced Data Transformations & Modeling
For more transform data power BI queries to review see here….