Power Pivot eBook Coming Soon

It's been a long time coming, but we are putting the finishing touches on the third installment of our free 'DIY BI' series. Consequently, we are excited to announce that the Power Pivot eBook will be officially released on Tuesday, July 3, 2018!

Power Pivot eBook

This brand new book will feature five of Ken's top tips, tricks, and techniques for Power Pivot, including:

  • Hiding fields from a user
  • Hiding zeros in a measure
  • Using DAX variables
  • Retrieving a value from an Excel slicer
  • Comparing data using one field on multiple slicers

Power Pivot eBook

 

About the 'DIY BI' Series

This free eBook series is available to anyone who signs up for the monthly(ish) Excelguru email newsletter. The series includes four books, one edition each for Excel, Power Query, Power Pivot, and Power BI. Each book contains five of our favourite tips, tricks, and techniques which Ken developed over years of research and real-world experience.

DIYBI eBook Series

We first launched this series in the spring of 2017 with the Excel Edition, and the Power Query edition followed later that summer. You can read some more about why Ken decided to create this series in his initial blog post about it.

The Excelguru Newsletter

The monthly Excelguru email newsletter features the latest updates for Excel and Power BI, as well as upcoming training sessions and events, new products, and other information that might be of interest to the Excel and Power BI community.

Don't Miss Out, Get Your Free Copy of the Series

If you're not already a newsletter subscriber, you can sign up here. We will send you the Excel Edition right away, and the Power Query Edition a few days later. All of our current and new subscribers will receive the Power Pivot edition once it is released on July 3, 2018. Be sure to keep an eye on your inbox for the new book.

We will be continuing to work on the fourth and final book, the Power BI Edition, over the coming months so stay tuned for details!

Trick to Protect Excel Tables

Slobodan emailed me to describe a trick to protect Excel tables that he is using to drive data validation lists.  The data validation lists are sourced from tables loaded via Power Query, and leverage a little hack to hide them from prying users eyes.  I thought it would be cool if he shared it with everyone, so asked him to write up a little blog post on it, and here it is!

Take it away Slobodan…

Hello everybody,

Recently, my team and I had faced a problem with refreshing PQ tables that we managed to solve with a simple trick (no VBA coding), and shared it with Ken who asked me to share it with community. Thank you Ken for this opportunity! Glad to make some kind of contribution, to all of you PQ users.

Solution Background

We created calculation model for our sales people (Full cost calculation).  Inside this Excel file, they have a lots of drop down lists from which they can choose customer, partner etc. The idea is to make these dropdown lists dynamic.  In other words, whenever a new customer is created in SAP, they should be able to select this customer in Excel using a dropdown list. This is where Power Query comes to the rescue.

We have scheduled daily export of all our customers from SAP to a file on a network drive, and use this file as the data source for a local PQ table in the workbook. We then use our Power Query table “Customers” as the source for dropdown lists in calculation model.

The Challenge

How to make it fully automated? We have two goals here:

  1. We want Power Query to be scheduled for automatic refresh on a daily basis
  2. At the same time, we would like to protect Excel tables sourced via Power Query from careless users

For the first point, we have Power Update - a tool which allows you to schedule daily refresh.

Note from Ken: I haven't seen Slobodan's model, so there may be a need to use Power Update to do what he's doing.  If you only need your Power Queries to update each time the Excel workbook is opened, however, you could force an update by changing the table's connection properties to force an update upon open.

Second issue, in order to protect Power Query table, we need to hide these sheets and protect the workbook.  The end result is that our Customers table is hidden and cannot be unhidden and everything looks promising.

clip_image002

Of course, Excel protects the whole workbook structure using this method, which causes Power Update to fail. In fact, query refreshes also fail if we try to refresh data manually.

clip_image001

So the obvious solution doesn't work.  I spent time Googling for solution to this but could not find one 🙂

Our Solution

I am not VBA guy, but I remembered one tip from Mynda Treacy’s dashboard course which I applied here.

Step 1

  • Hide the worksheet and open the Visual Basic Editor (press Alt+F11)

