Office – STL

In conversation with one of our Excel / Power BI trainers.

In this post, we sit down with one of our training delivery team to look at the pros and cons of Power Query in Excel, as a new way of working with data.

Jens, how would you describe Power Query in Excel in one word?

MAGIC!

That has my attention! How has Power Query changed the way you work with data?

When I started using Power Query five years ago, I was blown away. This tool has completely changed my way of working in Excel.

Power Query has reduced the time I spend in Excel dramatically and has minimised or removed boring repetitive tasks. This has allowed me to gain an elevated level of efficiency. Power Query has also minimised my use of VBA coding in Excel.

So, how does Power Query work?

One might see Power Query as a machine. Once you have built the “machine”, it will continuously repeat the tasks for any new data added to Excel. It is a much easier alternative to automating processes than VBA programming because the Power Query does not need coding.

Sounds good! What are the most useful key features of Power Query?

With 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

Let me explain each of these points:

Connect live to an external data source

You will find the tools to connect to external data on the Data tab in Excel, in the Get & Transform Data group. I normally tell my clients that you can connect to any live data if you have legal access to the external data. You may need to get help from your IT department to connect to your source. However, once you have created the connection, you can create your reports, analysis, or dashboards. They will auto update when you add new data to the source.

You can also connect to a folder, which is extremely useful if you receive data from clients. If you connect Power Query to a folder, the only thing you will have to do when you receive a new file, is to drop it in the folder you have connected to. Your report, dashboard, or analysis will update and include the new data from the added file.

Structure internal and external data

Through my work as an Excel trainer, I often notice how users struggle with badly structured source data in Excel. If the data is structured incorrectly, everything gets much more complicated and time consuming.

Excel prefers data in lists. You should arrange all source data in well-structured lists.

It does not really matter how you structure your data is initially. Power Query can restructure your data sets and when you have set up the query, the “machine” will restructure newly added data automatically.

The Power Query Editor:

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

Have you been in a situation where US dates, extra spaces, unprintable characters, spelling mistakes, empty rows and columns, incorrect formatting, or hundreds of other issues, have caused you endless cleaning work in Excel? Then Power Query is the right tool for you. And again, when the query is set up to do the job, it will do it for all data added in future.

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

Connect to multiple 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 Pivot Tables and the Subtotal tool in Excel but is also an extremely useful part of the Merge Data tool which generates related keys between tables.

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

Automate tasks

Like the macro recorder in Excel, Power Query records the steps you do in your data sets and writes code in a language called Power Query M.

Code example:

One of the differences between macros and Power Query is the way Power Query executes the code. Recorded macros will only run on a user command. Power Query M will automatically execute the steps every time.

Transfer data from the query connection to the Excel data model

Excel has a limit of just over a million rows per sheet. However, the number of rows you can add to the memory of the Data Model is almost unlimited. Few Excel users are aware of the data modelling tool called Power Pivot. Not only can it store billions of records, but also, the compression technology Microsoft has developed for this tool is outstanding.

If you are working with huge data sets, then using Power Query and Power Pivot together is a winning combination. You can connect to huge data sets, 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.

I am not saying it is easy, but a good understanding of databases, Vlookup, and Pivot Tables can be particularly useful towards understanding related data.

Are there any cons to using Power Query, Jens?

I called this article “The Pros and Cons of Power Query” but after five years of using the app, I have seen only pros.

All Excel users above a basic level should get a good understanding of this tool. It will change how they use Excel. For most users it will also have a massive impact on the time spent in Excel.

How would you summarise the pros and cons?

Pros:

A positive impact on the efficiency of Excel tasks
Removal of boring time-consuming repetitive tasks
Automation of tasks without complicated VBA codes
Improved data quality and structure

Cons:

None!

Thanks, Jens, for an excellent explanation of the pros and cons of Power Query. To be honest, I can see only pros!

changing to Office 365

Office 365 has more applications, as well as faster and more collaborative ways to work and communicate than ever before. There are both online and desktop versions available for most of the apps. Office 365 also offers amazing new tools and features. Are you upgrading to Office 365 but finding that managing the changeover is tricky?

Change brings challenges

The end user is often the last stakeholder to be considered when a business makes the change to Office 365. How do end users normally feel about the decision made by the business to make the software upgrade?

When any business undergoes periods of change, there will be a sense of uncertainty and hesitancy amongst the staff. The leaders of the business need to address these issues before they become concerns.

The solution is the pairing of training and support from Management. This will see any project through from the initial announcement through to its delivery and execution.

The business needs to address the following key areas to ensure that the emotional and practical needs of the workforce are met. Implementing these steps, the business will deliver on employees’ expectations.

COMMUNICATING THE CHANGE

End users will be moving to the Cloud with Office 365. For a lot of people, this could be a major change to how they work, depending on what they do. The management team needs to communicate the plan clearly and manage the change effectively. This will ensure a smooth transition.

Management also needs to communicate the reasons for the change. Keep the staff informed of the milestones of the journey and the dates when the key changes will be taking place.

WHAT does EFFECTIVE END USER TRAINING LOOK LIKE?

People often ask, ‘What exactly is Microsoft Office 365?’ The simple answer is secure access to cloud based email, calendars, websites and Office applications, anytime, anywhere.

A common misconception is that Office 365 is complex, technical and hard to learn. To address this, the business needs to provide a comprehensive training programme to ensure that all staff become confident and efficient end users.

The best approach is to use a blend of practical instructor led workshops where delegates will be shown how to access and use a variety of apps and functionalities in order to maximise the adoption of Office 365.

The training should take place a considered, inclusive, and safe environment. The delegates’ learning needs to be supported with plenty of time allotted for questions, demonstrations and practice.

POST TRAINING SUPPORT

Many businesses and training providers fall into the trap of not providing enough floorwalking support in the post training phase of Office 365 deployments. Having this extra degree of support once users are in a ‘live’ environment can make a real difference in meeting the needs of end users. It will guarantee a smooth transition as users embrace and fully adopt the new technology.

By always keeping in mind the emotional and practical needs of staff during a period of major change, with the right layers of support, end users will eventually start using the technology to its full potential, allowing the business to prosper.

Upgrading to Office 365 and managing the changeover can be tricky but effective management and training will ensure a successful roll-out.

More STL Blogs on Office 365:

15 Reasons Your Business Should be Using Office 365