In this article, we look at Power Query Editor, a data management and transformation tool in Excel and Power BI.
Microsoft Excel is a legendary tool used in many organizations across the world. Companies of all kinds and sizes rely on the spreadsheet software to gather data, manage operations and carry out analytics.
Old habits are hard to break. People keep using legacy Excel spreadsheets for doing tasks the software wasn’t really designed for. Recognizing the need for more robust data management, visualization and analytics tool, Microsoft developed Power BI.
We won’t focus on complex calculations and coding that is possible using Power Query and Power BI. Instead, we will look into relatively easy functions and data manipulation techniques. The majority of business users with Excel knowledge could start using Power Query right after installing Power BI. Below are some advantages of Power Query:
Big data
With the ever-increasing amount of data, file sizes can grow quickly. This can be a problem, as Excel cannot handle more than a million rows. Even when working with a few hundred thousand of rows, it becomes hard to use the vlookups, index and match functions.
Power BI solves this issue with Power Query. This is a great tool to pull various types of data from pretty much everywhere, clean it up and send it to the data model. Power Query uses the powerful M language code and uses the table and column level calculations instead of rows. This is an important distinction, which allows you to handle a lot more data (as much as 10GB in the Power BI Pro plan), making it an effective formatting and storing tool.
Power of DAX
Upon cleaning and formatting data, the next step is to make calculations and analysis. For these tasks you’d use DAX, another programming language integrated into Power Query. Many users find DAX, which stands for Data Analysis Expressions, to be quite intuitive and similar to Excel.
For experienced users DAX provides almost endless opportunities to manipulate data. Without getting too deep into details, it’s key to understand that DAX are great at doing dynamic arithmetic calculations on large datasets. For business users this means they can do more complex, insightful and faster analysis even using half a dozen basic DAX formulas.
Quick data transformation
A common task for many Excel users is to regularly append new worksheets into a master file. Once a week or month, someone would take a new file and attach it to the end of a big master copy. In Excel one must do it manually (it is technically possible to automate it using a 12-line VBA code; but let’s be honest, a few people can do it). In Power Query, manually combining multiple sheets and workbooks is a thing of the past. You can now import all data from a folder, write a simple M code. This will automatically take a new file, which you throw into the folder, and append it to your master file. All you have to do is to download a new file, copy it into the folder, and hit refresh in Power BI.
As you can see, Power Query is an extremely powerful tool, which can make our work a lot easier and faster. Instead of doing monotonous tasks in Excel, you can spend more time analyzing data and finding insights.
Why use Power BI?
Power Query isn’t only available in Power BI. In fact, Microsoft first released it in 2013 as an Excel add-on. Newer versions of Excel already come with Power Query. Chances are that you’re already familiar with it or with the Get & Transform tool.
Some may wonder why bother with Power BI if there is Power Query embedded in Excel? The thing is, Power BI is essentially a combination of various Excel add-ons: Power Query, Power Pivot and Power View. All three packaged together in one tool and sauced with a better engine. The important question is if you’re already using Power Query and Pivot tables why wouldn’t you switch to Power BI? You get all the Power Query capabilities, better visualizations and a much better sharing options.
Keep using Excel to organize data, perform tabular level calculations and understand your data. Once that is done and as your data grows, throw it into Power BI for more effective analysis. Read our previous article on how to effectively combine Excel and Power BI to build interactive financial models and forecasts, for example.
About Centida
If you’re a finance professional looking to integrate modern business intelligence tools in your company, but at the same time, you’re a bit put off by the IT element of it, reach out to us.
Centida consultants combine the IT expertise and finance experience. We follow the principle of “from finance practitioners to finance practitioners”, focusing on the improvement of operational, day-to-day activities in finance and controlling departments with the use of modern BI solutions.