Excel 2016: A fix and a new bug

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

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.


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


And wanted to turn it into this:


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


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:


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?


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:


Good stuff, so now just finish it off:

  • Right click the Customer column –> UnPivot Other columns

And all looks good…


… until I load it into the Excel table:


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:


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?

Pain Points – Power Query Credential Management

After my last post on Power Query Security Woes, Ehren – a developer on the Power Query team – sent me a message on Twitter about it.  I will blog about that later, after I’ve had a chance to test it out, but before I did, I thought I’d go and clean up some PQ stuff that I messed up.

A little history…

A while back I got the bright idea to pull down our web leads (at my day job).  I then though, “Hey, wouldn’t it be cool if I could plot all of them on Power Map?”  Sure it would.  But all I had was their IP address.  “No problem!”, I figured, “I’ll just use Power Query to feed out the IP to a web service and return a function to turn it into a real address!”


I cooked up a little script fed the IP to http://ip-api.com, which did exactly that. Here’s a sample from a random address:


I actually tried several services before I settled on this one, but ultimately  it seemed to generate the most accurate results.  Perfect, so I let the script fly, and it was awesome!

Success turns to failure…

Except… somewhere during my batch of 3,500 addresses it stopped working.  And when I went back to the site, I found out that I’d been blocked.  Ooops.  Apparently if you send over a certain threshold of queries in a certain amount of time you’re not a good person. (That’s why I’m not sharing the script.)  Sorry about that ip-api.com, I promise I’ll be good in future!

… and creates a mess in the process…

At any rate, the issue I then ran into is that I ended up with an entry for each URL in my Data Source Settings.  I was a while back, so I don’t remember if it happened by default, or if it happened because I was learning and just clicked the wrong thing.  End result is that I have an entry in my list for each IP I queried.


Since I’m going to be a good boy and not steamroll their site any more, I kind of want to clean them up.  No problem, right?  Click on the first address on screen, hold down SHIFT and click on the last address on screen and… huh?  I’ve only got the last address?  Okay, let’s try with CTRL… click, click.. ARGH!!

… and the mess turns into major frustration!

This interface only allows you to select one item at a time?  Really?  So to delete my 200 entries I have to click the line item, click Delete (at the top), click “Yes I’m sure” and then move on to the next one.  Ouch.  I see a lot of wasted time in my future if I really want to clean up now.  🙁

The missing feature(s)

Power Query team, (if you’re still listening,) please give us the ability to multi-select in this dialog, and multi-delete items.  We need to be able to clean up, as we’re all human and make mistakes as we go along.  This feels like a severe punishment right now!

In addition, while I’m here, why isn’t there an “Add” button in this interface?  When I go to edit one of these addresses, I see the following:


I’m guessing that if I’d just set that to the root at the beginning, I wouldn’t have an entry for every IP in that query.  Man!  If only I’d been able to declare this up front and realized that this was an option!  Yet there is no way to do this from a simple UI.  Instead, I (believe) I have to:

  • Create a query to one page at the domain
  • Set it’s security level as I pull data
  • Save the query
  • Go back to this interface and edit the anonymous access

Life would be SO much easier if I just had the ability to go into this UI and create my favourite and most used sites.  (And bonus points if I could push them out to users through group policy like I can with Office Trusted Locations!)

Okay, enough for now.  I’ll be back later once I’ve tested Ehren’s suggestion.  🙂

Power Query Security Woes

Recently I’ve been working on automating a business process for a client.  It’s become a really interesting project that uses a mix of Excel tables, Power Query, Power Pivot and VBA, as well as a WinAutomation script.  Before I talk about the issue that is facing me, it would be helpful to provide a bit of background on what we’re doing, and why so many of the Power BI pieces are needed.

Solution Background

My client has a business in which they outsource employees to other companies.  Each of those companies maintains the records of hours, and lets my client download a spreadsheet version of the hours logged by the subcontracted employee.  This is fairly helpful, as my client is the one that pays all of the employees, so getting the hours lets him know who gets paid for how many hours.  Easy enough, right?  But each spreadsheet is in a different format, and that causes a lot of manual entry to try and standardize it into a file that can be uploaded to the payroll processor.

