Power Query “Trusted” Locations

My last two blog posts detailed some frustration that I ran into when working with Power Query.  First the issue that I couldn’t trust folders, but got stuck trusting files, and then the issue where I overloaded my credentials area.  I’ve actually got a solution, in a way, for both.

One thing to be aware of here… there are actually two distinct entities that show up in the Data Source Settings dialog: Security Credentials and Data Privacy Settings.  You can see this in the following picture:

image

Notice how Exchange and the Database don’t show a privacy level on the right?  Those are security credentials.  Interestingly here, you can see two entries for the same thing (like a website), where one is the security credential, the other the privacy setting.

Ok, with that out of the way, let’s get on to the solutions…

Clearing out Security Credentials

In my last post I had over 700 security credential entries for ip-api.com that needed to be cleared.  To clear them manually took clicking the item, clicking delete and clicking ok before moving on to the next item.  Even if I could do one click per second (I find this UI slow and sometimes I missed the target), it would still take me 35 minutes.  I really wanted a “remove all” feature.  Sure I’ll lose Exchange, sure I’ll lose my database, but so what.  Small pain vs spending 30-60 minutes clicking Delete.

So I coded something to do it.  The download link is at the end of the post, but one of the features of the tool is to let you clear out your entire list of security credentials with on click:

image

And voila!

image

Now, are you going to use this every day?  Hell no!  But if you screw up like I did, you just may need a weapon like this in your arsenal.  :)

“Trusted” Folders/Files and URLs

As I mentioned in my last post, Ehren, a developer on the Power Query team messaged me on Twitter to tell me of one solution to the “Trusted Folder” problem.

If you set the privacy level for the folder, it applies to everything within that folder, including files in subfolders

Now that sounded cool.  So I set out to test it out.  First thing I did was went to add a new Folder to my Data Source Settings dialog and… there’s no option to do that. Hmm… okay… so how?

Well, you could go and build a solution that references a folder, just so that you can get a folder in there, I suppose.  But that’s awkward and contrived really.  You have to waste a bunch of time concocting something you hope will work.  And the worst part to me is that I don’t just have to do it on my system, I somehow have to deploy it to other people as well.  Ugh.

Screw it, I’m building a tool.  Here’s the features of what we’ve got:

Clear out all privacy levels

To be fair, this was probably more useful for me while testing, but I included it in case you run into the same issues.  Basically you click this button:

image

And now all the Data Privacy Settings are gone too:

image

Add Folder

So this one is way more useful to solving my issues.  I’m going to click “Add Folder” on the left.  It will let me browse for a folder:

image

And prompt me for the Privacy Level:

image

And once I click OK, it sets it up as a folder in the Data Source Privacy settings.  And yes, it really does trust all files in all subfolders:

image

Add Files

Same thing really, it just allows you to select a specific file.  This is a bit redundant to just declaring them as you go along but hey, once I already wrote the code for the folder this was a snap to add:

image

Add URLs

I love this one.  Easy to use, just provide the URL and the privacy level:

image

And boom!  There you are:

image

And again, this trusts all sub-sites of the main domain.  This was particularly key for me as I’m querying data from this site and have to provide a different URL for each year.

Now, again, on this one, the first time you query the site you’ll get another entry for each URL you touch:

image

This is because you have declared the privacy level, but not the security (unfortunately I can’t get in to modify the security files, for obvious reasons.)  So here’s what I’m going to do to fix this:

  • Select the first URL in the list
  • Click Edit Credential
  • Change the setting to apply to the root domain:

image

  • Click Save

You’ll notice that the first one disappears now.  What actually happened was that it merged the security credentials with the privacy level in one entry.  Cool.  So now I’ll just go back and delete the other two. And it never asks me again on refresh.  :)

The “Big Red Button”

So when it all goes really wrong, and you want to reset Power Query to a default state, what then?  That’s what this button is for.  It will wipe out all of your security credentials, data privacy settings, reset your Power Query formula bar and more.  It’s like a total factory reset of Power Query.

image

Some More Technical Stuff

I’ve now had this installed on Office 2010, Office 2013 Professional Plus and Office 365 Pro Plus versions of Excel, both 32 and 64 bit.  In addition, it’s been installed on Windows 7 Pro and Windows 8.1 systems as well.

Interesting enough, despite being an .MSI installer file, in my tests it has NOT required admin privileges to install.  (On one machine I installed this first without prompting, then got prompted for admin credentials when I went to update Power Query.)

Some Final Observations

I found this project pretty interesting, and it’s given me some ideas for some more useful tools to work with Power Query and other Power BI add-ins.  While I can’t promise a timeline on delivery, I do plan on adding a bunch of new useful stuff to this add-in and releasing a Pro Tools version at some point.

