Get Your Name in M is for Data Monkey

I’m pleased to say that our new book “M is for Data Monkey” is now complete, through the technical edit, and into the copy edit stage.  While there is still more work to go, the book is on schedule for release in November (with Amazon shipping in December, I believe.)

At this point we’re just finalizing the “Thank You” section, and wanted to invite everyone who has supported and pre-ordered the book the chance to get your name in M is for Data Monkey:

How to get your name in M is for Data Monkey

If you’d like to get your name inside, we need you to submit the following information via email to info@powerquery.training

  • A copy of your receipt for the pre-order of the book - if you haven’t pre-ordered it then you can do so here
  • The name that you want to be displayed in the book – for example, it can be Miguel ‘Mike’ Escobar, Ken ‘Power Query’ Puls, your company name, or whatever you want to see there.

We only have space for a limited amount of names (around 80), so please submit your information as soon as possible!

The deadline to submit this information is September 16th, 2015 at 23h59m US Eastern time, so don’t miss out!

Power Query’s Extract Text Feature

This will be a short post, as today we are leading our second sold out Power Query workshop at http://powerquery.training/course.  I wanted to make sure I still got something out for my readers today though.  This time I’m looking at a feature that was added in the August Power Query Update: Extract Text.

You can find these commands on both the Transform and the Add Column tabs, with the former just converting your selected column, and the latter creating a new column of results while preserving the original column.

SNAGHTML196640f7

Here’s what’s interesting to me about these functions:

They replicate the LEN(), LEFT() and RIGHT() functions, saving you having to build them manually as I discuss here.  This is handy, and pretty seamless.

Here, you’ve got a table of words, and the second column is generated using Excel’s LEFT() function.  The final column was generated by:

  • Selecting the Word column –> Add Column –> Extract –> First Characters –> 4

image

Nice and consistent with Excel’s LEFT() function.

Likewise, Last Characters replicates the RIGHT() function by going to:

  • Selecting the Word column –> Add Column –> Extract –> Last Characters –> 4

image

And Length replicates the LEN() function:

  • Selecting the Word column –> Add Column –> Extract –> Length

image

The Range function is a user interface implementation of what should be equivalent to the MID() function.  In this case, however, it’s still has the following issues for Excel pros:

  • It is Base 0, meaning that you want to start at the 3rd character of the text string, you need to specify that you want to start at character 2 (Power Query starts counting at 0, not 1)
  • If you provide a value for the “number of characters to return” that is larger than the total number of characters – the starting character, you’ll get an error.  (Unlike the MID function)

So when you try to use Range in place of MID as follows:

  • Selecting the Word column –> Add Column –> Extract –> Range
  • Starting Number: 5
  • Number of Characters: 4

You get this:

image

Ugh.  And correcting to subtract one from the starting index, you get this:

image

Better, but still errors.

Honestly, I was hoping the user interface implementation would solve those issues building the more complicated code shown in my blog post on the subject.

So, at the end of the day, it’s awesome, but still doesn’t offer full “Excel parity”.  And if you want that, you’ll need to learn to work with formulas in Power Query.

The good news?  We teach how to do that in our Power Query workshop.  In addition, we’ve just announced a new registration intake.  If you’re interested in learning how to master Power Query, check it out at http://powerquery.training/course.

Update: Refresh Power Queries With VBA

Some time back I posted a routine to refresh Power Queries with VBA, allowing you to refresh all Power Queries in a workbook.

Things Changing…

The challenge with that specific macro is that it refers to the connection name, which may not always start with Power Query.  (As we move closer to Excel 2016, we are now aware that these queries will NOT have names starting with Power Query.)

I have seen an approach where someone modified the code I provided earlier to include a variable, allowing you to easily change the prefixed text from Power Query to something else.  While that works, it’s still not ideal, as names can be volatile.  It makes more sense to try and find a method that is cannot be broken by a simple name change.

Refresh Power Queries With VBA

The macro below is a more targeted approach that checks the source of the query.  If it is a connection that is built against Power Query engine (Microsoft.Mashup), then it will refresh the connection, otherwise it will ignore it.

Public Sub UpdatePowerQueriesOnly()
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
End Sub

This macro works with Excel 2010, 2013 and 2016, and should survive longer than the original version I posted.

Remove All Rows Up To A Specific Value

A couple of weeks ago, Rudi asked how you would go about setting up a query to remove all rows up to a specific value.  Specifically, his question was this:

The other day I was asked if Power Query could delete all top rows up to a found value. I could not find a solution and its been a burning question till now.
For example: If I import a csv file containing columnar info, but the headings for the list are in different rows for each import. I know that the first heading in column A is called "ID Number", but each import has this heading in a different row.
How do I determine an applied step to delete all rows above "ID Number". I cannot use the delete top rows as its not always 5 rows, some import the headings start in row 10, others in row 3...but the label I am looking for is always "ID Number".

While the question was answered in the initial post, I still though it would be interesting to do a full post on this for others who might need to create similar functionality.

The Data Set

I didn’t have Rudi’s exact data, so I knocked up a little sample with an ID Number and Amount column starting in row 3, which you can download here.

