Display Last Refreshed Date in Power BI

One of my favourite tricks to use on a dashboard is to show the Last Refreshed Date.  This is important as it lets you know how stale your data is.  I think it’s even more important with Power BI, as it tells you if your automatic refresh is actually working!

Last Refreshed Date for Power Pivot

I’ve actually covered this topic in the past for Power Pivot solutions. You can find that article here if you’re interested.

Display the Last Refreshed Date in Power BI

Generate Last Refreshed Date with Power Query

Unlike the previous article, to work with Power BI, we need to generate the Last Refresh date ourselves using Power Query.  No big deal, it is as simple as this:

  • Open PowerBI Desktop
  • Get Data –-> Blank Query
  • Go to Home –> Advanced Editor and replace the code in the window with this:

let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source

  • Click Done and rename the query “LastRefresh_Local”
  • Click Close & Apply

This code makes a nice little table that returns the current date and time each time we refresh the solution.

Create the Last Refreshed Measure

To finish it off, we need to create a simple measure and add it to our dashboard.

  • Go to your Report window –> New Measure –> Define it as follows:

Last Refreshed (Local) = FORMAT(LASTDATE(LastRefresh_Local[LastRefresh]),"mmm dd, yyyy hh:mm:ss AM/PM")

  • Now let’s create a Card visual to hold it:

image

So far everything looks really good here.  The card is formatted nicely, and I updates to the current date and time every time I hit the refresh button.  It’s a thing of beauty!  What could possibly go wrong with this?

Publishing to the Power BI Service

Encouraged with our visual, we’ll now publish it to Power BI:

  • File –> Publish –> Publish to Power BI
  • Login to the Power BI service and scroll down to the Datasets area

At this point you should click the little … to the right of the dataset you uploaded (whatever you called it) and click Refresh.  It will prepare for a bit, then should refresh.  If your experience is anything like mine, your report shows this:

image

At first, you think “Hey cool!” until you realize that this is the data – to the second – of your original upload.  So even after you refresh the data set, you need to refresh the report:

image

And when you do, you get this:

image

Um… WHAT?  That’s 7 hours different!  How is this possible?

So I’m in the Pacific time zone, which is currently UTC-7.  (We are UTC-8 when we are not on daylight savings time.)  So apparently the date that is showing here is giving me the UTC date.

This really sucks, obviously. I was really hoping that the dates and times would show based on my browser settings or something, but obviously not.

I tried a bunch of things to fix this, including forcing the column to a DateTimeZone data type, but that made the DAX formula choke, as it only accepts DateTime data types.  I tried various combinations using DateTimeZone.UtcNow(), DateTimeZone.LocalNow(), adding periods, subtracting them and more.  The killer though was that as soon as the time zone got removed (which is necessary for the DAX measure to work), Power BI displayed the time assuming it was UTC. So the issue I’m seeing here is that – no matter the DateTime you feed Power BI – it thinks it has the same time zone as the server.  Not so good.

How to Display the (Correct) Last Refreshed Date with Power BI

Just to be clear here, the title above is actually a heinous misrepresentation.  The timestamp was correct, it’s just being interpreted incorrectly by Power BI (but not Power BI desktop).  So I had to fake it out.

I reached out to Chris Webb for his thoughts, and he suggested hitting an API.  I’d already tried looking for a page, but picked up on this API which turned out to be quite useful:  http://www.timeapi.org  Even more interesting is that both www.timeapi.org/pst and www.timeapi.org/pdt return the same thing.  I’m hoping that this means it is going to allow me to survive daylight savings time with no adverse impacts…

So armed with that, I decided that the only way I was going to get this to work was to pull back the UTC offset, and add it to my date.  That effectively returns a bogus date that Power BI will represent in UTC to match my time zone.  Complicated, no?

Collecting the Current PST Date/Time

I don’t think I’m going to clearly document all the steps for this, as that would make this article really long.  Instead, here’s how you can implement this:

  • Create a new Blank query and paste this code in the Advanced Editor

let
Source = Web.Page(Web.Contents("http://www.timeapi.org/pst")),
Data0 = Source{0}[Data],
Children = Data0{0}[Children],
Children1 = Children{1}[Children],
#"Removed Other Columns" = Table.SelectColumns(Children1,{"Text"}),
#"Inserted Last Characters" = Table.AddColumn(#"Removed Other Columns", "Last Characters", each Text.End(Text.From([Text], "en-US"), 6), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Text", type datetimezone}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",":00",".0",Replacer.ReplaceText,{"Last Characters"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",":30",".5",Replacer.ReplaceText,{"Last Characters"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Last Characters", type number}, {"Text", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "LastRefresh", each [Text]+#duration(0,Number.From([Last Characters]),0,0)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"LastRefresh"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"LastRefresh", type datetime}})
in
#"Changed Type"

  • Click Done and rename the query as “LastRefresh_API”
  • Click Close & Apply

The basic gist is that it does the following:

  • Pulls the web page and drills into the single cell we need
  • Splits off the time zone information, replacing the minutes with fractional hours and turns it into a decimal number
  • Extracts the original date/time (without the time zone) and adds the time zone offset to that value.  This essentially subtracts 7 hours from the original date for me right now
  • Performs as bit of cleanup to drill into the faked date/time we need

