Refer to other steps in Power Query

Last week I posted a technique to show how to calculate a rolling 12 months in Power Query.  One of the techniques used was to refer to other steps during the construction of that query.  Shortly after publishing that, a user asked a question on a non-related post that can make use of the same technique.  Because of this I thought I should focus on that specific technique this week, and where it can add more value.

The Question

I have a data sheet where the generated date shows up in a single cell up on the top and then the data table itself follows.

I created a query to just pick up the generated data but now I want to use that date within a formula of a new column in the 2nd query (the one that pulls/transforms the data table itself). How can I do that?

Now, the asker is working between two queries.  I’m actually not going to do that at all, rather focussing on getting things working in a single query.

The Mockup

I haven’t seen the asker’s original data, but I mocked up a sample which I believe should be somewhat representative of what was described:

image

As you can see, we’ve got a single cell with the data in A3, and a table below it.  While I’ve done this in Excel, this could easily be pulled in from a text file, web page, or some other medium. The key that I want to focus on here is how to get that date lined up with the rest of the rows in the table.

Options, Options, and more Options

There’s actually a ton of ways to do this.  Just some include:

  • Naming the date range, using the fnGetParameter function to pull it in, and pass it into the query that way.
  • Pull the data into Power Query, duplicate the first column, format it as a date, replace errors with null, fill down, and cull out the rest of the garbage rows
  • Add a custom column that refers directly the the 3rd field of the first column
  • And many more

But in order to pull this of today, I’m going to refer to other steps in the Applied Steps section of the query.  This is a method you can use to determine a variable through the user interface without resorting directly to M code.

Building the Output

Loading the data

To pull the data in, I’ll set up a named range, as this doesn’t exactly look like a table.  To do that I:

  • Selected A1:C8
  • Replaced the A1 in the Name box (just to the left of the formula bar) with the name “Data”

Which landed me the following in Power Query:

image

Filter down to just the date cell

This part is relatively easy, we just need to:

  • Right click Column1 –> Remove other columns
  • Right click Column1 –> Change Type –> Date
  • Go to Home –> Remove Errors
  • Filter Column1 –> Uncheck the null values

And we’re now down to just our date:

image

You’ll also notice, on the right side, the Applied Steps window shows this step as “Filtered Rows”.  I’m going to right click that and rename it to “ReportDate” (with no space).

Refer to Prior Steps

With this in place, we can now essentially revert to our original query.  To do that, we:

  • Go to the Formula Bar and click the fx logo to get a new query line:

image

Notice that it refers to the previous step.  No big deal, change that back to “=Source” (the original step of our query.  When you do, your “Custom1” step will look like this:

image

Perfect.  Let’s add a custom column.

  • Go to Add Column –> Add Custom Column
  • Set up the custom column as follows:
    • Name:  Date
    • Formula:  =ReportDate

Your “ReportDate” step gets added as a table:

image

  • Click the expand arrow to the top right of the date column header and expand it (without keeping the column prefix)

image

And now it’s just basic cleanup to get things in the right place:

  • Go to Home –> Remove Rows –> Remove Top Rows –> 4
  • Go to Transform –> Use First Row as Headers
  • Right click Column4 –> Rename –> Date

And you’re done:

image

So… could you build one query to get the date, then try to pass it to a query with your data table in it?  Sure, but why?  Much better to do it all in one place.

It’s Faster with M

Before Bill S jumps in and shows us that it’s faster when we manipulate the M code directly, I figure I’ll cover that too.  Bill is absolutely correct when he comments on my posts showing why we should learn M.  Here’s the (summarized) route to do the same thing using M code:

  • Load the initial table into Power Query
  • Go to Home –> Remove Rows –> Remove Top Rows –> 4
  • Go to Transform –> Use First Row as Headers
  • Add a custom column
    • Name:   Date
    • Formula:  =Date.From(Source[Column1]{2})

You’re done.  :)

Why?  The trick is all in the formula.  Let’s build it up gradually.

We start by referring to the Source step.

  • =Source

This would return a table to the column (as you saw earlier).  We then modify the formula and append [Column1] to this so that we have:

  • =Source[Column1]

This returns the list of all of the values in Column1 from the Source step.  (Never mind that we moved past that step – it will still refer to it as if it was still around.)  Next we append the index of the data point we want.  Remembering that Power Query is base 0, that means that we need #2 to get to the 3rd data point:

  • =Source[Column1]{2}

Now, if you went with this as your formula you’d find that it actually returns a DateTime value.  So the last step is to wrap it in a formula to extract just the date:

  • =Date.From(Source[Column1]{2})

Final Thoughts

So now you’ve seen two ways to pull this off… one via the user interface, one more efficient by writing a small bit of M code.  Different options for different comfort levels.

What I love about Power Query is that you don’t NEED to master M to use it effectively.  But if you DO master M, then it sure can make your queries more efficient and elegant.

Also, I should mention this… if the user really DID want to merge these two queries together, it is as easy as adding a new step (by clicking that fx button), then putting in the name of the other query.  That will bring the data over, and then it’s simply a matter of following the rest of this post to stitch them together.