The components I was after here was the ID Number header and an extra column of some kind.  In addition, I wanted to have some garbage data above, as I didn’t want to give the impression we can just filter out blank rows.

Now, I assume this data is loaded from an external file, but it doesn’t really matter, I’ll just load this from a range, as it’s just a data source.  The key is that the header row is not the first row.  So I defined a new range to cover the data

  • Select A1:B7 –> Name Box –> Data

I can then select the name from the Name box to select my data:

SNAGHTML1549a4a4

I then loaded it into Power Query by creating a new query –> From Table

Determine the Header Row

This is the first job.  In order to remove any rows above the header row, we need to know which row the header resides.  To do that, we need to add an index column, and filter to the specific header that we’re after.

  • Add Column –> Index Column –> From 0
  • Filter Column1 –> only select ID Number

This results in a single row of data, and conveniently has the Index number of the ID Number row listed.  In this case it’s 2.

Call up the Original Table

We’ll park that value for a moment, and call up the original table.  To do that, we click on the fx button beside the formula bar to create a new step, then replace the formula with =Source.

image

Remove All Rows Up To A Specific Value

Now comes the tricky part.

We cant’ remove all rows up to a specific value immediately.  We need to insert a step that removes the top 2 rows first, then modify it.  So let’s do that:

Home –> Remove Rows –> Remove Top Rows –> 2

This gives us the following table and formula:

image

The key is to understand this formula first.

  • The Table.Skip() function removes (actually skips importing) the first x rows
  • Custom1 is the name of our previous step
  • 2 is the number of rows

So what we really need to get is the number of rows.  We can extract that from the Filtered Rows step like this:

#”Filtered Rows”[Index]{0}

Where:

  • #”Filtered Rows” is the name of the step in the Applied Steps window
  • [Index] is the column we want to look at in that step
  • {0} indicates the first row of that step (since Power Query starts counting at 0

So let’s modify the function to:

= Table.Skip(Custom1,#"Filtered Rows"[Index]{0})

As you can see, it works nicely:

SNAGHTML1559cc91

In fact, we can go even better than this.  Why are we referring to Custom1 at all?  Doesn’t that just refer to Source anyway?  Let’s also replace Custom1 with Source, and remove the Custom1 step from our query:

SNAGHTML155cd378

Cleanup

We can now promote our header rows, remove the unnecessary Changed Type step and set our data types correctly:

SNAGHTML155f21ae

Testing

If you try inserting new rows at the top of the data range, then refreshing the completed query… it just works!  The output table will never end up with extra rows at the top, as we’re filtering for to start at the ID Number row.

SNAGHTML156196d4

Using M

If you learn to write your own M code from scratch, you can start combining lines as well, skipping the next to explicitly filter the primary table.  Bill S provided code in answer to Rudi’s question (adapted to this workbook), which is shown below:

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
Skip = Table.Skip(Source, Table.SelectRows(AddIndex, each ([Column1] = "ID Number")){0}[Index]),
PromoteHeaders = Table.PromoteHeaders(Skip)
in
PromoteHeaders

It essentially replicates the same steps as the query I built via the user interface, only he combined the Filtered Rows step into the Row Removal step.  To bonus here is that the code is a bit shorter.  The drawback is that it might not be quite so “de-buggable” to someone who isn’t as experienced with M.

Multiplying NULL values in Power Query

I was working through a scenario today and came up against something unexpected when multiplying NULL values in Power Query.

Background

I have a fairly simple table of transactions that looks like this:

image

And wanted to turn it into this:

image

Seems simple enough, but I ran into an odd problem.

Steps

Getting started was pretty easy:

  • Pull the data into Power Query using From Table
  • Remove the final column
  • Select the Bank Fee column –> Transform –> Standard –> Multiply –> –1

So far everything is good:

image

Then I tried to do the same to the Discount column.

Multiplying NULL values

At this point, something odd happened.  I did the same thing:

  • Select the Discount column –> Transform –> Standard –> Multiply –> –1

But instead of getting a NULL or 0 for John’s record, it gave me –1.  Huh?

image

This is honestly the last result I expected.  How can a NULL (or empty) cell be equivalent to 1?  I think I’d rather have an error than this.

Regardless, easy enough to fix, I just inserted a step before the multiplication step to replace null with 0:

image

Good stuff, so now just finish it off:

  • Right click the Customer column –> UnPivot Other columns

And all looks good…

image

… until I load it into the Excel table:

image

Seriously?  Negative zero?

To be honest, if I’m feeding a PivotTable with this anyway, I really don’t need the discount record for John.  To fix this I just went back to the Power Query and inserted another step right before the Unpivot portion when replaced 0 with null.  The result is now really what I was originally after:

image

End Thoughts

I can’t help but think that this behaviour has changed, as I actually tripped upon it refreshing a previous solution.  The killer is that the data has changed, but I’m pretty sure the old data set had these issues in it too, and it wasn’t a problem.

Regardless, I”m a little curious as to your opinions.  Is this expected behaviour?  Bug or feature?

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.)