(If you want to use this for your time zone, I believe all you should need to do is change the “pst” portion of the URL to your time zone name.)

Create a new Last Refreshed Measure

Creating this measure is pretty much the same as the last one.

  • Go to your Report window –> New Measure –> Define it as follows:

Last Refreshed (API) = FORMAT(LASTDATE(LastRefresh_API[LastRefresh]),"mmm dd, yyyy hh:mm:ss AM/PM")

  • And create a Card visual to hold it

You should now have this odd looking contrast:

image

Great, except that… it is 3:22 PM for me, not 8:22 AM.  But fine, whatever, let’s publish this and see what happens.

Publishing to the Power BI Service

To publish the file, we take the following steps again:

  • File –> Publish –> Publish to Power BI
  • Confirm that we want to overwrite the data set
  • Login to the Power BI service and scroll down to the Datasets area
  • Click Refresh Now

At this point you might be told you can’t as you haven’t provided credentials to the data set.  Just Sign In as Anonymous and you’ll be fine:

image

And of course, now we can refresh the Report as well.  And when we do…

SNAGHTMLf308f8e

As you can see, the API version is showing the correct time here, not the Local version.

Takeaways

The biggest takeaway here is that you have to care about how your audience is using your files.  If you are going to deploy your reports by emailing *.pbix files around the company, the setting up a table using the DateTime.LocalNow() will work great.  But if you are going to publish your files to the web… that obviously throws in some wrenches.  Here’s a side by side view of those two alternatives:

SNAGHTMLf368679

Probably the most frustrating thing to me is that I was trying to find a solution that will show it correctly in BOTH Power BI Desktop and Power BI Online.  There’s nothing worse than working with a report you know is broken depending on where you look at it.  🙁

PBIX File

If you want the file so that you can try it out in your own PowerBI service… here you go.  🙂

New Vancouver Power BI User Group

I’ve been thinking about this for a while and, after discussing it with a couple of others who are passionate about Power BI…  I’m pleased to announce that we have created a new Power BI User Group in Vancouver, BC!

What is the Power BI User Group about?

The goals of this user group are fairly simple:

We plan to meet monthly, and have a presentation on using Power BI technologies.  (This could be Power BI Desktop, Excel, Power Query or Power Pivot.)  Whatever it the presentation, and no matter how focussed it is on a specific area, it will ultimately be relevant to the over-arching Power BI path of taking your data from raw form to a published dashboard.  This user group is basically dedicated to bringing you content to inspire you and make you an expert in the Power BI technologies in your company.

Our secondary goal is to be a networking group for Power BI professionals.  If you’ve ever felt like the only one in your company that actually understands what you do… well that’s why we are here.  To give you someone to swap stories with, get ideas and maybe even change your career goals.  🙂

Oh… and did I mention that another goal we have is to keep these events free for attendees?

How can you get involved?

There’s actually a few ways you can get involved with us…

If you’re looking to attend…

Then it’s simple.  Sign up at our Meetup site.  Then attend a meeting. That’s it.  No cost, no fuss.  All we ask is that you register in advance and attend if you say you’re coming.  (We have limited space in our venue right now, only able to seat about 25 people.)

If you’d like to sponsor the event…

We are looking for a sponsor to cover the cost of pizza and soft drinks for our user group attendees.  It shouldn’t be much, and we’d be happy to tell everyone how awesome your company is.  If you’d like to come on board as a sponsor, please get in touch with me via my contact form.

If you’d like to speak…

Got something cool that you’ve built using the Power BI technology stack?  Would you like to talk about how to actually get Power BI traction in a corporate environment?  Got some other relevant topic that you’re passionate about?  Come to an event and chat with us.  One of our key goals is to make sure we have good variety in our speakers!

When is the first meeting?

Great question!  We’re going to be meeting Thursday, July 14 at 5:30pm in downtown Vancouver.  I’ll be presenting on how to build this self updating Power BI dashboard which is originally sourced from PDF files.

I Need Beta Testers

Today’s post isn’t of a technical nature, although it does have a pretty long technical background… I’m now at the point where I need beta testers for a new add-in I’m planning to release.

What Does the Add-in Do?

In short, this add-in is intended to help you do two things:

  1. Inject useful Power Query scripts into your workbook.  I’m working on adding more later, but basically my vision is to provide modellers with Rapid Application Development toolsets in order to take the grunt work out of model setup, allowing you to focus on real work.
  2. Audit Power Query scripts.  If you work with Power Query today and build complex models, you’ll know that there is no easy way to trace your queries through complex models.  I also aim to fix that for you.

And one of the MAJOR design points I follow here is this:  Your end users will NOT need my add-in in order to refresh any solutions built using my tool.  All the scripts are embedded in the workbook and are completely independent of my tool.

Current Features

Here’s what the user interface looks like today:

image

Parameter Table

I use parameter tables in virtually every solution I build (as we demonstrate in Chapter 23 of M is for Data Monkey.)  For that reason, the Add Parameter Table/Query feature is one of the first I hit.  It inserts a new worksheet, adds the required table (including the formula to return the current file path), and also injects the correct queries (set up to avoid the formula firewall.)  From there, your only job is to call the fnGetParameter() where needed in your queries.

