I want to go over some of the best practices in optimizing your rows and columns inside the Query Editor.
You can optimize your table so that when you create visualizations, you’ll be able to recognize your columns easily.
One of the best ways to develop the most intuitive Power BI model is to simplify your rows and columns inside your table.
Furthermore, this tutorial will give you data transformation techniques that are important when using Power BI. These techniques will help you in the DAX formula and visualizations that you’ll create in the future.
Cleaning Up Your Data Set
To understand things better in your data set, you need to clean up your data. When you clean and optimize your data, you can interpret it quite easily. You can also get rid of redundant information that only serves to use up memory in Power BI.
There are several ways that you can clean and transform your data. It includes optimizing your columns, removing irrelevant information, and so on.
To get started, click the Transform ribbon. You can see a number of options that you can do with your selected query. Some of the things you can do include grouping, reversing, counting, and transposing the columns.
There are a lot of things that you can do from here. One example is changing the format of your text by clicking Format under the Transform ribbon.
You can also click the Add Column ribbon to explore more options for your data set.
You can also do transformations by right-clicking any column and selecting an option such as Remove, Remove Other Columns, Duplicate Column, or Add Column From Examples.
If there are any errors inside your table, you can click Remove Errors to clean the respective data. In addition to that, you can also see options to group, fill, pivot, unpivot, and move your Power BI columns.
Lastly, you can make use of the filter feature inside Power BI columns. Just click the drop-down in the column name, and then filter the data accordingly.
Optimizing Your Power BI Columns
Let’s delve deeper into those techniques I mentioned. I’ll show you real examples of how you can optimize your columns. The first scenario is about removing a column from your table.
Let’s say that the Currency Code column is redundant information. You can remove this particular column by right-clicking and selecting Remove from the options.
After the Currency Code column is removed, take a look at the Applied Steps section on the right side. This section keeps a record of all the actions you’re taking inside the Query Editor. For instance, you can see the Removed Columns entry.
The Applied Steps section is important because it records all the data transformations, so when you refresh your data, the changes occur automatically. These records can help you when you want to go back to a previous step by clicking the X icon.
Now, I’ll give you another example of a different scenario. For instance, you want to create a similar column for Warehouse Code. However, this time you want its contents to have only the first four letters.
The first thing you need to do is to duplicate the column. To do that, right-click the Warehouse Code column and then select Duplicate Column.
On the duplicated column, right-click again, and then select Split Column by Number of Characters.
After that, you can see the Split Column by Number of Characters pop-up window. This is where you can enter the number of characters. Also, you need to select the Once, as far left as a possible option.
Lastly, I want to show you how to use the Add Column From Examples feature of Power BI.
You can use it to create another column for Channel Code. But this time, you can change the entries to the first four letters.
After you select Add Column From Examples, you can see a new column where you can enter the new values. So, instead of Wholesale, type WHOL, and you can see it applied to the rest of the entries in the column.
For the other code, change the DISTRIBUTOR code into DIST and it will also be applied to similar entries. This is possible because of the learning algorithm that works in the background.
You also need to change the column name to Channel Code. Now, you can utilize this column inside your Power BI model.
***** Related Links*****
Power BI Query Parameters: Optimizing Tables
Build A Comprehensive Date Table In Power BI Really Fast
Power BI Financial Reporting Tips and Techniques
Conclusion
Hopefully, this tutorial helped you think about the importance of data transformation within Power BI.
I highly recommend you explore all those options to optimize and transform your Power BI rows and columns. You can experiment and test out these techniques using the features inside the Query Editor.
Always remember that the queries you’re testing out are not yet committed to your model. Thus, you can do a lot of transformations and clean up for your data.
There’s no perfect technique for using Query Editor. The most important thing is to know and explore its various features as much as possible.
Sam
