It is general that time is not always enough for us to achieve all we want. It is therefore necessary for us to know how best to go about the repetitive actions that we take every day if there is a way out. In this article, I will be sharing with you some ways you can automate mundane tasks using Power Query.

What is Power Query?

Power Query is an ETL tool used for retrieving data from a source, processing it into a form conformable for analysis, and loading it into a system.

Uses of Power Query

There are quite a number of activities that Power Query can do for a user. The list below explains some of them:

1. Data cleaning: Especially when dealing with real-life data that are primarily dirty, it is necessary to do a bit of cleansing. With this, power query can help identify misspelled entries, duplicates, missing rows, etc.
2. Data transformation: Transforming data is an important step prior to data analysis. This takes care of misspecified column data types, table transposing, replacing values,
3. Data retrieval: Power query makes it easy for you to extract data from various sources including Oracle, XML, Azure, CSV, analysis services, etc
4. Data loading: Loading of data after processing is made easy with Power Query.

💡 If you are using MS Excel 2016 onward, you can access Power Query on the Get & Transform Data Tab, while for MS Excel 2010 and 2013, Power Query is available as an add-on for you to download. It is however not available for MS Excel 2007 downwards

Reasons you should automate your repetitive tasks

There are several reasons we should adopt automation of the monotonous activities that we indulge in. They include but are not limited to:

1. Time-saving: The utmost reason for automation is that it helps us in maximizing our time.
2. Opportunity for diversifying: When we have a lesser amount of work to do, this will create a chance for us to consider exploring some other beneficial things.
3. Increase in the output: It is clear that having the boring tasks (included in our daily activities) taken care of will allow us to yield more in a lesser time
4. Improved efficiency: Having our daily tasks reduced will encourage us to perform better and deliver better results.

Examples of tasks that can be considered for automation

There are several tasks that take our time everyday which can be

1. Gathering of data
2. Cleaning of data
3. Data manipulation

How to automate your routine tasks in Excel

In this article, we will be using Power Query for automating some data transformation steps.

The data we will be using for this is the Sample Superstore dataset. You can find the link to download the data here.

Now let’s begin…

• Open a blank MS Excel file on your computer.

Search and Open Microsoft Excel on your start menu after which you select a blank workbook.

• Launch the power query editor.
  1. Select the Data Tab
  2. On Get & Transform pane, click on New Query > Combine Queries > Launch Query Editor.

• Load in the data
  1. On the Power Query editor, on the home tab, navigate to New Query
  2. Click on New Source > File > Excel
  3. A window appears where you browse to locate your file and select it.

💡 We chose to select Excel here because the Superstore data has the.xls extension. Excel allows many types of data including XML, ODBC, Web, Facebook, SQL, and so on.

The Superstores dataset has 4 sheets but we will be dealing with the Orders sheet alone.

💡 You can see the Query Settings being displayed on the right-hand side. Applied Steps displays the changes being made to the table from time to time. In case, you will like to revert any changes at any point, then you simply click on the X symbol on the highlighted step

• Checking for anomalies in the data
  1. Checking for missing values in the columns
     1. Click on the dropdown arrow beside the column name
     2. A list of the entries appears with some filtering options up
     3. Click on Remove Empty to delete all the missing rows that are included

1. To verify that the data type of each column is correct
   1. Select a column (starting from the Row ID column).
   2. Go to the Transform tab
   3. Under the Any Column pane, you will see there the data type of the selected column. It is important for you to confirm that the data type being displayed corresponds with the entries in the column.
   4. If it corresponds, then you move on, if not change it by selecting the appropriate one from the dropdown list beside Data Type
   5. Repeat the same for all the columns
   6. The data types of columns Sales and Profit should be changed to Currency

• Creating a column for the duration of the delivered item
  1. Click on Custom Column from Add Column tab.
  2. A box appears for you to input your computations
  3. Change the data type from Any to Whole Number

Cost Price = [Sales] - [Profit] 

• Creating a column for computing the Cost Price from each order

i. Click on Custom Column from Add Column tab.

ii. A box appears for you to input your computations

iii. Change the data type from Any to Whole Number

Cost Price = [Sales] - [Profit]

• Go to the home tab and click on Close and Load.

• Plot a chart describing the average duration of items delivered by region
  1. Go to the Insert tab and select Pivot Table from Tables
  2. A box appears requesting the range of data you want to analyze and also the location you will like to plot your table on.
  3. Click Ok once you are done.
  4. from the PivotTable Fields, drag the Region field to Rows and Duration of the delivered item field to Values
  5. Change the calculation type to Average. The default type of calculation is Sum.
  6. Click on the dropdown button beside the Duration of the delivered item, and select Value Field Settings
  7. From the list of calculation types, select Average. and click OK
  8. Go to the Insert tab and select the Column chart

• Formatting the chart

For the sake of what we want to prove, we will display the data labels.

1. Right-click one of the bars
2. Select Add Data Labels

• Confirm the automation
  1. Add more rows to the Superstores data in its original location.
  2. Go to Data tab and click on Refresh all.
  3. The chart will be updated
  4. The table will also be updated especially the two custom columns; Duration of the delivered item and Cost Price

Interesting!. This and many more are things that are easily achievable using Power Query.

We hope that you found this blog exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!