Look at all those Calendars!

There are two methods for each of the calendars, but both are predicated off a core concept.  We inject a table into your workbook where you can control the key properties using the Excel formulas you know and love:

image

We also automatically inject the required Power Query scripts, and even link it to a worksheet or into Power Pivot’s data model, if you choose that option.  The “Pre-Defined Columns” version just loads a calendar with the columns we choose, the “Dynamic Columns” also inserts a Excel table which allows you to toggle which columns you want to include (and allows you to change your mind at any time.)

I’ve put a LOT of time into building these up to try and cover the most frequently used columns.  I know there are still some holes (like ISO week numbers and InCurrentPeriod for the 4-4-5 variants), but hopefully you’ll find them useful anyway.  Believe me when I tell you that this will be WAY more efficient than trying to write these yourself.

Copy Queries

This adds a quick interface to copy queries from one workbook to another:

image

 

Show Load Destinations

Ever wonder where your queries end up?  This view helps indentify where the loading end points are for your queries:

image

Trace Precedents/Dependents

This is something that I have found particularly useful so far.  There is still a LOT more I want to do with it, but as it stands today this tool will help you walk through the various queries to see what feeds into a query and where it goes; something that can only be done via reading your M code manually today.  Here’s a view of the dependency tracker, which shows all dependent queries, right through to the data model/worksheet table that it lands in:

image

 

Supported Excel Versions

This tool has been designed to run in both 32 and 64 bit versions of Excel 2016.  I know, you want it for 2010/2013, and so do I.  The challenge is that there aren’t any hooks into Excel that I can use to do this in 2010/2013.  It’s a bummer, but I don’t ever see that changing.  🙁

Anyway… I need beta testers!

