Sharing Power BI Reports (for External Users)

This is the fourth installment of this post series, and will demonstrate the process for publishing and sharing Power BI Reports.  Key things we’ll look at here are how to publish the dashboard, keep it up to date, and share it with users outside our company.  (We will look at sharing with internal users the next post.)

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Subscription Levels

In order to make this whole solution work, I’m assuming the following:

  • The person publishing the dashboard has a Power BI Pro subscription.  This is needed to enable the feature to schedule refresh.
  • All other internal users have a Power BI Free subscription… because I’m cheap like that. Smile
  • External users may or may not have a Power BI subscription at all.

So basically, to sum this up really quick, if you only have one person authoring and publishing your dashboards in your company, you only need one Power BI Pro license, and the rest of your team can be on the free version.

The good news is that it’s totally free to sign up for Power BI, you just need a “work” email.  (I.e. it won’t let you sign up with your hotmail, gmail, or yahoo address, but if it’s some kind of custom domain, you should be fine.)  In fact, you can most likely even sign up even if your IT department hasn’t yet.  If you want to try it, head over to the Power BI site.

Publishing the Report to Power BI

The first thing we need before we can start sharing our reports is publish them.  Doing that is simple once we have our account set up.  We start by going to Power BI Desktop and opening our Power BI Desktop file.

Now, if you haven’t signed in to Power BI, you’ll want to do that first:

  • Go to File –> Sign In
  • Enter the email and credentials to your Power BI account and commit them

Once you have successfully logged in, that will make things easier, as it won’t prompt you when you’re publishing.  So assuming you’ve done this, you can now:

  • Go to File –> Publish –> Publish to Power BI.

Because my company uses Office 365 Unified Groups, I actually get a long list of locations where I can publish my report, but I’m going to choose the very top one call “My Workspace”.  (You may not have or get any groups, but everyone has a the My Workspace area.)

A few seconds later I’m given a happy little message about how it was successful:

image

And that’s it for Power BI Desktop.  You can click the link or dismiss the message and log in at http://powerbi.microsoft.com.  No matter which, you can shut down Power BI Desktop and move to your web browser.

Exploring the Power BI Interface

On the left, you’ll see that you’ve got a few areas that you can navigate into, as shown below (yours may be expanded, I collapsed them for easier reading):

image

The important pieces for us here are the Reports and Datasets areas of the navigation pane.  The data for our file is separated from our report, so we need to talk to both portions.  But first, I’m just going to take a look at the report in the Power BI web interface.  To do that II :

  • Selected reports on the left (only required if it is collapsed)
  • Selected the Hotel Stays report

And, as you can see, I’ve got a nice report that looks just like it did in Power BI desktop.  It even cross filters the same:

image

So this is kind of cool.  It’s no longer on my PC, it’s stored in the cloud, as is the data set.  So in theory, I could update my Excel file with a new hotel, and it should refresh since that file is stored in my OneDrive for Business, right?

Sorry, but not yet.  See the Refresh button the red arrow is pointing at?  That will refresh the report from the data set (although I believe you need to refresh the data set first).  So manual, so let’s fix that first.

Keeping the Reports up to date with Scheduled Refresh

Again, in order to set up scheduled refresh of our reports, we must have a Power BI Pro subscription.  If you don’t… well… you can still use the solution, but you’ll need to come in and manually click the button to refresh it (and seriously, who wants to do that?)  I’m going to assume you do have a Power BI pro subscription, and we’ll carry on here.

So to set this up, I clicked on the Datasets button in the navigation window on the left, then selected the Hotel Stays data set.  That took me to a blank page that has a very Power BI Desktop like experience, including a Refresh button in the top right:

image

This area can be used to manually force the data set to update, and can even be used to build additional reports.  But neither is what we want.  What we need is a way to schedule the refresh, which is found by clicking the … icon beside the data set name:

image

Clicking Shedule Refresh takes me to a page and – after about a 1 second delay – gives me some other important info:

image

