Select Page

Making Life Easier With Power BI’s Query Editor

by | Apr 7, 2020 | Articles

Microsoft Excel is a truly 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, as 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 came out with Power BI, which serves as a perfect tool to take over some of the functions from Excel.

We won’t focus on complex calculations and coding that is possible on Power BI, but rather look into relatively easy functions and data manipulation techniques that the majority of business users with Excel knowledge could start doing right away after installing Power BI. With a bit of patience, Power BI will save you a lot of time and from many tedious tasks.

  • File size – 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 via its query editor, Power Query, which 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 is based on 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.
  • 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 functions are designed to perform dynamic arithmetic calculations on large datasets, cross filter across different tables and work with relational data. For business users this means they can do more complex, insightful and faster analysis even using half a dozen basic DAX formulas.
  • Refreshing data – 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, which would 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, hit refresh in Power BI and go make yourself a cup of coffee.

As you can see, Power Query is an extremely powerful tool, which can make our work a lot easier and faster. Instead of wasting your time doing monotonous tasks in Excel, you can spend more time analyzing data and finding insights.

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 and 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 and why is this article endorsing Power BI so much and bashing on Excel? The truth is, Power BI is essentially a combination of various Excel add-ons – Power Query, Power Pivot and Power View – all 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.

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.

Share This