I’m looking for a small crew of beta testers for this product.  But there are some requirements:

  1. You MUST have Excel 2016 (I’m actually interested in both subscription and non-subscription users here.)
  2. I need to know your Excel “bitness” (found under File –> Account –> About  Excel

image

In addition, by participating, you are acknowledging/agreeing that:

  1. You understand that this is BETA software and USE IT AT YOUR OWN RISK.  (I.e. run it on a backup copy of your workbook, not your main one!)
  2. You’ll submit any bugs you find to me so I can fix them. 🙂
  3. I have the rights to say yes/no to your inclusion based on my reasons/whims/whatever and I don’t have to disclose what they are.

Understand that my basic goal here is to get a good cross section of systems to test on.

What do you get?

Access to the add-in and any updates I release for at least a year.  I am looking at making a free/pro version, so you’d be entitled to the pro version.

How do you sign up?

UPDATE:  I'm closing this offer at this point, as I believe I've got a good cross section in the comments section.  Watch this blog to see when I release a public version.

Leave a comment below with the following info:

  • Your Excel SKU (found in File –> Account (this could be Office Pro Plus, Microsoft Office 365 ProPlus, or something else.  It’s found right under the big Office logo on that page)
  • Your Office “bitness”
  • Your operating system

Do NOT post your email.  I can access those through the blog control panel, and don’t want you getting spammed.

How do you know if I said yes?

I’ll email you and let you know.  🙂

32 Bit Excel Memory Limit Increase!

So this is just huge, especially if you work with Power Pivot models and are stuck in 32 bit Excel… Microsoft has just released a 32 bit Excel Memory Limit increase for users of Excel 2016, effective build 16.0.6868.2060 (which is the current build for the Insiders program.)

image

UPDATE:  Effective June 7, 2016 (and build 15.0.4833.1000), there is now a patch available for Excel 2013 (both MSI and subscription versions).  More info here:  https://support.microsoft.com/en-us/kb/3115162

Why a 32 Bit Excel Memory Limit increase?

Users stuck on 32 bit Excel were limited to only using 2GB of RAM for their Excel/Power Pivot models, no matter how much memory was available on the PC.  The answer to this in the past was to install the 64 Bit version  of Excel, as that could address up to 8 TB  of memory (if you had it, of course.)

There has been a hack/patch available for a while, (see below,) and I spoke to a user at the PASS BA summit who told me that without that he simply couldn’t use Power Query at all.

How big an increase is it?

Before you start thinking that you’ll now get the same memory access as with 64 bit Excel, let’s disabuse you of that notion.  It’s better, but not parity.  How much you get actually depends on the bitness of your operating system.

  • 32 bit Windows:  up to 3 GB
  • 64 bit Windows:  up to 4 GB

I suspect the first is an operating system limit and that the second is probably more of an internal architecture decision.  The world needs to move to 64 bit, but this will help give companies (even more) time to make that move.

What about non-Power applications?

This change doesn’t just benefit Power Pivot and Power Query; it benefits anyone who has been running into memory constraints.  So if you’ve been running out of memory because you’ve been pushing huge data sets via VBA/SQL, you’ll love this too.

How about Excel 2010/2013?

Yeah, no.  Sorry.  This is part of the benefit of being current… Microsoft is building for the current version of Office.  Excel’s biggest competitor is previous versions of Excel, so by providing a fix like this to a prior versions they’d actually be giving you reasons NOT to upgrade.  You’re in business, and I’m sure you understand that – as much as this sucks for you right now – you’d probably make the same call.

Having said that, if you want to install “the patch” to get your access in previous version, Rob Collie has a link to it in point 3 of this article.

The “Official Word” from Microsoft…

You can find that be reading KB3160741 for more details.

Newly added Power Query specific help forum at Excelguru

I'm honestly not sure what's taken me so long to do this, but I'm pleased to say that I've finally added a Power Query specific help forum at Excelguru.  I'm hoping that this forum becomes THE place to ask and answer Power Query (or Get and Transform) related questions for both Excel and Power BI desktop.  After all, we wrote the book, so it only makes sense that we try and host the Q&A on the topic.  🙂

Extra monitoring of the Power Query Specific help forum

As the forum gets up and running there are a couple of key people I've added for email notification as well.  The intent here is that we get notified when people post questions, and will try to focus on making sure that they get addressed and (hopefully) solved.  If you are a Power Query expert and would like to be included in that list, just email me or post on this thread.  I'll get you set up.  (Make sure you've signed up for an account on the site, as I'll need your user ID to do this.)

Naturally, if you've got a question pertaining to the topic posted on the blog, you can still ask it here.  If the question is a bit more general though, I'd encourage you to sign up at www.excelguru.ca/forums and post the question in the Power Query forum.

Re-focusing on the Power Technologies

While I was setting this up, I also took the time to set up forums specific forums for some of the other "Power" stack:

Hope to see you there!

Live Power Query and Power Pivot Training in Melbourne: Next week!

I know that this comes with limited notice but… as many of you know I'm currently in Sydney, Australia, and I'll be in Melbourne in a couple of days for Excel Summit South.  Well, as it happens, I'm actually staying in Melbourne for another week to deliver some live Power Query and Power Pivot training for a client.

Well guess what… we still have a bit of room, so we are going to open it up to the general public.  If you're interested in a full day of hands on training on either Power Pivot or Power Query, check out what we are doing at Parity Analytic's website, or download the individual brochures here:

(Registration information is included in the links above)

I'm very much looking forward to being able to share with a few more people, and hopefully you can be one!

Excel Summit South

Yes, you read that right.  If you haven't heard yet, I'll be coming to New Zealand and Australia in just under a month!  And the entire purpose of the trip is to come and share Excel knowledge for with my friends and colleagues south of the Equator.

I'm pretty jazzed about this, and not just because I get to go to the southern hemisphere for the first time in my life.  And also not just because I get to talk about Excel when I'm there.  That would be enough, but no… I'm jazzed because I get to do this with some pretty cool friends who are world respected leaders in their area.

Excel Summit South

The main purpose for my trip is the Excel Summit South conference.  Two days, two tracks of advanced Excel material in 3 different cities:

  • Mar 3&4: Auckland, New Zealand
  • Mar 6&7: Syndey, Australia
  • Mar 9&10: Melbourne, Australia

And the best part about this conference is that – while it's sponsorsed by Price Waterhouse Coopers – regisration is open to everyone.  So basically, you can check the schedule, pick the sessions that interest you, and learn things that will impact your Excel skills.  In other words, if Valuation Modelling isn't your thing, then you can go to a Power Query class.  And if Power Query isn't your thing… well… you're kind of odd, but there will be something that is.  Smile

The cast and crew for this conference really can't be beat.  Charles Williams, Bill Jelen, Jon Peltier, Zack Barresse, Liam Bastick are all Excel MVP's on the bill (as well as Ingeborg Hawighorst in our New Zealand apperance.)  Heck, we've even got a couple of guys from Microsoft attending and presenting as well.  This is a fantastic opportunity to not only meet some of the big hitter independant Excel folks out there, but also to talk to Microsoft directly.  How can you pass that up?

My Sessions

If it hasn't shown yet, I'm seriously looking forward to this conference.  Personally I'll be leading two sessions:

An End to Manual Effort: The Power Query Effect

What Power Query is, why you care, and how it can re-shape and transform the data experience.  What's really special about this session is that I'm going to take this data and turn it over to Jon Peltier who is then going to take it and turn it into a dashboard.  This is perfect, as I'm demoing how to automate data cleanup, and Jon will show you how to use it to add true business value… the real life cycle of Excel data in just a couple of hours.

The Impact of Power Pivot

This one will be fascinating, especially for those who have never seen Power PIvot in action before.  In just an hour I'll show you how big business BI (business intelligence) is at the fingertips of anyone with an Excel Pro Plus license.  It's applicable to companies as small as one employee, and scales up to multi employee small businesses, and even large businesses.  (Departments in large corporations eat this up, as they effectively just act as a small business within the larger whole.)

Register for Excel Summit South now!

Tickets are going fast for this event in all cities, so we ecourage you to register sooner rather than later, and hope to see you there!  You can find out more details and register at:  https://excelsummitsouth.wordpress.com/

Creating a custom calendar in Power Query

As it’s the beginning of a new year, I thought it might be interesting to show my spin on creating a custom calendar in Power Query. This topic has been covered by many others, but I’ve never put my own signature on it.

Our goal

If you’re building calendar intelligence in Power Pivot for custom calendars, you pretty much need to use Rob Collie’s GFTIW pattern as shown below:

=CALCULATE([Measure],
ALL(Calendar445),
FILTER(
ALL(Calendar445),
Calendar445[PeriodID]=VALUES(Calendar445[PeriodID])-1
)
)

Note:  The pattern as written above assumes that your calendar table is called “Calendar445”.  If it isn’t, you’ll need to change that part.

This pattern is pretty robust, and, as shown above, will allow you to return the value of the measure for the prior period you provide.  But the big question here is how you create the needed columns to do that.  So this article will focus on building a calendar with the proper ID columns that you can use to create a 445, 454, 455 or 13 month/year calendar.  By doing so, we open up our ability to use Rob Collie’s GFITW pattern for a custom calendar intelligence in Power Pivot.

For more on this pattern see http://ppvt.pro/GFITW

A bit of background

If you’ve never used one of these calendars, the main concept is this:  Comparing this month vs last month doesn’t provide an apples to apples comparison for many businesses.  This is because months don’t have an consistent number of days.  In addition, comparing May 1 to May 1 is only good if your business isn’t influenced by the day of the week.  Picture retail for a second.  Wouldn’t it make more sense to compare Monday to Monday?  Or the first Tuesday of this month vs the first Tuesday of last month?  That’s hard to do with a standard 12 month calendar.

So this is the reason for the custom calendar.  It basically breaks up the year into chunks of weeks, with four usual variants:

  • 445:  These calendars have 4 quarters per year, 3 “months” per quarter, with 4 weeks, 4 weeks and 5 weeks respectively.
  • 454:  Similar to the 445, but works in a 4 week, 5 week, 4 week pattern.
  • 544:  Again, similar to 445, but works in a 5 week, 4 week, 4 week pattern
  • 13 periods: These calendars have 13 “months” per year, each made up of 4 weeks

The one commonality here is that, unlike a standard calendar, the custom calendar will always have 364 days per year (52 weeks x 7 days), meaning that their year end is different every year.

Creating a custom calendar

In order to work with Rob’s pattern, we need 5 columns:

  • A contiguous date column (to link to our Fact table)
  • YearID
  • QuarterID
  • MonthID
  • WeekID
  • DayID

With each of those, we can pretty much move backwards or forwards in time using the GFITW pattern.

Creating a custom calendar – Creating a contiguous date column

To create our contiguous date column, we have a few options.  We could follow the steps in this blog post on creating a dynamic calendar table.  Or we could skip the fnGetParameter function, and just directly query our parameter table.  Whichever method you choose, there is one REALLY important thing you need to do:

Your calendar start date must be the first date of (one of) your fiscal year(s).

It can be this year or last year, but you need to determine that.  I’m going to assume for this year that my year will start on Sunday, Jan 3, 2016, so I’ll set up a basic table in Excel to hold the dates for my calendar:

SNAGHTML25692

Notice the headers are “Parameter” and “Value”, and I also named this table “Parameters” via the Table Tools –> Design tab.  For reference, the Start Date is hard coded to Jan 3, 2016, and the End Date is a formula of B4+364*2 (running the calendar out two years plus a day.)

Now I’m ready to pull this into Power Query and build my contiguous list of dates.

  • Select any cell in the table –> Create a new query –> From Table
  • Remove the Changed Type step (as we don’t really need it)

This should leave you with a single step in your query (Source), and a look at your data table.

  • Click the fx button on the formula bar to add a new custom step

SNAGHTML78078[4]

This will create a new step that points to the previous step, showing =Source in the formula bar.  Let’s drill in to one of the values on the table.  Modify the formula to:

=Source[Value]{0}

image

Reading this, we’ve taken the Source step, drilled into the [Value] column, and extracted the value in position 0.  (Remembering that Power Query starts counting from 0.)

Now this is cool, but I’m going to want to use this in a list, and to get a range of values in a list, I need this as a number.  So let’s modify this again.

=Number.From(Source[Value]{0})

 

 

image

Great stuff, we’ve not got the date serial number for our start date. Let’s just rename this step of the query so we can recognize it.

  • Right click the Custom1 step –> Rename –> StartDate

Now, let’s get the end date.

  • Copy everything in the formula bar
  • Click the fx button to create a new step
  • Select everything in the formula bar –> paste the formula you copied
  • Update the formula as follows:

=Number.From(Source[Value]{1})

That should give you the date serial number for the End Date:

image

Let’s rename this step as well:

  • Right click the Custom1 step –> Rename –> EndDate

We’ve now got what we need to create our calendar:

  • Click the fx button to create a new step
  • Replace the text in the formula bar with this:

={StartDate..EndDate}

If you did this right, you’ve got a nice list of numbers (if you didn’t, check the spelling, as Power Query is case sensitive).  Let’s convert this list into something useable:

  • Go to List Tools –> Transform –> To Table –> OK
  • Right click Column1 –> Rename –> DateKey
  • Right click DateKey –> Change Type –> Date
  • Change the query name to Calendar445
  • Right click the Change Type step –> Rename –> DateKey

The result is a nice contiguous table of dates that runs from the first day of the fiscal year through the last date provided:

SNAGHTML16e36f

Creating a custom calendar – Adding the PeriodID columns

Now that we have a list of dates, we need to add our PeriodID columns which will allow the GFITW to function.

 

 

 

 

Creating a custom calendar – DayID column

This column is very useful when calculating other columns, but can also be used in the GFITW formula to navigate back and forward over days that overlap a year end.  To create it:

  • Go to Add Column –> Index –> From 1
  • Change the formula that shows up in the formula bar to:

=Table.AddIndexColumn(DateKey, "DayID", 1, 1)

  • Right click the Added Index step –> Rename –> DayID

NOTE:  The last two steps are optional.  Instead of changing the formula in the formula bar, you could right click and rename the Index column to DayID.  Personally, I like to have less steps in my window though, and by renaming those steps I can see exactly where each column was created when I’m reviewing it later.

What we have now is a number that starts at 1 and goes up for each row in the table.  If you scroll down the table, you’ll see that this value increases to 729 for the last row of the table.  (Day 1 + 364*2 = Day 729).

Creating a custom calendar – YearID column

Next, let’s create a field that will let us navigate over different years.  To do this, we will write a formula that targets the DayID column:

  • Go to Add Column –> Add Custom Column
    • Name:  YearID
    • Formula:  =Number.RoundDown(([DayID]-1)/364)+1
  • Right click the Added Custom step –> Rename –> YearID

If you scroll down the table, you’ll see that our first year shows a YearID of 1, and when we hit day 365 it changes:

image

The reason this works for us is this:  We can divide the DayID by 364 and round it down.  This gives us 0 for the first year values, hence the +1 at the end.  The challenge, however, is that this only works up to the last day of the year, since dividing 364 by 364 equals 1.  For that reason, we subtract 1 from the DayID column before dividing it by 364. The great thing here is that this is a pattern that we can exploit for some other fields…

Creating a custom calendar – QuarterID column

This formula is very similar to the YearID column:

  • Go to Add Column –> Add Custom Column
    • Name:  QuarterID
    • Formula:  =Number.RoundDown(([DayID]-1)/91)+1
  • Right click the Added Custom step –> Rename –> QuarterID

The result is a column that increased its value every 91 days:

image

It’s also worth noting here that this value does not reset at the year end, but rather keeps incrementing every 90 days.

Creating a custom calendar – MonthID column

The formula for this column is the tricky one, and depends on which version of the calendar you are using.  We’re still going to create a new custom column, and we’ll call it MonthID.  But you’ll need to pick the appropriate formula from this list based on the calendar you’re using:

Calendar Type Formula
445 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=56 then 2
else 3
)
454 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=28 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3
)
544 Number.RoundDown([DayID]/91)*3+
( if Number.Mod([DayID],91)=0 then 0
else if Number.Mod([DayID],91)<=35 then 1
else if Number.Mod([DayID],91)<=63 then 2
else 3
)
13 periods Number.RoundDown(([DayID]-1)/28)+1

 

