June 2016 Power Query Update

Yesterday, Microsoft released the June 2016 Power Query update.  Even though there are only four items on the list of new features, some of them are quite impactful.

What’s new in the June 2016 Power Query Update

The four new features are:

  • Conditional Columns
  • Column type indicator in Query Editor preview column headers
  • Reorder Queries and Query Groups inside Query Editor via drag and drop gestures
  • Query Management menu in Query Editor

Microsoft has a blog on this here, but let me hit these quickly in reverse order to give my comments as well:

Query Management Menu in Query Editor

Honestly, to me this is kind of a throw away waste “button for the sake of a button” kind of feature.

image

Does it make things more discoverable?  Maybe.  But we can get to all these features by right clicking the query in the Queries pane on the left of the editor.  Personally, I would have rather seen them give me a feature to “pin” the Queries pane open and set that as a default, as I find the navigation from that area much more useful:

image

 

Reorder Queries via Drag and Drop

This is great… so great in fact, that the only real question is why it hasn’t worked in the past.  Time & resources is the answer, but it’s now working the way you’d expect it to work.

image

PS, if you don’t know how to group your queries, right click on one, say “Move to Group” and select New Group.  Pretty handy for keeping things organized.

Column Type Indicator

This is BY FAR the most important of the upgrades.  The reason is that this has been a deadly area of weakness since day one.  If you’ve ever been burned by an “any” data type, you know why.  And if you haven’t… hopefully this will help ensure you don’t.

We can now plainly see which columns have been defined with each data type:

image

Notice how easy it is to tell that the “Client, Task and Notes” fields are text (as shown by the ABC icon in the column header.)  Hours is a decimal number, rate is a whole number, and Date… is undefined.  That one needs attention as indicated by the question mark.  Very visual, and very badly needed for  a long time.  This one feature is, in my opinion, worth the upgrade.

Conditional Columns

This is also a pretty cool feature, as it lets a non-coder build an if then else (if) statement.  Full caveat here: this is the image from the official Microsoft blog, not one of mine, but it shows you the general idea:

June-2016-updates-for-Get-Transform-in-Excel-2016-1.png (1282×809)

As cool as this is, there are some issues here:

  1. You can only feed out full columns as outputs, not formulas/equations.  So if I wanted to check a column and return [Hours]*[Rate] in one case and [Hours]*1.5*[Rate] in others, it won’t work.  (Instead I’ll get text.)  To do that you’ll still need to write your formulas manually.
  2. You can’t provide IFERROR style logic to check if something errors and react accordingly.  To do that you’ll still need to create your own custom column formula using the “try otherwise” formula.
  3. Assume you created a custom column using the “Add Custom Column” button, and manually wrote your “if then else” formula.  You then committed it and want to change the logic, so you click the gear icon in the applied steps window… and you’ll be taken to the Conditional Column interface shown above, not the original window where you can create more complex logic.  So if you want to modify that formula to be more complex than this new interface allows, you’re now going to have to go to the Advanced Editor window.  I have suggested to Microsoft that they need a button to return is to the previous interface for this scenario.

Despite the shortcomings, we should recognize that this is a great new feature.  You can test if one column compares (match, doesn’t match, greater than, etc) another column or specific value without having to manually write any M code formulas.  You also aren’t obligated to feed out a column’s value, but rather can feed out text or values too.  So as long as your logic needs are fairly simple, you can use this feature.

Download the June 2016 Power Query update

You can pick it up from Microsoft’s site here:  https://www.microsoft.com/en-ca/download/details.aspx?id=39379

Also, I’ve started holding on to the previously released installers should you ever need to regress to a prior version.  You can find the installers I have in my forum here:  http://www.excelguru.ca/forums/showthread.php?5745-Installing-Power-Query

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.