Request new features for Excel

Have you ever wanted to request new features for Excel?  Maybe an upgrade of data validation, a new function, or something else?  Well guess what… there is a way…

In a recent blog post on the Office Blogs called “Your top 10 questions about Office 2016 for Mac answered”, answer number 7 was to the question “How can I request a new feature?”

And the cool thing?  This isn’t just applicable to Mac Excel.  It’s applicable to ANY AND EVERY flavour of Excel (Windows, Android, iPad, Mac, Online)

How to request new features for Excel

Go to the Excel Suggestion Box site at http://excel.uservoice.com

  • Create a user account if you want to add a new suggestion or vote on existing suggestions.
  • Select one of the feedback forums listed.
  • Check out the ideas others have suggested and vote on your favourites.

The Excel team does suggest that you search for your suggestion among existing requests before you post a new one.  This is actually important as you can then vote someone else’s suggestion up.  The more votes, the more likely it has a chance of being seriously looked at.

So on that note, check out the ones that are there.  Vote them up and comment if they are in line with what you’re thinking.  But add your own if you can’t already find one in place.

How to request features for Word, PowerPoint, Outlook and OneNote

As it would happen, they all have their own sites.  You can find them at the links below:

Rolling 12 Months in Power Query

Last week, in a blog comment, a reader asked how to filter their data to only show the most recent rolling 12 month period.  This post looks at how I made that work in Power Query.

Background

Assume we have a table set up as follows:
image

As you can see, we have sales categories down the left, months across the top, and values in the middle.  A classic setup when users are tracking information.  And now we need to pull the most recent 12 months only from that table.

You can download the completed sample file if you’d like to follow this along as well.

Filter the most recent rolling 12 months from a table in Power Query

Step 1: Grab the data

First thing we need to do is grab the data. To do this, I clicked in the table and:

  • Power Query –> From Table –> Confirm the range (if required)
  • Changed the query name to Rolling12

Helpfully, Power Query identified the data types in all the columns for me, so I’m pretty much ready to go.

Step 2: Show the most recent record first

To do this, we really need to get the data into an unpivoted list.  Easy enough to do:

  • Select the “Sales” column
  • Right click –> UnPivot Other Columns

Note: The “unpivot other columns” command was added to the right click menu in version 2.24.  While you can most likely still access this command, you are definitely running an older version, and for a multitude of important reasons, you should really update to the latest version of Power Query.

If you don’t want to (or can’t) do this for some reason, then go to Transform –> Unpivot –> Unpivot Other columns to accomplish the same thing.

We now get a nice unpivoted list:

SNAGHTML846714

Next I cleaned up that Attribute column:

  • Right click the Attribute column –> Rename –> Date
  • Right click the Date column –> Changed Type –> Date

And finally I sorted the records to show the most recent ones at the top:

  • Click the Filter icon on the Date column –> Sort Descending

Leaving us with this:

SNAGHTML87924c

Step 3: Create variables to hold the required data range

Next we need to work out the dates that we want to use for the top and bottom range of dates in the query.  This is a bit tricky, but uber powerful once you realize you can do it.

To start, click the fx icon in the formula bar:

image

NOTE: If you don’t see the formula bar, go to the View tab, and check the box next to Formula Bar

What this does is add a new step in the formula bar called “Custom1”.  And if you check the formula bar you’ll see that it just refers to the previous step:

image

The cool thing here is that we can modify this.  Why don’t we add some data to the end of that statement to pull the first value from the Date column of that table?  To do that, change the text in the formula bar to read as follows:

=#”Sorted Rows”[Date]{0}

Recognize here that:

  • #”Sorted Rows” refers to the table in the previous step
  • [Date] tells Power Query that you only want the Date column
  • {0} tell Power Query that you want the first value from that column (remember that Power Query starts counting from 0)

The result is a single cell with the most recent date:

image

Let’s keep things clean in our Steps window… right click the Custom1 step and rename it to “MaxDate”.

image

What’s cool here is that we’ve essentially created a variable to work out and hold the most recent date.

So now that we have the top of the range, why don’t we create another step to modify it to the date for the lower end of the range?

  • Click the fx step in the formula bar
  • Modify the formula to read as follows

=Date.AddYears(MaxDate,-1)

And the result is a new Custom1 step that shows in the formula bar as follows:

image

Note:  The formulas you can use are documented at Microsoft’s site here: http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx

Pretty cool, don’t you think?  We’ve now got a step that holds the lower end of the results too.  Let’s do our cleanup again.

Right click the Custom1 step and rename it to “AfterDate”

Step 4: Implement the variables into a filter

Our last step is to implement the variables into the filter, cutting the data down to the most recent rolling 12 months of data.  Before we can do that, however, we really need to get back to the table we had in the “Sorted Rows” step.

The challenge is that we can’t select and work from that step, as it’s earlier in the process than the creation of our variables.  So how do we get back to that step AFTER we’ve created our variables?

  • Click the fx button in the formula bar