As I’m building a 445 calendar here, I’m going to go with the 445 pattern, which will correctly calculate an ever increasing month ID based on a pattern of 4 weeks, 4 weeks, then 5 weeks.  (Or 28 days + 28 days + 35 days.)

SNAGHTML5ac6c5

This formula is a bit tricky, and – like the GFITW pattern – you honestly don’t have to understand it to make use of it.  In this case this is especially true, as the formula above never changes.

If you’re interested however, the most important part to understand is what is happening in each of the Number.Mod functions.  That is the section that is influencing how many weeks are in each period.  The key values you see there:

  • 0:  Means that you hit the last day of the quarter
  • 28:  This is 4 weeks x 7 days
  • 35:  This is 5 weeks x 7 days
  • 56:  This is 8 weeks x 7 days
  • 63:  This is 9 weeks x 7 days

The Number.RoundDown portion divides the number of days in the DayID column by 91, then rounds down.  That will return results of 0 through 3 for any given value.  We then multiply that number by 3 in order to return values of 0, 3, 6, 9 (which turns out to be the month of the end of the prior quarter.)

The final piece of this equation is to add the appropriate value to the previous step in order to get it in the right quarter.  For this we look at the Mod (remainder) of days after removing all multiples of 91.  In the case of the 445, if the value is <= 28 that means we’re in the first 4 weeks, so we add one.  If it’s >28 but <=56, that means it’s in the second 4 weeks, so we add two.  We can assume that anything else should add 3… except if there was no remainder.  In that case we don’t add anything as it’s already correct.

