Well, there’s good news and bad this time.  I just updated my Excel 2016 to 16.0.6868.2048 (First release version) and there is a fix and a new bug evident.

First, the fix

As I reported a couple of weeks ago, there was a new bug introduced in the April 2016 Excel update, as related to Power Query.

… if you are sourcing from a named range that doesn’t have an equal offset of rows/column.  I.e. if your source range doesn’t start in A1, B2, C3, D4, etc… then it pulls the wrong range.  Tables are fine, named ranges are the issue…

The issue was reported, fixed, and build 16.0.6868.2048 (which I finally got today) has fixed the issue.

I have to say that this is pretty cool.  Even though I was frustrated having to wait 2 weeks for a fix, the fact that it was only 2 weeks is pretty darned amazing.  In past cycles, this would have been several years until a new build of Excel came out. So even though we see new bugs, we also need to recognize that the team is working very hard to try and be responsive to them and get the fixes pushed VERY quickly.

Update: As of version 16.0.6868.2067 I can no longer reproduce this bug.  So that's more good news!

Unfortunately, a new bug

I’m not actually sure if this is new in a more recent update, or if it was there in the previous build and I just didn’t notice.  (While I do check updates almost daily, I don’t actually use every feature of Power Query every day.)  While this bug doesn’t prevent you from using your models, it is pretty irritating… Since I know this was a pretty major contention point for many in the past, I figured I should talk about it.

Once again, this is Excel 2016 specific, and doesn’t affect Excel 2010/2013.

So let’s assume you have some data as shown below:

Here’s what’s going on with these tables:

• The blue table is our raw data.
• The green table is a simple query that imports the original data, and sets the first column to a Date data type, then loads it to the table.  (Nothing fancy, it’s just that simple.)
• The orange table was created by right clicking the green table’s query and choosing “Duplicate”, then loading it to the worksheet.  It is an EXACT copy of the query that leads to the green table.

Make sense so far?  Now, let’s add a couple of rows to the blue table, then hit refresh all.  What you should expect to get is this:

What you actually get is this:

Check out the green table… those dates are pretty impressively formatted as serial numbers, not dates.  But yet, the orange table – an exact duplicate – is fine.  Huh?  Doesn’t this feel like a throw-back to early Power Query days, where tables didn’t hold the formatting properly?

Here’s the best we have from a temporary workaround point of view (courtesy of the Excel/Power Query team):

• Select the green table –> Table Tools –> Design –> Properties
• Un-check the Preserve Cell Formatting box

When we refresh now, we get…

And let’s just add some more data, then refresh again to make sure it sticks…

Stellar!  The number formats have remained, but the table style formatting has changed to a different one.  Ugh.

Now, it IS still a table.  But unfortunately the style and the number formats all seem to be controlled by that one selection.  So until they fix this, it appears that you can either have your tables pretty, or you can have your number formatting correct.

Or maybe you can create your query, immediately duplicate it for your reports, then delete the original, as the second one seems to behave properly.  (I have no idea why this is.)

Final thoughts on “a fix and a new bug”

The subscription model is a new thing for us, and personally, I’m pretty high on it, despite these kinds of issues.  My hope is that – with the connections I have at Microsoft – that I’m in the first ring of testers, and can get this stuff fixed before it hits you.  I’d highly suggest you also have one person in your company in the “First Release” program for this reason as well.

My understanding of this method is that the fixes we get into the First Release band are implemented before that version is shipped to the General Release band of users.  That’s a good thing, as the last thing we want to see is our end users having to experience two months of the first issue listed here!

With regards to the new bug, I’ve again reported this to the Power Query team.  They’re aware, and we are having some active dialog about it.  I know they are going to fix it, but I’m not sure how soon.  (I really hope it’s as quickly as the last one, as this is pretty visible!)