The Magic of Power Query

One word That describes Power Query in Excel – MAGIC! 

 Power Query will completely change the way you work in Excel! For most Excel users, Power Query could be the tool that reduces time spent in Excel dramatically. Power Query could also minimise the use of VBA coding in Excel. Power Query can be seen as a machine. Once the “machine” is built, it will continuously repeat the tasks for new data added to Excel. It is a much easier alternative for automating processes than VBA programming because Power Query does not necessitate coding. In this article, we will explore the magic of Power Query.

The Magic of Power Query

key features of Power Query

You can: 

  • Connect live to an external data source
  • Structure internal and external data
  • Clean, merge, append, and group internal and external data
  • Automate tasks
  • Transfer data from the query connection to the Excel data model
Connecting live to an external data source 

The tools to connect to external data are found on the Data tab in Excel, in the Get & Transform Data group. You can connect to any live data if you have legal access to it. You may need to get help from your IT department to connect to your source, depending on what your source is. When you have created the connection, you can create your reports, analyses, or dashboards and they will auto-update when new data is added to the source. 

You can also connect to folders, which can be extremely useful if you receive your data from clients. If you connect Power Query to a folder, drop any new data into that folder and your report, dashboard, or analysis will update, including the new data. 

Structure internal and external data 

Many Excel users struggle with source data that is structured incorrectly. This makes working in Excel much more time-consuming. Excel prefers lists, and all source data should be in a well-structured list. Power Query can restructure your data sets. When you have set up the query, the “machine” will restructure any newly added data in future.

 The Power Query Editor

Clean, merge, append, and group internal and external data 

If your data contains US dates, extra spaces, unprintable characters, incorrect spellings, empty rows or columns, incorrect formats, or hundreds of other issues, Power Query is the right tool for you. 

Power Query can also merge any number of tables. Say goodbye to complicated and memory-heavy lookup and reference functions. 

Connect to many lists and turn them all into one list by using the Append option in Power Query. 

The Group Data tool in Power Query can replace the use of Pivot Tables and the Subtotal tool, but is also particularly useful as a part of the Merge Data tool which generates related keys between the tables. 

All tools in Power Query are available both for external and internal data. 

Automate tasks 

Power Query works in much the same way as the macro recorder in Excel. The query records the steps you are doing in your data sets and writes code in a language called Power Query M.

Code example: 

One of the differences between a macro and steps in Power Query is how the code gets executed. Recorded macros will only run with user input, so you need to execute the code manually. Power Query M steps will automatically execute every time there is a task to do. 

Transfer data from the query connection to the Excel data model 

Excel has a limit of 1,048,576 rows. However, the number of rows you can add to the memory of the Data Model is almost limitless. Few Excel users are aware of a data model in Excel called Power Pivot. Not only can it store billions of records, but the compression technology Microsoft has developed for this tool is outstanding.

If you are working with huge data sets, the combination of Power Query and Power Pivot is the ultimate winner. You can connect to huge data sets. Then you can clean, structure, merge and append your data in Power Query. Afterwards, you can transfer the data to the Excel data model and relate the data sets. 

Power Query – THe Pros and Cons 

To be honest, there are really only pros. 

Any Excel user above a basic level should explore this tool; it will change how you use Excel. For most users, it will also have a significant impact on the time spent in Excel. Such is the magic of Power Query.

Pros: 

  • Positively impacts the efficiency of Excel tasks 
  • Removes boring time-consuming repetitive tasks 
  • Automates tasks without complicated VBA codes 
  • Makes other tasks simpler because data quality and structure will be improved 

Cons: 

N/A 

Everybody should be using Power BI – 10 Reasons Why

What is Power bi?

Perhaps you have heard it said that everybody should be using Power BI. But what is it, exactly? Power BI is a collection of software services, apps, and connectors. These work together to turn unrelated data sources into coherent, visually immersive, and interactive insights.

The application consists of several elements that all work together, starting with these three basics:

  • A Windows desktop application
  • An online SaaS (Software as a Service) service
  • Mobile apps for Windows, iOS, and Android devices

Built on Excel technology, the application will feel familiar to more advanced Excel users. You create visualisations in much the same way as you do with Pivot Tables. Power BI will make you much more productive  in your data analysis and reporting. This increased efficiency will lead to greater profitability.

everybody should be using power bi

Power BI is easy to use and it’s cheap to train users

It’s intuitive and easy to learn for most users. The training costs are low and learner adoption is rapid.

 it has free mobile apps

Microsoft have released mobile applications that are free for Windows, Apple, and Android.

You can subscribe via email

Most of us would like a static report to appear in our inbox every morning when we power up. All you need to do is to go to your report tab, click ‘subscribe via email’.  You will then receive a nicely formatted PDF document, ready for printing. However, these reports are so much better when you have full interactivity.

it can be embedded into your own custom apps

When working from the ground up, you’ll always have the option of designing your own architecture around it. So, if you want to build your own website and set user access via your own login credentials, this will be no problem for Microsoft.

it is extremely competitively priced

It’s free to try for as long as you want. However, if you want to take advantage of all the enterprise features such as automatic refreshes, controlled sharing of data, then all you have to pay is a mere $9.99 per month.

Power bi has a modelling engine that power users can learn

Microsoft is conveniently enabling a culture of self-service which is breeding a new generation of ‘developers’. Power BI uses a language called DAX (Data Analysis Expressions); this is a functional language that can easily be learned by Excel professionals.

The application has data loading tools that power users can learn

Power Query, which is also built into Excel, is so easy to use, it’s almost embarrassing to the skilled developers out there.

Power BI provides self-service Business Intelligence

The app is self-service Business Intelligence personified. Any competent Excel user can learn to use the tools, allowing them to become semi self-sufficient in their analytics and reporting.

it is an Enterprise-strength tool

Power BI has the foundation to be an enterprise-strength tool. No matter the size or complexity of your enterprise, the application is able to process your data.

consolidation dashboards

Among other things, Power BI is  a data source aggregator. It doesn’t matter where the data originates from, all you have to do is to click on a visualisation to view the information.

It is time for you to invest in Power BI, both the software and the training!