Creating a custom calendar – WeekID column

WeekID is fortunately much easier, returning to the same pattern we used for the YearID column:

  • Go to Add Column –> Add Custom Column
    • Name:  WeekID
    • Formula:  =Number.RoundDown(([DayID]-1)/7)+1
  • Right click the Added Custom step –> Rename –> WeekID

The result is a column that increases its value every 7 days:

 

SNAGHTML4fc488

Finalizing the custom calendar

The last thing we should do before we load our calendar is define our data types.  Even though they all look like numbers here, the reality is that many are actually defined as the “any” data type.  This is frustrating, as you’d think a Number.Mod function would return a number and not need subsequent conversion.

  • Right click the DateKey column –> Change Type –> Date
  • Right click each ID column –> Change Type –> Decimal Number
  • Go to Home –> Close & Load To…
    • Choose Only Create Connection
    • Check Add to Data Model
    • Click OK

And after a quick sort in the data model, you can see that the numbers have continued to grow right through the last date:

image

Final Thoughts

We now have everything we need in order to use the GFITW pattern and get custom calendar intelligence from Power Pivot.  Simply update the PeriodID with the period you wish to use.  For example, if we had a Sales$ measure defined, we can get last month’s sales using the following:

=CALCULATE([Sales$],
ALL(Calendar445),
FILTER(
ALL(Calendar445),
Calendar445[MonthID]=VALUES(Calendar445[MonthID])-1
)
)