Before you download and install this, I also want to make something very clear.  THIS IS BETA SOFTWARE.  I’m pretty sure it’s stable, and shouldn’t affect anything else, BUT YOU DOWNLOAD AND INSTALL AT YOUR OWN RISK.

The Installer

You can download the installer from this link.

PowerPivot training live in Victoria, BC!

Anyone who follows my website or Facebook Fan Page knows that I’m a huge fan of PowerPivot. Well great news now, that you can come and learn not only why this is the best thing to happen to Excel in 20 years, but also how to take advantage of it yourself!

I’ll be teaching a course on PowerPivot and DAX in Victoria, BC on November 22nd, 2013. While the course is hosted by the Chartered Professional Accountants, it’s open to anyone who wishes to subscribe.

If you’ve been trying to figure out how to get started with Power Pivot, you’re confused as to how and why things work, or you want to master date/time intelligence in PowerPivot, this course is for you.

100% hands on, we’ll start with basic pivot tables (just as a refresher.) Next we’ll look at how to build the PowerPivot versions and why they are so much more powerful. From linking multiple tables together without a single VLOOKUP to gaining a solid understanding of relating data, you’ll learn the key aspects to building solid PowerPivot models. We’ll work through understanding filter context, measures and relationships, and finish the day by building measures that allow you to pull great stats such as Month to Date for the same month last year, among others.

Without question, you’ll get the most out of this course if you’re experienced with PivotTables. If you don’t feel like you’re a pro in that area though, don’t worry! As an added bonus, to anyone who signs up via this notice, please let me know. I’ll provide you with a free copy of my Magic of PivotTables course so that you can make sure you’re 100% Pivot Table compatible before you arrive.

This is a hands on course, so you need to bring a laptop that is pre-loaded with either:

  • Excel 2010 and the free PowerPivot download
  • Excel 2013 with Office 2013 Professional Plus installed (yes the PLUS is key!)
  • Excel 2013 with Excel 2013 standalone installed.

If you have any questions as to which you have installed, simply drop me a note via the contact form on my website, and I’ll help you figure it out.

Full details of the course contents as well as a registration link, can be found at http://www.icabc-pd.com/pd-seminars-seminar.php?id=2849. Don’t wait too long though, as registration deadline is November 14th!

Hope to see you there!

Quick Tip – Flip Numbers From + To – (Or Vice Versa)

I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here’s how:

  • Copy the range of numbers
  • Right click the destination and choose PasteSpecial
  • Select Values and Subtract:

  • Say OK and voila:

Now interestingly enough, if there are values in the cells, it overwrites them. If there are formulas in the cells, it subtracts them from the formulas. Kind of odd that it isn’t consistent.

(And of course, if you subtract a negative number, it turns into a positive too.)

Setting up Out Of Office replies on Outlook 2010

I love this! If you’re running Outlook 2010 and Exchange 2010, you can set up your out of office replies any time during the day, and schedule them to take effect for a certain period. I wish I could do this with my phone… that way I could set it up when I think about it, and then forget about it. Currently I have to set a reminder to change my greeting just before I leave for the day… something that I’ve forgotten in the past due to the inevitable last minute chaos that always erupts just before you’re planning to go away for a week!

At any rate, back to Outlook, here’s how:

  • Go to the File menu and click Automatic Replies
  • Check the radio button to send automatic replies
  • Check the checkbox to only send during the time range

I set mine up to take effect 5:00 on the day I’m leaving until 5:00 of the business day before I’m back. Just click OK and you’re done! No need to worry about remembering to do it just AFTER you shut your computer down.

