Alright. Hello. My name is Jim Hudson. I work for oaktreestaffing.com and we’re going to take about half and hour and talk about Power BI. I’m going to look at a couple tools. One is the Power BI Desktop, which, assuming you can see my desktop, is the tool that we have open right now. We’re also going to look at the Power BI in the cloud. Basically, I’m going to build a report using the Power BI Desktop and walk through uploading a fairly simple report to the cloud and then if we have time, I’m going to take some of the different possibilities with a more complex report that’s already uploaded. Again — Jim Hudson, oaktreestaffing.com. For what it’s worth, we have classes on this and a lot of other subjects. So, what I have going at the moment is that I’ve opened the Power BI tool. This is free download. You can get it just by going to PowerBI.com.
But I’ve opened the desktop tool. And this desktop tool is going to allow me to create a report. Now, on this end, what I’m going to essentially do — first, I’m going to go get data. So, I’m going to go get data and it’s going to be really simple data. I’m going to grab a couple of CSV files. They’re pretty small. I’m going to grab a couple CSV files and I’m going to load them into the editor. Now, if you have any Power BI experience at all, when you see the editor, you’re going to think, “Oh, Power Query” and that’s essentially what it is. The previous iteration of Power BI from Microsoft was really Excel-centric and they had, basically, and extract, transform, and load tool called Power Query — which is just an incredibly powerful tool. Well, it’s built into the Power BI Desktop also. So, we’re going to pull the data into Power Query — or the editor (whatever you want to call it) and we’re going to modify the data just a little bit. We’re going to take a brief look at the M language then we’re going to load the data into the Power BI Desktop Report Writer. We will use the DAX language (the Data Analysis Expressions language) to extend the data and then we’ll create some visualizations. Those visualizations will allow us to present the data. Once we have a report that at least looks usable, again, given the amount of time we have, this is going to be pretty simple. Once we have a report that’s useful, we will publish it to the Power BI Cloud piece. I have an account set up. We will log on
I have an account set up. We will log on as that account. We will publish this. We will look it, but then, like I said, once we do that, then we will flip over to a more complex report that’s already created and we’ll take a look at some things that you can do with this tool. I want to be really clear about something — I really like this tool. I think it’s a great tool and I’ve been working with reporting software for a long time. I’ve seen this go through — Microsoft and otherwise — a bunch of iterations. If I run into something I don’t like, I’m going to tell you that. I think it’s my job to tell you what I think about the product, but please don’t hear that I think this is a bad product. I think this is a great product. I’ve had a lot of really good feedback from customers using this product and I see a lot of customers moving this direction. Alright, so, the bottom line is that we have some data and we’d like to report on it. And whether or not you call it a “report” or a “dashboard” — at the end of the day, I need to grab some data. I’m going to go over here to get data, text.csv, and of course, it’s going to decide to be slow because it knows a bunch of people are watching — and I’m just going to browse to the bottom of my local PC. I’m going to grab titles CSV files. Titles, table. At this point, I absolutely could just say “load” and it would load. But I know this data and that it’s got a couple of issues. I really want to go in and clean my data up. You could argue that in any reporting environment, we’re going to have
. At this point, I absolutely could just say “load” and it would load. But I know this data and that it’s got a couple of issues. I really want to go in and clean my data up. You could argue that in any reporting environment, we’re going to have a ETL (extract, transform and load) process. The tool that I’m about to open is essentially our desktop ETL tool. Again, if you have an Excel background, you’ve seen it referred to before as Power Query. Here, I think it’s just called the Data Editor. I could hit “Load” and it would just go ahead and load the data for me into the report, but I’m going to click “Edit” and it’s going to load the Query Editor for me. There are several things I want to do here. One, I want to go over here and look at pub (published) date. One of the things I don’t love about data that’s come from a SQL Server is that if you have used a date time data time and you don’t provide a time component, it just defaults to midnight. What I really want to do is convert this to today. Now, there are a couple different ways to do this. Here’s the fun part — you may not believe me, but I’m telling you that it’s true — the easy way to deal with this is to just do a right click and change the data type. About half of the time, that works and about half of the time, it doesn’t. Off of the same machine, the same CSV file, two different days, I’ll get two different outputs. I have no idea whether or not it’s going to work here. Anyway, I’m going to right-click, transform — actually, that’s not what I wanted. What do I want to do? I want to go to Data Type here and change it just to date. We’ll come back to this. I’m going to hit “Add New Stuff.” Alright, so it worked this time. Here’s one of the great things about this tool that because – I kid you not, I did a two-day class on this two weeks ago and I did this demo twice. It worked once and it didn’t work once. Again, same PC, same everything. One of the cool things about this is that, let’s say it didn’t work or for whatever reason I wanted to go back, I could go over here to my applied steps and go, “Yeah, just kidding — I didn’t mean to.” I love that and I’ll show you a little bit later another thing I love about it. For the moment, another way to approach it would simply have been to right-click and suddenly I am drawing a blank. I want to split it — transform. The first one who finds it wins. So, there we go — split column. I love the tool. I’ll confess that as they’ve modified the tool over the last couple of years, sometimes I’ve got split column in multiple places, so here it’s both on the home and on the transform ribbon, which I think is a little confusing, but anyway. I want to split the column by the limiter and I want to do it just by space and I want to do it at the left-most limiter. One of the cool things about it is that it automatically splits for me, so now I have my date and actually, if I look now it automatically changes this to a date. I can just go “Remove” that column. I’m going to rename this one so it’s not Update.1. There are two ways to accomplish the same thing. I’ve gotten rid of that. The next things I want to do is that I’ve got a couple of null rows in here, so I’m just going to pick one of the columns that are null. I’m going to a sort ascending and put my nulls at the top and then I’m going to go to Remove Rows, Remove Top, enter 2. At that point, I’ve fixed my date problem. I’ve gotten rid of my nulls. If I wanted to, I could go get rid of some other columns. One thing I could do is that I could go in here to Type and I could do a right-click, Transform, Capitalize Each Word. I want to replace values. (Folks, whomever just connected, would you make sure that you mute on your end. We’re getting an awfully lot of noise. Excellent. Thank you.) I want to replace underscores with spaces. Now, I’ve got mod cook rather than mod_cook and now I can right-click, Transform, Capitalize Each Word, and this looks a lot better.
I think this is one of the more powerful pieces of this tool and here’s why in two reasons. 1. Everybody who’s here — whether you’re here remotely or you’re here physically — you use Excel at some point. If you’re a business person, it’s almost impossible to escape using Excel. You have this exact same tool in Excel starting in 2010. You have to download it and install it, but 2010-forward, you have this tool and I can’t tell you think number of customers I’ve talked to over the years who talk about “Man, about once a month, I have to go grab this data and I have to bang on it and do all this stuff and it just incredibly time consuming” and I’ve absolutely been there. We write macros or we write applications with that problem. This tool is available both in Power BI and in Excel and as far as I know, as long as — even in Excel — you have a professional Excel license, it’s free. It may be free without that. I’d have to go check. But there’s just a huge value, in my opinion, with this tool. A. It’s available in two places. B. I just see people needing something like this a lot. This is my desktop ETL tool. Now, if that’s you ever want to do and you absolutely can use this tool and if you’re someone who generally deals with graphical interfaces, this is great. If you have any scripting background, if you’re a developer of any kind, the other thing to bear in mind is that what this tool is actually doing in the background is just writing code. This is a language called M. It’s very well documented on Microsoft’s site. It’s really straight forward. It’s one line at a time. It’s basically “set variable equals value.” First off, I have a variable source. Source equals CSV document, file.contents. It’s telling me where the CSV file is, the limiter, how many columns there are. Again, it’s writing this for me automatically. I didn’t write one character of this. As I mouse through here, it’s doing this for me. And then the next line is “variable equals” and it’s pointing to the variable from the previous line. Here’s why I think this is cool: I absolutely, if I’m willing, can save a copy of this and then go in and change things here. I can say, “You know what, that’s not what I wanted. I don’t want this to be an N64, I want this to be text” or whatever. There’s actually a book out on Amazon called “M Is For Data Monkey” that just talks about this tool and gives a huge amount of information on just the incredible things you can do with it.
For the moment, I’m going to get out of it. At this point, I think I’m ready to go ahead and Close And Apply. I’m going to hit Close & Apply and it closes the editor and I’m assuming it’s applying my data back to the Power Bi Desktop. I want one other table, Get Data, CSV, Sales.CSV — I don’t really have to do anything to this, I don’t believe. I’m just going to hit Load. Here’s one of the cool things I want you to see: I’ve got three panes over here — a relationship pane, the data pane, and then the visualizations pane. You’ll notice that it inferred a one-to-many relationship between two text CSV files. I’m used to my reporting tools going out and looking at SQL Server and looking at the metadata and going, “Wait, there’s a key relationship.” If you’re listening to this, you know as well as I do that there are no key relationships between text CSV files. What it did, when we pulled this in, it inferred or extrapolated those relationships. I think that’s just an incredibly powerful tool. The next thing I want to do is to look at my data. I’ve got two tables over here — I’ve got sales and titles. I’ve got my titles table here and all of my different columns. One of the things I want to do is to extend the data. In the previous example I used a language called M to do that. Here, I want to create an additional column called “gross.” Basically, I want to take price times year-to-date sales, minus advance. In in order to create an additional column, I’m just going to go up here and do a right-click, new column and it’s going to put the focus up here in the editor. I’m going to say “gross equals [parenthesis open]…” and then I’m going to turn on intellisense by hitting a left open bracket “…price…” I’m doing auto-complete with the tab key, “….left-open bracket, year-to-date sales minus left-open bracket advance.” Press enter. I have my gross profit column, and at that point, I’m good to go. The other thing I’d like to do is I would like to create a total year-to-date sales which it will do automatically for me in a moment, but I want to great a calculated measure. A calculated column basically is a value that just executes once per row, but I want to do something that actually takes place over a particular column. What I’m going to do is do a right-click New Measure, and (of course in the two-day class, we spend a lot more time on the DAX language and exactly the difference between a measure and a computer column and that sort of thing). I’m going to create one called business-to-date. To do that, I’m going to use to use a DAX function, a Data Analysis Expressions function called Calculate. Calculate is kind of the Swiss Army knife of DAX. It takes at least two input parameters — one is what we want to calculate and then the second-through-end parameters are a filter. I want to calculate this sum of year-to-date sales. One of the things I notice is that I’m using a single tic here to drop down my list of columns. If I’m doing a calculated column, I can get away with just specifying my column, but the second I start doing a measure, it’s very possible that a measure could touch multiple tables. I’ve noticed that this works a lot better if I explicitly declare my tables. I’m going to mouse down here, arrow down until I get “titles year-to-date sales” close the parenthesis. The next thing I want to do is the filter. I want to filter it on type. I’m going to say, “titles type equals business.” It looks like I’m good.
We’ve already used up 15 minutes of this thing. It’s always amazing — oh, more than that — how fast these things go. You’ve seen a couple of languages. You’ve seen a couple of languages. You’ve seen M and DAX. In my opinion, learning to use the user interface in these tools is not terribly challenging. It’s not anymore challenging than say, Excel or something like that. We don’t have that many options, folks. For me, learning this product, I’ve spent the majority of my time and brain cells learning the language — particularly DAX. You would argue that the Query Editor writes a lot of it for me. The majority of the time, when I’m working in M in the Query Editor, I’m taking a big wad of dough and modifying a couple of things. Maybe I’m turning it into a function or I’m in and saying, “No, that’s not the data type I want — I want this data type” or whatever. With DAX, I end up having to hand-write a lot of my own DAX. Honestly, if you end up using this tool or the Power Pivot tool in Excel or SQL Server Analysis Services Tabular, all three of those products support the DAX language, I really think you’re going to spend a lot of your time and your brain cells learning DAX rather than learning the buttons to push.
At this point, I’ve performed a simple ETL process on my data, I’ve brought my data in and I’ve extended the data somewhat and now it’s time to begin to work with some of the different visualizations. One of the cool things about this tool is how many visualizations there are. The other thing to notice is that we have the option of importing a lot of other different visualizations. There are some phenomenal visualizations out there. If you’re a developer, you can write your own. For the moment, I’m going to go simple. I’m going to grab “year-to-date sales”, I’m going to drop it over here. It’s going to process for a second. I want to see it by “type”, so I’m going to drag “type” over on the axis. I’m going to make it a little bit — since I’m old — bigger and easier to read. Then I’m going to make a copy of it. Control+C, Control+V, which sounds kind of silly, except for the fact that I can move this over here and I can make it a completely different visualization. I’m going to paste it one more time. I’m going to go down here and I’m going choose “table.” Then I’m going to go to the Format portion, going to go to Values — oh, golly — why am I drawing a complete blank making this bigger. I would have sworn it was in general. Does anybody see something I’m missing. There we go — making it big enough where I can read it. The last thing I want to do is make a slicer. I’m going to go grab Publisher ID and drag it here. I’m going to turn it into a slicer. Again, go back over to Format here, Items. At this point, I have the ability to do some basic filtering. I’m going to save this real quick. I’m just going to put it on my desktop and call it “webinar” and hit save. I’ve saved the file and it saved the file as a binary file called a PBIX file. I can then close this and reopen it later. The other thing I want to do is publish it. To publish it, I need to sign in, so I need to go ahead and sign in to my Power BI account. That’s the first time I’ve ever seen that. I don’t know if it’s because I updated it or what happened, but let’s see if I just, great. I’m going to flip over here and what I’ve done is I’ve browsed to
I’m going to flip over here and what I’ve done is I’ve browsed to PowerBI.com, clicked Sign In. For what it’s worth, if you have some kind of email address that’s a business email address, basically something that’s not Gmail or Yahoo!, then you can get a free account. If we have some time we can talk a little more about different levels of accounts that are available. The bottom line is that I went to PowerBI.com, signed in before the discussion here, and (gosh what a great time for my phone to ring), we can see my report — it’s been published here. Here’s my report and granted, it’s incredibly simple. That’s one of the things I’ve learned — remember to deselect your slicers because if you publish it with the slicers selected, then it absolutely is going to, when you open it, it’s going to have the slicers selected. At this point, I could do some really interesting things. I can deselect my slicer. I actually can go up here and use something called a Visual Filter also so I can say, “Hey, I only want to filter my data based on this.” And that’s cool, but folks, let’s go look at a slightly more complex, expansive example.
This is one of the examples that Microsoft has out there. It’s just, in my opinion, a really great example. This is a little more, I don’t know, grown up or professional look at some of the different things you can do with a published report. I absolutely can go use my slicer. I have slicers here and that absolutely is going to filter my data. I can go over here and use what I call a “click visual.” If I choose partner-driven, no, central region, then all of my other data sets on this page are then filtering by that. I have some other abilities for that also. In fact, I’m going to deselect by clicking here (oh, that’s not what I meant). I’m going to deselect my slicer and going to go here to Filters. I not only have the quick visual capability, but I also have some filters that actually offer me a bit more options. I can go to Probability, since this is a numeric value, I can filter on that. If I go back to Partner Driven, I can select here. These are all just going to impact the current page. I also have a Report Level Filter down here, so I could go filter this. (Boy, my screen is not big enough.) I can say, “Hey, I want to see just data for small and medium-sized opportunities” and one of the cool things is that I can go over here and that Report Level Filter then is filtering across the different pages of my report. The last kind of report filter that I want to show you is Drill Down. We have the option. I go over here and turn on Drill Down. I’ve got some date data which represents a natural hierarchy. I only have data from 2014. Now I can choose “I want to see it by quarter, I want to see it by Q1, I want to see it by January,”or I can drill back up. It just gives me a lot of really great opportunities as far as the data is concerned. Just some other things real quickly, just to talk about what’s available. If you have any expertise with the R language, you can embed R visuals with this.
We have a huge number of additional visualizations — like the donut chart here, and just a lot of additional visualizations that really can spice this up and allow you to display your data to any format that you think is appropriate. Some of things that you can do that are pretty impressive is that you can go down here to my data set. I can a right-click, Quick Insights, and what it’s doing in the background is it’s going to just kind of throw this at an AI module and here in just it a minute, it’s going to come back and say, “Here’s what we’ve seen.” I’m just going to point out a bunch of different data points. Some of them are a little obvious, but some them, honestly, are pretty insightful. Here’s count of partner. Lead has quite a bit more accounts than partner. Here’s a clustering algorithm and showing outliers. Again, just a quick linear regression here to look at the difference between revenue and factored revenue — just some amazing things you can do with the tool. Well, folks, it’s 12:30 pm. That’s kind of what I’ve got. If you have questions, I would love to hear those, but again — Jim Hudson, oaktreestaffing.com. We have a variety of classes on this and mostly Microsoft products, but again, that’s what I’ve got. If you have any questions, I’d love to hear them — local or remote.
Questioner: Quick Question.
Jim: You bet.
Questioner: All of the page on this report, I could just generically call it a dashboard, I’m guessing that they’re all targeting a data set behind the scenes.
Jim: They are. Absolutely. By “data set”, we mean whatever collection of tables that we’ve welded together before we uploaded it. But yes. Can I expound on that for just a second? One of the things that I didn’t take the time to do is that you can go to — let me go back to my dashboard real quick — I could go over to this page and I could say “Hey, I want to pin this to a dashboard” so I can go pick multiples from multiple reports from completely different data sets, and glom them altogether into a dashboard and then there’s obviously a lot more to it than this, but if I click on that visual, then it’s automatically going to take me to a given report.
Questioner: Of course, my next question is live data — is there a way to build this so that you can be getting a live data source for featured reports or do you need to aggregate your data, build your data set, and publish everything and what you’re viewing in the report is not going to be pulling from any live data source behind the scenes.
Jim: You do have an option for live data sets called Direct Query. The only time I’ve ever set it up is when I had a SQL Server in Azure. Since this is sitting essentially in Azure — sort of, not really — it could, obviously, if it had the credentials, see the Azure SQL Server — absolutely, you could be pulling live data. Most of the time, what we’re going to do, as you said, is we’re going to do a cloud ETL process, put it together in a big wad and then inject it into the report. Then at that point, you have the opportunity to schedule refreshes based on your gateway. And how many times you can refresh it a day is going to be impacted by your licensing. And they’ve changed that a couple times recently, so I’m not even trying to keep track of that.
Questioner: That’s interesting.
Jim: Does anyone remote have a question? Anybody local? Anybody?
Questioner: How does it deal with very large data sets like if I’m glomming together a few tables and my source data is several million records?
Jim: I’ve not tried anything that large with the cloud version. I’ve done the Desktop version in one of the classes and we do a 4 million row table — it seems to work just fine with Desktop hardware. Again, I’m going to take the chicken way out and tell you again that if you have larger data sets, you’re probably going to need to go with one of the more expensive subscription styles. In what little reading I’ve done, how they’ve changed the subscription levels, there absolutely is a level that’s designed for a lot of data. I think at that point, you actually get — not that you have to manage them — but I think you actually get dedicated Azure instances at that point in order to support that. I don’t know how much that costs.
Questioner: Interesting. Very interesting. Thank you.
Jim: Anyone else? Well, folks again, that’s what I’ve got. I appreciate you dropping by. I’ll see you soon. You’re very kind.