Watch our short video to see how you can quickly transform financial data into a proper database format for further analysis.
Finance professionals spend a lot of their time cleaning data imported from different sources. It is not that data is necessarily inaccurate, it is just presented in a wrong format to make any sort of meaningful analysis right away.
Power Query comes as a help for us to clean this data and make sense of it. The Power Query editor has some powerful tools for cleaning data imported from a variety of sources and finance professionals will appreciate some of these functions.
Financial tables imported from an accounting or budgeting software usually have a wide spreadsheet format – columns with dates going from left to right. A good example of that is data from financial statements and financial models.
In this video, we focus on the use of the powerful transpose function, which rotates a table by 90-degrees, changing the position of rows and columns. This can be helpful in situations when there is a need to stack up row values on top of each other, turning them into columns. In other words, we need to change the structure of a table from a spreadsheet to database format.
When building tables in Power Query, the general rule of thumb is to go for less columns and more rows. It goes back to the Power Query engine, which compresses columnar data very efficiently, improving performance.
However, it isn’t only about performance, but also flexibility to perform more operations, such as filtering out values, calculating differences, counting, averaging and other calculations at which the database structure is better than the wide spreadsheet format.