Advanced Power BI: Solving the Hard Problems

well you know what I did today Devon is gonna preserve expense so we are solving the hypotheses before seven present I'd like to go to a few things Sanchez for the square over to seven by the way this sir the session is going to be reported and then we'll post the recording on YouTube so exciting it for the past community as you may know already pass is going to launch the brand website which is like a growing community of data professionals and basically instead of calling a local group local pastors or virtual chapters we kind of do local groups through local groups and there will be new look new claims and same great past community passage stories to open if you will like it word stuff there are some selected here are some selected of which are capital Iggy's you can go to people passed out old flash events to seek a legal solution our futures for - booth meetings in February Andy Lee is going to present why your data task force is a matter are April hydrology is going to present initial impressions of sequel server we've got math I've got public preview sign in May Barbara leaming is going to present power bi 0 to dashboard in an hour we have has many which is / - good and we cover different areas you're welcome to join as many were selfish as you would like of course we would like you to join all of our attacks per group you know I'm not a member already and our group is called Saturday night sequel which is good and there are some upcoming at the booth our basis which is a very good rating to insist to go to family and as a federally-funded sisters and we want to check it out any classes the most when running by volunteers so if you like to volunteer please visit one Kia 20-odd single pass alone and Oliver Church chapters are looking for speakers so if you like to prepare for our church chapter feel free to reach out to us but you know us and Saturday nights equal and equal passcode and stay gave hope you may sign up for something with free memberships today if you are not a member and and joins my name is that water capital local group perfection life um how do you ask the questions in our session if you would like to intern Alexis's never ever directly please revealed hands for and you ask your questions directly um another way ask your question would be nice yokai stay in the cat window I'll read them out for you Thank You Alice I'm a transferred sense way back to screen to seven and as you know already he definitely is a very good speaker and and has been like a six books and he's presenting elements for this past community we really appreciate 77090 resentful or excessive hey I'm happy too all right all right thank you guys for the nice introduction and I hope you guys enjoy the session tonight so you guys are some pretty dedicated people showing up on Saturday night to be able to learn something so that's great and I'm sure many of you do other pass events like sequel Saturday that was brought up with just a few moments ago I'm sure you guys are already do a lot of those if you're you're dedicated to do a Saturday night session with me so what I first of all welcome and I'm excited to share with you something hopefully you'll learn at least something new from the session but we're going to be looking at a lot of power bi different intricate topics and we're going to be focusing in on some of the more difficult topics or really I should say kind of just going beyond the basics so many of you have probably seen power bi sessions that take you through some of the basics of being able to create a dashboard and some reports from doing some data extracts well what I'd like to do and my goal with the session is to really take you beyond those basics and show you some of the more details or more difficult problems that you might run into so that's why it's advanced power bi solving the hard problems what we're going to talk about tonight you can see my contact information here on the bull out below my name is Devin night I pretty active on Twitter and you also have my email address here as well if you'd like to reach out later for any questions that may be we've run out of time to talk about just a little bit more about myself here I'm the training director at pragmatic works so we do a lot of training whether live talk training or kind of previously recorded on-demand training we do that as well I'm also a Microsoft Data Platform and VP like I mentioned I've written several books most of them around sequel server and bi and I also want run a local group a local user group out of Jacksonville Florida so I'm from Florida I'm not sure how many West Coasters we have tonight and no new our leaders are from the west coast here and then I also blog at a website called Devon Knight sequel comm I mentioned that because I might reference it a few times and you may want to take a look at it because there is going to be some good reference material that you can find there later okay all right so here's kind of our plan for tonight I have three main topics that we're going to cover and again thinking about the topic here going advanced with power bi you might you might be joining us tonight just just because you're curious of what are some advanced things you can do with power bi because overall power bi is a pretty intuitive tool and you can solve a lot of problems fairly easily in power bi and so what we're going to do tonight is talk about some of those more difficult problems and we're going to really focus our attention and on what we can do with inside of the power bi desktop that might be difficult we'll touch on the power bi service one or two times but we're really focused in on the different aspects of the power bi desktop that might seem or might appear to be difficult in some aspects so we're going to start by solving some data import problems so that's all about the query editor inside of the power bi desktop we're then going to get into solving some data modeling problems so data modeling more of that power pivot if you're familiar with the excel side but really is not referred to as power pivot inside of the power bi desktop but more than modeling an organization side of things and then finally we will end with some data visualization problems now I do the sessions but quite a bit of content in here so I know we got about an hour and a half where we can get some questions at the end I'm going to try and keep under that hour so I can have a time for questions but there's a lot of stuff in here if you're interested in anything later that you'd like for me don't worry I'll be happy to send it out to you it's it doesn't really bother me to share my material here alright so the first thing we're going to look at though is not going to be more around the data import problems and so we're going to talk about what are some of the more complex problems that you might deal with and I have a couple scenarios here that we'll talk about and then we'll talk about how you can solve some of those problems okay so the first set of problems we're going to look at like I said they're all data import problems to that we're going to focus in on first and the problems that we're going to attempt to solve are going to be around things like turning reports into data sources okay so the idea here being that maybe I have a vendor system that sends me a report once a week or once a day and I want to turn that report that my vendor sends me into an actual data source and so what we're going to do is we're going to take a data source that I have that's really formed as more of a report we're going to transform it into something that's more usable as a data source inside a power bi so we're going to walk you through kind of the scenario there then we're also going to talk about running more efficient queries so how can we make sure the queries that we're running into the power bi are at the most optimal performance that they can be and that we'll be talking about a feature called query folding okay then finally we'll talk about making queries dynamic there's a section in here we'll talk about things like parameters and templates that you have available inside a power bi and those features allow you to really make a data extract dynamic okay so the first thing that we're going to look at is all about turning reports report data into a data source okay so I've given you an example of what I'm talking about here on the screen just imagine if you will hear that this is an extract that you get from a vendor system you have okay and so in this vendor system that I have I can see that I have subtotal rows okay and they happen to be up at the top so I can see London and that London value aggregates all the values I have for my employees and so I'm seeing the salary for all of London and then I see New York and here also as a subtotal and then I see New York is actually add the aggregated or summed up value of all the values below it and so I have these subtotal values and I want to take these up total values my goal is I actually want to take those city names and make them into a brand new column so I can see which city each of these individuals are coming from so my goal is to take what I see here on the screen now and transform into something more that looks like this it's the same exact data there's just no subtotal and so I'm removing the subtotal row and I'm now seeing the city column that appears that we've made and I want to make that city column in here using the power bi query editor okay so that's going to be our goal is to convert those subtotals into something that's more appropriate here for a data source okay so that's our first demonstration we're going to we're going to jump pretty quickly into some demonstrations here so our first one is kind of dealing with this data import problem where we want to turn a what is actually an ending report into a data proper data source and power bi all right so first things first I'm going to go ahead and launch the power bi desktop and if you're pretty new to power bi if you just haven't done a whole lot with power bi power bi desktop is the application that we're going to use for developing our power bi solutions and you can download it for free by going to power bi com so if you go to power bi com you'll find the free download which is what I'm using here and there is a paid service whenever you actually want to publish these items or publish these things that you create and you want to refresh the data there is a paid component of the service as well that will kind of not talk about today because it's more of an intro topic all right so I've launched the power bi desktop I'm going to close this little startup menu that opens up for us and we're going to begin by going to get some data that we'll use for this example and so to get started here of course if we want to pull some data into the power bi desktop we'll go to the top section here where it says get data and we'll use that to be able to bring in the data that we're going to be using for this example all right so I'll go ahead and select get data and we're going to be pulling data from excel now the example that we're going to be using here is going to be coming from my presentation folder so let me go there right here and I'm going to go ahead and select from my past folder because this is actually a past presentation that I've done before we'll pull in this file with groupings example here I'm going to go ahead and open this file I'll see this is going to launch launch open the navigator pane and this just simply lets me navigate through the different spreadsheets that I have in this workbook and I only have one spreadsheet and this is example where it's called vendor report so I'll go ahead and select vendor report and then click Edit to launch over the query editor if I hit load by the way is just to show you down here in the bottom if I hit load that's immediately going to load the data that you see above into the data model if I hit edit that's actually going to allow me to do some data transformations and apply some business rules to the data before I load into data model so we want to do the edit option here in this scenario alright so I'm going to select edit and that's going to launch open the query editor and it's going to allow me to now view the data and the query editor and I can start doing some data manipulation here so you can take a look here if you want to zoom in here you can see here's my subtotal rows so my subtotal rows are the city name so you can see London in here again you can see in New York in here as a subtotal row so these values have been brought in here and they're visualized in the query editor as basically hey a null row or a null record row that's really my subtotals okay so what I'd like to do though is I want to get rid of those subtotal rows and I'm going to create a brand new column here that really indicates what the city name is for each row that I have inside of this data set so to do that there's this really one way to make this happen and it's by writing a small little M script M is the query language that's used behind the scenes inside the query editor M just is just a letter M by the way stands for mashup it's a mashup language is used for doing data transformations like we're going to be using here now there are a lot of ways that you can avoid writing m and M is a pretty new language for a lot of people in fact this is what it looks like if I hit the advanced editor you can kind of see what it looks like and it's kind of nasty it's not something that you would just want to open up and start writing yourself but it is something that if you look at it long enough you can kind of figure out what it's doing but typically what you'll find is by using the interface here inside of the query editor it can solve about 90 to 95% of your problems without you ever having to write any M yourself in this particular scenario we do need to write a little bit of M because we're going to do with something to kind of advance here and basically what we're going to do is we're going to say anytime we see the value of no oops sorry don't want to put the break up any time we see a value of null under the position column we're going to assume that's a subtotal and so we can see that this is a subtotal row this is a subtotal row if I scroll down this is a subtotal row as well and so what I'm going to do is we're going to think about this logically first before we go jump into writing any code we're going to think through our process here we're going to say that anytime we see the position column have a value of null we know that that means that this is actually a subtotal row and we want to take the value of name and bring it over into a new column we're trying to basically isolate the city name by itself without the individual names here alright so let me show you what I mean by that so what we're going to do is we're going to go up to the top ribbon inside the query editor and we'll select to add a column when we select add a column you'll see there's a couple different ways that you can add call seven hello it will see a screen now then we cannot hear you that girl and me I'm him doesn't I use ass can't hear me whether we can hear you okay um hi everybody this is Sarah I'm trying to get ahold of 7:00 and see if we could uh fix it audio or his because I hey Jamie no yes I'm sorry about that you know what I did which is kind of silly I happen to hit the hotkey and it put my machine into sleep ha ha no wonder oh it's great turn black I am sorry button that's against a magical bag a little bit maybe um a couple of minutes back sure you're not a problem yeah I don't know what happened there I just hit a hotkey and I put my machine to sleep so yeah oh I think I know where I stopped I'll kind of pick up right where I left off wonderful thank you alright sorry guys thanks for for your patience there alright so what we were talking about is we kind of walk through logically what we wanted to do we wanted to make it so that we had a new column here that had the city listed in it and so we talked a little bit about how we could do that just briefly we got started talking about that at least is we can come up to the top ribbon here underneath the add column section and then that allows us to actually write a little bit of that language called M I mentioned earlier that we can create a brand new column that doesn't already exist ok so what I can do here is I can actually hit the custom column and that allows us to create a brand new column that doesn't even exist yet and what we can do in here is we can write a little bit of that end language it's pretty simple at least what we want to do here it's going to be a matter of doing a little if statement and so I would write if the position right here so you can see the columns that we can use from the data source that we're currently using so we can actually say if the position is no then we want to return back the name column ok like so and we'll add a little space here we'll say else return back null alright so basically what we're saying here is if the name if the position column is no which we kind of identified a few moments ago is that we know if it's a subtotal or not but if the naiton position column is null then we want to return back the name column else just return back null so take a look what happens when we do this I hit ok here it's going to create a brand new column here for me brand new here it took called a custom column and we can rename it of course here we could have done it in the editor we were just looking at but this new column allows us to now have a little bit more fun where we can actually see that we this is our list of cities and we're going to create a list of cities here and the way that we're going to make this really a list of cities where we can see each individual and the city that they're from is by using another transform that we have inside of power VI called fill down still downs a pretty nice one check out what fill down does if I select the custom column here so I'm just going to select the column that we just created a moment ago and then I'm going to go up to the transform ribbon this time so back up in the ribbon here where you see transform we're going to select and we're going to choose that we want to do a fill down and basically what fill down does is it sees a value can kind of tell here from the icon in here that it indicates is it's going to see the value of the city name right here and it's going to duplicate that city name all the way down until it sees a value to replace it with so check this out if I click on fill up I did the wrong one I meant if you fill up let's try that again if I do fill down check out what happened to your my data you can see that in that column that we just created it duplicates the value for City all the way down until there's a value to replace it with and whenever it finds a value to replace it with it starts with the new value and duplicates it all the way down and so there's another value to replace it with so we use a combination of a couple advanced things one we wrote a little bit of an EM statement using an F and then we also created a new column here called city which we'll go ahead and rename City with our value that we had from our subtotal row and we've duplicated the value all the way down now if we want to get rid of that subtotal row if we don't really want the subtotal Road we can simply filter out the column that has a null in it and if we filter out the null value from the position column here you can see we get rid of the subtotal rows and then now we can just bring this into a report as it is and build out a nice visual on top of it okay so that's kind of our first quick little example sorry it kind of broke got broken up there by my machine shut shutting down or going to sleep that's the first one I want to show you here as we get started the next example that I want to show you is I'm actually going to close this one out and you can by the way you can build a visual on top of this but really the key thing was more around building out the query and making it so that we can take that sub tool too and make something more valuable out of it so the next one though I want to know what I want to show you is we're going to go connect to a new data set and in this data set that we're going to have we're going to actually show you how query folding works now if you're not really familiar with query folding works I have one slide to kind of talk to this let me pull up that slide here for a moment the cool thing about how query folding works is it's just something that's natural with inside of power bi is not really something that you have to configure although it is something that you can leverage if you if you choose things wisely okay so basically the idea behind query folding is this let's say that you have a data source you're trying to pull in some data from and that data source has billions of rows in it okay maybe it's a it's a fact table or two transactional table that you have that has billions and billions of records that have been there for years and for the purposes of the report that you're building you really only need the last six months worth of that data okay and the last six months worth of that data might just be a few hundred thousand rows okay so what query folding does and the idea behind query folding is whenever it goes to query that table that has billions of rows in it and you apply a filter on it to just bring back the last six months of that data it'll actually apply the filter on the server and not on your client machine not on your laptop not on your desktop so the idea here was query folding is it allows you to push all the work from your query back to the server and it churned through and do the filtering on the server as opposed to doing the filter on your laptop that's a big deal because think of the if it didn't do this if it didn't actually do this operation of query folding it would import that billions and billions of rows across the network onto your laptop and then apply the filter on your machine as opposed to doing it on the server so your laptop obviously is not going to be do as good at churning through and filtering down billions of rows as a server would now query folding doesn't work all the time there's some caveats to when query folding does not take place some of those caveats have to do with things like the type of source system you're pointing from so for example you query --fill they would not take place if you were pulling in data from something like a text file or a CSV file if you're pulling in data from like a text file there's not really any place for it to push the query back to you there's no server right it's just a text file and so because there's no server to run the the query against it just has to do it on your client machine there's no other option for you but if I was doing a query against the database system like sequel server or Oracle or care data for example then it does have options to actually push back that logic and push back that operation back to the server and do any kind of filtering on the server side as opposed to your client side now there's also some transforms that don't allow query folding to take place all right so for example there might be something that you're doing in your power power bi query editor that just doesn't exist inside the source system I'll give you an example of what I mean by that is you might be connecting to a data source that you feel the need to do a transform called capitalize each word capitalize each word is a transform that's built into the power bi query editor but if you wanted to do that you would have to do that more on the client-side because there is no capitalize each word piece equal statement that you can do there's nothing equivalent to that outside of you writing your own kind of custom function and sequel server so because of that there's some transforms that just cannot be folded and so you should really be aware of some of those transforms I'm going to show you an example of that here in a few moments because the whenever you have transforms it can't be folded and you're probably going to want to change the order of the transforms that you're doing because that can actually help and benefit you the last thing I'll mention here about query folding is query swimming is not an all-or-nothing thing and what I mean by that is you can actually have a partially folded query meaning that some of the query is done on the server side and some of it is done on your client side now the caveat to that is whenever you have a partially folded query as soon as it hits a step like a transform that can't be folded back to the server and everything after that point is going to be done on your client machine okay so let's go ahead and take a look at how this works I'm going to go ahead and launch back open our power bi desktop and I'm going to go connect to a sequel server instance in this example so I'm going to go over to get data from sequel server which we know is a server that I can have queries holding take place in and I'm going to go ahead and connect to my local instance of a database I have here and I'll just hit OK to make sure I select the right database name here and we're going to go connect to our database here that I have called wide world importers GW and I'm going to use the table in this example called dimension dot city ok not really relevant which table I select but just so you know for our example I'm going to choose to edit and go into the query editor and choose edit here so that I can actually manipulate this data inside of the query editor and so what I want to show you in this example is how query folding works and how do you know whether or not it is actually working is there a way to for you to test and validate that query folding is working an answer is yes there is so let's take a look at how we can test this out so I gave you the example earlier I kind of talked to the example where you're connecting to a table and maybe has billions of rows and then you apply a filter on it and the goal of query folding is to push the logic of that filter back to the server and what it will actually do is it will convert your power bi query into a t sequel query in this case alright so how do I know it's doing that well let's test this out here's how you can test it out I'm going to just find some column here and apply a filter for example the sales territory column I'm going to find the sales territory column and apply a basic filter on this I'll say that I want to filter this and only see the records let's say from the southeast ok so I'll do a filter say filter everything down to just the south east records and then I'll hit OK alright so what happened here is pretty basic it just applies a filter to this I can see that the filter has been applied I only see the southeast sales territory and then if you want to validate that this is actually pushed that logic back to the server there's a way you can do that you can go over to the applied step section here on the right-hand side so the applied step sex here if you've worked with power bi a little bit you've probably seen this already once before the applied step section is what you'll use to be able to delete transforms that you create you can also change the order of transforms that you've created so I can move this one up or down if I wanted to and this is also where you can test whether or not a query is being folded how do you do that well you right-click on a step or a transform so I would right click on this filtered rose step here and I would see that there's this option here called view native query now what View native query does is it will actually pop up on another new dialog box for me and that dialog box will show the native T sequel query that's being used and pushed back to my sequel server and that way I know that it's actually applying query folding so let's test this out I'm going to hit the view native query here you'll see it opens up a native query window here for me and what we're looking at here is a select statement this is a sequel server or T sequel select statement it's doing some kind of interesting way of writing it here but what you'll notice is on the bottom of it is it has a we're closet it added and it says where sales territory equals South East so we know that it actually is working we can see here that query folding is taking place because it's converted my power bi query into a T sequel query that's being run against the server all right well how do we know if power if power bi is not doing query folding well let's test that out I have another column over here called city and you can see here that the city column is in all lower cases it has lower case for every value in here and so what I'd like to do is I want to apply a transform on the city column that actually converts the value and it's going to change it so that it upper take up those are basically proper casing or capitalize the first word here because in some cases I might have more than one word in the city name so at like avalon beach down here for example avalon beach i want to make sure that both the a and the v get capitalized here okay so there's a transform that I can apply on here and I can apply that transform by right-clicking on the column name and I'm going to tell it that I want to do a transform called capitalize each word now I mentioned this transform earlier and what's special about this transform is there's nothing in sequel server nothing of late at least that exists that's comparable to a capitalize each word you can of course write your own function to do this but there's no standard T sequel function that can do a capitalize each word on a column and T sequel so if I select capitalize each word here you'll notice that it does work it does capitalize each word I can see avalon beach down here does have a capital A and a capital B but the downfall of that is query folding is not actually going to take place and here's how I know if I go back over to the applied step section I can right click on the capitalize each word transform that we looked at earlier and I know that query folding is not taking place because if I right click on this you'll see that the view native query option is grayed out and it's grayed out because you can't do it see it there is no no actual native query query folding didn't happen in this case in fact everything after this point will not use query folding as well because what as soon as you hit a point where you're not folding any queries or folding in a transforms anything after that point also won't be folded now you can always go back if I go back to the previous step here the filtered row step you can see I can still go back and look at the query from that point because everything was hunky-dory at that point but as soon as I hit the capitalize each word as soon as I hit that point everything below it will no longer be folded so let me show you what I mean by that if I were to go to another column here and do another basic transform maybe I right-click on state/province and I do something like just an upper case upper case is pretty standard one you can do upper case in any T sequel language or any sequel language and so when I applied upper to my state/province you can see it up did upper case on every one of the state letters and if I want to look at the applied step section here again you can see again it has the view native query option grayed out now one thing to keep in mind is you can restore it or reorder the transform so if I were to move this up and I move upper case above capitalize each word notice now that you can view the native query on this because I actually moved it before the one step that caused it to not fold anymore and so this capitalize each word transform is really a bad transform as far as performance because as soon as I hit that point everything after it has to happen on the client-side so what you might consider doing is kind of restoring these if you have to use something like this capitalize each word you might consider restoring these like I have in this case if as long as the sort order of the transform does not matter so if uppercase can go below or above I should say capitalize each word and you can least sort them and it gets you a little bit added benefit here all right great so that's our second demonstration here what I'd like to show you in the next one is really a fun thing that I'm not sure many people have really played around with a whole lot yet and it's all around using the our query language now if you haven't played a hole if you have played around with the our language quite a bit yet our is a fun language it looks like there was some virtual chapters that are actually going to be talking about our and its integration with sequel server well it has some integration in with power bi for a little while now as well and so are inside of power bi allows you to actually do two different things you can use are they are query language and two different places you can use it in the query editor to actually do some data manipulation or you can also use it as a data visualization tool there's a data visualization here for our that allows you to take data that you have and it allows you to be able to do some kinds in some form of different predictive analytics the benefit that you get from using the art query language is it really expands what you can do in power bi it doesn't limit you to what you can see and the different power bi transforms or even the in query language it really expands what you can do by far because the our query language has a lot of components to it that you might just not have available to you inside of the power bi desktop so I want to show you an example of how this works what I'm going to do is I'm going to import one more data set here or at least another data set in this case it's going to be a CSV file and I'm going to go get this from power bi so this is the file that I have this is actually a Microsoft example so you can find it on the web as well but we're going to pull in the EU stock prices here and what I have inside of this file is let me show you what the file looks like it has in it a list of days and then it has what the stock price value was for the particular day we're looking at but unfortunately some days like this day here for example just did not record or maybe there was an error during the recording process where it did not record the stock market price for that day and you can see it also happened down here on the 20th as well and so what the art query language would allow me to do here in this case is actually look at a sampling of the data around it and replace the n/a value that you see here with a with its best guess or its close as guessed so the value that should be used all right so to show you how this one works we're going to click Edit on this data source and I've brought the data now into the power bi editor and what we're going to do is we're going to use an R script now I already have the R script written out because I didn't want you to have to watch me write it all out here but it's a pretty simple little script now if you haven't worked with the our language yet I do recommend it a couple prerequisites that you'll need to install the best way to probably find those prerequisites is this if you were to go up to the file menu instead of the power bi desktop and if you went down to options and settings and then select options you'll see there's an option in here for our scripting so if I select our scripting here you'll see here where you can actually go to install R on your machine ok and there's a few things that you do here on the bottom as well but I'm not so concerned about that right now but if you click on this it'll show you where you can go download the ark client it'll also walk you through any kind of prerequisites that you'll need to have installed on your machine and then once you install the our client on your machine you'll also want to point power bi to your preferred our home directory so there's actually a step where you'll need to come back in here and point it to where you installed your our home directory the reason why you need to do that is because that's where power bi is going to search for any libraries that you install or any packages that you install in your our library or in your our directory will be located in that spot so you'll actually need to after installing the our client need to come in here and browse to the location where your our home directory is so I've already done that here just to kind of save us a little time because we're already a good amount into our presentation here but once I have installed the our clients and pointed it to the home directory I can start using the our script language now a lot of the different are examples that you'll be able to find out on the web will require that you install packages and basically packages are different little libraries of code that you can add to your our client and so to give you a little example of what I mean by that is I would go launch open the our client that I just downloaded from the install requirements I showed you where to find the in sell requirements there inside the options menu and so I would open up the Microsoft our client I'm using Microsoft are you can certainly use other our clients that are out there but this is what the our client looks like it so it has a little R console here this is a Microsoft are open console that we're looking at and what I would do whenever I find a script or a creative script that I want to be able to test out I would install those packages or libraries here first so my first step in this scenario is I have a little set of code that I want to use to clean up this data that we're looking at my first step would be to actually come up here and install a little package okay and basically what this package is is like I said earlier it's a library of code so I would come down here taste this little bit of code in and it's going to install this new library in here for me and basically what this little library is called mice what this little library will do for me is it will help me find missing data or it will actually collect and make its best guess on replacing missing data which in our case will be really helpful because we can see in our example that we're using right now we have quite a bit of missing data I can see the 15th is missing the 20th is missing if I would continue to scroll down here you'll see there's other days that are missing like the 29th the 34 the 40th these are just number of days I have a lot of missing data and so I want to be able to use our to be able to predict what that value should have been even though it's missing so to do that we'll go up to the transform ribbon up inside the power bi query editor and on the far right all the way over here you'll see there's an option called run our script all right so I'll select run our script and then I'll paste in this little bit of code that I have right here okay now the difference between the two different code sets that I have is the one on the top this install package it says mice that's installing the libraries that we're going to use and then the set that I have highlighted right now is actually using that library that we just installed okay so that's the difference between the two this top portion is just for getting the library ready to go and then this top portion on the bottom is actually using that library all right so I'll copy this out and I'm going to paste this into the our script here and basically what it's doing is going to read in the day that we have in the column here called SM I'm missing values and it's going to create a new column called completed values for me that predicts the values that should have been where I'm missing data all right so I'll hit OK when I hit OK it's going to process and run that information for me run that query and it's going to give me an output here you have two different outputs you can choose from in this case we're going to select the one here called outputs and it's going to give me a new set of values a new output that has in it the original column here and then the new column that's based off of the script that we just ran so we have this new column here called completed values that's a completely new column that didn't exist until just now and that's actually replacing the values that we're missing so check this out where those columns or the where those rows had missing values it's now made its prediction on what the value should have been in place here so this is a neat way whenever you have missing data like this to be able to do predictions and be able to still process and use that data here even though we were missing values so this is all about the our script or running the our scripting language here so I could then take this data and hit close and apply and actually start to visualize some of this if I wanted to all right good deal so that's our next let's really our last demo when it comes to the data query aside of things let's actually now talk about the data modeling side and we have a couple things that we can we can show here I'm going to kind of highlight some of these because I know we're already running out of time here but we have two main things I want to show you one is talking a little bit about how you can implement things like row level security okay so how can I make it so that I can actually add a row level filter down the data so that certain users will see certain values and then the other thing we're going to talk about here is how we can add in synonyms to enhance enhance a feature called QA if you haven't seen QA before that's a really cool feature we'll talk about that here as we get going all right but let's first talk about row level security all right now row-level security instead of power bi is done by you creating roles you create roles inside of the power bi desktop and then you publish your power bi desktop file to the power bi service and then you can assign users to the role that you created inside of the power bi service and you can filter things at a row level and you can actually have individual records that you filter and you can even actually test out the security that you create by doing an option called impersonate roles so let's go ahead and take a look at how this works we're going to jump right in to a demo here I already have this example partially built out I know we're running out of time here so this is a simple example here not a whole lot of fancy going on here the only thing I'll highlight for you here as we look at it here to begin with is you'll notice that this has a list of multiple countries that we're looking at we're looking at sales by country here basically and what we want to do is we want to filter this so that only the only records that we see are the values from the United States ok so we want to filter this down to just my US sales representative and we want to make it so that we create a security role so anytime that individual logs in they only see the data that's relevant to them ok so it's kind of the goal of what we're doing in this example all right so to show you how this works we're going to go up to the top ribbon up in the three top here you'll see in the top ribbon here you have three options you have a home ribbon of view ribbon in a modeling ribbon the security settings are all going to be found underneath the modeling ribbon so underneath modeling you'll find this option here the set of options underneath security and you're going to select that you want to manage role here so I would click on manage roles and it would pop up in a new dialog box for us here where we can create a new role all right so I'm going to go ahead and select I'm going to create a new role here and when we tell it that we want to create a new role we'll tell it first of all we'll give will give the role a name so let's call this my US sales rep ok and then we'll tell it how do we want to filter this role well I want to filter this role based on the sales territory we just talked about in a moment ago I want to filter so that we only see the country values that equal the United States and so what I would do in here if I would tell that I want to add a filter home you zoom in on this you can see I'm trying to be very careful zooming and that's how I put my machine to sleep earlier so I would go underneath the sales territory you click the little ellipsis here and I would add a filter and I would add a filter on the country column okay and I would tell it that I want to filter the country to only to only show when the value here is United States so that would allow me to filter this just for that particular entity here inside of my data model now when I hit save it's going to save this definition into my model so hit save here and then it doesn't really make any change here initially but if you want to test out and see what it just did you can hit this option up here on the top called view as rolls right here and if you click that view as rolls option you can tell it that you want to test out that new rule that you just created called US sales reps so if I select US sales rep here and hit OK you'll see that you're now looking at a filtered view of the report based on the security model that you just created now that's nice you know it's nice that you're able to do that it's nice that you're able to test out the role but you'll notice that you haven't really assigned any users to it when you want to assign a user to this role and I just stopped the impersonation there when you want to test out the security model and assign users to it you have to assign users to it from the power bi service and so what I would do is I would go to the home ribbon here let me sign in real quickly so I would sign in to my power bi account okay and I'll give my password okay and once I sign in you should see my name up top here and I can hit publish now and I can publish this to the power bi service it's going to ask me to save it locally here as well but I'm going to go ahead and publish this to a workgroup that I have here called past session and I'll hit select and it's now going to publish it off to the power bi service and so what I can do next to actually assign users to that role is I can go over to my web browser I'll see if I hit if i refresh my web browser here I should see a new data set that's been added I'm just refreshing here and I can see here now that the row level security data set we just published appears and if I want to apply security to it or assign users to it I can hit the little ellipses next to the row level security and then select security so if you're ever kind of curious if you if you kind of forget steps um what to do inside of power bi there's a lot about lips is everywhere if you ever get lost click the ellipsis you'll probably find what you're really looking to do here in this case if I click the ellipsis next to row level security and select security that'll take me to where I want to go so I'll click on security here and you can see here that I have my US sales rep role again we created that role inside of the power bi desktop and then we can start to assign either users or groups usually you would want to assign groups to this so you're not having to assign individual users but I can assign either users or groups to this role and so for example if I assign someone like Ken wood this is a co-worker of mine if I assign him to this and hit add you'll see that there now is one individual that is in here as a US sales rep and so what it'll happen once I save this is he'll now be able to view this report using that particular security sales role that I've assigned on it you can also test this role out if you hit the ellipsis here again so you'll notice here there's another set of ellipsis right here if I click those ellipsis I can hit test as role and it allow me to test it from the power bi service as well and so again I'm able to see this data now filtered down inside of my web browser through the power bi service okay so that's how the role of security can be implemented here and by the way this ties in with your Azure Active directory that's actually what you just saw there a moment ago is my Azure Active Directory and I'm assigning to individuals that are part of my Azure Active Directory all right so that's row level security inside a power bi the next thing that I want to show you guys though is also an interesting one which is called synonyms so let's just actually jump rather than join some slides on this let's actually show you what synonyms are if you haven't seen synonyms before here somewhat synonyms are it's a really cool feature basically the idea of synonyms here is it allows you to actually type in text it's almost like it's almost like Google or Bing for your data so if I were to type in something like total units so let's just say total units and United States and this is inside the power bi dashboard I can type in total units in the United States it recognizes the field in here called USA it'll convert it to that for me it will say total units in USA and I can see the total units that I sold in USA that's how Q&A works you do it from a dashboard so here's my dashboard and then you have this little section of top here where you can ask a question about your data so I asked for total units sold I could also say let's see total units sold by operating system and so it tells me here that the total units I sold by operating system it has ILS Android Linux and other and it has here a graph that is automatically created for me that's how QA works you type in a question about your data or some text about your data and it will actually return back a visualization here that shows you that information depicted now the problem with QA is it's not always super intelligent there are some cases where you might ask something that it's just not able to figure out so let me show you an example of letting me what I mean by that let's say I wanted to see something like total units by product this will work by asked for total units by product you'll see that I have tablets and phones and so it's showing me the total units sold my product category okay and it's doing that well but if I were to say something like total units sold by device which is another common nomenclature for products you'll notice that it doesn't really work it's actually just showing me all the units sold here so the problem is I'm using a nomenclature about the data that power bi doesn't know about and there's a way that we can fix this but the problem is here's this is a certain way I talk about the data and the column name that I have inside the data source or inside of my power bi solution just isn't using that column name and so because of that there's something that we'll want to implement inside the power bi desktop to actually recognize other names that we might use things like total units by device I want it to be able to recognize that so I have an example of this here I'm going to bring up the let's see where does it go synonyms example right here so what we're going to do in this scenario is we're going to create what's called a synonym on that particular column we're going to create a synonym on the product category and what that synonym will do is it'll tell power bi that there's multiple ways that we refer to this particular column so if someone says product they'll be able to see that information if someone says device they'll also be able to see that information so the way that you can create synonyms is underneath the relationship view so you have to come over here first and select the relationships view so I'll do that here and then once you're in the relationships view you'll need to go underneath the modeling ribbon and select synonyms so okay so two steps one go to the relationships view and then to go to the modeling ribbon and select synonyms so really three steps there I guess alright now once you go to the modeling ribbon and select synonyms you'll see this appear on the right-hand side you'll see this little synonyms sidebar up here over on the right-hand side and what we'll be able to do with that is we'll actually be able to select either tables or column names and create alternate names for them as individuals might identify them whenever they're interacting with the data so I can select the table you'll see the table name and all the columns below it so in this case we want to go to the table here called tablet sales and what I want to do is I want to create a synonym on the column here called product category right here there's a product category column and what we'll do is we'll create another version of that column name and we'll call it devices or device okay and so this way we'll be able to refer to it as product category or product and device I could even do something like this I can create a synonym called Saturday night sequel kind of silly to do that but I can even create as many synonyms as I want you do a comma-separated list of all the synonyms or all the columns that you might want to refer to this column name right here adds all right so here's what we'll do now that we've created a couple sims ends I'm going to hit enter on that first before I move on and I'll make sure I save this and we're going to republish this model back out to the power bi service I'm going to hit publish to send this back off to the power bi service once again and hopefully this will let me sign in I had when I went through my demos earlier this kind of froze up on me and I had to restart so let's see if that will eventually come back it looks like it's going to act up on me again let's try and kill that one more time there we go I'll launch that again it seems like if I don't sign it ahead of time it gives gives me some fits occasionally so let's go back here and I'm gonna launch open that example one more time and I'm going to sign in before we make those changes this time all right sorry about that guys I had it open let me sign in before I attempt to make those changes again okay and I'll sign in one more time all right so I've got this back open this actually gives you a good scenario where you can actually see how I got there again I want under relationships and modeling and then I selected synonyms and then I chose the table and you can see it looks like the synonyms are still here so all I really need to do is just publish this out so I'm going to publish I'm already signed in so it doesn't have to freeze on me again and I'm going to publish this off to where it says pash past session and I'll hit select I'm going to replace the the power bi desktop file I already have on the service and it's going to publish this off to the power bi service now and once it does I can go back over to power bi and I should be able to ask this question again remember what my question was it didn't work a moment ago it was total units by device and you'll notice now it does work I can actually type totally miss my device or guess what I can also type total units by Saturday night sequel and looked at also works we made it out as a synonym for product category and so because we made that as a synonym here we're easily able to ask multiple ways or multiple versions of a question and it still be able to bring back that data pretty clearly here so really interesting way that we can kind of work through this here inside the power bi desktop and the power bi service all right let's go on to our next example here the next thing I want to show you guys and then we really run into that time now so I'm going to kind of pick a couple one two that mean white we might want to show the next thing I want to show you guys is around data visualizations okay and I have quite a few different examples that we can show I know we're running out of time and I want to save some time for questions so we're going to pick let's take two of them here and I know we'll even have kind of limited time to show two of them but one of them is pretty quick so the two examples on the show are the top two here the non measured values and s tooltips and also I'll show you an example of a custom visual because I want to highlight something in there that's pretty pretty interesting the our visual gallery I'll show you here there's actually an R scripting gallery or showcase that you can go to and if you go to that our script showcase so you'll find is there's a bunch of our scripts that are kind of pre been written ahead of time for you and you can use those our scripts to be able to create your own visuals and we'll kind of talk talk a little bit about that if we have some extra time or if you guys have some questions on that alright so the things that I want to show you here number one is an example of working with tooltips okay so I have this example here of a basic column chart okay and you'll notice that in column charts you do have tooltips whenever you hover above a column you'll see a tooltip pops up in this case the tooltip shows me the name of the country and the sales amount but what's nice about these tooltips that you have inside of the power bi visuals is you have the ability ability to add new items to that tooltip say for example I wanted to add something like the order quantity to the tooltip section I added a new value here to the tooltip and you can now see there's a new item shows up underneath the tooltip I see order quantity it's not formatted but I can see there was 20 1344 or 20 sold that's pretty nice the downside of how these tooltips work is whenever you want to put text in the tooltip it doesn't interact here very nicely so say for example I want to see a list of all of the regions that were underneath that country I might want to do something like drag and the sales territory region and as a tooltip and so if I were to drag in the sales territory region as a tooltip I would see that it only shows one region so it's showing the central region here because it's actually showing the first region that I've found on the list you'll notice there it's using a function there called first well I know that happened to know there's multiple regions underneath the United States and so what I'd like to do is I want to create a adapt expression that actually handles this and an exact expression that will show me all of the regions underneath the example that we're looking at here okay in this case the country of the United States so I've already pre-written a little bit of code and to give you a hint on where I got this is actually isn't something I created I got this from one of my buddies called day his name is Destin Ryan and you can find his blog if you go to sequel dusty calm he has a couple of really great blogs a set a set of blogs on power bi tips and one of the tips that he gives is how you can actually leverage writing a little bit of a DAC statement here and I'm going to show you two different ways to do this by writing a short Dax statement you can return back all of the regions in this case that would appear underneath the United States and so what I'll do here is I'll create a quick little new calculated measure and I'm going to paste this in we don't have a ton of time to talk about the code so let me just show you kind of the end result here and basically the idea of what we're doing here is we're creating a new measure that we can apply to the tooltip and whenever I look at the tooltip notice what happens now it doesn't just show the first region that I happen to pull in and by the way it pulled in that central region because of these sort of alphabetically but now it pulls in all of the regions that are underneath the United States with the small little DAC statement okay so it's using a little concatenated X function along with the calculate function to be able to concatenate all of the region values together now one downside of this way this function is written is that you know in this case I only have five regions underneath here but what if I had 25 regions under here well in that case it gets a little excessive the list gets a little bit better and so what Dustin blog what Dustin's blog shows is another example here it's a little bit of a longer statement here but what his statement does here in this case what does Dax expression does is it'll actually look at the number of values that it has and instead of concatenate all the values together it'll see whenever you have three or more values or more than three values it'll put the vet the text of and more here so you can see it's north west north east central and it says and more just because there's too many regions that really should be filling in into a tooltip here so if you want to see the more you can do like a drill down and actually see more granular view of the data so it's an interesting way to we're going to leverage tooltips here alright we're kind of at a time here as far as new stuff the one last thing that I'll show you here and I'll do this as quick as quickly as possible because I do want to save time for questions I want to respect that for for Debbie and Sarah here they run this group I want to show some of the custom visual capabilities here I'll do this very quickly you can find custom visuals if you go to the custom visual gallery inside of the power bi website the power bi website if you go to visual power bi comm that's probably the easiest way to find it so if you go to visual to power bi comm this URL right done keeper here is a healthy again let us give him a few minutes and then we can I hear you if I party again sit down can ya me can't hear you you here we go yes yes okay sorry about that I must go again so we're kind of rid of that time there anyway see would you guys like me to go ahead and go into questions now or show any more weight would you like to finish this part and then we wouldn't go through the question see if it's not you know we have so many questions maybe what I'll do I'll just collect them all and you know you and then you have fun to answer that maybe you can post it on your blog or something well then those are in class that's sounds yeah apparently you guys hang you're hanging out with me a hug nice nice amount of folks down here so I want to make sure we and I get you a began so where I left off here is I would talked about the custom visual gallery and I made a mistake of hitting and hitting a bad button here but I need to adjust those hotkeys on here but the custom visual gallery is really neat there's there's more than 60 different visuals that you can download and add to power bi so if you're ever thinking oh man there's just not many visuals available instead of power bi there's a lot more that you can go download that Microsoft and other community developers have built out so if you go to visual power bi comm which is what you'll see up in my URL here that try to zoom in on earlier that's where you can find all these custom visuals and so one of the newer ones that I want to show you guys is the infographic this is a really cool and you actually saw it a little bit earlier with one of the examples that we're showing you saw a kind of an infographic example but I want to show you how you can use this info graphic designer it's really cool and this will be our last example for the night so I have this info graphic designer right here this is my example I have a blank sheet here so basically what we're going to do is I'm going to show you how you can use this info graphic designer and if you want to go find it and use it yourself you can go to that visual gallery that we have here and you can select the info graphic designer and download the visual to play around with it yourself and you can download the sample as well this one's developed by Microsoft so what I'm going to do in this example is we're going to go use the info graphic designer which i have is a visual right here and I'll make this a little larger and we're going to be looking at one consumption so if you're a big wine fan if you like drinking wine this is all about wine consumption here by different regions and so what I'm going to do is add to this infographics and data that I have around the wine consumption and I'm going to add in the region as well as the amount of consumption right here and you can see it starts us off with kind of a basic column chart now if you want to actually apply an infographic to this you'll see there's a little pencil icon right here and it's called an edit mark and if you use that edit mark that will allow you to start to turn this into a true infographic so if I click Edit mark here I can do things like change the visual to instead of using a column chart I can tell us that I actually want to use something like a different shape so maybe if we're talking about why drinking wine I might want to make this into a wine glass or a wine bottle like this one here and you can see when you could select that it actually converts the column chart into wine bottles you can also do things like changing the the fill so you'll see there's a fill percentage right now that's set to none I can change that to instead of none to be based off the consumption and so you can kind of see that a percentage of the wine bottle is actually being shown based on the consumption that we have in our data now if you're going to change the fill percentage you also need to go and change the layout as well so if I were to go underneath the layout section here underneath the layout section there's an option here where you can change the bound to option instead of being bound to enter if you change it to outer check out what happens as soon as I click outer it makes this more of a full chart in that fill percentage that we're showing here is actually more useful you can also do some other things here underneath the layout section you might want to insert multiple maybe some text in here so say for example I wanted to put a data label next to this to actually show me the value that we have I could do something like that I can tell it that I want to insert some text right here and you'll see that it has a little text box that's added on next to each one of my wine bottles and what I can do underneath that is I can change it instead of just being plain text maybe I want to actually find that text that you see here to some data and so there's an option here where you can make this data binding so I can hit the data binding off and actually turn on data binding and say that I want to bind the data marks that you see here the text box you see here to consumption and knows what happens as soon as I hit apply it's actually going to change those two B values here so I can see that I'm kind of adding labels to my infographic now if I go back I can do some other additional changes here I can actually go underneath the layout section again and then underneath the layout section I can choose whether I'm identifying the the text box or the wine bottle and you can kind of change around how this is being shown so maybe I want to for example I want to make it so that I can actually see the text a little larger I can increase this to sixteen point font if I wanted to I can also do something like go underneath the layout section and I can change it so that I can actually see the values a little bit better so I can change this to be something like give me 30 points or 30 pixels here and you can actually change it on the other side here as well you can kind of adjust how this is visual visualized so if I kind of play around here I can really kind of see the data a little bit better and it's all about kind of how you picture it in here so you can really adjust some of these things you can change it here let's say for example I want to actually adjust the shape here for the wine bottle itself I can change this to 30 points like so and they're just kind of moving around and shifting around things inside of the visualization so you can really play around with this quite a bit so that's one example of a infographic another way that we can do this as well and make this a little smaller here and I'll insert one more infographic you can also have multiples in here so let me show you another variation of how you can do play around with the infographic we did this one more is kind of a column chart you can also make this as more of a bar chart and so you can change and kind of toggle back and forth on a bar and a column chart by going underneath this format paintbrush here so you'll see over here there's a format paintbrush option right here and you can go under the chart option you can toggle back and forth between whether or not you're using this as a column or a bar so if I select this as a bar chart this time you'll notice that it converts it to a bar chart and then we can go back to that little pencil icon here and we can really kind of play around this quite a bit more so let's actually fullscreen that and let's tell it that I want to make this into wine bottles again like we did last time okay and you'll notice what it does whenever it's a bar chart is it just makes them really wide well that's not really what I want to do what I want to do is I want to see a wine bottle multiple wine bottles here as a way of indicating the bar chart so what I can do is I can turn on this option here called multiple units right here if I turn that option on what it will do is it will actually just multiply the icon that we're using over and over to represent the bar chart and then if we wanted to if we wanted to see these different regions as different colors you'll see there's an option here to actually make the color data-binding as well you can either just simply change the color here to a certain color if you want to do and that'll actually make all the colors the same I made a white that wasn't a good choice or you can choose this little link item here and you can make it data binding so if I click on data binding I can bind it to my region and you can have certain colors for each of the regions if I hit apply on this you'll see that it actually gives me an an icon iczer should say a new color for each of the regions that I have by the way these things work together so I can actually have cross filtering apply where I select one of the wine bottles up top and it filters the ones in the bottom or I can multi-select the ones on the top and it'll filter the ones on the bottom so they can interact with each other just like any visuals that you have instead of power bi alright well I think that's all the time I have for new stuff I went I went a bit over there but let me put my contact information up here in case you'd like to reach out later and then I'll open up the chat here to see any questions you guys have any questions kind of queued up here or you let me just kind of go down the list and read through them oh sure yeah so I see the first one here it says please recommend a book on our language in M language so there's quite a few books out there for our I actually haven't yet to purchase one there's quite a few that you you be able to find but one I definitely know there's not many and language books out there but there is one called M is for data monkeys I believe is called nits bike in pools and I want to remember the other individuals name I think is McGill but there's a that's a great book that I'd recommend it's I have glanced through that one that it's pretty good if you're trying to pick up how to learn the M query language the our language is an open source language but there are a lot of stuff out there for that as well you'll be able to find a lot of material there if you take a look next question I have here is how do you give power bi service to do some Auto analysis on your model so Tomas Tomas asked this question so he's asking about kind of an option that's called a kind of giving you insights is basically what it's called so he what he'd like to see is from a power bi model if it can automatically create some insights for you and it can and so what I'm looking at this is a power bi service and if I wanted it to automatically create some some analysis for me you can do that by going over to your datasets and if you select your datasets you'll see there's an option in here to do quick insight the option here called quick insights actually does some automatic analysis for you and so if you quick insights option you'll notice up in the top right that it now says that it's searching for insights and what it'll do as soon as it's done doing that is it gives you an option there to select and see some of the automatic analysis that it's done for you sometimes that analysis is actually helpful and some of the times the analysis is not helpful at all because basically what it's doing is it's looking at your data model and it's making its best guess on what's interesting to you and not all it's not always right sometimes it's wrong whatever it does that automatic analysis and so I'll kind of let this run here as I go to the next question and we'll click on it whatever it's kind of done doing that analysis set some offsets to your question here let's see next question the past week Microsoft just announced that SSRS technical preview was available but it seems that it still cannot publish a power bi report using the power bi desktop to on-premises SRS we can only upload a power bi workbook file to SSRS and click on the file and open it inside the power bi local power bi desktop in order to see a report is that true with the SS or technical previews us by Holly Holly to be honest I haven't had a chance to test out the new technical preview I know you you are absolutely right the current sequel 2016 install if you want to be able to get a power bi file and reporting services you're right there's not a does not a deploy option you actually have to upload it so as far as speaking to the technical preview I haven't had a chance to install it myself to test that out to see if that's changed at all but I know that the goal is to make it very seamless to be able to go from the desktop to power bi power bi for reporting services so I'm excited to see if that's actually been changeable but I can't can't speak to that myself next question here's how many how many is a maximum number of rows and columns of tables can be opened inside the power bi the editor by power bi so this is a good question and the question the answer the question is it depends so this has to do with how much data can I basically get into power bi and it really depends on the resources of your machine how power bi works at least by default whenever you go to import data is it brings it into a data model and that data model is an in-memory technology it's actually using the in memory the memory engine of your laptop or desktop to be able to import data so if you don't really have a whole lot of RAM on your machine then that really limits the amount of data that you can bring it also if you're working on like a 32-bit operating system and that'll also limit the data that you can bring and because of the 32-bit operating system will limit the amount of RAM that your power bi desktop can use so there's some limitations there that really have to do with your machine itself you're limited to the amount of data you can bring in based on the resources at your laptop or desktop has now there's an exception to that if you if you're using options like direct query or live connection direct query will actually connect you to be to the original data source and when it connects to that data source it's pulling in live data so you're not really limited to the amount of data that you can bring in because it's looking at the live version of the data on the server so it's actually pointing to the server instead of importing data into the power bi desktop it's pointing to the server and bringing in data that way and so because of that you're not limited the amount of data but you do see some performance degradation because you're pulling in data live let's see this feature is still in preview like 10 million rows I'm not sure what that's a reference to that Eric said if you've got Pro account it takes an hour to upload 1 million rows in the power bi service that's more just a comment can power bi use for procedures can it use parameters and stored procedures like SSRS okay so kind of two-part question there this is an interesting one so the downside of the what I've won yes you can use for procedures let me start with the first part of question yes you can use stored procedures and side of power bi and the way you would do that is as you go to do a data import from your data source so I would go and say I'm going to connect the sequel server and when I go to connect to sequel server you'll see there's an option down here in the bottom where I can actually connect to my stored procedure the downside of this is it doesn't really work like report like you're suggesting so you're kind of suggesting can I have reporting services parameters or reporting for like reporting services parameters that interact with a short procedure that I might execute down here the short answer to that is no not like reporting services but the longer more in-depth answer is there are parameters that you can do they just don't quite work like you would experience inside of reporting services so there are parameters inside of power bi but there are more parameters like you would think of in SSIS so if you if you've worked with SSIS before and you've done parameters there you know that you have parameters inside of SSIS that actually allow you to limit what data gets imported but this is this again is eliminated limiting the data that's being imported into your model it's not necessarily just displaying the data differently so if you think about reporting services it'll display the data and you kind of have an interactive parameter that the users can interact with you don't really have that kind of interactive parameter inside of power VI yet what you do have is the ability to apply a filter filter parameter on your data set where you're given your data source like database name and that's built into the query editor the query editor itself actually has an option up here on the top called manage parameters it's a little bit different than reporting services I recommend taking a look at my blog where I've shown some examples of how that works all right next question do you suggest any books or online resources for power bi yes so this is an interesting question there you'll find there's quite a few books available the problem with buying books around power bi or really any cloud service for that matter is it changes too frequently so if you were to go out and buy a power bi book right now and if it wasn't a digital copy if you went out and got a print copy that doesn't get updated very often then you're probably getting something that's very out of date and so while there are power bi books out there frankly and original by some really nice people and I respect them I wouldn't necessarily buy any power bi books but as far as other resources I will plug myself a little bit there I actually have a power bi class that we keep up keep up to date very well so if you're interested you can go pragmatic works calm and check that out I won't talk a whole bunch about that but I would suggest taking a look at that I also have a primary course comm we do some free webinars as well and you can actually see our free content if you go to our website as well let's see how does power bi compared to tableau in terms of performance can power bi exports visuals to excel so great good question so in terms of performance I have actually used tableau a little bit and power bi is going to outperform tableau and here's why when it comes to performance when it comes to performance tableau has just made some purchases recently and they announced using the last couple months that they're trying to implement their own end memory technology like power bi has so power bi has the end memory technology built into it through the data modeling process that you do so anytime you go to import something into power bi by default it gets placed into a data model and that's if you're if you used a lot of Excel power bi in the past that basically PowerPivot inside the power bi desktop well if tableau doesn't really have that so what some people would experience would be a performance segregation in tableau because it's connecting to the data data source and it's having to kind of churn through and write queries to pull back from the data source unfortunately though it didn't have any kind of data modeling engine that allowed it to and it least not like power bi has to be able to bring that data and memory now Java was making some changes there they've actually made some recent purchases to be able to handle that and also to do some data transformation like power bi has it made some purchases there as well those features haven't been implemented yet but they're they're coming to tableau this example is trying to catch up on that side of the world here can power bi export visuals to excel no you can't you can't really export visuals but there are power bi capabilities the kind of power bi for excel like Power View where you can create your own visuals instead of Excel now what you can do to excel this is kind of an interesting feature what you can do to excel is you can actually connect to a power bi data set in Excel and so what I mean by that is if I were to go show you this data set that I have here called synonyms example you'll notice that if I hit the ellipsis next to it that there's an option here called analyze in Excel and basically what that will do is it will create a connection to this data set in Excel and I can start building my own Excel visuals on top of this data set like pivottables and pivotcharts or really anything else i want to do instead of Excel and all you have to do is click analyze in Excel there is a plugin you have to have downloaded which I believe I already have downloaded well it's prompting me to download it but you download this plug-in and basically it allows you to connect into your power bi data connectors or data sets and then you can use that inside of Excel to connect into your data sets that you have saved on the power bi service I'm going to install that real quickly I'll show that as I get to the next question here what is the data gateway for power bi so this question was by Amanda so the data gateway is what you'll use to be able to connect your on-premises data that you have so think about your sequel server that you have installed on your your your local server farm anything that you don't have in the cloud basically so anything you don't have in the cloud that you want to connect the power bi service you'll use the data gateway to be able to do that alright so let me show you what that is here I'll go show you where the data gateway is and we minimize this for a moment the data gateway you'll find if you click on inside the power bi you'll click on this little Settings icon here and you'll see there's an option to manage gateways you'll also find that before you can get to managing gateways you need to download the data gateway and that's in terms of the download section here so underneath the download section you'll see there's a data gateway option and what this data gateway will allow you to do is install you will install the data gateway on your machine or on your server and then that'll allow you to connect your on-premises data sources to your power bi service and that allows you to do things like doing automatic data refreshes it also allows you to do things like live data connections I have a live data connection for like analysis services or a direct query connection the sequel server I can use the data gateway to be able to connect that live data and power bi so that's kind of the goal with the data gateway is connect your on-premises data data to the cloud and so I got my by the way I got my connection into power bi here if I launch open this is going to launch up an excel that has a live connection or another live connection but I has a connection into power bi and then I would sign in here looks like it prompt me to sign in but it's flat it went away if I sign into here I make sure to check that right I signed in you'll see it'll launch open this is basically like a pivot table where I can start to bring in data and build a pivot table on top of it so I can see something like let me show you the regions and the consumption by regions here and I can do something like places under the battle that's pretty brought in here is a bad data type but I can actually kind of analyze the data here if I wanted to and in excel formats if you prefer excel you can certainly go and bring the data in this manner as well so that's analyzed next cell it's kind of a side side bar question there all right next question I have can you present more in the future yeah absolutely I enjoy presenting for you guys I'd love to present again I'm sorry I had a couple taps on my side there were I hit some hot keys but my machine asleep but I'd love to present for you guys again next question so with are you never have the library loaded in the power bi you always need to load the library to are first before you run the script with power bi is that correct from physical Thomas G yes no Thomas you're right generally speaking whenever you are using the R language whether it be for creating our visuals or whether it be for using the R scripts for transforms you're going to need to go into the our client first and load the loads of library here first or install the package here first there is one exception to this where you don't have to do that's but that's that first the exception to that rule is if you ever use any of the custom visuals you'll notice in the custom visual gallery that I showed earlier that there's two different types of custom visuals you have the custom visuals that someone kind of built out all this this is a list of 60 plus odd visuals I talked about earlier but then you'll also notice there's this are powered visuals and the art powered visuals are ones that have been created by developers and I think some of these are by Microsoft as well that you can download and it doesn't require you to actually write the are yourself but it does use our behind the scenes so these are powered visuals if you download one of them it allows you to import them as basically a custom visual that users aren't behind the scenes and it will automatically install the libraries for you so rather than you having to open up the our client install the libraries these visuals that you see here will install the libraries for you automatically outside of this one section here you're going to have to go to the our client and install them yourself first unfortunately so yeah you're right generally speaking Thomas but here's the one exception with that can power bi reports refresh weekly automatically after they were published on power bi edit comes to us from Amanda so talking about refreshing the data they can yeah you actually have the capability to tell it what frequency you want to update the data and the way that you schedule a refresh I'm not sure if I'll be able to go through all the steps of that right now but the way that you schedule a data refresh is you go find your data set so I have my data sets over here on the left hand side and I would tell it you hit the ellipsis next to the data set right here and you can schedule refresh here and to schedule a refresh you will likely need to have that data gateway that we talked about earlier configured so for example if I'm trying to refresh data from sequel server and a power bi then I would need to create a data gateway installing data gateway on my sequel server or on my server that has to equal server on it and then I can come in here and schedule a refresh and then I can it'll walk me through actually configuring that and then I can also actually tell it whether I want this refresh you can see it's off right now but I can tell it here whether I wanted to be daily weekly or whatever I want the timing to be and I can configure that here inside of the data set configuration well there's a couple of the questions that got asked via email earlier and I remember remember a few of them one of the questions that we have earlier was around downloading power bi desktop files from the power bi service so a newer feature that was released gosh it might be like maybe two months ago what is the capability to actually download once you publish a report so once I publish a report to the power bi service the question is is there a way to download it back to my machine so say for example it wasn't one that I created but it was one that someone else created and I now want to be able to download it locally to my machine is there a way to download it from the power bi service and there is the way you can do that is by going to the report so I'm looking at a report here that I have and I can download it by hitting the file menu and there's an option here called download report now this feature is still in preview you can see it says preview next to it but I could download this as a ppi X file that's the file extension for power bi desktop and I can use that inside of my on my own workstation even though I downloaded it from the service now I will warn you here this feature only works on fairly new models that have been published to the power bi service so if you haven't published the power bi service and you know the last two months or so then you might not even see this option or it might be grayed out just because the feature is so new you haven't deployed since the futures been released so there's some new capabilities that are implemented as you publish and newer power bi services another question that I saw that was sent earlier was around saving QA question remember we talked about QA earlier and I showed you how this work I showed you how you can come in here and ask questions so I can ask a question like this and I can say show me the total units by device okay I remember we showed you QA earlier and someone's asked the question via email before the session about how do i how do I print this or how do I save this as a PDF and I did a little research there is a way to do it it's kind of kind of a little bit of a hack but basically what you can do is you can pin this visual to your dashboards so there's a pen option up in the top right and I can pin this visual and I'll pin it to my dashboard and then I can see it appears in my dashboard right here now and one of the neat things that you can do here to be able to save this as a PDF and then again this is a little bit of a hack to solve that question that somebody had but you can come up here to where you see the ellipsis remember I told you if you want to find something to do just go up to the ellipsis and that probably solved your problem if I go up to the ellipsis here you'll see there's an option where I can print the dashboard and if I select print dashboard I can tell it that I actually want to print do Microsoft print to PDF and then I can canvas save as a PDF version of that visual and so I can have this whole - it actually does the entire dashboard but I can have that entire dashboard saved as a PDF and it will show me the visual down here in the bottom they be the one that we created be a QA can be saved from the dashboard itself so that's that was a question we got via email as well I think we got another question popped in here so good session well power bi replace SSRS or can power bi implement all functionality all functions provided by SSRS so it's from Amanda as well so short answer I managed because I know we're running time here short answer is no I don't think SSRS is going away in fact you'll still see SSR SSRS used a lot for things like more operational reporting and when I say operational reporting I think of that as more detail reporting where you have a bunch of one item type data where you're just displaying on a report reporting services is still going to continue to be the answer for more line item reporting capabilities or more operational reporting take a power bi more for for dashboards you of course can create tables and and matrices inside a power bi but it's not really ideal for those scenarios so it's not ideal to just have a list of data in power bi if you want to show a list of data reporting services going to continue to be the best better tool for that job you also might find reporting services better when it comes to things like subscriptions or report delivery now power bi actually just released a new feature this week and I'm going to show briefly I'm not going to dig too deep into it here at a time now but there's a new feature in power bi that was released this week called subscribe right here and you can subscribe to individual reports unfortunately this feature is is pretty limited it's still new but basically what subscribe allows you to do is probably similar to a feature that you're familiar to in reporting services called subscriptions and when you hit subscribe here what it allows you to do is actually subscribe to a report so I can tell which of the reports on the report that we're looking at here so I have multiple report pages which one of these do I want to subscribe to so I can hit any one of them and then hit save and close and if I had a data refresh configured on this then I could actually tell whether I wanted to subscribe to a daily or weekly and then it would allow me to have a image it actually saves it as an image and emailed it to you to directly to you now this subscription feature here is pretty weak right now compared to the reporting services capabilities what you have in reporting services is much more sophisticated than what you can do here at least right now it's a brand new feature but what you can do here inside of power bi is you can subscribe either daily or weekly but it will only give it to you it will only give you an deliver a report to you once a day it will never deliver a report to you more than 24 hours more than within a 24 hour time span it will also only deliver reports to you that have data refresh configured properly so they're I shouldn't say that it'll only deliver reports to you whenever a date data refreshes initiated so that's something to also keep in mind can can we show power bi report on a wall TV in a company or company I try to use the URL link with credentials embedded in the link but it doesn't work somehow great question so being able to put it on a TV absolutely I actually do that in my office now let me show you how to do that so if I wanted to put this on a TV here's how I would do it I would go over to my dashboard and you can see right here is the dashboards kind of getting started there's not much to this dashboard but I would go up to the ellipsis here and you'll see there is an option here called let's see where does it go there was a feature in here wonder what they moved it there was a feature in here basically that would do that for you where you could click one button and it would kind of fullscreen oh here it is right here it's called enter full screen mode and if I select enter full screen mode here's what it does it kind of sets it up here for you so you can visualize this or you can see it in full screen on a TV and so you don't really know that power bi is behind the scenes here you do have to kind of log in a power bi to use this but it's kind of a nice way to be able to see it on a TV alright next question here how about saving what people asked for example how okay how many people wanted the total sales unit so so basically Greg's asking a question here around saving what people asked for in QA so when somebody asks a question in the dashboard does it save those results it does to some extent so it does save some of them you can actually see it down here for example so you can see that it's saving some of these synonyms for us or saving some of these enquiries or these questions that we've asked in the past but they're actually be used to be an additional feature in the old version the first release of power bi that did that capability for you and there's actually a whole auditing framework around all the questions that were people people were asking so you can kind of fine-tune it a little bit and it also used to have but again this is this is the old kind of v1 a power bi so I'm hoping some of these features come back there was also a feature in QA capabilities called raising so I showed you synonyms today and synonyms have been around for a while but there was an older feature called phrasing that you do in addition to synonyms and basically that here's how phrasing would work and I'm hoping this feature comes back it's not here yet but phrasing would work this way it would allow you to create phrases for how people talk about the data alright so think about it like this we we just had a presidential election right and you might ask questions about your presidential election data let's say you had a data set to have presidential election information and so you might ask it something like this you might ask who won the election that's a question you might ask of the data who won the election well if you were to ask power bi who won the election it doesn't understand what it means to win something you know that's a term that we use but really when you're asking that information about the data to win something has to be relative to the data so to win an election means that you would have to create a phrase that was indicating someone winning an election and for someone to win an election they needed to get 535 electoral votes a certain I'm not five hundred 270 electoral votes men means winning an election and so you would actually in the past create phrases that would enhance the q-and-a capabilities and so that's something that I'm hoping is going to come back it's just not not here yet does power bi have top in analysis built in it certainly does now it's a newer feature that's in here as well so this new feature that you have in power bi allows you to actually do what you've suggested so say for example I want to see top end capability so I'm looking at a power bi quick report here what I can do is I can select a visual and if I go over to the values section here and do quick calc so what you're suggesting is underneath this quick Cal capabilities if I select quick calc you'll notice here that it pops open a new dialog here for me and underneath this new dialog let's hope that it's here it should be here I can tell it that I want to show this value as it's only doing percentage of total here I think I need to actually have this as a different type of aggregate there is there is that capability to do top n it's just not showing here for some reason let me see what I'm missing I try and create a new table here it is a newer feature so it's possible I am just not showing it for some reason so let me do something like maybe I want to see top cities and so I would do something like a quick calculus coming maybe it's something that I saw on the roadmap that's just not here yet I know that feature I thought was already here but it could just be on the on the roadmap to come soon because I it's supposed to be underneath this quick calyx section if I remember right all right next question heavier is can we use Cortana to talk with power bi Q&A yes a question from Amanda you certainly can I don't currently have a configure to do that but you can configure Cortana and basically you would you would ask a question from this section I don't have a configure to do this currently but you can configure it to actually interact with QA just like you suggested and so as you ask a question within your Windows 10 device you can it will immediately connect into as long as you're signed into your power bi account it will be able to return back data and actually show you visualizations with inside of Windows 10 interacting with Cortana so there are some neat capabilities that have been added in pretty recently one other feature here that often times people ask for that's fairly new as well is you have the ability now to do things like include or exclude values this is a new feature so say for example I wanted to include just a couple of these cities you can actually highlight a couple of these values here right click and you can tell it that you want to group these values together or maybe you want to exclude these values so you have some capabilities of some new features and some of the visuals to include exclude or even put these into different groups if you want to choose check this out you can do like a group and it will actually group these items together and what I can do now is I've created a new group that visualizes the items that I selected and then everything else that's a new feature as well a power bi a couple months back I think we got another question here inside each individual statistic but I'm not sure what that one's asking inside each individual stat but sweb power bi I'm not sure what laughing SSRS can be integrated into applications the power bi have the same functionality so this is from Holly Xia Holly you can integrate into your application so there's something called power bi embed and what power bi embed does is it allows you to as the name implies it allows you to embed a power bi of visuals and reports into your application so if you have an application that you've developed you can use this feature called power bi embed and it allows you to basically integrate it into your application that you develop so there's a couple open-source projects out there that you can actually see now on how to do that I will note this just so you're aware of it remember that power bi is all a service so whenever you go to deploy it it's using the cloud service as ur to be able to visualize that data or really to store that data so it does it does actually bill you by the number of sessions that people use the number of sessions that people have inside of your report even if it's embedded in your application is how they charge you for that so that's something to be aware of someone someone help me out here so clicking the ellipsis in the top right corner of a graph can thank you whoever helped me out with that so let me see how I can do that here let me take a step back so someone helped me out here a little bit by telling me how to do the top end thank you very much for doing that they said hit the ellipses up here and I'm not seeing it unless I'm going to the wrong place I believe it right it's probably your summer because I think I've already seen it but maybe I'm just missing it here no problem all right so we have a last question here I get have to have to roll myself the M the M L so I think that's machine learning the ML used to do the quick insights in the cloud maybe the question is does ml does ml use for quick insights here in the cloud I don't know if it's necessarily ml that does the quick insights let me see if that one ever if it ever brought back my quick insights it was taking forever when we were doing it earlier let's see if it ever actually brought it back so I have this example here where I call told it to do Oh still it's still getting insight so second is taking this time doing that I don't know so it's a good question I don't know the answer to that question being does it use ml Microsoft machine learning to do that I'm not sure what it's using behind the scenes to do that it's a great question either yeah yeah using one yes sorry I was going to be I power bi class today and inside each of the insights on the side but this is only in the cloud you can do insights on the you can do scoping insights on the quick insight but the inside is only on the cloud products it's not on love it's not on the desktop products I just want to mention that oh yeah yeah great great great point great any other questions you guys have Debbie or sure to pick her up Oh No got it Jackie look out here the question says has a revival fun I said yeah I answered a couple of just a few moments ago let me send me Paul I'll pause my screen she doesn't see email I can read it out the first one is a housing schedule especially for by other users and the kinetically I think only the original report developer has the option to schedule refresh other users even Adam and all have not such enough as an option we would grant the admin all offense at the original creating bigger sets otherwise the report were seeing in the original service company and religion assessment data yeah yes that's a great question so I was under the impression I thought that if you made someone else an admin they could do that to the sounds like they maybe have actually walked through that experience and it didn't seem to be the case I thought my last experience where I had multiple admins so I basically what I did is I created a workspace and once I created that workspace I could assign other other individuals as admin and they can kind of contribute I can contribute and then we can both be administrators of the data set and schedule refreshes so that was the experience I have this sounds like they're having a different experience so maybe whoever that was kind of have a follow up with question - I think I think I showed that earlier question - just as a reminder whoever asked yep so that one was if you want to download from the power bi service you just go up to the file menu here and you select download report so that one you can do now is a fairly new feature let's go back here how do you export into Excel or PDF file from the natural language generating dashboard so I showed that one briefly that was the option I showed you where you can actually do a quick print so if you do print to PDF I there's no option so far to be able to plug it into Excel but you can save it to a PDF by doing that print to PDF option that I showed earlier so that's that's kind of one workaround to be able to get that from QA and then the last one here is how other I guess there's a fifth one there - how to set a default dashboard to power users so after created creating several dashboards okay so I guess here so you have several dashboards created basically and you want to be able to set one as the default so there's not a capability to set a default but what there is a capability of I go back over here is you can set favorites so this is how you're compromised here to be able to make this work so if you have multiple dashboards so let's say I had let's create multiple dashboards here I'll create dashboard one create dashboard two here alright so I have multiple dashboards that I've now created what you can do if you want to set one as more of a default you can turn on favorites which you see up here I have a favorite section and if I turn one on as a favorite here's how it shows I mean make the infographic one here is my favorite you select the dashboard and then you come to the top here where you see the option called favorite and you basically select that and if I select favor it now appears underneath the favorite section and what that does the reason why that's important is because there's actually a change coming to the power bi service I'm not sure how many of you guys have actually looked at the preview of what's coming to some of the new features of the power bi service if you ever want to test out what's in preview there's a way to do that and what they're doing with this new preview capability is they're basically making the favorites almost like default dashboards and so what I mean by that is here's what the preview looks like here's how to turn on the preview by the way as well if you go to the settings option here and select settings again you'll notice there's preview features right here underneath settings and so I can turn on preview features and if I do that it gives me early access to some of the previews and you notice when I hit apply here it's going to change the way this works over here on the right-hand side and what happens now is it only shows me by default it'll only show me my my favorite dashboards usually kind of prioritized so as soon as I go to log in to this workspace it prioritizes the favorites first that's the first thing you're going to see whenever you log in so that's kind of a might be a workaround I think there's some features coming very soon that that preview preview a feature will kind of insight you give you some insight and what's happening there all right all right so then was the one there was one other question I believe let's see the last question we have here was oh is SSIS related okay yeah I did some quick search this looked like it had to do with a script component or a script task so this question was do I have an SSI package that uses VV code to open and refresh the excel file save it and close it and then load the excel data into a database the package works fine for me on my machine but doesn't work on the sort of the POC server server has Excel 2016 64-bit so this this one of course I will be able to give you a quick answer here because I have to come see what the script is doing and and kind of dig into that a little bit but usually what the scenario is here is if you're requiring it to open and close Excel on like a remote server I have had try to think if I've done that before I think I have actually and there were some issues with doing that kind of on a remote server I don't know if you've been able to schedule that on any machine and at work or or what but I think you might have some issues doing that on a remote server if I remember right okay yeah I think I'll maybe maybe I'll contact you later and any particular and I think I got somebody we're saying that the top and see trades under visualize visualization oh one of the filter section it looks like okay so they're saying underneath filters and then memory it's the column type I'm using here let's bring in a different one here let's try because it wasn't showing there let's try profit here if I try and filter on profit hey tell me you love you are on your that maybe you can solve cases for this this person was the one measures of the top and feature dress and talk okay are you there yeah I guess how did Jagger like a nothing a number colony I don't know why but if you want money Jack I'm not like that I'm gonna name the city name water to do oh I see you're saying so trade on rather than trying it on uh there it is yeah yeah you're right I thought you were right I said I've I couldn't remember if that was a feature that had already been implemented or if it was on its way but you are you were absolutely right so then I can drag it in until it I want top by profit for example and hit apply and you you are absolutely right come on guys miss Nettie reckless hahaha right any other questions goodness I think that's about all we have thank you to attention got wonderful presentation we got a lot of people interested in this topic this this is a great end session thank you so much cool thank you for having us yeah we look for your next one alright thank you so much ticker thank you thank you I will attend you tonight okay and I

Channel: Saturday Night SQL VG

Views: 18,427

Keywords: Power BI, Advanced, Not perfect data, Complex Business Problems, Tips, SQL Query

Id: YJvV3ZFQWOQ

Length: 113min 29sec (6809 seconds)

Published: Sun Jan 22 2017