Once again, we get a new step in the formula bar:

image

The problem is that it’s referring to the previous step.  So what if we changed it to point to the #”Sorted Rows” step?

image

Now how cool is that?  Not only can we refer to the previous step of our query, we can change that to point to ANY previous step, or type in our own formulas against any previous step!

NOTE:  If your steps have spaces in the, don’t forget to wrap the step name in quotes and then preface it with the # symbol.  If your steps don’t have spaces, then you don’t need to do this.

Okay, so now let’s filter our data.  We’ll start by doing it manually:

  • Click the filter icon on the Date column
  • Select Date Filters –> Custom Filter
  • Set up your filter as follows:

image

Notice that we have to pick the values from the list here.  (Wouldn’t it be cool if we could type in our variable names here?  That would be awesome!)  Regardless, we can set up the filter as we’d expect to use it.  This will filter our list, and leave us with the following formula in the formula bar:

image

NOTE:  You can expand the formula bar to show as I have by clicking the little down arrow icon.

Good stuff… now we need to do a little surgery.  Let’s replace the manual dates with our variables:

= Table.SelectRows(Custom1, each [Date] <= MaxDate and [Date] > AfterDate)

If you check the table now, you’ll see that it is filtered down to only contain records between Mar 1, 2014 and Feb 28, 2015.  And better yet, because the variables are created dynamically when the query is run, it will ALWAYS return the most recent rolling 12 months!

Step 5: Pivoting the data back into the original format.

Now we need to put the data back into the format the user wanted.  To do this, we need to pivot it back.

The trick to pivoting in Power Query is to select the column you want to use as the new column headers.  This time it is the Date column.  So…

  • Select the Date column –> Transform –> Pivot Column
  • Change the “Values” column to the one that holds your values (in this case it’s actually called Value)

image

And the result:

image

Bingo!  The most recent rolling 12 months of data from our table.

At the point you can click File –> Close & Load, and load it to a table.

Proof Positive

Go and add a new column of data.  You can insert it into the existing table, put it on the end, it really doesn’t matter since Power Query will sort it anyway.  Once you’re done, right click the new table and refresh it, and you’ll find it works nicely.

One minor point of note… in the version I did we’ve actually reversed the column order (the most recent date has moved to the left from the right.)  If we wanted to change that it’s fairly easy too.  Just before we pivot the data back into the pivoted form, just sort it in descending order.

Important Power Query Update Available

At last, the Power Query update I’ve been waiting for has finally landed in the download site.  This is version 2.24, and sets my parameter tables technique back to a working state!

You can download version 2.24 direct from Microsoft’s site by clicking here.

Why is this Power Query Update important?

This Power Query update is pretty important for a few reasons:

  • It fixes the issues with the MultipleUnclassified/Trusted error on refreshing parameter tables (as I blogged about here)
  • If you’re running version 2.22 it also fixes issues with loading to the data model

Does this Power Query Update have any NEW features?

Of course it does!

  • Improvements to ODBC Connector.
  • Navigator dialog improvements.
  • Option to enable Fast Data Load vs. Background Data Load.
  • Support for Salesforce Custom environments in Recent Sources list.
  • Easier parsing of Date or Time values out of a Text column with Date/Time information.
  • Unpivot Other Columns entry in column context menu.

The big thing to me, though, is the Power Query update fixes the critical bug(s) listed above.  If you’re running 2.22 or 2.23 I highly recommend updating.  (And if you’re running an older version I’d update too, as there is new functionality released every month.)

Fix Date Errors

I was teaching a course on Power Query yesterday in which we imported a text file.  Almost immediately, some of my users pointed out that their dates weren’t importing correctly, and we had to cover how to fix date errors right away.

And to underscore the importance of this… this morning I woke up to comments on one of my previous blog posts with similar issues.  I figured it’s time to cover the easy way to fix date errors.

The Symptom

My data came from a text file, and was shown in the following format:

image

As you can see, the data is set up in the Month/Day/Year format.  The issue for the user is that their system is set to a different format… in the case of the users in my class their default windows settings were set to Day/Month/Year, which is the Canadian date format.  When they tried to convert the data from an “any” data type to a date, it messed it up.

The problem comes in to play because many systems export into a MDY format as they were programmed using US date standards.  But with our operating system set to a different format, it tries to interpret dates in the standard set there.  So when importing a text file, it looks at 1/12/2000 and interprets it as Dec 1, 2000, not Jan 12, 2000.

Then it hits a date like 1/13/2000.  Because there is no 13th month, it returns an error.

One of the class attendees brought up an interesting point as I was explaining this… “I thought that dates were just a format on top of a serial number?  So how can it get it wrong”.  He was absolutely correct.  But in this case we are importing data from another source into Excel (via Power Query)… Excel (Power Query) is trying to determine what that date serial number is based on the system settings.  That’s where the issue hits us.

How to fix date errors