As an added bonus, as we’re using Power Query, the calendar will update every time we refresh the data in the workbook.  We never really have to worry about updating it, as we can use a dynamic formula to drive the start and end dates of the calendar.

As you can see from reading the post, the tricky part is really about grabbing the right formula for the MonthID.  The rest are simple and consistent, it’s just that one that gets a bit wonky, as the number of weeks can change.  (To be fair, this would be a problem for the quarter in a 13 period calendar as well… one of those quarter will need 4 weeks where the rest will need 3.)

One thing we don’t have here is any fields to use as Dimensions (Row or Column labels, Filters, or for Slicers.)  The reason I elected not to include those here is that the post is already very long, and they’re not necessary to the mechanics of the GFITW formula.

 

 

If you’d like a copy of the completed calendar, you can download it here.  Be warned though, that I created this in Excel 2016.  It should work nicely with Excel 2013 and higher, but you may have to rebuilt it in a new workbook if you’re using Excel 2010 due to the version difference on the Power Pivot Data Model.

Suggestion to Improve the Pivot Table Experience

This is a special post to to discuss a suggestion to improve the Pivot Table experience, especially for Power Pivot users.

This week I’m at the 2015 MVP Summit in Redmond, WA.  It’s a trip I’m lucky enough to make every year, and certainly one of the annual events that I look forward to the most.  It’s a chance to reunite with my friends in the global community of Excel experts, as  well as make some new friends there too.  In addition, we get the opportunity to meet with the Microsoft Excel engineers, give our feedback, and talk about the things that are/aren’t working in the program.

Of course, this doesn’t mean that they can or will implement the suggestions we have.  Excel is a massive program, and every feature change can cause bigger issues elsewhere.  But they do listen, and they do want this product to be the best it can be.  Like every company, they have to work out what they can afford to do, and where the best investments are for their limit of resources.

In the spirit of the summit, I thought I’d share one of the ideas I have that I think would be really beneficial to Power Pivot users.  Maybe it makes the radar, maybe it doesn’t, but I think it would be a really useful change.  I’m fairly certain it could also be implemented without causing any issues with other features in the product as well.

The Issue

For those working with Power Pivot, you know the power of DAX.  This leads to creating many different DAX measures, each of which are landed in the columns of the Pivot Table.  This is awesome, but it brings up a challenge with the usability of the Pivot Table field list:

image

Back when we just dropped singular fields into the Values area, things weren’t so bad.  I generally only ran with a few fields, and I didn’t feel super constrained by the size of the window.  Yes, I overran the limit on occasion, but it wasn’t a big deal.

With Power Pivot, things have changed.  I have so much more flexibility to write the DAX measures I need, which leads to many more columns being defined.  If you think about things like forecasting an annual cash flow statement, I’ll write at least 13 different measures (one for each month), plus a total.  And that’s just one scenario.  For a regular financial statement the same thing… Actual, Budget, Variances, Year to date Actuals, Year to date Budgets, and so on.  Again, it’s not uncommon to see a statement with over 12 columns.

This proliferation of measures leads us to the issue… the Values are of the Pivot Table field list is too small today.  It only holds 3-4 visible columns at a time.  Trying to move a measure into the right place is a real pain, especially if you add a new measure to the bottom, and you have to drag it up.  I’m sure you’ve had massive “overscroll” problems where the thing seems to speed up to mach 5 JUST as you are trying to move it up that one last row…

The Slightly Better View

The Pivot Table field list has an alternate view called “Field Section and Areas Section Side-By-Side”.

SNAGHTMLec87838

 

This is a bit better, as we can at least see more fields in the area on the left.  But that’s only helpful for scrolling and finding the fields we need, not placing them on the Pivot:

image

You see?  I’ve still only got three rows showing (four when my Excel is maximized on screen.)

But here’s the thing…

When I’m building my Pivot, I rarely end up putting anything in the Filters area, as I tend to use Slicers.  I might have a few fields in there that I don’t want users messing with (I hide the top rows of the Pivot Table), but generally I’m looking at between zero and two fields in there.

And when I build my Rows and Columns, I tend to drag them on the Pivot and call it a day.  I could use more space on occasion when I’m layering on my Row fields, but Columns are usually sufficient.  Especially now that I’m writing DAX formulas.  The measure gets dragged in to the Values area, and doesn’t need anything in the Columns area at all.  It’s partly for this reason that the small size of the Values area is killing me.  The old logic for how the Pivot was build has essentially changed, with the description moving from the Columns area to the Values area.

What that means is that I’ve got a ton of wasted whitespace in my Filters and Columns area.  So why not reclaim that whitespace?

Suggestion to Improve the Pivot Table Experience