Okay, so I need to confirm my password.  I’m not going to cover this in much detail, as Chris Webb recently covered this part in detail.  I will point out that OneDrive for Business is, in fact, SharePoint (just in case you’re wondering why we see a reference to SharePoint there.)  So all we need to do is click the Edit Credentials link and re-login to the Office 365 account.  Just make sure you change it from Anonymous to oAuth2 before trying to sign in.

image

Once done, you can expand the Schedule Refresh area, flip the flag to Yes, and you should be good to go:

image

A few of key points to be aware of here:

  1. Sometimes Power BI seems to forget that it has been authorized.  You may need to come back in and re-login.  That’s only happened to me once so far, but I’ve seen people mention that it happens to them more frequently.
  2. The above is why that last checkbox is SUPER IMPORTANT.  Microsoft will email you whenever a scheduled refresh fails, so that you are aware and can fix it.  This is huge, and I’d recommend you leave that box checked.
  3. You can add more frequent refreshes although – in my experience – you’re limited to about 10 times per day.  I really don’t need more than daily here, but for other dashboards I’ve set them to refresh every 2 hours during business hours.

At any rate, with this set, we click Apply and forget about manual until Microsoft emails us to let us know something stopped working.

Sharing Power BI Reports Externally

Now, the data is being kept up to date, and now we want to share the report with others externally.  How?

  • Click Reports and drill into the report you want (Hotel Stays for me)
  • On the report, go to File –> Publish to Web

SNAGHTML12de556

  • Click Create Embed Code then Publish

This will give you a nice dialog that provides two things:

image

Here’s a direct link to the email-able URL, which you can see renders nicely in the online app.

And here is what happens when you embed the iframe in your site.  (Yes, it’s fully clickable!)

So that’s pretty cool and pretty easy.  The only caveat (which works fine for me for my purposes) is that this is 100% public.  While the end audience can’t see any of the underlying data, anyone on the internet who gets this url can see and play with the dashboard.

Final Thoughts

So this is a pretty cool thing so far.  We’ve got a solution where the data set can be access from OneDrive for Business via the Excel app on my phone.  I can add new records there and they are saved, then automatically refreshed in the Power BI report that lives in my Office 365 tenant.  And better yet, I have been able to publish a fully interactive report to the web that will update over time and that you can click and drill into.  Neat stuff for a pretty low cost.

But I know what you’re thinking… you’re thinking that this is all great, but what if we want to share it with users INSIDE the company, and we don’t want to just publish it as a public facing page.  Well hang tight, because that is the focus of the next post in the series.

Creating Power BI Card Visuals

In this post I’m going to continue the process of creating a Power BI dashboard built off the data I sourced from OneDrive for Business.  This time I’m going to look specifically at creating Power BI Card Visuals.

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Today’s Goals – Adding Power BI Card Visuals and another Chart

Let’s just circle back on the wireframe I presented in my last post:

image

I’ve already made the Map and the Stays by Country chart.  Now I need to add my Key Stats and the Stays by Hotel Brand chart.

Showing Key Stats with Power BI Card Visuals

Ultimately, what I want to show on my dashboard is some key indicators that filter as I select other visuals on the dashboard.  Something that looks like this, which is reacting to my filtering to only stays in Canada:

image

Creating DAX Measures for my Power BI Card Visuals

So the first thing I want to do is think about the data that I want to display.  I’m tracking nights away from home, which I call (Hotel) “Stays”.  There’s 3 main measures that I’m after:

  • Stays (Selected): This is base measure which was created in the last post in the series.
  • Stays (All): This value should always show the total nights I’ve been away from home.
  • Stays (YTD): This value should show the total nights I’ve been away from home for the current year only.

As a reminder, the Stays (Selected) measure was defined as:

Stays (Selected) = COUNTROWS(Stays)

Creating the next two measures is just as easy as the previous one.  To do this I selected the Modeling tab –> New Measure, and created the following measure formulas:

Stays (All) = CALCULATE(Stays[Stays (Selected)],ALL(Stays))

Stays (YTD) = CALCULATE(Stays[Stays (All)],Year(Stays[Date])=Year(TODAY()))

Each was set with a whole number format with commas and zero decimal places:

SNAGHTML978ef3