So here we enter Power Query.  With Power Query we can import each of the files individually, reformat the data into a consistent set of columns, and output it into a table.

Each pay period we start a new Excel file, and import the data files for that pay period by running a WinAutomation script.  The script logs in to the remote systems, passes the correct parameters to the system, and downloads the Excel and CSV files required.  It then saves them in a subdirectory of our application under the pay period end date.  My Power Query scripts then use the pay period end date, dynamically build a file path to the file and import the data.

It’s beautiful… except…

The Issue

Here’s where the pain begins…  Every time you touch a new data file in Power Query that you haven’t used before you get a security message asking you what type of data (Public, Private or Organizational) you are using.  (You can learn more about those here:  http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx) The issue I have is that each payroll I create new files in a subdirectory, so Power Query sees them as unique.  To that end I can’t just trust the data files once and be done with it.

Now, there is a workaround… just enable the Fast Combine feature.  That kills off the warning and lets them go, but it also has an issue.  This setting is workbook specific, which means that when my VBA saves the master payroll control file under a new name (we preserve history this way) the setting doesn’t stay selected.  Grrr.  Given that there is no way to touch this setting via code, my user has to remember to click the Fast Combine button every time they run the update.  Is it minor?  Sure.  But it’s ridiculous, it gets forgotten, and concerns them when they get the permissions messages.

The Solution We Need

Now don’t get me wrong, I’m all for security, but where it makes sense.  In Office 2007 we got a new macro paradigm that allowed us to trust folders on our computers/networks.  This setting is set on an application level basis, and persists between Excel sessions.  Beautiful, as I can set it to a specific folder and forget it.  I throw trusted files in there, I don’t throw in the ones I don’t know.  It actually allows me to practice safer computing overall.

In my opinion, this setting is drastically lacking in Power Query.  I really need the ability to set my Power Query add-in to have trusted root folders with trusted subfolders.  This would allow me to trust my application’s data directory and not have to remember to click Fast Combine each time I create new files.  It also means that I might pay attention to the message when it does come up in future, as it would be unusual.  Currently I see the error so much I just cancel it and go straight to Fast Combine.  Hmm… kind of like how we set macro security to “Low” in Excel 2003 and earlier to bypass the annoying message, which essentially left us unprotected.

The Solution (Some of us) Can Implement

To be fair, I believe I do have a workaround for this now, but I don’t think it should be necessary.

Basically what I could do is use VBA to drive the refresh of my tables, and therefore the Power Query scripts.  Given that, I could use VBA to copy and replace the files in a central repository where the hierarchy does not change.  I.e. I could set up a folder like AppData\Timesheets\Current and use VBA to copy the required data files from my dated subfolders, replacing the ones in the “current” folder.  I would then target my Power Query scripts against the files in the “Current” folder, and build my solution off that.

Because Power Query holds a list of trusted files at an application level (rather than workbook level), these files should stay trusted even if I do create a new file, removing the need to constantly click the “Fast Combine” button.  So I think this should work.

So what’s the problem?  It only involves creating a VBA macro to do that.  Fine for me, as I know how to code with VBA and make this happen.  But for most of the Power Query target market I would suggest that this is probably outside of their normal skill set.

Final Thought

Even though it is technically possible to work around this issue, I still argue should not be necessary.  We need a proper option to trust the files in a local folder so that dynamically referring to a file in Power Query can be scripted without invoking a painful security paradigm.

Nasty little F Lock key…

No really, that is it’s name!

I was trying to figure out why pressing F4 wasn’t toggling from relative to absolute referencing in my Excel formulas, and was starting to think I found a bug in Excel 2013.  But then other stuff started to get weird too…

  • F4, which I use to toggle absolute and relative formulas all the time seemed to do nothing.  But when I was just in a worksheet, and not the formula, it opened a new workbook.
  • F2, which I use to get into formula editing mode, started undoing my last action… which is really scary as I still don’t know if I un-did anything important. I only actually realized this after I typed a formula, clicked on it, pressed F2 and it went away!
  • Alt F11, to get in the visual basic editor did nothing at all.  What the…?

