Bad data is a big pain for everyone. It is said that data analysts spend most of their time cleaning data, as it rarely comes in the format they need.
Power Query comes as a perfect solution to this problem. In this tutorial, we will look at a sales table and learn how to clean and format it, so a further analysis could be performed.
Upon importing the sales table to the Power Query editor, users can rely on functions, such as trim and format data, split columns using delimiters, change data types, filter, merge and unpivot columns and rows.
None of these functions require a special knowledge of coding, they are quite simple and can be performed with the right-click of a mouse in the Power Query editor. When you understand concepts behind the transformations, you would be able to shape your data into the format you need in 10 minutes.
When building tables in Power Query, the rule of thumb is to go for less columns and more rows. This gives users the ability to perform a variety of operations, such as filtering out values, calculating differences, counting, averaging and other calculations at which the columnar database structure is far better than the spreadsheet format.