Editing and Duplicating queries

Private: PowerQuery for Finance Teams Editing and Duplicating queries

Editing and Duplicating Queries

Previous Session Summary

In the previous lesson, we covered how to enter the PowerQuery editor for the first time by highting some Purchase Order Data in our current workbook and then going to the Data tab and then going to From Table/Range.

We then looked at the different components of the PowerQuery editor being the Preview Screen, the Query Pane, the Tabs and Ribbon and the Query Settings Pane.

Lesson Synopsis

Now in Lesson 5, we’re looking at how to get back into PowerQuery and then duplicate the PO query that we’ve already performed. This is a really useful option in a lot of scenarios but in this lesson we’re going to use it to duplicate the query that we’ve already performed for our PO dataset.

We’re then going to point the new duplicated query at a PO dataset for another month.

PowerQuery will then go and find the new PO dataset and perform all the same steps as we asked it to for our initial PO dataset.

This means that when we get in a new dataset we don’t need to key in the transformation steps all over again manually. No, we just piggyback of the steps we’ve already recorded in the workbook! Depending on the amount of transformation steps, this could be a great time saving

Resource Listing

The Video

Play Video

01:39 - How to re-enter the PowerQuery Editor from Excel
01:58 - How to change the datatype of a data column
03:58 - How to convert a range of data into a formatted table
04:35 - How can I duplicate a query?
05:02 - How can I point my duplicated query at a new dataset in the same workbook?

Lesson Notes

Datatypes and Changing Datatypes:

As we looked at in Lesson 3, there are a number of different datatypes in PowerQuery including Text, Whole Number, Decimal Number, Date, DateTime, Percentage and True/False. PowerQuery automatically assigns the Changed Type step in certain circumstances, but it is possible to manully change the datatype of data columns.

You might want to do this when PowerQuery hasn’t been able to assign, or hasn’t yet assigned a datatype for you. You’ll be able to tell this as the logo on the column header will be ABC and then 123 underneath. Alternatively, you might want to manually change the datatype that PowerQuery has assigned for you.

Making sure that each column has the appropriate dataype can be really important in PowerQuery. Here’s a couple of reasons why:

a) You may wish to sum a column with numbers in it. However if the column is formatted as a text datatype, you won’t be able to as the PowerQuery sum function can only be summed if the datatype is a number (whole number, decimal number or fixed decimal number).

b) When merging data (which we’ll get to later), you can only merge using columns that have the same datatype.

c) PowerQuery functions are split by datatype – some are specifically for use on Text datatypes, others for Number datatypes, still others for Date datatypes and so on. They can’t be used with columns of a different datatype unless you convert the data first.

To change datatype, in the PowerQuery Editor, highlight the column where you want to change the datatype, and then go to the Home tab and then to the Transform section of the ribbon. From there, select the datatype that you would like.

Duplicating Queries:

Duplicating queries is a really quick and easy way of taking all the steps you’ve performed on one query and then applying them to a different dataset.

When you opt to duplicate a query, no changes are made to the original query, you just end up with an exactly duplicated query. It’s then very straightforward to change the dataset to which you’d like the newly duplicated query steps to apply.

When you duplicate a query, PowerQuery will call the new duplicated query the same name as the old query but put a (2) in brackets at the end of the name. You’ll then be able to see in the Query Settings pane, that all the applied steps in the original query are present.

To duplicate a query, in the PowerQuery Editor, select the query that you’d like to duplicate in the query pane on the left hand side of the screen. Go to the Home tab, and then on the Query section of the ribbon, go to the Manage drop down button and select Duplicate.

Pointing a Query at a new dataset in the same workbook as an original dataset:

Once we’ve got our duplicated query then we need to point the new query at a new dataset – otherwise we’ll just have two queries doing exactly the same thing.

To do this, then we need to go to the Source step in the Query Settings pane and select it to bring the code for the step up in the PowerQuery editor. Hopefully you’ll remember from a couple of lessons ago that the Source step is the step that tells PowerQuery where to go to find the dataset that we want it to transform. At duplication, the code in the formula bar should show something similar to this:

Source = Excel.CurrentWorkbook(){[Name = “Table3”]} [Content]

This code tells us that the query (like the one in the original query) is looking into the current Excel workbook, then looking for an object called Table3, and then getting the content from that object. When you create a Query from a range of data, Excel automatically converts the range of data into a table – in this case, it called the dataset “Table3”.

All we need to do then, to point the duplicated query at a new dataset, is change the name “Table3” to the name of the table where our new dataset is. In the video, the name of the new dataset is “Table2”, so we just need to change the “3” to a “2”.

When we get on to connecting to Excel files rather than datasets in the current workbook then the code will look a little different as PowerQuery will need to go hunting for a file. However we’ll still be able to change the file address in the code in this situation and we’ll cover this in the next few lessons.

Previous Lesson

Back to Course

Next Lesson