Step 2

  • In the Project Explorer Window (Ctrl + R if it's not showing) select the sheet which  contains the Power Query table

clip_image003

Step 3

  • In the Properties Window (press F4 to display this), set the Visible property to "2 - xlSheetVeryHidden"

clip_image004

Step 4

  • Go to Tools --> VBAProject Properties --> Protection
  • Check the box next to "Lock Project for Viewing"
  • Set a password so only you can access it
  • Close the Visual Basic Editor

The Effect

Our sheet containing the Customers table is hidden, and there is no possibility to unhide it.  It doesn't even show up in the menu!.

clip_image005

At this point the only way to unhide the worksheet is to go into the Visual Basic Editor, and reset the worksheet's Visible property - but you protected the VBA project with a password so no one can get in there.

The great thing is that refreshing the Power Query tables will work, because you didn’t actually lock the workbook structure.

Caveat

This solution is intended to protect data from regular excel users, who can easily mess up your workbook.  Do be aware that users with VBA skills will be able to break the password, or extract the hidden sheet contents.

Hopefully someone finds this useful 🙂

Take care!

Ranking Method Choices in Power Query

My recent post on showing the Top X with Ties inspired a discussion on ranking methods.  Where I was looking to rank using what I now know as a standard competition rank, Daniil chose to use a dense ranking method instead.  Oddly, as an accountant, I've never really been exposed to how many different ways there are to rank things - and I'd certainly never heard the terms skip and dense before. (At least not tied to ranking!)

Naturally, after a few emails with Daniil and a bit of a read over at Wikipedia on 6 different common ranking methods, I had to see if I could reproduce them in Power Query.

What are the 6 different ranking methods?

Let's look at a visual example first.   These were all created in Excel using standard formulae:

image

The first thing I had to do was figure out what each ranking method actually does.  So here's a quick summary according to Wikipedia's article on the subject:

  • Ordinal Ranking - This ranking method uses sequential number for each row of data, without concern for ties
  • Standard Competition Ranking - Also know as a form of a Skip ranking, this method gives ties the same rank, but the following value(s) are skipped.  In this case, our values go 1,2,3,4,4, 6.  (5 is skipped as the 5th item is tied with the 4th)
  • Modified Competition Ranking - This is similar to the Standard Competition ranking method, but the skipped values come before the ties.  In this case, we would get 1,2,3, 5, 5, 6.  (As 4 and 5 are tied, they both get ranked at the lower rank.)
  • Dense Rank - In this ranking method, ties are given the same value, but the next value is not skipped.  In this case we have 1, 2, 3, 4, 4, 5.
  • Fractional Rank - Now this one is just weird to me, and I'd love to know if anyone has actually used this ranking method in the real world.  In this algorithm, ties are ranked as the mean of the tied ordinal rank.  Very strange to me, but it won't stop me from building it!

So know that we know what they all are, let's build them in Power Query so that we can perform them in both Power BI and Excel.

Groundwork for demonstrating the ranking methods

If you download the sample workbook, you'll see that it has the full table shown above.  To make this easy, I set up a staging table called SalesData as via the following steps:

  • Select a cell in the Excel table --> Data --> From Table/Range
  • Select the Item and Sales columns --> right click --> Remove Other Columns
  • Load it as a connection only

This gave me a simple table with only the product names and values as shown here:

image

As you can see, the values column has already been sorted in descending order, something that is key to ranking our ties.

One thing I should just mention now is that - for every ranking method - we will actually start every new query by:

  • Referencing the SalesData query
  • Renaming the new query to represent the ranking method being demonstrated

That means that I'm just going to give the steps each time based on the view above, since that's what we should get from the referencing step.

Ranking Method 1: Ordinal Rank

This ranking method is super easy to create:

  • Sort the Sales column in descending order
  • Sort the Item column in ascending order (to rank ties alphabetically)
  • Go to Add Column --> Index Column --> From 1
  • Rename the Index column to Rank
  • Reorder the columns if desired

Yes, that's it.  It simply adds a row number to the way you sorted your data, as shown  here:

Ordinal Ranking Method in Power Query

Ranking Method 2: Standard Competition Rank

This ranking method involves using a little grouping to get the values correct:

  • Sort the Sales column in descending order
  • Add an Index column from 1
  • Go to Transform --> Group
    • Group by the Sales column
    • Create the following columns:
      • Rank which uses the Min operation on the Index column
      • Data which uses the All Rows operation
  • Expand the Item column
  • Reorder the columns if desired

The result correctly shows that the Dark Lager and Winter Ale - 4th and 5th in the list, but tied at 557, each earn a rank of 4, and the Member Pale Ale (6th in the list) comes in with a rank of 6.  There is no item ranked 5th, since their rank was improved to be in a 4th place tie.

Standard Competition Ranking Method in Power Query

Ranking Method 3: Modified Competition Rank

To create ranking following the Modified Competition ranking method, we need to:

  • Sort the Sales column in descending order
  • Add an Index column from 1
  • Go to Transform --> Group
    • Group by the Sales column
    • Create the following columns:
      • Rank which uses the Max operation on the Index column
      • Data which uses the All Rows operation
  • Expand the Item column
  • Reorder the columns if desired

The only real difference between this ranking method and the standard competition rank is that we create the Rank column using the Max of the Index column instead of the Min used in the previous method.

The result correctly shows that the Dark Lager and Winter Ale - 4th and 5th in the list, but tied at 557, now earn a rank of 5 (not 4 like the standard rank).  There is no item ranked 4th, since their rank was dropped to reflect a 5th place tie.

Modified Competition Ranking Method in Power Query

Ranking Method 4: Dense Rank

The dense ranking method requires a change to the order of the steps from what we did in the standard competition ranking method.  Namely the Group By command must come before the addition of the Index column:

  • Sort the Sales column in descending order
  • Go to Transform --> Group
    • Group by the Sales column
    • Create the following columns:
      • Rank which uses the Max operation on the Index column
      • Data which uses the All Rows operation
  • Add an Index column from 1
  • Expand the Item column
  • Reorder the columns if desired

This method will yield the results found here:

Dense Ranking Method in Power Query

The result correctly shows that the Dark Lager and Winter Ale - 4th and 5th in the list, but tied at 557, ranked in 4th place - just the same as the Standard Competition rank.  But where it differs can be seen in the ranking of the Member Pale Ale.  6th in the list, it is ranked 5th, as there are no gaps left after the ties.

Ranking Method 5: Fractional Rank

As I mentioned at the outset, I find this to be one of the strangest methods of ranking.  Like the others though, it's actually really easy to create when you know how. (And certainly more straight forward than using an Excel formula to calculate it!)

  • Sort the Sales column in descending order
  • Add an Index column from 1
  • Go to Transform --> Group
    • Group by the Sales column
    • Create the following columns:
      • Rank which uses the Average operation on the Index column
      • Data which uses the All Rows operation
  • Expand the Item column
  • Reorder the columns if desired

One thing I will say… it's certainly makes it obvious that there are other ties in the table.  Maybe that's the point of it?

Fractional Ranking Method in Power Query

Final Thoughts

I was actually surprised to see how easy it is to change the ranking methods with just some minor modifications to the order of steps and/or the aggregation chosen when applying the grouping method.  It certainly gives us some robust choices!

And while we can certainly create each ranking method using Excel formulas (each is demonstrated in the sample file if you're curious), this is even more awesome.  Now we don't need to load data and land it in the grid.  We can go straight to Power Pivot or Power BI should be need to.

If you'd like to download a file with each of the methods illustrated, just click here.

Creating Dynamic Parameters in Power Query

A couple of years ago, the Power Query team added Parameters as a proper object, but I kept on Creating Dynamic Parameters in Excel Power Query the same way as I always had.  The reason for this is two-fold: the first is because I was used to it, the second was because the built-in Parameters are quite static.  Sure, you can set up a list and change them at run time, but you have to enter the Power Query editor to do that.  And is that really something you want your boss doing?

So why do we care about creating dynamic parameters, anyway?

Let's take a look my last technical blog post to understand this.  In that post, I pulled a Top 5 value from an Excel cell, and used that to drive how I grouped my items.  It works great, and is truly dynamic.  It puts control of the grouping in Excel, allowing a friendly user interface for the end user to work with.  They simply change a cell value, hit refresh, and all is good.

The challenge here is not from the end user's perspective, it's from the developer's.  One of the instructions I gave in the post last week was to:

  • Create a Custom Column using the following formula:
    • if [Rank] <= TopX then [Item] else "Other"

Why a Custom Column?  Why not just use the Conditional Column dialog?  The answer is simple… TopX in this case was a query that returned a value, but it was not a proper Power Query Parameter.  Does it work the same in code?  Pretty much yes, but you can't see it in the Conditional Column dialog as you're building the query.

Even worse, if you want to make any modifications to the logic, you have to do it in either the formula bar or the Advanced Editor, as the gear icon returns the conditional column builder, but can't resolve the query:

Conditional Column Dialog

Wouldn't it be nice if we could create dynamic parameters that actually show up as valid Parameters to Power Query?  That's the goal of this post.

Groundwork - Creating the dynamic parameters in Excel

There are two different ways we can do this:

 1. Fetching dynamic parameters using a Named Range

This is the super easy method.  To do this:

  • Enter your parameter value in a worksheet cell
  • Go to the Name Manager and define a name for the cell (I called mine rngKeep)
  • Select the cell and pull the data into Power Query
  • Right click the value in the table's cell --> Drill Down
  • Rename the query
  • Load it as a Connection only

For this example, I renamed my query to XL_TopX_NamedCell.  It's a long name, I know, but you'll see why in a bit.

2. Fetching dynamic parameters from a Parameter Table using the fnGetParameter function

I've detailed this technique on the blog before, so if you'd like to review this technique, you can find a detailed post on that here.  The quick summary:

  • Create a two column table called Parameters, with Parameter and Value columns

Parameters Table

  • Copy in the fnGetParameter function (from the other post)
  • Call the function as needed

Just to check the value, I then:

  • Created a blank query
  • Entered the following formula in the formula bar
    • =fnGetParameter("Keep top")
  • Named this query XL_TopX_fnGetParameter
  • Loaded it as a connection only

Query to check the dynamic parameter value

So what makes a parameter a "Real" parameter?

At this point, I decided to create a new parameter and look at what happens.  To do this, go in to the Power Query editor and…

  • Go to Home --> Manage Parameters --> New Parameter
  • Give the Parameter a name (I used Test)
  • Set a Current Value of 0
  • Click OK

Next, right click the Parameter in the Queries pane on the left and go to the Advanced Editor.  You should see code that looks like this:

0 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

So this is interesting… 0 is the value, and the rest is just a meta tag to tell Power Query that this is a real parameter…  This got me wondering… am I stuck with this value, or can I feed it a Power Query code and actually create a dynamic parameter that updates at run time?

Converting a Query to a dynamic Parameter - Take 1

The first thing I did here was copy everything after the 0, then exited this query.  I then:

  • Jumped over to the XL_TopX_NamedCell query
  • Entered the Advanced Editor
  • Pasted the copied line of code at the end
  • Clicked OK

And it didn't work.  Not to give up, I jumped back into the Advanced Editor and wrapped the original query in parenthesis like this:

Wrapping the original query in the Advanced Editor

And this time, something did change:

Dynamic parameter appears in the query list

There are 3 things worth noting here:

  1. It has the parameter icon (Yay!)
  2. It doesn't show a current value but shows an exclamation icon
  3. It shows the value of (…) in the name - meaning it doesn't know what the value is

I wasn't too worried about this last one though.  Dynamic named ranges show the same way in Excel, so would this work to create dynamic parameters?

Conditional Column Dialog

It sure does!  Not only does it show up in any parameter drop down, but the value gets read correctly and allows me to make my comparisons.  How cool is that?  I've actually got a dynamic parameter now!

Converting a Query to a dynamic Parameter - Take 2

Now, as cool as this was, there is something that bothered me about it.  When you tag the meta data at the end of a functional query and turn it into a parameter, you lose the applied steps.  If anything goes wrong, it makes it hard to debug.  (Reminds me of the classic custom function setup.)

To solve this, I decided to remove all the meta tags and parenthesis from the XL_TopX_NamedCell query, returning it to what is was before I turned it into a parameter.  I then created a new blank query called TopX_NamedCell and edited the code in the Advanced Editor to read as follows:

XL_TopX_NamedCell meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Why?  Because I now have the query that pulls in the original data.  When I click on it, I can see the values and debugging steps to get there:

Checking the dynamic parameter value

And I also have a Parameter, which pulls from this value and can be used in my drop downs:

Conditional Column Dialog

Extending dynamic Parameters to leverage the fnGetParameter function

If you've used the fnGetParameter function before, it only makes sense that you'd want to know if we can leverage this function to pull values and return real Parameters.  And indeed you can.

 Parameters that pull from fnGetParameter

Here's the quick and dirty way to create dynamic Parameters by calling the fnGetParameter function directly:

  • Create a new blank query
  • Name your new Parameter  (I called mine TopX_DirectFromFunction)
  • Go into the Advanced Editor
  • Paste in the following code:

fnGetParameter("<Variable Name>") meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

  • Replace <Variable Name> with the name of the variable you want from the Excel Parameter table.  In the example this would be fnGetParameter("Keep top")
  • Click OK

Yes, it's just that easy.  You've now got a fully functional and dynamic Parameter… at least, you do if you replaced the variable name correctly with one that exists in the Parameter table!

NOTE:  I recommend that you rename your query before you edit the M code since you lose the applied steps window during the process.  You can still rename a parameter, but you'll need to right click it in the queries pane on the left and choose Rename to do so.

Making dynamic parameters that pull from fnGetParameter auditable

There's only one problem with the above approach. How do you test the value is resolving correctly before you try to use it?  Or how do you look to see what is actually happening when your downstream queries return an error?

For this reason, I actually recommend that you don't use the fnGetParameter query in a real Parameter as outlined in the previous section.  What I recommend you do is create an intermediary query which leverages fnGetParameter to pull the value from the Excel table, then reference that query from the Parameter query.  So in short:

Create an intermediary query

This is also fairly easy to set up.  The full process would be:

    • Copy in the fnGetParameter function
    • Set up the Parameters table in Excel and populate it with data
    • Create a new blank query to retrieve the parameter value
      • Name it
      • Enter the following in the formula bar:
        • =fnGetParameter("<variable name>")
        • replace <variable name> with the name of the parameter you wish to retrieve
      • Load as Connection only
    • Create a new blank query to be the real Parameter
      • Name the parameter as you'd like to see it in drop down lists
      • Go into the Advanced Editor and enter the following
        • QueryName meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]
        • Replace QueryName with the name of the query you created above
      • NOTE: Parameters will automatically load as Connection Only queries
    • Use the new Parameter in other queries

See it in action…

The attached sample file contains three different variables based on the methods above, any of which can be used to drive the Conditional Columns step in the Grouped query:

Dynamic Parameters listed in the Conditional Column dialog

And if you're curious, they are related as shown below.  The TopX_NamedCell parameter is driving the version I saved, but as per the above, you can change that out easily.  (Naturally, in this case they all serve up the same value though!)

Query Dependencies View

Some Observations

As I was playing around with this, I noticed a couple of things that are worth bringing up here.

Yes, these work in the Power BI service!

To test this out, I cooked up a small sample that used a dynamic parameter using the methods outlined above to read the most recent year's data from a SharePoint folder.  I then published it to the Power BI service, added a new file to the server and refreshed the data in Power BI online.  Worked like a charm.

For the record, I haven't tested, but don't anticipate that this will work well with Power BI templates, as they will most likely clear the parameters and prompt you for values.  Any data points you wish to be preserved should be left as queries.

The Convert to Parameter function

Assume you created a new query, then typed a value into the formula bar (not a formula, but it could be numeric or text).  This would return a single (scalar) value that is static.  You'd then be able to right click the query in the Queries pane and choose Convert to Parameter.  Unfortunately, if your query returns anything that is dynamic or has multiple data points, this option is greyed out.  That's too bad, as this would be a really cool thing to be able to do.

Avoid the Add/Manage Parameter UI

Unfortunately, adding even a single dynamically-driven parameter renders the Manage Parameter dialog useless to you.  The reason is that as soon as you try to say OK to any parameter in that list (whether modifying or creating a new one), it appears to try to validate the current value of each of the listed parameters:

Add/Manage Parameter UI

This is unfortunate, as it means that you'd need to kick over to a blank query to create any new Parameters or debug any existing ones.

UPDATE:  Thanks to Andrew in the comments, I know that you can uncheck the Required value when creating your parameter.  If you do that the M code upon the initial creation comes up as:

0 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=false]

