Microsoft Office Archives - STL

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

When managing a project using Microsoft Project, stakeholders often need to receive progress updates. Because not everyone can read a Gantt chart, MS Project comes with a set of pre-designed reports and dashboards to help you better understand your data. You can easily create amazing reports with Microsoft Project. Below is an explanation of the different types of report that can be created.

Dashboard Report Types

Burndown

How much is completed on a project and what’s left to be done.

Cost Overview

The current status of top level tasks showing planned, remaining costs and cumulative costs.

Project Overview

How much of your project is complete, upcoming milestones, and tasks that are past due.

Upcoming tasks

The work that has been done in the current week, the status of any remaining tasks that were due, and what tasks are starting in the next week.

Other Report Types

Cash Flow

The cost and cumulative cost per quarter for all top level (summary) tasks.

Resource Cost Overview

The cost status of work resources showing cost details in a table and a chart showing cost distribution.

New Reports from templates

Chart
A chart for your project data, showing actual work, remaining work, and work by default.

Table
A table for your project data, showing the Name, Start, Finish, and % Complete fields.

Comparison
Two charts side-by-side, showing the same project data.

Visual Reports

View visual reports instantly in Excel or Visio

In MS Project, you can also view specific project data as a visual report in Excel or Visio, if you have those programs installed. Select Reports, Visual Reports, select an Excel or Visio report for your project data, and click View. Excel builds a local Online Analytical Processing (OLAP) cube file and shows your data in an Excel PivotChart or Visio PivotDiagram. This is a legacy feature from MS Project 2010 and earlier.

Examples of Reports

Here are a few visual examples based on a sample project.

Cost Overview

The Cost Overview report shows the following:

Costs, Remaining Costs and % complete.
A chart of cumulative costs.
A chart of cost status for top level tasks.
A cost status table for top level tasks.

Reporting changes to a project

In the same example, the first two phases of the project were completed as planned. There is a delay of 4 days with excavation due to bad weather. The project plan shows which tasks are complete.

The Cost Overview Report now reflects the changes made and shows the project as 14% complete. The Cost Status table identifies the cost variance for the Foundation phase.

Burndown

Select Report, Dashboard, Burndown to show graphically how much work and how many tasks are completed as well as what’s left to be done.

Overallocated Resources Report

Select Report, Resources, Overallocated Resources to identify which resources are overallocated and at what points in the project. This is a great way of seeing where unnecessary costs are being lost.

Cashflow Report

Being able to report and analyse your cash flow visually is a great way to track the spend of a project. It also makes the data digestible and ideal for sharing with clients and colleagues via email or PowerPoint presentation.

Select Reports, Cost, Cash Flow to see a chart and table of the project cash flow.

To change the timescale of the report from quarters to months:

Select the chart, click the Edit button for the Time category in Field List and choose Months.

To learn how you can create amazing reports and project plans with Microsoft Project, why not consider an STL training course on Microsoft Project.

Blog Categories

Category: Microsoft Office

The Magic of Power Query