So here’s my suggestion to improve the Pivot Table experience: modify the “Field Section and Areas Section Side-By-Side” view as follows (excuse the rough mockup…)

SNAGHTMLed7c5f1

The key changes here are really about the arrows to the right of the Filters, Rows, Columns and Values areas.  These are the same arrows as used in the Field List on the left, where the white arrow pointing to the right shows the area collapsed, and the black arrow shows the area expanded.

To be clear, the proportions aren’t correct here, but my thought is that the expanded areas consume an equal share of the remaining whitespace.  So if all four areas are expanded, they each get a 25% share of the remaining space, as it what we see in the current implementation.

But collapse one field (let’s say Filters), and each remaining area expands, as it now gets a 33% share of the remaining space.  Collapse two (as I’ve shown above), and the remaining two get 50% each.  Collapse three, and all remaining whitespace goes to the final area:

image

This would be fantastic, as it would let me build my Pivot much more easily.  I’d be able to see what I’m working with, especially on Pivot Tables with higher levels of Row or Values fields.

I didn’t scope this in, but it would also probably be a good idea to append a number in parenthesis to each area as well, indicating how many fields exist in each area.  So in this case: image

Naturally, when you’re first building a Pivot, it should open with all areas expanded to 25% of the share… but bonus points if there is a way to save the default view for a configured Pivot.  The reason that I say this is that my guess is that 75% of the time when I’m modifying a Pivot it’s the Values area I’m doing, 20% is Rows, 4% is Columns and the remaining 1% of the time I’m modifying Filters. Respecting that others have different uses though, the ability to choose which fields are expanded/collapsed by default on an already existing pivot would be incredible.

At any rate, that’s my idea.  Here’s hoping a program manager on the Excel team thinks there’s merit to it and starts to look at the feasibility.  Feel free to share your thoughts on the subject below.  🙂

If you like this idea...

Please throw it some votes at Excel UserVoice.  The more votes it gets there, the more likely it will be implemented!

Power Query Errors: Please Rebuild This Data Combination

I got sent this today from a friend.  He was a bit frustrated, as he got a message from Power Query that read “Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.”

What Does This Mean?

This message is a bit confusing at first.  For anyone who has worked with Power Query for a bit, you know that it’s perfectly acceptable to merge two queries together.  So why when you do some merges does Power Query throw this error?

image

The answer is that you cannot combine an external data source with another query.

Looking At Some Code

Let’s have a quick look at the code that my friend sent:

image

Notice the issue here?  The Merge step near the end references a query called DimShipper.  No issue there.  But it tries to merge that to an external data source which is called in the first line.

This is actually a bit irritating.  But let’s break this down a bit further.  Looking at the first line, it is made up as follows:

Source=Excel.Workbook(File.Contents(Filename())),

The Filename() portion is calling a function to return the desired filename from a workbook table, (based somewhat on this approach.)  We already know that works, but we also know that this is definitely pulling data from an external workbook (even it the file path is for this current workbook!)  And to be fair, this would be the same if we were pulling from a web page, database or whatever.  As long as it’s an external source being combined with another query, we’re going to get smacked.

So it’s suddenly starting to become a bit clearer what Power Query is complaining about (even if we think it’s frustrating that it does complain about it!)

Let’s “rebuild this data combination”

Right.  Who cares why, we care how to deal with this.  No problem.  It’s actually fairly straightforward.  Here’s how I dealt with it:

  • Right click the Query in Excel’s Query window
  • Choose Edit

I’m now in the Power Query window.  On the left side, I found the Queries pane and clicked the arrow to expand it:

image

Duplicate the Existing Query

The query in question here is “Purchase”…

  • Right click “Purchase” and choose Duplicate
  • Immediately rename the query to PurchaseList
  • Go to View –> Advanced Editor
  • Selected everything from the comma on the second line down to the last row of the query:

image

  • Press Delete
  • Change the final line of “Merge” to “Purchase_Sheet”

Perfect… so my new PurchaseList query looks like this:

image

  • Click Done

This query will now load.  Even though it is pointing to an external data source, it’s not being combined with anything else.  So it’s essentially just a data stage.

Modify the Existing Query

Now we need to go back and modify our existing query.  So in that left pane we’ll again select the Purchase query.

  • Go to View –> Advanced Editor
  • Select the first two lines and delete them
  • Put a new line after the let statement that reads as follows

Source = PurchaseList,

NOTE:  Don’t forget that comma!

And what we end up with is as follows:

image

So What’s The Difference?

All we’ve really done is strip the “external” data source and put it in it’s own query.  Yet that is enough for Power Query to be happy.  The new Purchase query above now has two references that it is comfortable with, and it works.  (And that’s probably the main thing.)

Designing To Avoid This Issue

I make it a practice to land all of my data sources into specific “Staging Tables”, which are set to load as connections only.  From there I build my “finalization” tables before pushing them into my Data Model (or worksheet).  You can visualize it like this:

SNAGHTML409dfd44

The key takeaways here are:

  • I always go from data source into a Staging Query (and do a bit of reshaping here)
  • My staging queries are always set to load to “Connection Only” so that the aren’t executed until called upon
  • All combining of data from disparate sources is done in queries that reference other queries, not external data sources

I’ve found this approach works quite well, and always avoids the “rebuild this data combination” error.