After finding out that it was doing the same thing in a new workbook in Excel 2010, I realized that this was bigger than just Excel.

Apparently I fat-fingered the F Lock key on my (Microsoft Natural) keyboard, which tells the keyboard to start using alternate commands.

Gave me a bit of a heart attack on a Friday afternoon!

My Workbook Got Stuck in a Cyclone!

I thought I actually posted about this some time back, but I don't see it. It's driven me nuts before, and is doing it again.

I have to print out our massive financial model for a meeting tomorrow. It's almost 120 pages, and is laid out in a mix of colour and black and white, landscape and portrait settings.

The first time I ever saw this I was floored, as I thought Excel was turning each page just to drive me nuts. As it happens, it's a little more predictable (although no less annoying.)

When I print my model, here's the output of the pages:

Colour pages (landscape):


Colour pages (portrait):


Okay, so no biggie. I'd rather have the top of the colour landscape pages on the left so I didn't need to turn them before putting in a left hand binding, but at least it's consistent. At least, it is until I start mixing in some non-colour worksheets:

Black & white (landscape):

Rotated 180 degrees from the colour worksheet! What the heck is that about? (Yes, Top should be written the other way up, I just wanted this to be easy to read.)

Black & white (portrait):


Seriously? 180 degree rotation from colour again?

Run that out for a bit…

So now picture that you've got 120 pages, mixed colour and black and white, mixed landscape and portrait. I need to bind at the left, and want the top of the pages to be consistently like the colour landscape and portrait black and white, so the I can bind them on the left side. I'm going dizzy trying to sort this out!

There's no setting to change this that I can find, so it's a process of going through each pile of paper, one sheet at a time, to make sure none get missed. What a waste of time!

I'm not sure if this is a printer thing, or an Excel thing. It did it in Excel 2010, and is doing it in 2013.

I'm curious, if you have a colour printer, do you see similar effects?

Updating Page Sizes

This drives me crazy. I'm not sure if it was the move to Excel 2013, or if it was something else…

I have this massive model, and we print it out on 11x17 paper. Somewhere, sometime, Excel decided that this is a "Custom page size", which causes me problems. I need to reset all the pages to 11x17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11x17.

Not so fast… if you do that, it replicates ALL the print settings including orientation, margins, fit to x pages by x pages, etc.. Nasty stuff. You can actually see why when you record a macro to change the paper size. This is what I get:

[code] Sub Macro1()
' Macro1 Macro
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
.PrintTitleColumns = ""
End With

Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = "Printed &D &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaper11x17
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

Application.PrintCommunication = True

End Sub[/code]

This is what I need:

[code]Sub Macro1()
With ActiveSheet.PageSetup
.PaperSize = xlPaper11x17
End With
End Sub[/code]

Actually, even more pointed, this will fix it without messing all my other settings up:

[code] Sub Macro1()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PaperSize = xlPaper11x17
Next ws

End Sub[/code]

I know why the code records as it does, as Excel doesn't know what settings I truly need, so it records the current state of all PageSetup stuff. But I sure wish when I tried to update one print setting via the user interface that it gave me the ONE I changed, not everything.

PowerPivot – It sure would be nice if…

I've been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

  • Connect to a view or table, pulling in ALL columns
  • Work through my data scenario until I've worked out my logic and solve the issue I'm trying to solve
  • Cut the table/view query down to just the required columns
  • Add WHERE clauses to cut the data down to just the bare minimum I need
  • Deploy to my users

By doing all this, I really focus on trying to optimize the file size and refresh time as much as possible.

As I do this, I cut the number of columns out, then flip from the GUI table view to SQL to add my WHERE clause(s). One thing that I find that really sucks though, is that when I do flip the "Table Properties" to SQL view, it comes out like this:

