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.
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