Now, to be fair, you don’t have to set up all your DAX measures up front.  You can (and I frequently do) create DAX measures as you need them.  I had already visualized what I wanted, so knew I’d need these ones.

Multi-Row Power BI Card Visuals or Single Cards?

So now that I have my DAX measures built, I’m want to get these into the cards.  My first instinct was to grab a multi-row Power BI card visual and just check all 3 measures.  The problem is that it gives me this:

image

Now, I can get rid of the bar on the left (Formatting –> Card –> Show Bar = off), and I can change the size of the values (Formatting –> Data Labels –> Text Size).  What I can’t do, however, is centre the values over the text like I can with individual cards.  (At least not as of the latest build of Power BI Desktop.)  It’s always the little things that get me, but it looks like we’re going to need to go to individual cards for what I want.

Creating my Power BI Card Visuals

Creating 3 visuals instead of one is a little bit painful, but certainly do-able.  Here’s how I did it.

  • I went to the Visualizations gallery –> Card
  • Then went to Fields –> Stays (Selected)

image

Which gave me this:

image

Perfect.  So then I just created the other two.  The hardest part, honestly, was lining them up.  Rather than dragging and drop I set the properties manually, as I could then ensure that my card heights and widths and top position were identical.  I then only hard to play around with the X position for each card.  (You can find this menu by selecting the card, then clicking the format icon (under the visualizations gallery).  Once you’ve done this, expand the General section.

image

One thing that does bug me about this is that as soon as you select the next visual, the General section will collapse on you again.  I really wish it stayed expanded, as it would make it much easier to quickly compare values.

Regardless, I ended up with the visuals I wanted and they react properly when I click Canada in my Country column chart:

image

Or Australia:

image

Creating the Stays by Hotel Brand chart

With the key stats done, we can now focus on creating a Power BI Bar Chart to show the Stays by Hotel Brand.  There’s no major secret to this one, in fact it’s even easier than the column chart I created in the last post, only because it doesn’t have any drill down levels.  Here’s what I did to create this one.

  • I started by clicking on the blank canvas (to clear selections so I don’t accidentally turn a card into a chart!)
  • I went to the Visualizations gallery and selected the top left icon for the Bar Chart
  • I checked Hotel Brand – automatically adding it to the Axis
  • I checked Stays (Selected) – automatically adding it to the Values area

SNAGHTMLa3291a

That seemed pretty easy so far, but it could use some tweaking.  So I clicked the formatting roller and made a few more adjustments to it:

  • X-Axis = Off
  • Data Labels = On
  • Title
    • Title Text = Nights Away From Home by Hotel Brand
    • Font color = Black
    • Alignment = Centre
    • Text Size = 12 pt

And then I resized the chart to show the full width of the hotel brand names.

image

There are a couple of options I’d love to see here that don’t exist today, mainly revolving around the Y axis.

  1. While I have the option to show the axis on the right side, I don’t have any ability to align the text to the left.  Personally I can’t stand the fact that the text is not aligned with every word started at the same horizontal position.  It’s just weird.
  2. In order to show all the text, I have to expand the entire chart, making it super wide.  Why?  Because I don’t have the ability to set the axis width independently, nor do I have the ability to control the font size for the axis.

At any rate, it looks pretty good for my purposes right now, and I’m sure the font size and alignment (if not the axis column width) will be added in future.

Most importantly, we can see that by filtering my Nights Away from Home chart, it still cross filters this chart.  The view below shows just how brand loyal I’m not when filtered to Canada:

image

Final Thoughts

With a couple of additional text boxes added to the page, my dashboard development is complete, returning something that I can easily cross filter and explore:

image

Note that I started this post a while ago, so the data has a few more nights in this version of the screenshot.

In the next post I’ll look at publishing this to Power BI, creating an interactive view for the public, and schedule the refresh to keep it up to date.

Creating Power BI Visuals

Last week I grabbed some data from OneDrive for Business.  This week I’m going to start the process of creating Power BI visuals to build a dashboard which will be hosted in the cloud.

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

  • Get Data From OneDrive for Business
  • Creating Power BI Visuals (this post)
  • Creating Power BI Card Visuals (forthcoming)
  • Sharing Power BI Reports (forthcoming)
  • Sharign Power BI Dashboards (forthcoming)

Dashboard Wireframe

My friend Jon Peltier exposed me to the concept of creating a wireframe for a dashboard, which is essentially a quick design layout.  (It’s more complicated than that, but for my purpose I know my key stats, so am looking at the layout now.)  So here’s the draft of what and how I want to place my visuals:

image

Now, I’m not going to build all of this in this one post.  (I’m only going to build the left side in this post.)

A little DAX – Creating a measure for Selected Stays

Before we get too deep into this, we need to quickly talk about about the key metric is that we want to display in the dashboard visuals.  All maps and charts show key values of some kind, in my case I’d like that to be the number of nights that I’ve been away from my home.  As I mainly stay in hotels when I’m away, I’m going to call the measure “Stays (Selected)”.  (The reason for the “Selected” component will become clear in the next post.)

To create this measure, I needed to go to the Modeling tab and choose New Measure.  In Power BI Desktop, you provide both the measure name in the formula bar, as well as the actual measure itself.  So here’s what I went with:

Stays (Selected) = COUNTROWS(Stays)

The measure name comes first, followed by the = character.  (In Power Pivot for Excel, we’d see := instead.) The measure itself uses the same DAX formula as we would in Excel, which is a simple count of all unfiltered rows remaining in the table.  This is good, as it will allow my reports to cross filter based on selections made in other visuals.

The final thing I did after creating the measure was to tweak the formatting a little bit.  I set it to Whole Number, ensuring that it had 0 decimal places and used a comma format (for when my hotel nights creep over 1,000!)

image

Power BI Visuals – Map

With the DAX measure built, I’m ready to finally start using my data.  And I really want to start with something visual, so I’m going to start with the map.

There are two Power BI visuals for maps in Power BI Desktop; the map and the filled map.  I’m going to use the first one.  So here’s exactly what I did:

  • I clicked the Power BI Visual for Map
  • I selected the Full Address field
  • I selected the Stays (Selected) field
  • I wondered why nothing showed up on my map

Oh yes, this was a serious WTF moment, honestly.  Here’s what I saw (with arrows added to prove out the mapping):

SNAGHTML940271

I recreated this map twice and added other visuals before I figured out what was going on here… as it happens, my data was actually too wide for the default map size.  Look what happens when I widen the map:

image

So Power BI team, if you’re reading this, the default size of the map is just plain wrong.  Give us one that shows the entire world!

At any rate, now that this I know my data is actually there, I can clean up the chart by giving it a better title.  To do that I:

  • Selected the map visual
  • Clicked the format icon under the Visualizations gallery (now looks like a little paint roller)
  • Expanded the Title field
  • Change the Title Text to:  Nights Away From Home by Location
  • Changed the Font color to black
  • Changed the Alignment to Center
  • Set the Text Size to 12

And the result is as follows:

image

Power BI Visuals – A Basic Column Chart

With the map done, it’s now time to move on to the next Power BI Visual: a column chart.

To create this, I did the following:

  • Clicked the blank canvas outside my map.  (I can’t tell you how many times I’ve pressed CTRL+Z to undo changing a visual from one type to another!)
  • Clicked the Column Chart (second icon in the Visualizations gallery)
  • Selected Country from the fields list
  • Selected Stays (Selected) from the fields list

Not only do I get my cool chart, but clicking on one the countries drills in to all the stays I’ve had, and zooms in the map as well.  No extra connections or configuration needed.  In this image I’ve drilled in to USA, and you can see that I worked my way down the Oregon coast this summer, as well as the stays I had in the Seattle and San Jose areas for conferences:

image

Using the chart is pretty easy at this stage: just click a bar.  Click a different bar and it cross filters to those selections, click the same bar again and it un-filters.  Hold down your CTRL key as you’re making selections and you can even get multiple countries like this view of Canada and USA:

image

As cool as that is, I’d still like to customize the chart a little.  To do so, I selected the chart and clicked the formatting icon below the visualization gallery again.  The changes I made were:

  • Y-Axis
    • Off
  • Data Labels
    • On
    • All other options left as defaults
  • Title
    • Title Text: Nights Away From Home by Region (Drillable)
    • Font Color: Black
    • Alignment: Centre
    • Text Size: 12

So when all is complete, it looks like this:

image

To me that is much cleaner.

(Note that according to my wireframe, the column chart will live under the map.  I’m only leaving it next to the map for now to make better use of space in the blog post.)

Power BI Visuals – A Drillable Column Chart

So the column chart is cool and all, but I wanted to be able to use it to drill down into a finer level of detail, like Province/State.  Unfortunately just typing that in the title won’t do it, we need to make a change.

So far everything I’ve built has just worked nicely.  I’ve been able to single click everything, and it’s just gone right into the correct place.  But when I click the Province field in the Fields pane, I get this disaster:

image

This plainly is not what I want!

The issue here is that Power BI Desktop makes the assumption that the Province field should move to the Legend.  We need to move it to slide into the Axis, just below country.  As you drag it, you’ll see a solid yellow line snap in just under the Country field:

image

And when you let it go, your chart looks like it’s reverted to what you had before.. but there is a difference.  Near the top of the chart, we see a few new icons:

image

And here’s what they do:

  • The first (currently shaded) icon is “drill out” to go back up one level.  (It’s shaded, as I’m at the top level)
  • The second (with the double down arrows) drills in to the maximum level.  This would show nonsense data for me, as it shows every Province/State in all countries, without their parent country.
  • The third icon (single down arrow) is a toggle to turn on/off drill down mode.  It’s a bit awkward, as you’ll see, but is very effective for my purposes.
  • The funky little square near the end just sends your chart into full screen mode.

So let’s take a look at my USA stays.

We’ve already seen above what happens if I click the bar for USA; it filters both the map and the chart.  So let’s drill in.  I click the single down arrow on the right, then click USA.  My data re-plots as shown below, and the single down arrow turns black to show it is in drill down mode:

image

So this tells me that so far I’ve spent the same amount of nights in California, Oregon and Washington.  (That was actually a surprise, but fair enough.)  What’s odd to me here though, is that it removes all filters from the map.  I personally don’t think that this should be the case if I’ve selected something then click the drill in arrow.

So now I want to drill in to just Oregon.  So I click the OR bar, and nothing happens.  And this is exactly what I meant when I said it’s a bit awkward.  If you had more levels below this (maybe a city field), you’d drill in there.  If you want to filter the chart to just Oregon, you need to click the down arrow again to clear drill down mode, so that you can go back into filter mode.

So after clicking the black arrow, we can then select the Oregon bar:

image

Or Washington:

image

To get back to BC, I need to drill up to get back to my Country selection.  I can do that by clicking the up arrow in the top left.  That will take me back to the Country choice.

Now, based on what I’ve said earlier, you’d expect that you would need to do the following to drill into BC:

  • Click the Drill Down arrow
  • Click on Canada
  • Click the Drill Down arrow again to clear it
  • Click BC

And that’s EXACTLY what you’d have to do if you were using a touch screen to consume your dashboard.  For PC users (which you probably are as the author), you also have this option.

From the Country view

  • Leave the Drill Down arrow un-selected
  • Right click the Canada bar –> Drill Down
  • Left click BC

And it works the same as the touch version with 2 less clicks:

image

For the record, Drill Up works the same way when you are in a drill down level, as you can see here:

image

Final Thoughts

At this point, I’m going to slide the column chart down under my map, as per my original wire frame.

I should probably also mention here that you’re not stuck to only one level of drill down.  If your data is properly hierarchical and has multiple levels, add them to the axis.  It can make for a pretty powerful experience when a user is able to keep drilling in.

My only complaint with this feature is really around the un-filtering of other visuals in the report when I drill in.  Personally, I think the other visuals should be filtered to that level.  (I.e. if I drill in to USA, I shouldn’t be seeing Australian cities in my map until I click a State name.)

Next post will focus on building the cards, as well as the final chart.

Get Data From OneDrive for Business

It’s been a few weeks since I’ve posted a technical blog post, and I need to get back to it, so here goes.  This is going to be a two part post on how to Get Data From OneDrive for Business, (using Power Query,) which will allow us to create a dashboard that reads from an Excel file hosted in the cloud.  In addition, as a bit of a departure for me, I’m going to demo this using Power BI Desktop, although connecting to the data this way will work with Excel as well (creating the visuals would of course be different.)

In this installment I’ll show how to get data from OneDrive for Business.  Next week I’ll show how I created some visuals, published it to a public facing web page, and how I set up the scheduled refresh to keep it up to date.

Architecture Background

Before we get into how to connect to the data, let’s just talk about where the data lives and why I decided to architect the solution using the route I did.

Folder Setup

Some time in the past, I went and connected to my OneDrive for Business folder, synching it to my laptop.  This allows me to create Excel files locally to store my data, but have them available in the cloud as well, and even access and update them via Excel on my iPhone.

image

Obviously I don’t have a ton of folders in my OneDrive for Business folder, but the key one I’m after is a file called Hotel Stays.xlsx which resides in the Hotel Stays folder.  This is the file that I use to track the number of nights I’m not sleeping in my own bed.  As I travel a lot now, I figured that this might be interesting to track.

Excel File Setup

The structure of the Excel file is fairly simple.  It has a single table in it (called “Stays”), and I update it with a record for each day that I’m away from home:

SNAGHTML637bfd2d

I think this is pretty straight forward, the only nuances here are:

  • Every hotel night is logged as a new line
  • There are independent columns for the street address, city, province and country

So the cool thing here is that I can log updates from my PC or via my phone to keep this up to date.  As I don’t capture a ton of info it’s actually pretty quick to do.

Other Architecture Decisions

Now I could have just connected to the Excel file locally, but I really want to host this as a report and schedule refreshes in the Power BI service.  To do that, I can’t really read from the local copy of the file, as that would require me to configure a personal data gateway and also to ensure that my PC is always on.  As I travel with my laptop I can’t guarantee that so – to allow scheduled refresh without errors – the data needs to read from the cloud hosted version.

So basically, what I want to build is an architecture that looks like this:

image

Get Data From OneDrive for Business

So let’s get started on this, and see where it takes us...

What Connection Do We Use?

As it turns out, in order to connect to an Excel file that is stored in a OneDrive for Business folder, we actually need to connect to a SharePoint folder and drill into the Excel file we want.  It’s kind of like using the classic From File –> From Folder method, only targeted at a web hosted link.

The only problem is… what address do you throw in there if you’re working from your OneDrive for Business folder?

I’ll admit that this wasn’t the most intuitive to figure out.  To work it out, here’s what I did:

SNAGHTML63bca25e

This helpfully takes me to a new page that shows all my files in the web browser.  As it happens, the URL for this page also contains the information I need in order to be able to connect to the SharePoint folder using Power Query technology.

image

The URL we need is to be constructed as follows:

  • https://TenantName-my.sharepoint.com/personal/UserEmailAddress/

So, as you can see, my Tenant Name is xlguru and the email address has both the @ and . replaced with underscores.  This is kind of an important thing to realize here… the Tenant Name is not the same as your domain name.  This gets chosen when the Office 365 account is created, and you can register several domains against it.  (I have 4 domains associated with my xlguru tenant.)  So if you aren’t the admin, the steps above may be the easiest way to work out what it is.

Connecting to a SharePoint Folder

Now that we know the URL we need, it’s time to connect to the folder.  I got started by creating a new Power BI Desktop project.

In the past we didn’t have a way through the user interface to provide the necessary connection, so we had to write the M code manually.  Fortunately we’ve now got a connector to do this for us, it’s the Get Data –> More… –>File –> SharePoint Folder command (in Excel it’s New Query –> File –> SharePoint Folder).

SNAGHTML63b64d46

NOTE:  If you don’t have this icon in your list, it means that you are on an older version of Power BI Desktop/Power Query in Excel.  If you’ve updated to the latest version and you still don’t have it, it’s a bug that will be fixed.  You can still do it, you’ll just need to create a blank query and paste in the M code I’ll show you in a bit.

I then pasted the URL to my OneDrive for Business into the URL.

Of course, you’ll then be prompted to sign in.  To do this you’ll want to sign in under Organizational Account and provide your Office 365 credentials.

image

Successful completion will take you to the preview window where you can just click “Edit”:

image

Quick side question here… does anyone find this dialog is actually useful?  I can’t think of a single time that I haven’t clicked Edit to do more work with it, so it’s basically just a pretty way to slow me down.

If You Don’t Have the SharePoint Folder Option

If you don’t have the SharePoint folder connector for some reason, you can get to the same place by doing the following:

  • Create a new Blank query
  • Go to the Advanced Editor
  • Replace the Source line with:

Source = SharePoint.Files("https://TenantName-my.sharepoint.com/personal/UserEmailAddress/", [ApiVersion = 15])

This will get you a full list of all the files in your OneDrive for Business folder.

 Accessing The Data

Now that we’re connected to the OneDrive for Business folder, we can get to the data we want.  In my case, I want to dig into the Hotel Stays file and retrieve the data from the Stays table.  To do that I now just treat it like any Excel file:

  • Locate the correct file
  • Click the “Binary” of the file to expand it

image

  • Drill into the Stays table

SNAGHTML683b59fd

End result is that I’ve managed to land some nice data that I can use for my visuals:

SNAGHTML683cd736

There is a small manipulation I do want to make to my data before I call this done though.  I’ll need a “Full Address” field in order to plot things on a map visual.  To get that I:

  • Selected the Address, City, Province & Country fields
  • Went to Add Column –> Merge Columns
  • I chose to use a 2 character separator of “, “ (comma + space)
  • I called the column “Full Address”

And finally I named the table “Stays” and clicked Close & Apply.

Checking Our Progress

Okay, so looking back on what has been accomplished so far, I’ve managed to:

  • Create an Excel file with my data
  • Save the file in a local folder that syncs into my OneDrive for Business folder
  • Create a Power Query that reads from the web hosted (not the local copy) of that file

This is the groundwork I’ll need in order to create a solution where I can enable auto refresh of the dashboard and publish it publicly… which I’ll discuss next week.  Until then, hopefully this has been helpful to understand how to connect to a OneDrive for Business hosted file.

Opportunity: Influence Excel Charting Features

Hi everyone, I’ve got a quick message/opportunity for you from the Excel team.  In their words:

This survey is being conducted by the Microsoft Excel team. In the future, new charting features will be added to Excel. We want to better understand how you expect charts, that have new features, to be displayed and behave in older versions of Excel that don't have these new features.

This survey will take approximately 10 minutes. Feedback from this survey will be used to improve the user experience in Excel. Responses to this survey will not be associated with any personal information.  Please see the Microsoft Privacy Statement for further privacy details on all Microsoft products.

Thank you for participating,

Excel Team

Click to Start Survey

Fix: Excel Formulas don’t update in Power Query tables

If you’re new to Power Query, chances are you’re more comfortable doing tricky mathematics using Excel formulas, rather that Power Query formulas.  No shame there, but you’ve probably run into a situation where you set up the formulas, refresh your query and the Excel formulas don’t update in Power Query 's output table.

I’ve worked with this issue for a long time, and it’s actually caused me to avoid using Excel formulas in tables generated via Power Query all together. Having said that, there is now an easy way to fix this which renders that avoidance obsolete.

The Issue:  Excel Formulas don't update in Power Query tables

Let’s take a quick look at this scenario.  We have a simple table called Animals as follows:

SNAGHTML2968fa38

And it gets landed in another table.  But in this table, we added a new column called “Est” to the end, which holds the following formula: =[@Price]*[@Quantity]

SNAGHTML296a33f0

So far so good, but what happens when we add a new line to our Animals table and refresh it?

SNAGHTML296c10ce

Plainly, this is not good at all!

The Fix:  Excel Formulas don't update in Power Query tables

The fix is remarkably simple, once you know what to do:

Step 1: Change the Table Design Properties

  • Select any cell in the OUTPUT table (the green one)
  • Go to Table Tools –> Design –> Properties (External Table Data group)

SNAGHTML2970d8de

  • Check the box next to Preserve column soft/filter/layout and click OK

image

Now, at this point, nothing appears to change.  In fact, even refreshing the table seems to make no difference.

Step 2: Ensure the Formulas are consistent

The reason the formulas didn’t fill correctly for us is different now.  It is entirely based on the fact the formula in the last column is no longer consistent.  Naturally, that means that Excel won’t auto-fill the formula, as it doesn’t know which is correct (the formulas or the blank cell.)  We need to fix that before this will work for us.

  • Copy from the first formula cell down the entire column (I've got reports that this DOES matter, and that copying from another cell may not fix it.)

Our data should now look something like this:

SNAGHTML297810e4

Step 3:  Test it

And now, when we add new data and refresh the Power Query…

SNAGHTML297934e2

Wrap-up Thoughts

On my Excel 2016 this behavior is now default.  I don’t know when it changed, to be honest.  And if your behavior is different, I’d love to know.  I’m running the Office Pro Plus subscription – first release.

On Excel 2010/2013, the old default of not updating the tables appears to prevail.  It’s actually for this reason that I covered this, as it came up as a question in my Power Query forum.

I’m not sure if this is good or bad, but this setting can/must be managed for each output table individually.  There doesn’t seem to be a way to set one behavior or other to apply to all tables.  To be honest, I think they’ve got it right in Excel 2016, so at least it’s fixed if you’re current.  (And for reference, my understanding is that this required a patch to Excel, not Power Query, which is why I suspect that we likely won’t see it fixed for Excel 2010/2013.)

Do you use (legacy) Get External Data features?

Hey everyone,

A contact at Microsoft is looking for people who DO NOT use Power Query extensively, but DO use “legacy” methods to get external data into Excel.  These “legacy” methods include using:

  • Any/all of the commands from Data (Tab) –> Get External Data (group)

SNAGHTML5cf2dc50

  • VBA to retrieve, clean up and/or land data in Excel

In his own (okay, slightly edited) words, my contact is looking for users:

…who are importing data using the classic Get-Data Excel capabilities and have very little (or zero) familiarity with Power Query. [I’d like to] get their feedback on new ribbon sketches.

UPDATE:  My contact at Microsoft now has sufficient users for his study, so no more users will be accepted at this point.  Thanks!

This is a cool opportunity to get in touch with Microsoft and give your impressions and feedback on some potential designs that they are thinking about for future product improvements.  If you are interested and are not a heavy Power Query user, just drop a comment below (please don’t include your email.  I can access that via the blog control panel.)

July 2016 Power Query Update

Hey folks,

I'm actually on vacation, so this post is going to be short.  I just wanted to make sure you all are aware that there is a new Power Query update available.

New features in the July 2016 Power Query update:

  • New SAP HANA connector.
  • New SharePoint Folder connector.
  • New Online Services connectors category.
  • Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
  • Improved Text/CSV connector, now exposing editable settings in the preview dialog.
  • Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
  • Data Source Settings enhancements, including “Change Source” capability.
  • Advanced Filter Rows dialog mode within the Query Editor.
  • Inline Input controls for Function invocation within the Query Editor.
  • Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
  • Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
  • New context menu entry to create new queries from the Queries pane within the Query Editor.

My Thoughts (without actually using it yet)

Now you can get full pictures at the official blog from Microsoft, but I'll just call out a couple that I think are pretty darned important from a usability perspective.

  1. Continuing with last month's update where we got Drag and Drop for the query groups, we now get Drag and Drop for the query steps.  That is just plain AWESOME.
  2. The new Advanced Filter dialog looks pretty good.
  3. The Date Pikcer also looks pretty helpful.
  4. A context menu to create new queries is also SUPER helpful.  One thing I'd like to see added here, is the ability to set each new query to load to connection/table/data model from INSIDE the query editor.  (Currently, the choice you make is applied to ALL new queries - the main reason I have my defaults set to load to connection only.)

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.  🙂