At first, you might be tempted to flip the date format in your Windows settings, but that won’t actually help you in the long run.  In fact, in the worst case it may fix the issue for the current import, and blow apart other solutions that you’ve built.  So that’s really not a practical solution.  What we need is a way to tell Power Query what the date settings are for THIS data source.  Fortunately, there is a way to override the date format.  In truth, this doesn’t so much fix date errors, but rather prevents them from occurring in the first place.

What we do is select the column with our dates in it then:

  • Right click the column
  • Choose Change Type –> Using Locale

image

(Yeah, I know… this is hardly a term that Excel users are familiar with, but it allows you to force a different regional setting on the data source.)

You’ll then be prompted with a new dialog where you’ll choose the date, then the Locale you want to use to read it:

image

The key here is to recognize WHICH locale your data format is emulating.  There are hundreds of countries in this listing.  My guess is that you’re probably going to pick either your own or English (United States) most of the time.  In truth, when working with dates, the country is actually not the important part.  The important part is that you pick a country where the MDY or DMY format is consistent with your data source.

Future Proofing

I’ve been fortunate enough to have to deal with this issue very little in my career.  I generally leave my system in a US English configuration and most of my imports follow the US date standard, so no issue.  But I recognize this as a huge issue for Europeans, as well as any company that conducts business in multiple countries.  In both cases this issue comes up over an over again.

There are two great things about using the “Change Type With Locale” feature:

The first is that it avoids relying on an implicit shortcut, explicitly declaring the source data format.  This is REALLY handy for future proofing.  In Canada, we typically end up with some users in the organization using Canadian standards and some who use US standards (we are a very confused country sometimes.)  By specifically declaring the data type, I know that this solution will continue to work even when I send it to someone who uses a different date standard on their PC.  Why?  Because it’s now defined for the DATA, not the SYSTEM.

The second great thing is that this is a DATA SOURCE SPECIFIC feature.  I can set a different format for each data source used in my solution, allowing me to combine several data sets from different countries and still get it consistent.

Drawbacks/Improvements

One thing that I struggled with is this.  Back in the old text import tool for Excel, we had a nice feature that looked like this when we were setting data types:

image

This was fantastic, as I didn’t need to try and figure out which region the data came from, I simply chose the date format that I could see.  While it’s great that we can now exhibit some national pride by choosing our country, that doesn’t always help.  In the case of Canada, I’d bet that if I asked 5 different people what our official data format is, I’d get 5 different answers.

clip_image002

It would be SO handy if the Power Query team would add some indicator at the end of these options to indicate what the format is.  That would be such an easy change to make here, and SOOOOO useful.  I honestly don’t think I need to care if my setting is set to English Australia/UK/Ireland/Canada/Belize if it gives me interprets my date in the correct MDY order.

(I actually did email this thought to one of the program managers a few days ago.  So hopefully one day we’ll see that change take place.)

MultipleUnclassified/Trusted error

If you’ve been using my fnGetParameter function to build dynamic content into your queries, you may have noticed that passing some variables recently started triggering an error message that reads as follows:

We couldn’t refresh the connection ‘Power Query – Categories’. Here the error message we got:

Query ‘Staging-Categories’ (Step ‘Added Index’) used ‘SingleUnclassified/Trusted/CurrentWorkbook/’ data when last evaluated, but now is attempting to use ‘MultipleUnclassified/Trusted’ data.