[code]SELECT [dbo].[vw_opt_MemberProfiles].[AccountType],[dbo].[vw_opt_MemberProfiles].[CustomerType],[dbo].[vw_opt_MemberProfiles].[SortCode_Account],[dbo].[vw_opt_MemberProfiles].[SortCode_LastName],[dbo].[vw_opt_MemberProfiles].[ClubMemberCode],[dbo].[vw_opt_MemberProfiles].[Full_Name],[dbo].[vw_opt_MemberProfiles].[LastName],[dbo].[vw_opt_MemberProfiles].[FirstName],[dbo].[vw_opt_MemberProfiles].[MemberCardNumber],[dbo].[vw_opt_MemberProfiles].[GranCert_Previous],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_To] FROM [dbo].[vw_opt_MemberProfiles] WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'[/code]

I would LOVE to see it come out more like this by default:


FROM [dbo].[vw_opt_MemberProfiles]

WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'

It would sure make it a LOT easier to read, and a LOT easier to work with.As it is, I now copy my code out of PowerPivot, then head over to Instant SQL Formatter.  Copy, paste, format, copy and paste it back into PowerPivot.  A heck of a lot easier to read, but also an unnecessary pain in the behind.

I’m confused

Back in the days of classic Excel (versions 97 through 2003), I displayed about 10 toolbars, working out to hundreds of commands represented by tiny icons. We recognized them, and were able to have all of our most used commands one click away.

Then came 2007 which gave us the Ribbon in its place. The Ribbon consumed as much real estate as three rows of toolbars, and gave us a lot less commands one click away. The story we heard was how Microsoft was trying to make the experience easier for new users… making commands more discoverable. The revised system was supposedly more logical than all the commands buried under menus in the old structure.

To some extent, I'd agree that Microsoft accomplished their goals here. The applications are much less intimidating than they were for new/less experienced users. The issue is that it smacked the power users pretty badly. I still feel that I'm less efficient with the Ribbon than I was with the toolbar hierarchy, and I've written the book on how to customize the user interface. The only reason I got into ribbon customization in the first place was to try and get some of that efficiency back that we lost under the new paradigm.

Interestingly, as we can see with the copy/paste icons in Office 2010, Microsoft is starting to move toward and icon basis again, without as much text:

I wonder if this is to make it easier to port the application to other languages? I also wonder how long it will be before we start seeing a Ribbon with no text on it at all? After all, it just wastes space when you know what the commands do.

Now, here's where I get confused… I installed Internet Explorer 9. And here's the basic install:

This is discoverability? I'm not saying I want a full blown Ribbon here, but you can't tell me that these two philosophies are the same? The menus are gone, granted that's consistent, but where are the favourites that I used to be able to have one click away? I actually had to download a toolbar for it. In fact, I've found the lack of discoverability of controls to be so frustrating that I went and installed Firefox. Unfortunately this seems to be the new thing.

I don't get the completely opposite directions here. In one app we're putting in big, bloated user interfaces to be in the users faces. In the other we're trying to remove it all and make them hunt for it. What gives?

Installing RSAT on Windows 7 SP1

Today we began the process of migrating away from VMWare to Microsoft Hyper-V for our server farms. Something I'm sure that Microsoft would be pretty happy to hear. And yet I got burned by an issue in the process that irks me.

I keep my OS pretty current, and installed Windows 7 SP1 as soon as it was pushed out in Windows Update. Today we go to install Microsoft's Remote Server Administration tools so that I can connect to Hyper-V to build and manage my Virtual Machines, and it won't install. What the hell? I get a nice little error message telling me "This update is not applicable to your computer." Like hell it's not!

After some searching, I found out that someone has come up with a route around this issue to get it to work correctly, which you can find here.

Microsoft has acknowledged it as an issue. In their KB's wording: "Microsoft has confirmed this to be by design, as RSAT was designed for Windows 7 RTM version. A newer version of RSAT is slated to be released in the future." Their advice is to uninstall SP1, install RSAT, then reinstall SP1 again. To me that sounds more dangerous than the route I went to fix it.

Personally, I don't think this is good enough. If this is truly "by design", then someone needs a smack upside the head. Microsoft wants people to keep their software current, and these are the exact people getting smacked!

I get that software is tough to deploy, but if the route I went is all that's needed to fix it, surely someone could roll up a quick hotfix to release in a few hours.