If the required setting is false, then the manage queries dialog can still be used without forcing an update!

 The Parameter meta tag

The only part of the Parameter meta tag that is actually required is the following:

meta [IsParameterQuery=true]

Having said that, I got mixed results doing this.  Sometimes the Parameters were not presented in my drop down list.  Editing those queries and restoring the full meta tag to the end resolved that immediately.  I.e.:

meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Return a Specific Day of the Next Month

In a comment on a previous post, a reader asked how you return a specific day of the next month from any given date.  In other words, I've got a date of March 5 and I want to use Power Query to return April 10 in Excel (or Power BI).  How do you do it?

The Excel User's First Guess

So my first thought was to jump straight into the Power Query Formula reference guide to review the date functions.  Surely there must be something in there to manipulate dates and such, right?

Here's a quick list of the the functions I knew I'd need:

  • Date.Year()
  • Date.Month()
  • Date.AddMonths()

So those are awesome for ripping dates apart and shifting them, but what I really needed at the end was a way to put things back together.  I needed an equivalent of Excel's =DATE(year,month,day) function.  I couldn't find one.

Return a Specific Day of the Next Month

After poking around with this for a while, it suddenly occurred to me that I was doing this all wrong.  To return a specific day of the next month, I just needed to provide the "literal" #date() and I was good to go.