In addition, you can also set up a different message for those outside the organization… (What you’re seeing above is the message to anyone inside my company.0 Click the “Outside My Organization” button and set up your message there. (Notice you can also opt not to respond to anyone externally.)

You might notice that I also dragged my return by a day for those outside the organization in this case. Gives me a bit of time to catch up on the internal stuff first.

This is pretty cool stuff, and a major improvement over earlier versions. I think it would be great if I could control the time range for external messages separately as well, but hey, I can live with this for now. J

An Interesting Use For Slicers

Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”

Wow, cool. This is a great thought. Combined with the weather, this makes it a forward looking document that should be really useful.

It’s not like we don’t have the information, either. We have a shared Outlook calendar that we use for recording all of our events. So it’s just a matter of getting the appointments out of the Calendar and into Excel. Easy, right? Ha!

Outlook has a weird way of storing appointments… especially when you get into recurring appointments. It seems that if you run code looking for appointments it ignores the recurring ones. Run the code to get the recurring appointments and it gives them to you, but with the date of the first recurring appointment… even if you select a recurring appointment from within a date range. (I pulled all appointments from 2011-04-04 to 2011-04-11, and was getting appointments from 2010!) Apparently Outlook doesn’t actually store the dates of the recurring appointments at all, only the first and the recurrence pattern.

After a couple of days fighting with this, (on and off,) I happened to hit Bing looking for help. Lo and behold, I found an article by Jimmy Peña that does it all! Sweet! (Thanks Jimmy!)

I had to make a very simple change to the article in order to make it work on a shared calendar, but that was it. Jimmy’s code pulls down the key pieces I needed and places it all in a nice table:

Now, what we did with it…

I really wanted something to put on the bottom of the report that looked nice, and gave the users appropriate information. I could have pulled this into a PivotTable and put it on the report, but I didn’t really like the look of it. Then I got to thinking… I like the way the slicer looks, I wonder if… So on a whim I decided to try something:

  • I created a column for each type of event I wanted to display

  • I created a PivotTable that showed the list of events
  • I added a slicer off the Pivot Table for each of the event categories at the bottom of the report

So now, I have the following to place on the bottom of my Dashboard to show the team what events are upcoming over the next week (You can picture the charts and tables above that.)

I don’t think that anyone at Microsoft ever intended that someone would use slicers are the report output, but for our purposes it works.

Now, some observations here…

  • I can’t get rid of the blank lines. The table formulas use “” at the end of the IF statements, but they still show in the slicers. Using NA() returns #N/A in the slicers.
  • One could argue that it might be better to have these grouped by date, rather than by category. We may still do that, depending on the feedback from the team.
  • It’s unfortunate that I had to link these to a PivotTable… I wish I could just create a slicer and give it a range to populate, like a chart
  • Slicer formatting options are severely limited. You pretty much can choose a colour, to sort ascending/descending and if you’d like to shade unavailable options. (How about hide them all together?)

Granted, this may not be perfect for everyone, but I kind of like the use for this. It adds a bit of polish to our overall report, as the slicers are a bit more glamorous than a standard Excel table (with the rounded edges and all.)

I’m curious what you think of it.

Excel Events Calendar!

Hi everyone,

As per my last post, I recently set up a free public Excel Help Forum on my website. I’m pleased to say that over the past week and a bit we’ve attracted 50 new users, and now have over 40 threads with over 200 total posts on the site. Not bad for 10 days!

I’ve spent a lot of time configuring different options to try and make this forum as consumable as possible for people. In addition to the basic forum functionality, some of the modifications include:

  • Serious anti-spam solutions to keep the board all about questions and answers, without having to wade through garbage
  • Facebook integration, for those of you who like to tell your friends what you’re doing ;)
  • RSS feeds, for those who like to keep up to date on the topics, and drop in when you see one that interests you
  • Tapatalk integration, for those who want to keep up with the site on a mobile device

And those are just the big ones. There’s been a lot of tweaks under the hood to make the experience as optimal as possible, and a few others are coming soon.

The latest thing I’ve worked on is adding what I hope to be THE Excel Events Calendar on the internet…

I’d like to invite everyone to use the Calendar on the forum as a public Excel Events Calendar. If you have, or know of, an Excel event in your area, please post it for the world to see. I’d like to make this the most comprehensive Excel training calendar on the internet.

There are only 2 rules I’d like to attach to this at this point:

  • Put the location of the event in the title, if applicable. The goal is that people will be able to find events that they can attend in their own backyard, worldwide.
  • Only the date(s) of event should be posted. (I don’t want a post each week reminding people.) If there is a registration cutoff date, please post it in the event information.

I think if everyone observes the above, the calendar should remain relevant and helpful to everyone.

Events I would expect to see include training courses (live or online), new book release dates, conferences that deal with subject matter relevant to the users on this site. (Be it BI, SQL, Sharepoint and more, so long as it has an Excel flavour, it counts.)

If you are a trainer, teacher, publisher, or whatever, I invite you to participate. Register for the site, if you’re not already, and post your event to the Excel Event Calendar. The more up-to-date and accurate we can make this, the more our community can rely on this being the source to come to for training courses. The more that happens, the more likelihood you’ll get signups. The more signups you get, the more likely you are to want to put on more training events, and the more the users win. It’s a self-fulfilling cycle that is in all of our best interests.

Oh, and one final word on this to everyone. I am not doing this to solicit commissions, affiliate links or funding in any way. There is already enough advert links on the site. This is about people helping people in our Excel world.

New Excel Help Forums at Excelguru.ca

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time… creating my own Excel forums.

I’ve been absent from the online forums for the last few years, and I’ve greatly missed it. With the amount of teaching I’ve done over the past few years I’ve constantly been referring people to other forums and blogs, and I’ve received several comments from those people asking about a forum on my own site. So during this time I’ve been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I’ve been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I’ve put off for my job over the past few years.

To this end, I’ve begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I’ve also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it’s important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You’ll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you’re trying to do. After all, a picture saves 1000 words, right?

And if you’re an expert, or even if you’d just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won’t always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you’d prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

Write-back Using PowerPivot