If you’re like me, that was promptly followed by cursing and head scratching in the pursuit of a solution.  (I would suggest clicking the “was this information helpful?” link in the bottom of the error, and submitting a “no”.

image

As far as I can tell, this error started showing up in Power Query around v 2.21 or so.  (I don’t have an exact version.)  I know that it existed in 2.22 for sure, and still exists in version 2.23.

Triggering the MultipleUnclassified/Trusted error

Triggering the error isn’t difficult, but it does seem to show up out of the blue.  To throw it, I built a solution that uses my fnGetParameter function to read the file path from a cell, and feed it into a query as follows:

let
fPath = fnGetParameter("FilePath"),
    Source = Csv.Document(File.Contents(fPath & "SalesCategories.txt"),null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"POSPartitionCode", Int64.Type}, {"POSCategoryCode", type text}, {"POSCategoryDescription", type text}, {"POSReportingGroupCode", type text}, {"POSTaxTypeCode", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Lnk_Category", each Text.Combine({Text.From([POSPartitionCode], "en-US"), [POSCategoryCode]}, "-"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"POSCategoryCode"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
#"Added Index"

The killer here is that – when you originally build the query – it works just fine!  But when you refresh it you trigger the error.

Fixing the MultipleUnclassified/Trusted error

There are two options to fix this particular error:

Option 1

The first method to fix this problem is to avoid the fnGetParameter function all together and just hard code the file paths.  While this works, you cut all dynamic capability from the query that you went to the effort of implementing.  In my opinion, this option is awful.

Option 2

If you want to preserve the dynamic nature of the fnGetParameter function, the only way to fix this error today is to perform the steps below in this EXACT order!

  1. Turn on Fast Combine (Power Query –> Options –> Privacy –> Ignore Privacy Levels)
  2. Save the workbook
  3. Close Excel
  4. Restart Excel
  5. Refresh the query

Each of the steps above are critical steps to ensure that transient caches are repopulated with the “fast combine” option enabled – merely setting the “fast combine” option is not enough on its own.

Expected Bug Fix

Microsoft is aware of this, and has been working on a fix.  I believe (although no guarantees), that it should be released in version 2.24.  Given the update schedule in the past, we should see it any day now.  Fingers crossed, as this is just killing my solutions!

I’ll keep checking the Power Query download page and post back when the new version goes live.

Create a Dynamic Calendar Table

I know this topic has been covered before, but I’m teaching a course on Power Pivot tomorrow, and it’s something that I’ll probably be brining up.  As we need a calendar table for our Power Pivot solutions, a method to create a dynamic calendar table is pretty important.  If you haven’t seen this before, I think you’ll be surprised at how easy it is to create a complete calendar driven by only a few Excel formulas and Power Query.

Setting up a dynamic source

The first key we need to do is set up a parameter table in order to hold the start and end date.  To do that, I’m creating a basic parameter table as described in this post.  Mine looks like this:

SNAGHTML3f7266cf

  • B3 is simply a hard coded January 1, 2014
  • B4 contains the =TODAY() function, returning today’s date

Once I created this table, I named it “Parameters” (as described in the aforementioned blog post), then created the fnGetParameter function in Power Query (again, as described in the aforementioned blog post.)

With that work done, it was time to move on to creating my calendar.

How to create a dynamic calendar table in Power Query

What I did at this point was create a new blank Power Query:

  • Power Query –> From Other Sources –> Blank Query

In the formula bar, I created a simple list by typing the following:

={1..10}

SNAGHTML3f7892f2

As you’ll see if you try this, it creates a simple list from 1 through 10.  That’s great, but it’s just a temporary placeholder.  Now I need to get my hands a bit dirty… I want to use the fnGetParameter function to load in my start and end dates as date serial numbers (not actual dates.)  To do this, I’ll retrieve them and explicitly force them to be Numbers.

  • Go to View –> Advanced Editor
  • Insert two new lines as follows:

let
    StartDate = Number.From(fnGetParameter("Start Date")),
EndDate = Number.From(fnGetParameter("End Date")),

Source = {1..10}
in
Source

So, as you can see, we’ve used the fnGetParameter function to retrieve the Start Date and End Date values from the Excel table, then converted them to the date serial numbers (values) by using Number.From.

With that in place, we can then sub the StartDate and EndDate variables into the list that we created in the Source step:

let
StartDate = Number.From(fnGetParameter("Start Date")),
EndDate = Number.From(fnGetParameter("End Date")),
Source = {StartDate..EndDate}
in
Source

And when we click OK, we now have a nice list of numbers that spans from the first date serial number to the last from the Excel table:

SNAGHTML3f810c61

“Great,” you’re thinking, “but I really want dates, not the date serial numbers.”  No problem, lets do that.

First we need to convert our list to a table:

  • Go to Transform –> Convert to Table –> Click OK (with the default options)

image

  • Select Column1 –> Transform –> Data Type –> Date
  • Right click Column1 –> Rename –> Date

And look at that!

SNAGHTML3f84f29c

And now it’s just a matter of adding the different date columns we need.  If you select the Date column, you’ll find a great variety of formats available under Add Column –> Date.  Just browse into the subcategory you want (year, month, day, week) and choose the piece you want to add.  In the table below, I added:

  • Date –> Year –> Year
  • Date –> Month –> Month
  • Date –> Month –> End of Month

SNAGHTML3f87a345

There are a lot of transformations for a variety of dates built in… for numeric or date values.  One thing that’s missing though, is text versions.  For those you need to add a custom column.  Here’s 3 formulas that you may find useful if you want to add text dates to your table:

  • Date.ToText([Date],"ddd")
  • Date.ToText([Date],"MMM")
  • Date.ToText([Date],"MMMM dd, yyyy")

To use them, go to Add Column –> Add Custom Column and provide those as the formula.  Their results add a bit more useful data to our query:

SNAGHTML3f907072

As you can see, they work like Excel’s TEXT function, except that the characters are case sensitive.

Conclusion

Overall, it’s super easy to create a dynamic calendar table using Power Query to read the start and end date from Excel cells.  This makes it very easy to scope your calendar to only have the date range you need, and also gives you the ability to quick add columns on the fly for formats that you discover you need, rather than importing a massive calendar with a ton of formats that you will never use.

In addition to being easy, it’s also lightening quick if you’re prepared.  It takes seconds to create the Excel parameter table, a few more seconds to set up the fnGetParameter function (if you have the code stored in a text file/bookmarked), and only a little while longer to create the original list and plumb in the variables once you’re used to it.  I can knock up a calendar like this in less than two minutes, and let it serve my data model every after.  :)

PowerQuery.Training

I should also mention that this is one of the techniques (amongst MANY others) that we cover in our PowerQuery.Training course.  We’ll be announcing a new intake soon, so don’t forget to sign up for the newsletter in the footer of the site so you’ll know when that happens!

Listing Outstanding Cheques

Power Query is all about transforming and filtering data, and automating the process.  One of the tedious tasks that accountants get to deal with all the time is bank reconciliations, which is essentially the process of filtering and matching items to see what is left over.  It’s been on my list for a while now, but I’ve been thinking that we can use Power Query for listing outstanding cheques (or checks if you’re in the USA).

The completed workbook is available for download by clicking here.

Background

Because I don’t want to run on to 100 pages, I’m going to start with two lists that show just the cheques, in two tables:

SNAGHTML1901ec84

The table on the left is the table of cheques that have been issued, as per the list maintained in the General Ledger. We’re making the assumption that we’ve dumped that list into an Excel worksheet, formatted it as a table, and given the table the name “GLListing”.

The table on the right is the table of the cheques that have cleared the bank.  Again, the assumption is that we’ve been able to download a list of the transactions, and filtered them down to show just the cheques that have cleared.  (Maybe we’d even use Power Query to do this.)  This table has been named “Bank”

Creating the data Staging queries

The first step is to create the staging queries to connect to these two tables.  One of the important things I wanted to ensure is that I can match transactions where both the cheque number and amount are identical.  (If a cheque clears for the wrong amount, I want to list it as outstanding at this point, as I need to review it.)  I’m going to keep that in mind as I create my staging tables.

The GLListing table:

To set this up I:

  • Clicked inside the GLListing table –> Power Query –> From Table
  • Set the data types on each column (Whole Number, Date, Decimal)
  • Selected the Cheque and Amount columns –> Add Column –> Merge
    • Separator:  Custom (I used a dash)
    • Name:  Issued

The end result in Power Query:

image

  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

The Bank table:

It’s virtually the identical process:

  • Click inside the Bank table –> Power Query –> From Table
  • Set the data types on each column (Whole Number, Date, Decimal)
  • Selected the Cheque and Amount columns –> Add Column –> Merge
    • Separator:  Custom (I used a dash)
    • Name:  Cleared

The end result in Power Query:

image

  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Listing Outstanding Cheques

Now to build the important part.

  • In the Workbook Queries pane, right click the GLListing query—> Reference

At this point you’ll have a pointer to the GLListing table.  We also want a pointer to the Bank table.  To do that, let’s click the fx icon on the formula bar:

image

This will create a new step in your query.  The formula in the formula bar will read =Source (it refers to the previous step), and you’ll see a new step in your Applied Steps area called Custom1.  Let’s update both of those:

  • Change the formula to =Bank
  • Right click and rename the step to “Bank”

image

The key things we now have are a Source step (which contains the output of the GLListing query) and a Bank step (which contains the output of the Bank query).  The Source step has an Issued column, the Bank query a Cleared column, and we’ll like to know which items between those two columns are different.

To work this out we’re going to knock up a little M code.  Here’s how:

  • Click the fx icon on the formula bar
  • Replace the formula (it will read =Bank) with this:

=List.Difference(Source[Issued],Bank[Cleared])

The result will be as follows:

image

So what happened here?  Let’s break this down.

List.Difference generates the items that are different between two provided lists.  And fortunately, when we feed a column to the function, it passes it in as a list.  So that’s what you’re seeing there:

  • Source[Issued] is the Issued column from the Source step of our query
  • Bank[Cleared] is the Cleared column from the Bank step of our query

And the result is the only items that don’t exist in both lists.

Expanding the Details

As great as this is, it is returning a list of values.  We want to convert this back into a table, and get the original data of issue as well.  So let’s do that.

  • Go to Transform –> To Table

image

  • When prompted, select that the list has a Custom Delimiter of a dash and click OK

You should now have a nice table split into two columns:

image

Let’s clean this up:

  • Right click Column1 –> Rename –> Cheque
  • Right click Column2 –> Rename –> Amount

Now, the next tricky part is getting the issue date back in.  I’d like to feed this from the current query – just to keep it self contained – but it’s easier to start by merging it with another query.

  • Go to Home –> Merge Queries –> GLListing
  • Choose to merge based on the Cheque field on both tables
  • Make sure you check to “Only include matching rows”

image

  • Click OK

This works nicely to add our column, but we’ve already pulled this data into this query once, so why reach outside it again?  If you look in the formula bar, you can see that the formula reads as follows:

= Table.NestedJoin(#"Renamed Columns",{"Cheque"},GLListing,{"Cheque"},"NewColumn",JoinKind.Inner)

Highlighted in the middle of the text is the name of the table we merged into this one.  So why not just replace that with the step name from this query that has the same table?  Modify the formula in the formula bar to read:

= Table.NestedJoin(#"Renamed Columns",{"Cheque"},Source,{"Cheque"},"NewColumn",JoinKind.Inner)

It doesn’t look like anything happened, does it?  That’s okay.  Remember that the source step just pulls in the data from the GLListing query.  Since we didn’t do anything to it in that step, it SHOULD look identical.

Now we can continue on and finalize the query:

  • Expand the “NewColumn” column:
    • Only expand the Date column, as we have the others we need
    • Uncheck the “Use original column name as prefix” setting
  • Move the Date column between then Cheque and Amount columns

image

  • Rename the query to “Outstanding”
  • Go to Home –> Close & Load

And the final result:

SNAGHTML1b93e715

Final Thoughts

Figuring out which records match is actually pretty easy.  We simply merge two tables, and choose to only include matching rows.  Working out differences is obviously a bit harder.  (Wouldn’t it be awesome if there was an inverse setting on that merge dialog that let us only include unmatched rows?)

I left my full time controllership job before I ever got the chance to implement this technique for our bank reconciliations.  Currently there is a lot of VBA and manual work needed to clear both the cheques and deposits on a monthly basis.  Given this, however, I know that I could have re-written the bank reconciliation to very quickly eliminate all the records that match, leaving me with only the transactions that I actually needed to focus on.

Taking it even one step further, with another table of adjustments added in to the mix, I’m sure I could build it to actually produce an ever diminishing listing of un-reconciled transactions, and most likely even an output report replicating a full bank reconciliation.  Pretty cool, especially when you consider how much could be refreshed when you start the process next month!

Calculate Hours Worked

Chandoo posted an interesting challenge on his blog last Friday, challenging users to calculated hours worked for an employee name Billy.  This example resonated with me for a couple of reasons.  The first is that I’ve had to do this kind of stuff in the past, the second is because I’ve got a new toy I’d use to do it.  (Yup… that toy would be Power Query.)

It always blows my mind how many people respond on Chandoo’s blog.  As the answers were pouring in, I decided to tackle the issue my own way too.  I thought I’d share a bit more detailed version of that here as I think many users still struggle with time in Excel.

Background and Excel Formula Solution

Chandoo provided a sample file on his blog, so I downloaded it.  The basic table of data looks like this:

SNAGHTMLeb2f08

Now, for anyone who has done this a long time, there a few key pieces to solving this:

  • The recognition that all times are fractions of days,
  • The recognition that if you omit the day it defaults to 1900-01-01, and
  • The data includes End times that are intended to be the day following the Start time

The tricks we use to deal with this are:

  • Test if the End time is less than the start time.  If so, add a day.  (This allows us to subtract the Start from the End and get the difference in hours.
  • Multiply the hours by 24.  (This allows us to convert the fractional time into a number that represents hours instead of fractions of a day.)

Easy enough, and the following submitted formula (copied down from F4:F9 and summed) works:

=(D4+IF(C4>D4,1,0)-C4)*24

SNAGHTMLea6d3e

Also, there was a great comment that Billy shouldn’t get paid for his lunch break.  Where I used to work (before I went out on my own), we had a rule that if you worked any more than 4 hours you MUST take a lunch break.  Plumbing in that logic, we’d would need a different formula.  There’s lots that would work, and this is one:

=((D4+IF(C4>D4,1,0)-C4)*24)-IF(((D4+IF(C4>D4,1,0)-C4)*24)>4,1,0)

SNAGHTMLf008e9

So why Power Query?

If we can do this in Excel, why would we cook up a Power Query solution?  Easy.  Because I’m tired of having to actually write the formula every time Billy sends me his timesheet. Formula work is subject to error, so why not essentially automate the solution?

Using Power Query to Calculate Hours Worked

Okay, first thing I’m going to do is set up a system.  I’m set up a template, email to Billy and get him to fill it out and email it to me every two weeks.  I’ll save the file in a folder, and get to work.

  • Open a blank workbook –> Power Query –> From File –> From Excel
  • Browse and locate the file
  • Select the “Billy” worksheet (Ok, to be fair, it would probably be called Sheet1 in my template)

image

  • Click Edit

And now the fun begins…

  • Home –> Remove Rows –> Remove Top Rows –> 2
  • Transform –> Use First Row as Headers
  • Filter the Day column to remove (null) values
  • Select the Day:End columns –> right click –> Remove Other Columns

And we’ve now got a nice table of data to start with:

SNAGHTMLfb3d71

Not bad, but the data type for the Start and End columns is set to “any”.  That’s bad news to me, as I want to do some Date/Time math.  The secret here is that we need our values to be Date/Times (not just times), so let’s force that format on them, just to be safe:

  • Select Start:End –> Transform –> Date/Time

Next, we need to test if the Start Date occurs after the End Date.  Let’s use one step to test that and add one day if it’s true:

  • Add Column –> Add Custom Column
    • Name:  Custom
    • Formula:  =if [Start]>[End] then Date.AddDays([End],1) else [End]

So basically, we add 1 day to the End data if the Start time is greater than the end time.  Once we’ve done that, we can:

  • Right click the End column –> Remove
  • Right click the Custom column –> Rename –> End

And, as you can see, we’ve got 3 records that have been increased by a day (they are showing 12/31/1899 instead of 12/30/1899

image

Good stuff, let’s figure out the difference between these two. The order of the next 3 steps is important…

  • Select the End column
  • Hold down the CTRL key and select the Start column
  • Go to Add Column –> Time –> Subtract

Because we selected the Start column second, it is subtracted from the End column we selected first:

image

Now we can set the Start and End columns so that only show times, as we don’t need the date portion any more.  In addition, we want to convert the TimeDifference to hours:

  • Select the Start:End columns –> Transform –> Time
  • Select the TimeDifference column –> Transform –> Decimal Number

Hmm… that didn’t work quite as cleanly as we’d like:

image

Ah… but times are fractions of days, right?  Let’s multiply this column by 24 and see what happens:

  • With the TimeDifference column selected:  Transform –> Standard –> Multiply –> 24
  • Right click the TimeDifference column –> Rename –> Hours

Nice!

image

Oh… but what about those breaks?

  • Add Column –> Add Custom Column
    • Name:  Breaks
    • Formula:  =if [Hours]>4 then -1 else 0
  • Add Column –> Add Custom Column
    • Name:  Net Hours
    • Formula:  =[Hours]+[Breaks]

And here we go:

image

At this point I would generally:

  • Change the name of the query to something like:  Timesheet
  • Close and Load to a Table
  • Add a total row to the table

SNAGHTML12fe424

But just in case you only cared about the total of the Net Hours column, we could totally do that in Power Query as well.  Even though it’s not something I would do (I’m sure Billy would trust YOU implicitly and never want to see the support that proved you added things up correctly…), here’s how you’d do it:

  • Go to Transform –> Group By
  • Click the – character next to the Day label to remove that grouping level
  • Set up the Grouping column:
    • Name:  Net Hours
    • Operation:  Sum
    • Column:  Net Hours

Here’s what it looks like if you set the column details up first, indicating where to click to remove the grouping level:

image

And the result after you click OK:

SNAGHTML1344d4b

Holy Cow that’s a LOT of Work!?!

Not really.  Honestly, it took me about a minute to cook it up.  (And a LOT longer to write this post.)  But even better, this work was actually an investment.  Next time I get a timesheet, I just save it over the old one, open this file, right click the table and click Refresh.  Done, dusted, finished and time to move on to more challenging problems.

Even better, if I wanted to get really serious with it, I could implement a parameter function to make the file path relative to the file, and then I could pass it off to someone else to refresh.  Or automate the refresh completely.  After all, why write formulas every month if you don’t have to?

:)

Excel Text Function Equivalents

For some time it’s bothered me that we don’t have full parity between Excel and Power Query functions.  In this post I’m going to look at four Excel text function equivalents, intended to allow an Excel user to just work with formulas as they’re used to.

=LEFT()

The first function is the LEFT function which, if implemented in M, would be:

(text as text,num_chars as number)=>
let
ReturnValue = Text.Start(text,num_chars)
in
ReturnValue

You’d call it like so:

=LEFT([Word],4)

And it would return the Excel text function equivalent as shown here.  (Excel’s output is in teh LEFT(x,4) column, and the Power Query LEFT function is shown in the pqLeft column.)

image

This one may not be a big deal, as it’s just a new function name, but it now works exactly as an Excel user would expect.  (Except for the fact that you MUST type the function name in uppercase.)

=RIGHT()

We can do the same thing with the RIGHT function:

(text as text,num_chars as number)=>
let
ReturnValue = Text.End(text,num_chars)
in
ReturnValue

And call it with:

=RIGHT([Word],4)

Returning:

image

=LEN()

This is yet another simple one:

(text as text)=>
let
ReturnValue = Text.Length(text)
in
ReturnValue

Called with:

=LEN([Word])

Returning:

image

Why I care about Excel text function equivalents?

Beyond allowing a user to just use the knowledge they’ve been investing in for years, it’s the next function that makes a difference.  With the MID function we have a bunch of things that are different, and to replicate our expectations we need to do some manipulation and conditional testing.

=MID()

This is what it takes to replicate the MID() function in Power Query:

(text as text,start_num as number,num_chars as number)=>
let

tLength = Text.Length(text),
StartCorrected = if start_num>tLength then -1 else start_num-1,
ReturnCorrected = if (StartCorrected+num_chars) > tLength then tLength - start_num + 1 else num_chars,
ReturnValue = if StartCorrected >-1
then Text.Range(text,StartCorrected,ReturnCorrected)
else null
in
ReturnValue

That’s a fairly big pain to just knock up in a couple of seconds!  But once done, it’s easy to use:

=MID([Word],5,4)

And it returns results consistent to Excel as well:

image

Final Thoughts

I really wish these functions were built under and consistent with Excel’s function names to begin with.  While we can replicate their form and function, it takes a lot of work to create/debug and implement them in a power query solution.  In addition, some of the functions are VERY difficult to built.  But regardless, at least we can.