Let's take a simple table like this:

image

I pulled it into Power Query, went to Add Column --> Custom Column, and added the following formula:

=#date(
Date.Year(Date.AddMonths([Dates],1)),
Date.Month(Date.AddMonths([Dates],1)),
10
)

And at that point it works beautifully:

image

Basically, the #date() literal works just like Excel's DATE() function, you just case it differently and put a # tag in front of it:

#date(year,month,day)

It's a weird one, for sure

Returning a specific day of the next month is one of those odd cases where you have to use one of Power Query's literals to create the date you want, rather than employing a function to convert values as you're used to in Excel.   The good news though?  Miguel does an amazing deep dive into the M coding language in our Power Query Academy, including explaining what literals, tokens, keywords and more are all about.

If you want to understand this in depth, check out our course:

image

PS:  Sign up for our free trial first, to make sure you like our style!  And when you're convinced… you won't find better Power Query training anywhere.  Smile

Protect Power Queries

How you protect power queries is a question that will come up after you've built a solution that relies heavily on Power Query, especially if you're going to release it to other users.

(This is a quick post, as I'm in Australia at the Unlock Excel conference, but still wanted to get a post out this week.)

Can you Protect Power Queries?

The answer to this is yes, you can.  It’s actually very easy, and prevents your users from not only modifying your queries, but adding new queries to the workbook as well. Essentially, it shuts the door on any additions or modifications to query logic, while still allowing queries to be refreshed… at least, it should.

So how do we Protect Power Queries?

To protect Power Queries we simply need to take advantage of the Protect Workbook Structure settings:

  • In Excel (not Power Query), go to the Review tab
  • Choose Protect Workbook
  • Ensure that Structure is checked
  • Provide a password (optional)
  • Confirm the password (if provided)

Once you’ve done this, the Power Query toolsets will be greyed out, and there is no way for the user to get into the editor.

image

Does refresh work when you Protect Power Queries?

This part kills me.  Seriously.

The answer to this question depends on whether or not you use Power Pivot.  If you don't, then yes, you're good to go.  As long as all your tables land on worksheets or as connections, then a refresh will work even when you protect Power Queries via the Protect Workbook method.

If, however, you have a single Power Query that lands in the data model, your stuffed.  If Power Pivot is involved, then the refresh seems to silently fail when you protect Power Queries using this method (and I don't know of another short of employing VBA, which is a non-starter for a lot of people).

It's my feeling that this is a bug, and I've sent it off to Microsoft, hoping that they agree and will fix it.  We need a method to protect both Power Query and Power Pivot solutions, and this would do it, as long as the refresh will consistently work.

Caveats about locking your workbook structure:

Some caveats that are pretty standard with protection:

  • Losing your password can be detrimental to your solution long-term. Make sure you have some kind of independent system to log your passwords so this doesn’t happen to you. And if your team is doing this, make sure you audit them so you don’t get locked out when as staff member leaves for any reason.
  • Be aware that locking the workbook structure also locks the ability for users to get into Power Pivot.
  • Workbook security is hackable with brute force macro code available on the internet for free. (Please don’t bother emailing me asking for copies or links to this code. I don’t help in disseminating code which can be used to hack security.) While protecting the workbook structure will stop the majority of users from accessing your queries, it should not be mistaken for perfect security.

Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required





Solving Business Problems – Power Query Does That

This week's blog post on solving business problems with Power Query is a guest post by Alex Jankowski

Does your business or work group have manual tasks that are repeated regularly, such as data re-entry, copy/paste functions, sending the same old email, cross-referencing lists, etc.? We can automate these kinds of activities with a wide variety of tools, such as Excel (including VBA, Power Query and Power Pivot), Access, SharePoint and MS Power BI, as well as with other, more sophisticated software packages.

Solving Business Problems You Didn't Even Know Existed

I have found the following to be common themes that can inhibit identifying and solving business problems:

  1. Organizations are illiterate about their data.
  2. Organizations are allergic to cost.
  3. Many managers lack technical imagination. They can’t even conceive of the problems that the organization’s data could solve, let alone conceptualize potential solutions.
  4. Organizations think that only financial data is worthwhile.
  5. Organizations roll out massive ‘suites’ without a concept about how to get their money’s worth from the licenses. My guess is that on average less than 25% of the capabilities of MS Excel are utilized due to lack of training/imagination, which is not a good ROI.
  6. Organizations think that the IT group has a handle on this, whereas IT thinks that data belongs to the business or prevents anyone from accessing important data (IT acting as a “department of productivity prevention”).

And I’m not even talking about the mission critical data that resides in big ERP systems (including HR, Corporate Finance, Time Records, and Project Financials) or “Big Data” that everyone is looking over their shoulders at.

What about:

  • Detailing a list of technical drawings on a project?
  • Tracking hours spent on a project activity, or the employee vacation schedule against entitlements?
  • Creating a dataset of employee skills and performance reviews?
  • Linking that scheduling software package to a list of real deliverables?

The Basic Win - Owning Your Data

Every business has what can be termed ‘islands of data’ which are often generated and managed manually.

Islands of Data

There are 'islands of data' that exist in every organization

Let’s say that your business splurges to automate and standardize some elements of the work process to be more effective. What are the benefits? Well, there will be a reduction in repetitive costs that offset the application implementation costs. People can then graduate from data makers to data owners (where they spend more time analyzing report data rather than creating the report itself), and begin solving business problems by making smarter, data-driven decisions based on questions like, "Is that it? Was it worth it? Where’s the big payback?"

The BIG payback is in linking the ‘islands of data’ with each other to gain unexpected insights. Don’t believe me? Let's take a look at a simple real-life example.

The Unexpected Win

A few years back, I was working in an engineering firm with a very large project. The Operations Manager (thankfully, a guy with great data imagination) wanted to a way to track piping isometric drawings as they were reviewed by various project leads. The drawings would get lost in the review process, like when the head of electrical left them on his chair when he went away for a month in the summer. So we came up with a solution to identify every isometric in the review process, who had approved it, when it was approved, and where it was going next. We met the application goal and stopped losing the isometrics in the review process.

Sample Isometric Drawing

Sample Isometric Pipe Drawing (from http://www.svlele.com/drawings.htm)

Was that the BIG win? Nope. The big win was when we started matching the list of drawings that had completed the review cycle with the list of drawings issued for manufacturing in the document control system (which would have been a great opportunity for Power Query – I wish we had it then). Guess what? We found a small count of approved drawings which had not been issued to manufacturing.

The Real Cost of Not Doing the Data Work

You're thinking, "So what?" Well, each isometric drawing represents a section of pipe that is fabricated by the manufacturer with flanges, valves, fittings, etc. The fabricated pipe is then sent to the construction site holding yard to wait its turn to be assembled in the pipe rack by the construction team. The impact of the drawing not being issued to the manufacturer is that the pipe section is not on site when it’s time to construct, and nobody recognizes it is missing because the drawing was never issued.

What’s the cost of that?

  • extra cost: Rush to find and issue the missing isometric drawing
  • Extra cost: Construction team has to re-sequence its work to allow for the missing pipe section
  • Extra Cost: Rush fabrication by the pipe manufacturer, possibly resequencing their production schedule and possibly charging overtime rates
  • EXTRA Cost: Rush shipping charges to get the late pipe section to the construction site
  • EXTRA COST: Possible overall delay in the construction schedule
  • BIG EXTRA COST + LAWSUIT: Possible overall delay in the refinery startup schedule

That extra cost could be thousands of times more than the cost of doing the data work, and possibly end up eating all of the project’s margin and more. (If you’re going to be allergic to cost, this is the one to be allergic to). Creating the application for isometric tracking was a win, but matching it to the document control system drawing list was the BIG win and nobody expected it!

Delivering the BIG wins

Let's take a look at some ways at solving business problems in Power Query using the data you already have. What if you:

  • Need to find files, audit folder contents, or document a folder structure on the server? Power Query does that.
  • Need to develop an email filing system for your project? Power Query can list the emails, their contents, and attachments.
  • Need to open server files from an Excel list without navigating through the File Explorer? Power Query can help with that. (Eliminating the practice of people navigating from "My Computer" into the bowels of the server structure for each individual file is one of my personal lifetime goals.)
  • Need to create a templated report about projects, employees, or hours from an ERP system extract? Power Query does that.
  • Need to pull data from SQL for templated operations reports? Power Query does that.
  • Need to read multiple CSV or Excel files into a single data set? Power Query does that (check out some of Ken's other blog posts, like this one on Combining Excel Files).
  • Need to report on the status of drawings in your document control system? Power Query does that.

Once the 'islands of data' are being processed, Power Query does an awesome job of combining the data sets together, especially if all the column names are different and the data formats need to be aligned.

What data problems do you need to solve? How are you currently solving business problems, and are there opportunities for BIG wins in your business? Give us some feedback in the comments!

 

 

Values Become Text After UnPivoting Other Columns

Have you ever set up a nice query to UnPivot other columns, only to find that the query data types change when you add new columns?  This post will cover why values become text after unpivoting other columns.

Background

We’ve got a nice little table called “Data” showing here.  Nothing special, it just summarizes sales by region by month, and our goal is to unpivot this so that we can use it in future Pivot Tables.  (You can download the source file here.)

SNAGHTML552a2a7

Now, you will notice that April’s sales are outside the table. This is by design, and we’ll pull it in to the table later when we want to break things.  Smile

UnPivoting Other Columns – The Hopeful Start

If you’ve been following my blog for any period of time, you’ve seen this, but let’s quickly go over how to unpivot this:

  • Select a cell in the table
  • Go to Power Query (or Data in Excel 2016) –> From Table

We’re now looking at the Power Query preview of the table:

image

Great, now to unpivot…

  • Hold down the Shift key and select the Country and Prov/State column
  • Right click the header of either of the selected columns and choose Unpivot Other Columns
  • Right click the headers of the two new columns and rename them as follows:
    • Attribute –> Month
    • Value –> Sales

Re-Pivoting from the Data Model

With the table complete, I’m going to load this to the data model and create a Pivot Table:

  • Go to Home –> Close & Load –> Close & Load To…
  • Choose to Load to the Data Model

The steps to create the Pivot depend on your version of Excel:

  • Excel 2013: Go in to Power Pivot –> Home –> PivotTable and choose a location to create it
  • Excel 2016: Click any blank cell and go to Insert –> PivotTable.  As you have no data source selected, it will default to using the data model as your source:

Iimage

With the PivotTable created, I’ve configured it as follows:

  • Rows:  Country, Prov/State
  • Columns:  Month
  • Values:  Sales

And that gives me a nice Pivot like this:

image

Let’s Break This…

Okay, so all is good so far, what’s the issue?  Now we’re going to break things.  To do that, we’re going to go back to our original data table and expand the range:

image

In the picture above, I’ve left clicked and dragged the tiny little widget in the bottom right corner of the table to the right.  The table frame is expanding, and when I let go the Apr column turns blue, indicating that it is now in the boundaries of the table.

With that done, I’m going to right click and refresh my Pivot Table, leaving me with this:

image

Huh?  Why was the sales measure removed?  And if I drag it back to the table, I get a COUNT, not a SUM of the values?  And even worse, when I try and flip it back to SUM, I’m told that you can’t?  What the heck is going on here?

image

Importance of Power Query Step Order

To cut to the chase, the issue here is that when we first created the table in the data model, the Sales column was passed as values.  But when we updated the data to include the new column, then Sales column was then passed entirely as text, not values.  Naturally, Power Pivot freaks out when you ask for the SUM of textual columns.

The big question though, is why.  So let’s look back at our query.

Our original data set

If we edit our query, we see that the steps look like this:

image

To review this quickly, here’s what happened originally

  • Source is the connection that streams in the source data with the following columns:

image

  • Changed Type set the data type for all the columns.  In this case the Country and Prov/State fields were set to text, and the Jan, Feb & Mar columns were set to whole number.  We can see this by looking at the icons in the header:

image

Note that if you don’t have these icons, you should download a newer version of Power Query, as this feature is available to you and is SUPER handy

 

  • We then selected the Country and Prov/State columns and chose to Unpivot Other Columns.  Doing so returned a table with the following headers

image

Notice that the first three columns are all textual, but Sales is showing a numeric format?  Interestingly, it’s showing a decimal format now, but it shows the numeric format because all unpivoted columns had explicitly defined numeric formats already.

The final steps we did was to rename our columns and load to the data model, but the data types have been defined, so they were sent to the data model with Sales being a numeric type.

Why Values Become Text After UnPivoting Other Columns

Okay, so now that we know what happened, let’s look at what we get when we step through the updated data set.

  • First we pulled in all the columns.  We can plainly see that we have the new Apr column:

image

  • The Changed Type step is then applied:

image

Hmm… do you see that last data type?  Something is off here…

So when we originally created this query, Power Query helpfully pulled in the data and applied data types to all the existing columns.  The problem here is two-fold:  First, the Apr column didn’t exist at the time.  The second problem is that Power Query’s M language uses hard coded names when it sets the data types.  The end effect is that upon refresh, only the original columns have data types defined, leaving the new columns with a data type of “any” (or undefined if you prefer).

  • We then unpivoted the data, but now we see a difference in the output

image

Check out that Value column.  Previously this was a decimal number, now it’s an “any” data type.  Why?  Because there were multiple data types across the columns to be unpvioted, so Power Query doesn’t know which was the correct one.  If one was legitimately text and Power Query forced a numeric format on it you’d get errors, so they err on the side of caution here.  The problem is that this has a serious effect on the end load to Power Pivot…

  • Finally, we renamed the last two columns… which works nicely, but it doesn’t change the data type:

image

Okay, so who cares, right?  There is still a number in the “any” format, so what gives?

What you get here depends on where you load your data.  If you load it to the Excel worksheet, these will all be interpreted as values.  But Power Pivot is a totally different case.  Power Pivot defaults any column defined as “any” to a Text data type, resulting in the problems we’ve already seen.

Fixing the Issue

For as long as we’ve been teaching our Power Query Workshop, we’ve advocated defining data types as the last step you should do in your query, and this is exactly the reason why.  In fact, you don’t even need to define your data types in the mid point of this one, that’s just Power Query trying to be helpful.  To fix this query, here’s what I would recommend doing:

  • Delete the existing Changed Type step
  • Select the final step in the query (Renamed Columns)
  • Set the data type for each column to Text except the Sales column, which should be Decimal Number (or currency if you prefer)

image

When this is re-loaded to the Data Model, you’ll again be able to get the values showing on the Pivot Table as Sum of Sales.

Avoiding the Issue

Now, if you don’t want Power Query automatically choosing data types for you, there is a setting to toggle this.  The only problem is that it is controlled at a Workbook level, not at a global Excel level.  So if you don’t mind setting it for every new workbook, you can do so under the Power Query settings:

image

Is Changed Type Designed in the Correct Way?

It’s a tough call to figure out the best way to handle this.  Should the data types be automatically hard coded each time you add a new column?  If the UnPivot command had injected a Changed Type step automatically, we wouldn’t have seen this issue happen.  On the other hand, if a textual value did creep in there, we’d get an error, which would show up as a blank value when loaded to Power Pivot.  Maybe that’s fine in this case, but I can certainly see where that might not be desirable.

Personally, I’d prefer to get a prompt when leaving a query if my final step wasn’t defining data types.  Something along the lines of “We noticed your final step doesn’t declare data types.  Would you like me to do this for you now (recommended)” or something similar.  I do see this as an alternate to the up-front data type declaration, but to be honest, I think it would be a more logical place.

October News and Events

It’s a busy month here at Excelguru. Instead of a technical post we wanted to catch everyone up on our October news and events!

Live Course: Master Your Excel Data October News and Events

Ken is teaching a LIVE, hands on course in Victoria, BC on Friday, October 21 from 9:00am-4:30pm. This session is great for anyone who has to import and clean up data in Excel and will change the way you work with data forever! Ken will teach you how to use Excel Tables, Pivot Tables and Power Query. Space is limited to only 20 attendees, so don't miss out on your chance to sign up. For full details and to register for the session, visit: http://www.excelguru.ca/content.php?291-Live-Course-Master-Your-Excel-Data.

October News and Events: Power BI Meet-up

The next Vancouver Power BI User Group meet-up is happening on Thursday, October 13 from 5:30-7:00pm. Scott Stauffer, Microsoft Data Platform MVP, will be presenting on How to Operationalize Power BI. Together we’ll look at some solutions that might help pass your Power BI solution over to IT to manage enterprise-wide. Dinner and soft drinks will be provided. View the full details and sign up to attend at: http://www.meetup.com/Vancouver-Power-BI-User-Group/events/234126999/.

Microsoft MVP Award Received

For the 11th straight year, Ken has received the 2016 Most Valuable Professional Award from Microsoft! The previous 10 years, Ken’s award has been in the Excel category, but this year’s award is in the Data Platform category. The new category reflects the work he’s been doing this past year with Power Query and Power BI. Congratulations Ken, your guru status remains assured.mvp_horizontal_fullcolor

Our Team Has Grown

As we mentioned the other day, Rebekah Sax has recently joined the Excelguru team. She brings with her a wealth of experience in marketing, communications, event planning and administration. Please join us in welcoming Rebekah as she helps us make new connections and continue to grow.