I think this is kind of neat…

In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform writeback using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking… we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!

To be clear here, this only works if your entire model is built in Excel and PowerPivot. You can source data from elsewhere to supplement it, but the key is that the information will be written into the PowerPivot cube as the ultimate database. I am certainly not advising anyone to toss a database in favour of a PowerPivot file, but if you don’t have a database, and want to user PowerPivot as your DB, then this could work.

Here’s how I generated a writeback scenario…

To summarize this:

  • The initial step is to create a table of data in Excel, format it as a Table.
  • Next, we need to link the Excel table into PowerPivot. (Create Linked Table.) This action will upload the structure and data into PowerPivot, forming the beginning of the PowerPivot database.
  • The next step is that we create a PivotTable in Excel, based off the data. At this point we will have a data table in Excel, and a PivotTable based off that data in another sheet. While I haven’t tested this, I don’t see any reason why you couldn’t avoid the PivotTable and just use cube functions based off the PowerPivot cube instead if you prefer. At any rate, here’s the PivotTable I set up:
  • I then create a working sheet.
    • The purpose of this worksheet is to pull in the values from the PivotTable (or be populated with OLAP formulas), then allow the user to “override” the key sections. In my test, I used a bunch of GETPIVOTDATA functions to pull the values from the PivotTable (although I could easily have one with an Index/Match combination or something else too.) This section looks like as follows (notice the override cell):

    • Depending on the complexity of the model, I might also create a summary section to re-summarize all the inputs. This would also give the user a place to review for reasonableness, as well as give a nice range to use for an INDEX(MATCH(),MATCH()) combination. You can see tha the summary version uses my override, not the original PivotTable value here:

  • Finally, we go back to the original data table we uploaded to PowerPivot. At this point, we need to have it read the data from our adjusted data tables. In the case of the example here, I used an Index/Match setup to read from the data table immediately above.

And now we have the ability to generate our writeback. We start at the top of the image below, where the blue circles are manual steps and the red circles are automatic.

You can download a sample of the setup I used to test this. It’s fairly simple, but it does demonstrate that it works. J

Data Validation in Excel Services

I’m a huge consumer of Data Validation in Excel. Techniques I use include:

Pro-active Data Validation:

  • Excel’s Data Validation toolset
  • Form controls
  • ActiveX controls
  • PivotTable filters
  • Slicers

Re-active Data Validation:

  • Conditional formats
  • Contextual formulas (IF)

So naturally, as I was trying to convert one of my web pages to use Microsoft WebApp off SkyDrive, I ran into issues. Unfortunately at this point in time, the majority of the techniques that we use for data validation in the client are not yet supported in WebApp. And yet, if we’re trying to build a web application, sanitizing the data is really important to make sure it works correctly.

I decided to convert my “Automation Evaluation” worksheet, which was intended to display how much it cost someone to do repetitive tasks over time. The overall goal of this file was to convince someone that they should pay me to automate their work, back when I was still doing consulting projects. I don’t do them any more, but I think the exercise is still worth having on the site.

In this file, it was really important to me to control the options people can select to work with for how many minutes/hours/days they spend on a task. I want the right data in there to drive my chart, yet my options are VERY limited in WebApp.

I tried the slicers, but they aren’t really built for this scenario. Set horizontally or vertically they take up way too much space. You can set them to a single column then make your user scroll down, but if you only have one option on the screen (like a combo box) then it gets awkward to figure out which option(s) are selected, and involves extra clicks. And that doesn’t even touch the “how do I return the clicked value from a slicer” issue. (I haven’t figured out how to get a value from a non-PowerPivot slicer yet.)

So this time I reached to PivotTables. I ended up making some very small tables with the options I needed and created a PivotTable that uses only the Page Field. This give me the ability to get a drop-down with pre-defined options. It works, but it does have a couple of issues:

  • In the client it defaults to a “Select one item” approach and you have to check a box to allow you to select multiple items. In WebApp, it allows you to select multiple items. So I had to add a contextual formula to feed back info to the users if this happens.
  • There are actually hidden rows in the worksheet to allow the Page Field to be changed. Even though the options selected always end up in the page field line, it seems to need extra space to refresh the table.

At any rate, it’s not perfect, but it seems to work. Here’s a look at the file:

Another Excel Services (Skydrive) Example

This isn’t done yet, and had some extraneous information in it as I was converting a file with much more in it to an interactive web version. This particular file (when complete) will show an interactive breakeven analysis for an event entailing food and beverage.

The link will update as I rebuild the file, but for right now, it’s working, and shows that you can interact with the file and have the chart redraw. Probably the best route is to adjust by very large amounts to see the effects. Once I’ve got it all up and running properly, I’m going to build this into a full page in my site, but I’m uploading for a demo for a client that I need for tomorrow morning.

Here you go, have a play!