Excel to Power BI [Full Course] 📊

[Music] hi everyone welcome in my name is jonathan silva this is our learn with the nerds for our class today that we are going to go ahead and look at how we can bridge all of our amazing excel skills into power bi now the focus of today's class again is so we can leverage all of our existing knowledge from excel migrate into power bi and see how we can pull in everything together now this three hour session that we have today is live and we have a lot packed into this session so i do want to make sure that everyone knows that i can see some items that are in the chat that people are trying to download the class files to be able to get in and follow along and that's absolutely great if you'd like to do that but because we are rather limited on time as far as you know our three-hour block here i want to tell everybody and make sure everybody knows that we're not going to be able to really slow down if you're having any issues you have any additional questions on certain things along the way because there's just so much for us to pack in now with that being said this is being recorded so if you want to come back afterwards you can pause you can slow me down you can speed me up you can get your subtitles all that good stuff as you re revisit this session later on so i do want to encourage you to come back later on after this is this live piece is done and we can go ahead and make sure everybody has access to everything along the way um also before we go ahead and get started i did want to make sure to let everybody know that we are currently running an amazing special on our on-demand learning platform we actually have a 50 off special going for all attendees for this event today so if you would like to go ahead and do that go ahead and navigate over to our uh website there training at pragmatic works or learning trackmatic pragmaticworkstraining.com to be able to sign up and use that 50 discount i do believe that uh anthony and julia are helping me out with uh the chat today so hopefully they're able to throw in that link there for everybody to be able to get access to it all right so let's get right into our class now as we start off today i just wanted to kind of do a quick little uh brief background into who i am what i do here for pragmatic works again i am a trainer um here at pragmatic works we are located in the jacksonville florida area so it's usually nice and sunny during this time period it's actually quite cold today we're in the high 30s low 40s this morning when i woke up so used to the 60s and 70s it's a little bit chilly outside today i'm also a microsoft certified trainer which just generally means that i've gone through all the the hoops and everything for microsoft to be able to um train on their products on their platform um to be able to you know give out the different training methods there i'm i also specialize in the power platform which uh is part of just all the microsoft ecosystem in the power platform we're talking about power vi power apps power virtual agent and power automate all four of those together the four legs of the power platform and of course excel is a major part of all four of those now a little bit about myself as a background i am a former educator i was actually a high school teacher for roughly 10 years primarily in the dc metro area but also down here in in florida where i i spent most of my time teaching a lot of those advanced level ap ib i even taught a lot of the cambridge academy the a-level courses for um economics and mostly european and world history so with that i like to keep that background that education background in mind for all the training opportunities that i um i'm involved with to really help everybody get a good you know foundation for the topics that we're going to be going through less of click here do that but more of why are we doing things how can we do things maybe some of the best tips and tricks and practices along the way now for this class today for this session that we are doing together today the general agenda of what we are going to be covering throughout the three hours together is as you can see on the screen there okay we're going to start with just simply understanding um excel and the power query editor that lives in excel by the way also lives in power b and we'll showcase that a little bit as well and so with that understanding what can we do with the power query editor some of the different things options that we have with there um and then how we can use that and leverage that into powerpivot which is the data model that lives in the back end of excel and then from there with that powerpivot data model that we are going to build we'll then look at how we can migrate that data model into power bi and how we can use and leverage the amazing visual tools inside of power bi to build out a report and to be able to take that report and then send it out to be able to share and publish out on the power bi service and so we'll see how we can use excel and power bi together at the power bi service to be able to share out all the amazing work that we do to others either within our organization or outside of our organization and then finally in our last bit together we are going to come full circle and we are going to take all of the work that we did to produce that final report in power bi and publish it out back into excel and showcase how we can inside of excel leverage a power bi data model leverage the cube that we're going to be building automatically to be able to populate uh pivot tables pivot charts um and other slicers and things inside of excel really taking everything again like i said full circle from start to finish so when we think about what we're doing in terms of business intelligence and data analytics when we're looking to build out a report right to understand our data to be able to have some type of action taken from it in the end we go through a general process each time in order to get that final result now we think about the evolution of a report we often start with just someone sitting behind a computer looking at the raw data and trying to figure out what that data means and how i can use that data to understand better things about either a problem my organization may have or how we can leverage our current data to better position us into the future and so we think about the evolution of report our first step is always to work with that raw data and then what we're going to do today is look at how we can use that raw data bring it into the power query editor where we can get data no matter where it may live maybe it's on a sql server maybe it's in an excel file itself or in a web page or in an azure database a data lake or it may live and anywhere else that your data might live you can bring into the power query editor and apply some transforms on it and then set that data up cleanse it set it up in a way that really makes sense for you and your organization to go ahead and to try to populate your reports from and so from that step inside of the power query editor what we'll end up doing is we're going to build out a data model and what our data model will do is it'll allow us to take queries or tables from multiple different data sources wherever they may live and to set up relationships between them and those relationships between them allow them to communicate and so we can populate a single visual a table a chart maybe a map in power bi or anything we want to build a single visual all populated from multiple different data sources all at the same time and so with that we'll be able to really make some advanced calculations as well we'll look at our dax our data analysis expression language and power bi later on and how we can leverage that too and then finally when we have our data model all wrapped up ready to go we have some options do we want to create some visuals inside of excel using our canvas do we want to um create those pivot tables pivot charts etc or do we want to move to power bi and inside of power bi do we want to leverage some of the amazing modern visuals those interactive visuals that um many of us if we don't know about will come to absolutely love and because they're just simply super easy to use and very very detailed and structured in that way now again we are going to start our session together really focusing on the power query editor and the data model inside of excel and then when we leverage that we'll then migrate over to power bi afterwards so again when we think about the power query editor for excel some of the things that are that we are capable with of doing inside of that power query editor are we can really take all of the data cleansing steps that you may do in your daily process with with excel cut that time in half right we're no longer needing to do uh you know cut copy and paste we don't need to worry about adding in macros or any other type of formulas or functions inside of excel to try to get the data all set up in those rows and columns that we all know that excel is so powerful of doing for us inside of the power query editor leveraging the ui the user interface we can do all of those steps in the matter of clicks a single click does everything for us another click we'll add a column or remove a column or do things that we may need inside of that power query so we really allow the the machine to do the work for us set up um you know a process for us so in the future when we want to come back to this we don't have to do those steps ever again we could just leverage all those existing steps let them work for us and we never have to worry about you know trying to repeat that process that that manual process again let the machine let it all do it for us and then for our data model known as powerpivot inside excel what we are able to do is we are able to again link together two or more tables that may exist and multiple different data sources no matter where they are started either in access again or all the other data uh plate sources that we might be able to store our data in we can pull them into the power query editor we can then use the data model or powerpivot in excel to leverage relationships between them so they can communicate they can go ahead and really populate single visuals now that the example that i have there on screen is we have customers right a lot all of our organizations are businesses we all have customers and for our customers we might store our customer data on a separate excel sheet or in a separate table somewhere just a list of names addresses birthdays all those kind of good things right and then what we also have with our customers we might have appointments with those customers and we might have another sheet that has all of our appointments our schedules for those appointments how we can connect our customers to that we also have contracts for those customers maybe that once we have a written up contract we then have that stored somewhere else that say hey this this is going to be something an action taken or it's already been done then we also have referrals right we have maybe our customers fill out a google form or a microsoft form for a referral to say hey these other people we give a little incentive program or something with that we store all those referrals somewhere else we can actually take all of those different tables that are stored in a couple different places make connections to all of them and so we can now have one data model with our entire organization our whole company that we can leverage in order to really understand how well we are doing and how well we can better serve our customer base and so that data model when we think about what this data model can provide us we like to think of our data model as a single version of the truth the end goal for organizations that leverage the data model from excel and power bi is to have one for all of us where we have an original author that looks at the raw data that pulls through the data wherever it may live goes through all the processes of building that into the power query editor and into our data model sets up that data model for everybody in the organization to be able to leverage and so that original author might want to create some reports from that some visuals from that if they would like but then others inside the organization can also pull from that data model and make their own reports make their own versions of that to be able to work for them maybe the original author worked in i.t maybe they were in marketing maybe they worked in a different department and each department now can leverage that single data model that single version of the truth to be able to pull in and populate their own reports on the data for the entire organization to work through so it's a great way to have everybody know hey i want to know things about our company i want to know things about our customers we all go to the same central location we pull the data that we need for our specific purposes our specific reports and then move on from there and another great thing about this data model is we can also do everything in excel so if you are you know what i live in excel i'm in excel all day every day that's what i use or it's like top three every single day or every week that's what i know best that's where i live i feel comfortable there you can still do all that in excel too so you don't have to get out of your comfort zone you can just start to add in some extra skills so new tips and new tricks along the way to really round out your skill base to you know not just make your company work better but make you work better too and so then our evolution of the report from there after we have all of our cleansing steps done we've looked at our data model we can then take a look at how we can migrate and start to build out some you know really good tables or visuals or charts along the way to understand our data to better get a better feel of what that data is telling us and what we can do with it so we can build out our pivot tables our pivot charts we can have our just our general tables in excel you know how we all like to color code and have all those things with those on a daily basis right but we can also go into power bi and going into power bi is an amazing way for us to be able to really see some of these modern visuals that are out there for us these interactive visuals not just for us but for anybody that else is going to going to use this report any of our end users maybe if we're doing a presentation or anything along the way that'll be able to view these reports to be you know be able to feel some interaction to take some control over that data for themselves and so when you think about like why should we use power bi because there may be a lot of us out there that just don't know what power bi is we're not very familiar with what power bi can offer well some of the great things about power bi is first of all power bi desktop is free it's a free download right all you need to do is search power bi say in any type of browser you may have go to the website download power bi desktop all right you'll need to eventually log in to create an account with either a work or a school account for microsoft and then you can just go ahead and play around with power bi and you can start building out reports for yourself you can really leverage everything for free right away it's very simple just get started with and then when we do that we can start to build out some real-time dashboards and interactive reports once we publish that out to the power bi service which we'll showcase a little briefly today not too much because we're going to focus on the big picture here but we can we can still see a little bit there today what we're also going to be able to see is that inside of power bi we can leverage some of the natural language query and ai that is built in now the ai that i'm talking about is we have an ability inside of power bi to ask questions in just plain english or spanish about the data i want to see you will simply type in all of my sales for q3 2017 as a bar chart and it will automatically populate that for you so long as you had that data built into your data model so it's a really great way for us especially if we're new to get started with understanding how to build out visuals but also for we're developers we're used to this we've been doing this for a while it's still another way to hey i'm not quite sure how to visualize this this is what i want to see let's see if power bi can go ahead and do it for me another great thing about power bi is the full integration with excel because power bi is built essentially like the newer the younger cousin of excel powerpoint sharepoint and teams where we can take our power bi reports into all of those and be able to really work collaboratively with our others in our organization to be able to leverage all of the amazing data models and reports that we're building out and then finally why a lot of people come into power bi are the visuals we have these custom visuals and custom connectors that we can leverage inside of power bi that are simply amazing these visuals allow us to look at our data in ways that we have maybe never been able to think of before where we can have just simply the graphic interface of this is it's it's mind-blowing the first time you look at it and all of the custom visuals we're looking at 250 plus i'll tell you right now it's probably upwards over 300 by this point custom visuals that we can bring in to our power bi reports either directly in the power bi app itself or by going to appsource.microsoft.com and by looking at those custom visuals we can leverage a lot of different ones available to us to be able to bring in to just showcase our data in so many different ways and in ways that we want to see it just work best for us and for those inside of our organization all right so what we're going to do from this point on is we're just going to go straight through some demos okay the whole purpose of this is you want to see this actually worked out you want to see this done in in real time right so we're going to do a bunch of demos here today now for these demos today i'm going to start off by really focusing on excel and the power query editor inside of excel and then from that from those the first set of little demos there some of the new features the really cool features of the power query editor i'm going to start to work on an end to end report starting in excel going to power bi into the power bi service and then back into excel so as i mentioned as we started off this session here today if you want to try to follow along we have the student files uh there in the description for you go ahead and grab those download those try to follow along however i'm gonna be moving at a decent clip okay so keep that in mind i'm not gonna be able to stop too much along the way simply because we are rather limited on time here however once again this is being recorded right this is a live session that is being recorded you can come back to this later on um you can revisit as many times as you need pause me stop me rewind fast forward slow me down speed me up anything along the way there you'll be able to do all that as you need to really be able to pull out this whole report and i have some beginning and ending files in there as well set up so everybody should be able to to really access everything that they may need all right so what we are going to do is we're going to have a little bit of a timer there let me get rid of that timer is i'm going to open up excel okay and here you have this is excel 2016 okay so inside of excel this is a very blank excel workbook that all of us are very used to using okay what we're going to do is we're going to jump right into leveraging the power query editor now the power query editor again is this amazing etl tool extract transform load tool that we can use to leverage and bring in data inside of this excel workbook to be able to transform cleanse do all those things that we might need to do to make that raw form just look and work a little bit better for us now in order to do that we are going to migrate over to our data tab now if i select the data tab here at the top what you'll notice here on the far left is we have our get and transform data area and we are going to leverage this right here this is going to be the focus of how we can get into the power query editor now if i want to get to that right the first thing is well why what do you need to do right where you're going to pull something in in order to leverage the power query editor you need to work with data so let's go ahead and do our first little demo here and the first demo that we are going to focus on is say that for our organization our company we have an excel file that we might be using on a weekly basis or say a quarterly or monthly basis along the way and we have this same excel file that we just create new versions of every week every month every quarter every year whatever it is just to update maybe inventory or anything along the way and so we may have if you think about how we store this 35 40 versions of the same file with all the same columns all pretty much the same data just the newest versions of that for these the dates that we're looking at and because we have all these files that are exactly the same we often want to see all the data for like the entire quarter all at once so what we need to do is usually we just copy and paste that data all into one workbook have everything there and then we can start to analyze all those tables together however using the power query editor we can do that entire process and make it so much easier and so much faster than ever before so i'm going to showcase how to do that here so what i'm going to do is i'm going to select get data and here and get data is what i'm going to use in order to pull in multiple different excel files all from one single folder because we're going to leverage that folder inside of the power query editor to be able to pull every file in at once combine all those files into one and apply transformations on all those files all at once so no longer do i need to do that same process over and over and over i do it once and it's ready to go and also the best part about this is every time i do this if i set this up once any time i get a new file maybe for next week or next month or something all i need to do is drop it into that folder every transform every step that i've taken to clean clean those existing files up will automatically be applied to that new file as well so let's take a look at how we can do that so i'm going to come over here and i'm going to choose from file and then i'm going to come down here and choose from folder now we'll talk about as we come through all the different options here that we can bring into the power query editor here in excel the different types of files and sources locations as we get along the way but we're going to start right here from folder and so now what i'm going to do is i'm going to point to the folder that my data is existing in and what i've done is i've simply saved that same file location same folder onto my desktop just for a quick access here today and i'm going to come into my data folder and i'm going to point to my international sales and in my international sales okay i'm going to pull in all of this data okay and so we look at our data here okay that we are going to be leveraging here inside of excel you see you'll notice that we have all of these files that are already set up ready to go for us right everything's there for us we don't have to worry about understanding anything from that point okay we don't have to really bring in anything further it's all set up ready to go for us okay all these files are all there now what i'm going to do is actually i'm going to close this out for a moment i'm going to pull this once again because it looks like that file here i'm going to go from our folder here i'm going to open up that folder that's actually the world sales one inside i'm not sure give me the full one but we're going to do the world sales folder more specifically and there we go i'm going to open it up and so we can see we have a couple different countries that we are selling to um every year that we want to see the data for okay and so now here i have some options so if i you look here we have a couple options at the bottom inside of the power query we have transform data we have load and we have combine now remember the end goal of what we want to do is we want to bring in all of these files we want to combine them all into one and to be able to apply different transforms on them so we can have them all be able to showcase the data the exact format that we want to see so what we're going to do is we want to transform and combine at the same time now if i just hit transform anytime you see that what i'll do is it'll automatically open up the power query editor right now if i just do transform i'm going to see all of them separately don't really want that yet if i hit load what load does is it takes the file in its current way whatever it's set up in currently and it just simply loads it into an excel just the way it is now we think about the power query editor and working with reports most of the time we're working with data that's just simply ugly it's just not nice to work with there's stuff all over the place if you got it from a co-worker the way they put set up their data might be different than the way that you like to see it in excel so sometimes you have to go through the process of cleaning it up we're not going to use load because i want to make sure it works for me and makes sense to me and so what we're going to then do is we're going to actually leverage combine here and if i open up and hit this drop down for combine what you'll notice is i have an option here to combine and transform put them together as one and go into the power query to do to do those transform which is exactly what we want to do we can also combine and load into excel or combine in load two and i'll tell you in a little bit what load two means but it's a quick preview there it's going to talk about the data model that we absolutely want to leverage so what we're going to do is we're going to hit combine and transform and so what will now happen is i'm going to get a pop-up a new window here okay we have our preview here roughly the first 20 rows or so of our data that we're pulling in of the files that we're going to be using and so with this right now you can see the sample file it's just showing the first one for australia just coming in alphabetical order if i wanted to i can choose the other ones there as well it's really up to me if i wanted to see which ones but in this case we're just going to see a quick one there and i can go ahead and hit ok and when i select ok what it'll do is now going to get another pop-up window which will give us the power query editor now here is the power query editor this amazing etl tool for us and so the power query editor it's quick lay of the land things that we're looking at at the top here we have just our our area right this is our ribbons our tabs um just you know things that we have our options to be able to do things similar to what you're used to in excel or any other microsoft product on the far left i'm going to make sure i make this nice and big for us here on the far left we have our query pane and this query pane is where we can see all of the different queries or tables in a list that we can go ahead and leverage so we can go ahead and clean and play around with right now we only have one we have our world sales notice it's the same name as the folder that i chose to bring in and combine all that data from now in the middle we have our query preview area okay so this preview area is where we can see the first thousand rows of every bit of data that we're looking at now it's only showing us the first about 999 plus really thousand rows of data here because the power query editor is memory intensive okay it's going to work and try to store and compress a lot of data for us automatically and because of that it's going to use up a lot of memory and because it it does that we don't want to bring in every single row it's just going to slow down the process it's really going to slow down our computer a lot so what it does it's going to look at the first 999 000 rows of data that we're looking at apply all the changes we're doing that we can see and verify directly from that but it also applies those changes to everything else just in the background now if we want to make sure it's actually working and it's actually applying to the others all we need to do is we can see on the top of the right hand side of each column we have a little filter option okay that drop down there gives us our filter if you want to see things that are happening for say row 35 700 right pick a random number all you need to do is find out what might exist in that row and then filter down to the specific locations there okay something to think about there then on the far right hand side inside of the power query editor we have our query settings all right it says it right there and in the query settings we have an area where we can rename or name our query our table that we're working with we also have probably the most important place in the entire power query editor over here our applied steps now these applied steps again are the most important because they give us a detailed description of every single step every single thing that was done in this query here on this table is listed in order from start down here at the bottom so it goes in that sequential order there in order there to let us know everything that we've done now in the power query editor there is no undo there's no back button there's no control z capability it doesn't exist however the applied steps area is better because if we ever make a mistake if we ever do something like i don't like that step for some reason it just didn't work for me notice if i hover over you get this little x on the far left of each of those steps you can very simply delete that step right where it exists and start over or what's even better is if you see on the far right of those steps you will see a little gear icon and for those little gear icons you can edit that current step right where it is and any edits that you're doing to that step where they are will then be applied to every single step afterwards automatically so if you're on step say you're down here and you're in step 18 and you're like oh i made a mistake i'm back on step four i don't want to have to redo the whole thing you don't have to go back to step four edit that step here with the gear and then come back down you'll notice all those changes are then applied all the way down through so it's a really great way for us to be able to you know very quickly make these changes and fix changes if we need to all right so here we that's our power query editor quick lay of the land so what we're going to do with this table with this query that we just brought in is we're just do some basic transforms on it to just showcase some of the capabilities here so the first thing we're going to do okay is we're going to take a look at some of the data types that exist inside of these columns now the data types are you could tell they're right here on the top left of each column okay you can see the different data types available there okay there's four of them and what those data types tell you is what power query thinks is actually inside of that column itself for our first one here our source name right now the data type if i select that i click that icon with is a text which generally means power queries identified you know what's in that column those are characters like alpha characters there so it's a text type okay if you come over to product id you'll see product id one two three there's a whole number okay there you have it it's now showing that you see their actual whole numbers there same thing for date it's it's a date data type well because the data in there they're dates right they're identifying it if you come over here to revenue you can see revenue is as a decimal number okay it's identifying there's a decimal in there right it's doing a decent job of doing that now if there's a data type that you know what i see that but it's not really what i want to see it's not the exact format that i want to see you can go ahead and change it like for this one down the middle for zip now a zip code is an identifier column right the geo column that we use in the united states and outside the united states to identify like a specific location geographic location now because it's an identifier column we are not really likely to ever do any type of aggregation to it any type of sum min max average all those things that we do to our numbers right and because of that we don't really want this zip to be a whole number here's an example in the united states if you live in the northeast of united states you are likely living in an area that has a five digit zip code and the first digit of that zip code may be a zero now that leading zero goes away when you have a data type as a whole number because if you think like the number 12 like one two well really it's a zero one two or a lot of zeros and then a one two then a decimal but we don't need to see them because we know they're there they're just not necessary but for a zip code particularly if you have a leading zero in a zip code you need that right if you don't have that you're not getting the right place it's not working so what we want to do on a lot of these identifier columns like a zip code sometimes like a product id if you need we're going to want to change the data type from a whole number to text okay and then what we can do is we can choose what are we going to do with that step are we going to add a new one or we're just going to change or replace the current step that was done there and in this case we're going to go ahead and replace that step you notice that the abc you know we have our data type there for our text is now going to jump everything to the left-hand side it's going to match over on our source name there as well okay so there's the first one that we want to think about okay the the second thing is we have some columns here now when we go ahead and build out a report in power query and then we'll migrate over to our data model and power bi and everything we really want to limit the columns that we're using right we only want to keep the columns we know we want to use for the report that we want to see so what we want to do is a good practice get rid of all the fluff get rid of all the extra that you're not going to touch that you know you're not going to see because it just takes up extra space so we're going to want to do is get rid of some columns that we don't care about and the column in this case that i don't really need to see is this source name column because we already have the name of the country we don't need to know that it came from the source of the csv file for australia for the other countries you can see the filter if you hit load more you can see all of the files that we're looking at okay we don't need to see those so we can just go ahead and get rid of that we already have it over there for our country in order to get rid of a column super simple you can either hit remove column with that column selected i'm personally i'm a right click kind of person i like to have the access right there all in one spot see if you right click you can just hit remove and if you ever make a mistake by the way of removing a column you want to keep guess what you can come over here delete that step it's going to pop right back up so it's a real quick quick way to see things all right so we've gone through some easy data cleansing steps here everything looks nice there okay what we could do is we could just go ahead and say you know what i'm ready to go i'm ready to populate a visual let's go do that so over here on our home tab on the far left i can hit close and load close this out load it in excel let's make a table let's make a pivot table pivot chart something so let's go do that i'm going to hit that get us back into excel here and what we'll see now is we have our queries and connections and it's just going to load in all of the rows from that combined file that we put all into one that we can be able to use to leverage in order to build out you know our table our chart whatever we want to have and so you can see all the rows that are coming in we're now at 1.5 million 1.6 million i can if i hover over i'm starting to see there's a there's an issue you can see that little red x there those of us that know excel you know there's some things happening that was not going to work right we're going to get something's going to break here in a second and if you know excel you know why it's because there it is we have more data than actually can fit on an excel worksheet we have 2.99 million rows of data that we're trying to bring into excel and we know that excel you can't do that right that is not possible to do in excel if we're limited to i'm going to hover over here i believe the limit is and it says our limit yep 1 million 48 000 rows okay so what do we do do we just give up no of course not we do something we leverage something that allows us to bring in as many rows as we need we leverage powerpivot the data model that exists here inside of excel and so when we can we send our data our query here to the data model we don't have that row limit we are able to bring in millions and millions and millions of rows in here in excel to be able to leverage to make all of our charts that we want to see so what i can do is i can choose right here all i did was hover over our world sales there and choose to load to the data model i can also if i want to right click on it and i can select load to right here anytime you see load two here inside of excel you should automatically think load to like where we're loading to the data model okay otherwise known as powerpivot so you could do it there also if you wanted you can select edit now if i select edit what will happen is the power query editor pop-up window will pop will open up again and i can do everything back from there as well so for this case i'm just gonna select load two now when i hit load two i now have an option like okay how am i gonna do this how am i gonna import this data into the data model what are the ways i'm gonna do it what we wanna do to make sure we're leveraging the data model properly is we are going to only create a connection okay we're only going to connect to it we don't want it the data actually to live on this workbook it's actually going to live in excel in the back end okay we're not going to see it here only in the data model so we're going to create a connection to that and then add this data to the data model okay so everything's going to be pushed into there and then i can hit ok and so now what you'll see is we're going to go through the same process again on here on our world sales table or query that we have it's going to start to load in all of those rows again but now what you'll notice is we are no longer going to get that that pop-up of hey things didn't work it's gonna load through all the way all i believe is what two million nine hundred uh nine ninety nine thousand nine hundred ninety thousand i'm choking over my words there there it is okay start to look at relationships are there multiple tables and look we have a little confirmation things are good to go and so now if i want to see all of that data almost 3 million rows all i need to do is here in our data tab i can come over and select manage data model now if you do not have this option this is from excel 2016 and newer and even if you have excel 2016 and newer and you don't see this it might be because your uh your screen is just the resolution is a little bit smaller so you may want to come over to the far right and hit a drop down here and you'll see the little green icon for that that is an option or you have to go ahead and set it as an added okay go through the process and add that in if you don't already see it so just keep that in mind if that's not something you currently see and then if i select manage data model what you'll see is another pop-up right now what we're seeing is power pivot for excel but what you'll notice is we have every single row you can see this is row four if i'm selecting row four there if i come down here at the bottom you'll notice i have row four of two million nine hundred ninety thousand every single row is all is now available to us that we can now use to build out our column our pivot tables our pivot charts anything we want to see now i can verify that i have every single file in there if i select this filter drop down you can see all of them and you will notice how fast the data model is it is nearly immediate so if you want to see leverage your data millions and millions of rows at a very very fast pace you should use this data model here to be able to use all those all together at once now before i mentioned one of the great things about using this full this folder example is that may say next week we are adding in this scenario here we added another country that we're selling to and we want to see all the data for that country here as well so we can try to analyze you know sales from australia to japan or to nigeria or to mexico or anything a long way i want to see that specific country and maybe i add in a new country here or for your sake you might add in like just next week's version of this excel spreadsheet all you need to do and i'm gonna showcase here i'm gonna open up that folder here that i have with my data all you need to do is drop it into the folder so what i have here is i have data for canada okay same we are now you know what we're selling in canada we're going to try to expand our footprint up into canada here so i want to go ahead and get that data for canada and add it to my full report all i need to do i'm going to select it and i'm going to drop it into world sales so now in my folder there i have all of these countries all of them every file there now if i want to see canada as part of this data set originally with excel i have to now copy and paste that add it in or do an append or anything along the way i don't need to worry about it because i did all those processes those transformations all i hit is refresh that's it let the work be done for you it's going to go through the process here it's going to first load in that entire folder into the power query editor it's going to look at all the steps already taken apply all the new changes to any new files or any new rows that are in there and then take that entire process that we did in the power query editor and then load it here into the data model and now you can see it's retrieving all these rows we're actually more than we had before now we're over three million rows and so if i close this what you'll be able to see is now i can see there's canada if i want to verify that now canada is in there as well hit okay we have all canada data it's that quick it's that easy we don't have to worry about doing this over and over and over every single week spending you know monday morning like 9 00 a.m i know i gotta take last week's files add them in do copy and paste get all this ready to go to be able to transfer out just hit refresh drop it in whenever you need hit refreshing look how quick that's done for you nice and easy everything's ready to go cool all right so the next thing we're going to do is we'll just go through some other different ways that we can use the power query editor some other tips and tricks along the way that we can go ahead and do this so what i'm going to do is now i'm going to pull in more data okay that's it we're going to pull in more data from different sources go through some transformation processes and i'm going to showcase some of the i think just simply the cooler ones if i'm going to be honest with you just like the cooler ones the nicer ones the faster ones especially for those of us that do so much in excel on a daily basis and so for this next one what i'm going to do is i'm going to load data so i'm going to go ahead and get data again and i'm going to choose in this case instead of a folder i'm going to choose just another excel workbook okay i could choose here from a database you could see we have our sql databases here we have access that we can pull from all the azure databases that we can have our blob storage all those right you can pull from a power bi data set which we're going to showcase later from an online service or even other services like from the web or other places okay you can do all that from here as well but again for this one i'm going to pull from an excel workbook so i'm going to select from workbook and so now i'm going to go back into our data folder and for this one what i'm going to do is i'm going to pull in this excel workbook called body mass index now i'm gonna go ahead and import that and we'll get this preview window as we got before um to see what we're pulling in from that workbook now for this this file here on body mass index what i've just done was i've collected data about the body mass index of many different countries like actually a lot of countries uh over about a 25 26 year period okay actually i think it's a 28-year period and i'll just all i want to be able to do is i want to see over time what is the average bmi body mass index for say united states for all that entire time period or maybe i want to see the entire global average in um 1997. or i want to just filter and see all of like north america or for africa or for asia and be able to to move those around to be able to see body mass index along that way just for this specific example we're working with so bringing in this file i can do all of that now for this you can see we have multiple different tabs that are located inside of this workbook okay we have all these different tabs that have all different data on them some of them have null some of them other items that we've brought in now if i want to bring in multiple tabs from one workbook what i could do is i can hit here select multiple items and then i can choose whichever ones i want or which ones i don't want in this case we are only going to bring in just one okay our data tab has pretty much all the metric data that we really want to focus on and what you'll see here is a quick little preview of that data too and so now what i'm going to do is i'm going to select transform data again i want to get into the power query editor to go ahead and cleanse up this data to make this actually work for me for whatever report that i want to be able to build so if i hit transform data we're now going to get as we did before the power query editor and so we can see our new table has now been added along with the original table that we had before with all of our countries and if i select the drop down here again you'll be able to see we have every single country we had before now again including canada there it is the one we get we added in from that folder all of it's there still existing on here and now we have our new one so the first thing i like to do when i add in new queries or new tables here into the power query editor is make sure the name of that table or that query like makes sense like actually makes sense for what i'm doing in this case this one's called data which is a terrible name for a table or for a query right it doesn't really tell me like data for what right it doesn't really give me a whole lot of information so what i want to do is i want to rename this so i'm going to come over here on the far right and our query settings and i'm going to change the name from data and i'm going to call this body oh if i spell that right body mass index okay and notice once i hit enter or click away from this area that's also going to change the name over here where it says data so hit enter very quickly there it has body mass index pretty quick pretty easy to do another nice little step for us to have okay so the next thing is let's go ahead and cleanse this data to make sure it works well for our analysis purposes so if we look at this data this table that we're working with we're at you know 200 rows honestly that's not a whole lot that's actually pretty small we've 30 columns if we scroll across the 30 columns you can see we have just the column 9 10 11 at the top here similar to what we might have in excel that might be a b c d e f you know the letters across the top and then our first row we have what looks like actually is like different like years right we have 85 86 87 88 90 all the way across and then we also have our countries here and what looks is probably this is the body mass index for afghanistan for 1980 right for afghanistan for 1981 right this is the way in excel that most of us like to enter in data because it's easy to do this way right data entry in this crosstab format is pretty easy but the problem is if we're actually trying to like understand the data for one specific country over time say i want to see the average bmi for australia for every single year in this data set well for me to do that i need to go ahead and find australia for each column for each that it's here add all those up divide by the total right then we're just adding in more math steps right what we want is make this as easy as possible right i want to be able to do this fast i want to be able to do this in like one click because you know that just makes my life a lot easier and why not make my life easier right so what we need to do is we need to set this up in a better format and the format that we want to set this up is in a way that we can see the country and then the year and then the body mass index for that year and then the country then the country again and the year and the body mass index again and i'll showcase that here with a quick little up a little white board okay and so what we want is we have three what we want in the end is really three columns right we want to have country so let's say usa right then we want to have say the years i believe 1980 was the first one and then we want to have the bmi and it's probably in the us we're probably in the mid like 24 or 0.5 on it was just guessing then the next thing we want to do is we want to have all of usa listed together for the every single year after that so 1981 and then 1982 and then 1983 and so on and so forth and then have the bmi for each of those years across so if we want to filter we say i only want to see usa all we're going to see is the data for usa or i only want to see uh 1981 every country that has 1981 we're just going to see that all of them from 1981. it'd be a lot easier to understand our data for you know analysis or reporting purposes and so what we need to do is something called unpivot now in the past you may have used transpose right take our columns to rows rows to columns use transpose to get this now what in this case using unpivot what we can do is we can set up exactly how we want this to work when we use onpivot we take an original column say for this one for our countries and then we change the others and so to showcase that what i want to do is i'm going to just go ahead and show how to do it now the first step here on one of the issues we have if you can see across the way is we actually don't have the right column headers right we can see our our first row here is actually our column headers across the board so we need to cleanse that out first we need to go ahead and use the first row as headers right here or you can come into the little table icon in the corner there and use first row as headers so now things are set up ready to go make it easy for us and so what i'm going to do is select the column you want to keep in this case we want to keep the country column because that's how we want to see the data across and then what i'll do is i'm going to right click on that column and down here i'm going to choose instead of unpivot this column i'm going to unpivot the other columns and when i unpivot the other columns all of my column headers my years in this case will become their own column it'll be called attribute then all of the data inside of that for each year each cell will go along the way we'll then create its own new column called values and so then we can see everything is set up in an amazing way for us to go ahead and do any type of analysis or report for it so i'm going to choose unpivot other columns and watch how quickly how easily that data is now set up for us to be able to go ahead and filter so now if i want to and i'm going to quickly change the the title of our columns here by just doing a double click on the header there we can see year and bmi if i wanted to see the data only for let's say i'll pick any country in here for cambodia right so i'll just uncheck all these others and choose say cambodia and hit okay now very easily i'm just looking at the data for cambodia i can filter down i can now just do an average of this column and we're good to go or i can do it by year i can clear out a filter here select the clear that filter and choose maybe i want to see only four 1990 and hit ok and now i see every country and the bmi for that country for 1990 only so it's an amazing way for us to set up our data to go ahead and be in a way that we really want to have it for analysis right for a report that we want to build out just for for to understand our data at a better level okay so that was unpivot so now our next little mini demo mini example here what i'm going to do is i'm going to pull in another source okay another different source and this other source is going to be another excel workbook and for this other source the the background of it is we're looking at some some sales table uh data right and the sales data here is going to be on some products that we might be selling and how we can set up our data on our products to really understand you know where we are selling them better how we may be able to better position ourselves in the future to sell these products maybe more or less or whatever we want to see okay so what i'm going to do is i'm actually going to stay here in the power query editor this time and here inside of the power query editor what i'm going to do is i'm going to select new source instead of going all the way back out and coming in again i just select new source here from our home tab i can go ahead and do the same steps we did before okay the same things right so what i'll do is i'm going to come here and i'm going to choose another excel file okay if it was saved as a csv i could pull in a csv or a json that metadata type file a pdf a sharepoint folder you can see all the different options there but in this case i'm just going to keep another excel workbook file to keep it simple and so the one i'm going to do is i'm going to pull in a table from our us sales folder here and i'm going to pull in our bi dimensions so these are dimension tables these are descriptive attributes describing the the the items that we have for sale right not describing any of the sales but the items we have for sale and then i'm going to go ahead and import those and so now we have for this workbook we have again multiple different tabs these three are tabs that we can pull in or we actually have a table that was already created by someone else to be able to pull in as well so in this case we have some options what do we want to see do i want to pull in everything do i want to pull in the table of the products or just the simple products in their raw format the way they were put in it's really up to you if you see here if i select table product table okay already has headers all set up our product tab all we need to do is push that first road a header so you can see it's a quick little step there in this case the only one i actually want to use for our quick little demo here is the product table but we could choose the others as we wanted to cleanse those up as well if we really needed to so i'm going to choose product table and when i import the product table okay it's going to go through and pull those in here we can see i'll refresh that and we can see here's our products okay again it's just a table here describing the products that we have to offer so we have our product id the product the category that product is currently placed in that we have for our organization our manufacturer id and the price of that product and so for here there are a couple things that we want to do to really clean up this table to help us better understand our product first thing again i'm kind of like a stickler for like i do the same things every time kind of in this order that's a little bit of my ocd but hey you do your way i like to fix the name in this case i don't need product table right i don't need it to say that i know it's a table describing our product so if i just put products right quickly i understand what i'm looking at simple as that okay the next thing i want to do is i'm going to look at these columns and what's in this column in these columns is try to understand the data now for you you know your data better than anyone else right you are the subject matter expert you know how this is supposed to be set up so you should know like this doesn't really go well with this or i want these separate or all those things in this case because i am that subject matter expert here because i know this data i know that this product column actually has two different items in it i have our product name okay and i also have the segment in which that product is placed in inside our organization okay in this case all these products are bicycles and so all of these uh in these all season these are all season bikes that you can do in you know cold weather hot weather i'm not sure about snow but probably close but you can use them in all of our different seasons and so what i really want to do is i want to separate this column out to say you know one of them's our product the other is our segment so i know like i can filter down by my segment later on and so what i need to do is i need to split this column and so what i like to do to split this column very simple is just do a right click on the column header itself and come down here and choose split column now when i want to split these columns and you could split every different data type as different options here when we do this by the way okay so for a text column that's what we're looking at right now i have some options here to split this column by delimiter which is any type of character that's out you know a comma colon a space a hyphen a backslash those types of things uh number of characters positions from you know three from the left four from the right um lowercase to uppercase uppercase the lowercase digits of non-digit and non-digit to digit i have those options in this case i'm going to choose by delimiter and for this you'll see the power query editor has automatically identified that we have a a delimiter in there we have a hyphen right here right between the m a and zero one but that's not the one we want right we actually want to use this vertical line this pipe stem delimiter is the one we want to use to separate this column out so instead of having our hyphen what i'm going to do is i'm going to hit shift and backslash to get to my pipe step and so from that i need to say well how am i going to split this column right what am i going to am i going to split it all the way on the left all the way on the right or every single time it shows up imagine some of us have seen this quite often you have a column with maybe you know somebody's address or something just somebody started typing in just data right into like a forum or something and they have columns commas excuse me or colons designating the next like set of data you might have four or five that you know that they don't go together they need to be separated out if you do that by each occurrence you'll get five different columns depending upon how many colons or semicolons or anything or commas along the way it happens now for this case in this specific example we only have it show up once so it doesn't really matter but for me by default i usually like to pick far left and start from there because it gives me a little feel of control like i know we're going from left we all read from the left here so we're gonna do that and then if i hit okay very quickly i split that column i don't do i don't have to copy and paste i don't have to create a new column it's just done right there nice and easy for me i don't have to worry about anything else from that all right so again that's the point of us using the power query editors to just make the general processes that we do on a daily basis easier and faster and so there's a split and so now what i can do is i can just rename these i can rename this first one to product the second one double click there to segment that's good to go like i'm done with that i don't have to worry about it and every single one along the way is now now we see our different segments the different products that go with it all of them that are there okay the next step is we have this category column now for excel when we we know that we have a value here we have all these nulls we largely know that that just means it's going to go with whatever value that is above it right we a lot of us just know that that's how we use it but when we start to put this into a visual we don't want to see null like we want to see the actual value especially if we want to start to filter this down and understand it so we need to populate each and every one of these cells now the old-fashioned ways we have to copy and paste or double-click and it goes down double-clicking it goes down right we could do that however the easy way here in power query editor we could do everything every single cell with one click that's the great thing about power query editor is we can leverage just the ui capability the one click get it done for us this low code environment so what i want to do is i want to take mix and i want to fill every single cell that is ugly i want to fill eric there it is every single cell that says underneath it for mix and then the next time i hit say rural i want that to now be filling in all of these all the way down along the way so every cell is populated so what i can do very very quickly and easily is right click on category come down here to fill and fill down and when i select fill down boom everything's done that fast that easy i don't have to copy and paste i don't have to rewrite anything i don't double click it's just done i'm done and that's exactly what we want you go through our entire data set everything is now set up for us the entire thing is ready to go so i don't have to worry about it i don't have to to think about it i just do it once and i'm done and so if i ever want to come back to this in the future one of the great things that we can do here in the power query editors we can come over to our applied steps and we can say hey i did something here i want to make sure i know maybe in a month's time when i revisit this like what that exact step was or if i pass this off to someone else i want them to know what that step actually did so what i can do is where it says fill down if you right click on a step you can choose to rename that step i can say fill down category so i know exactly where did i fill down so if i have 30 more steps i can go okay where did i do that fill down that fixed this problem oh i don't have to think about it and click on each one i just go straight to it so you can change out those steps it saves in here when i save this file in the future i come in here i know exactly what i did again i want to make my job easier i want to make it faster i want to make it just generally a lot better for me to be able to do all right so let's go ahead and close and load all of these to that data model i want to get every single table all of them into that data model so what i'm going to do is over here on our home tab instead of just clicking the icon i'm going to hit the drop down here and instead of closing load we're going to close and load two again we want to load these to the data model with that world sales table just because maybe we want to in in our organization we want to leverage all those tables all those together for one report that we are building now we're not going to do that here just just through the example for this case but if we want to there's our way to do it and so here we can we can import the data again we're going to choose only create connection create that connection we don't want to get every single row here create the connection to the data model okay the data model power pivot and then if i hit ok i'm going to let it go it's going to kind of load in all the other rows that we didn't have before these are pretty small you can see them there and so now if i open up our data model again manage data model we can now see we still have our world sales okay with our you know our three million plus rows if i clear that filter notice how fast that was by the way i could still i could see my body mass index and my product stable all there all at once and so we can if we wanted to we can start to build say a pivot table off of it we can build other items from there if we add more and hit refresh again nice and simple nice and easy that's the way i like it right i want to make my job easier so i can do other things like in the organization so i can make more money for my company or so i can show my boss like how much better at this i am i need to raise right that's part of the goal too so we can go ahead and do that now i'm going to go ahead and save this because the next thing we're going to get into i'm going to close out our data model i'm just going to quick do a quick save as here and i'll just do it to my desktop we'll do our learn with the nerds demo one okay what i'm going to do is we're going to now start on our end to end demo our end into an example we're going to start with different data existing in a different place we'll come up we'll have this scenario that we're going to work with that we're going to go ahead and populate all the way through to really see how we can start here in excel go to power bi and really go over that full circle all right so let's go ahead and get started with that what i want to do is i'm going to open up a blank workbook a brand new one okay start fresh in this case and so the the scenario that we're going to work with for the rest of our time together is that we uh work for a um a company right that is an insurance company essentially right and so what we want to do with our organization with our company say we're pretty large we want to understand um just data around the country around the united states and we want to be able to position ourselves to see you know where we are as term in terms of medicare right maybe we don't quite offer um insurance for patients with medicare and we want to see hey what do we don't know about that or we're kind of in med we work with medicare we want to see you know which hospitals have the most claims um which states maybe have the most claims amounts right for each claim so like to charge the most for their customers we want to just get a lot of just broad information about data inside the united states surrounding medicare and the money and position of medicare in the u.s and so we have all of our internal data that we use on a daily basis of our own customers our own clients our members but there's just stuff out there that we're not sure about so what we can do is we can reach out to outside sources bring in data to try to understand that for ourselves and so that's what we're going to do we're going to actually go out to the web and we're going to pull in data from the web here in excel and powerpivot or power query excuse me bring it into powerpivot afterwards to really understand that data so what i'm going to do is i'm going to open up a web browser okay i'm going to pull in my web browser here and what i'm going to do is i'm going to navigate to data.medicare.gov okay it is a web page provided by the united states okay for data on as you could tell medicare right so on just health care data and so here at this webpage it's free source of information right you don't have to log in completely anonymous source of information data.gov by the way for the united states is another great place that has like 300 000 different data sets freely available to us to pull in outside data either for uh like dummy data for practice or for like real purposes especially like weather and other things like that and so here on our webpage data.medicare.gov is where i'm going to pull in the just the data that i want to use with the data that i want to leverage for this so i'm going to search for general hospital information like if i could spell information right there it is general hospital information just to get an idea of just a list of all the hospitals that have been registered with medicare you can see it right there right phone numbers hospital type rating address all that good stuff i just want to see where they are like what state what city how many hospitals are out there that actually deal with medicare so i select that i can now see the table here on this webpage and so for that i can now go ahead and download the csv file that all this data is contained inside of you notice by the way it's most recently updated it's like pretty new and so i can go ahead and do that now what i i can do again is download that csv file however i'm not going to because what happens if you can see it was just last updated in january 7th that's what three days ago or no it was a month ago so excuse me what if happens if it's now going to be updated in a week if i download that csv file i'd have to come back here re-download it again when i find out it's been updated and do this all over i don't want that instead what i'm going to do is i'm going to right click on that download link and i'm going to choose to copy the link address now when i copy the link address and i come back into excel i can leverage that link address to pull that data from so i'll come over here into our data tab and then i can choose to either get data right it's down here in other sources or if you notice here from web is a most often used option so i can just select from web right there and i can just paste in the location of that csv file from the web and point to it so every time the data is now updated on the web i now get the most updated version of that all i need to do is hit refresh and all those changes are made again just like we did with our folder example it's the exact same way so it's an awesome way to leverage this so i'm going to go ahead and hit ok and so when i do this as you as we've seen already a couple times we're going to get a preview preview is roughly the first 20 or so rows of that table that data set that we're pulling in okay and then from that i have our options again to either load this directly into excel or to transform data which is the one we want to choose so when i do that here is our power query editor what we're now used to working with okay so i'm going to go ahead and refresh that's a little old there from what i had last time i've done this okay and now what i can do is first step i do it every time okay again it might be my little quirk maybe here i'm gonna rename this i don't need it to say hospital general information like i just don't okay what i wanted to say is just hospitals it's a list of hospitals a distinct list of all the hospitals inside the united states that deal with medicare that's it nice and simple so i know what i'm looking at so maybe if i have like other users that are going to take a look at this later on they will know what they're looking at as well when they pull from here and so now we can go through just some general data cleansing steps we can go through some process here to set up our table to maybe work for us that a little bit better and so there's a couple things that we want to do okay if i look at our columns here you can see we have 38 columns so we have a lot right that we're working with right the first thing is we kind of go through the first couple of these are like okay i see facility name i see address city state all these okay um we have a couple things here well we talked about already the zip code right we mentioned that before particularly like in the northeast of the united states right they have a leading zero right now our zip code is we could see it's set up as a whole number if i showcase our filter here you notice we have some that are four and they're a four-digit because i can guarantee you they are somewhere in the northeast like let me just pick one uh let's pick i don't know i'm picking a random one six zero seven six if i do that where are we looking at oh connecticut i knew it i knew it right new jersey new york connecticut area they are gonna have that leading zero okay i grew up there so i know that and so we we want to make sure that this zip code is changed because we want to see every single digit we want to see that leading zero so let's go ahead and change that out to a text data type replace that current temp that current step excuse me from change type and now we get that leading zero so if we look at northern new jersey or new york or connecticut we are going to be able to see those specific areas that have that leading zero in it okay we'll be able to actually see that when we pull that into like a map or something all right so then we have some other data cleansing steps that we um want to use now i mentioned before that we have a lot of columns here as i scroll through we're looking at 38 columns um over a thousand rows of data here okay but a lot of these columns as you scroll through just count of facilities safety measures do we we're not really caring about that safety we just want to see the medicare data right the raw data there so we don't really need all these extras so what we could do as we did before is pick a column and just remove it or right click and remove it now the problem here is because we have so many that's going to be a process right if we you know control select right we can do that but again that's a process of going over and doing that instead we have a better option and the better option here and this is the fastest way to do it by the way is to come up here and select choose columns because if we select choose columns what we're able to do is decide the exact columns you want to keep everything else goes wet okay so if i do that i'm going to hit choose columns i can now see a vertical list of all of the columns that i have inside of this table so maybe for some of us we know that we have um for some columns we have a prefix tool right some type of identifier for that for our organization for the department or whatever we have that so we can search in a prefix maybe if i do like a hospital or something i'd search for that and i can say you know what i want these that all say hospital in them so i can select all of them right to make sure that they're there so when i come back here they're going to be selected okay or you can do the same thing in search form and say you know what i want those but i don't want these other ones and if i hit and i clear this out now i can see that those are not selected in my full list so it's a good way for us to see exactly what was chosen or what wasn't chosen and it's easier and faster for us to do it so what i'm going to do is i'm going to deselect all these and i'm going to choose all of these first few all the way through hospital ownership okay facility id through my hospital ownership all this extra stuff for what we're looking at right now it's just fl it's not fluff but it's just extra we don't we just don't need and then i can hit okay okay and now that i have okay i've done that i can see i've removed the other columns i've keep kept these and removed others so now we can see we we're now down to 10. we get all the way here but if you look at this one i i see phone number it's not very often that we like analyze data by phone number like i don't really care what a hospital's phone number is if i'm trying to see how many claims they have right i'm not going to like call them up and ask them so do we really need phone number in this case probably not so instead of me doing that whole step over one of the great things again about the applied steps here for power query is if i just hit this little gear icon i can come over here and i can say you know what let's just go ahead and get rid of phone number like i don't like it let's just get rid of that i made a mistake i hit okay it's gone right it's just it's gone that quickly and so if i if i didn't realize this until like the very last step i could do that too and it's nice and quick for me to do it so it's an easy way for me to do that all right so the next thing we want to do is let's look at what we have here we have facility name address city and states zip code county name and all that stuff now when we think about city state in this example here if we look at how maybe we want to analyze our hospitals by city right if we want to say i want to see how many hospitals that deal with medicare claims um and i want to see how many are in springfield you might be like well okay springfield what like springfield illinois springfield new jersey springfield massachusetts springfield new mexico like where are we going and so because we have such generic names for some cities in the united states especially having these as separate columns in a lot of cases can become very difficult later on right so what we actually might want to see is we might want to have one column that has city and state together to be able to more closely identify like a precise location right i want to see springfield m.a springfield massachusetts all right i want to be able to hone in on that one and see all the hospitals that might be there so what we need to do is we actually need to combine this but if i combine it then i'm also losing the individuals so one of the great things we have here in the power query editor is a way to add in new columns very very quickly very easily while still keeping the originals there if we want so i'm going to come over here and choose add column now i can choose to add a custom column i can leverage the m or mashup language that is here in the power query editor i can do something of like a combined text where i'll take the city um i'll do an ampersand i'll do a double quote comma space another one pulling the state i'm using some of my data language there to be able to pull it in if you know it how you can do it but if like you don't know that like you don't know what i just said or you're like well you did a lot of stuff there i'm not sure what he was talking about then don't you don't need it you don't need to know it and that's the great thing here is you can actually leverage the ai here and let it do it for you so i'm going to come over here and instead choose column from examples and i'm going to make sure i choose from all columns and here we now have a little pop-up window inside of power query editor and you can see here what we're going to need to do enter sample values to create a new column so we're going to simply say in this row over here we're going to enter a sample value from any of the columns that we have and it will populate this final row so if i want to see city comma state if i select this first row i can do d-o-t-h-a-n right comma for dothan so i'm going to cross the row here a-l for alabama and once i hit enter watch what happens i now have every single row populated automatically without having to do anything i don't have to retype it it's just done for me instantaneously in the format that i want notice right here in our city these are all caps i just put a capitalized first letter so now i see the exact format i want i hit enter and it's done it for every single one in my table just like that i can put in i can add in a full address if i wanted to i can go even further you can just do all that by leveraging the ai here to help do that for you so now all i need to do is rename this merge to city state and hit okay and i will get a new column here that now goes if you i'll select number eight here alabaster alabama there it is alabaster alabama that's pretty easy to do right that's fast that makes our life a lot easier i don't have to worry about anything from that point on and so now when i look at it i go okay that was cool but what about this city column again like it's all caps like i want it to match city state because i don't like seeing all caps it's a quick little transform on a text data type if i right click on it i can come down here to transform and i can choose to capitalize each word just like that and i that's as easy i don't have to worry about anything it does it for me i have so many amazing transforms here inside of the power query editor that i can do on you know specif especially our text types if we look at our whole number types here we can do transform we can uh that's a text that's not a whole number let's see do we have any no these are all text um so we we have here so we have different data types that we can see our date transforms we can ch say we want to parse out the year the quarter the month the day the week we could say when we want to do that in our transform that we can create all this and really have it set up in the exact format that we want to do so things are pretty good for us all right so what we're going to do is we're going to pause here for a moment because what i want to do is in this uh this next set here is i want to showcase some of the amazing things that we have here from pragmatic works that we can go ahead and provide to you i'm going to pull and make sure my folder here is set up for us provide to you as any type of things that you might want to see in the future if you like to if you like this presentation or any other future presentations we might have so the first thing i want to do is i'm going to come over and open up our web browser and i'm going to come over and you're already in youtube you've seen our youtube page you can see on our youtube page for pragmatic works that we have a lot of amazing vid videos for you okay so i'm going to come over here and i'll come in youtube pragmatic works and you can see on our page and you again a lot of you have seen here we provide all free videos all the time okay that are out there for you to be able to leverage to be able to use consistently some of the other things that we also have available on uh on fora for purchase as well is that we offer some really great on-demand learning now our on-demand learning platform is an amazing place and i think anthony's going to go ahead and put a link into our on-demand learning platform is a way as a place that it's honestly we have so many different courses i'm gonna go ahead and pull up that platform as well so many different courses available for you to take for to really open up your horizons here to understand more about not just what we're looking at today but just everything in general so here we have learning.pragmaticworkstraining.com this is our on-demand learning platform and we have here we have over 70 different courses available we're roughly 17 we have more courses coming in even the next few months here available we have 17 courses on power bi alone that you can come through and leverage to be able to really expand what your knowledge base is here with everything you're looking at now if you scroll through we have some of our free ones from our in a day courses that we've given permission by microsoft to record because we are a microsoft partner so microsoft let's let's get a little bit extra there you can see all of these different courses here available that are that if you want to expand what you might want to know for yourself or for your entire organization we have them for you now right now as i mentioned as we started we are running for this specific session here a 50 off on all of our on-demand learning platform i'm telling you it's usually 4.95 cut that in half that's for you for today okay so if you want to go ahead and get on to that for an entire 12 months a full year access we are now running that right now for this class that we're all working with here as well and so with that on-demand learning platform we also offer a lot of other private and public engagements one of the things that we do is we do boot camps now we offer some public boot camps where you can go to our webpage and you can sign up for a boot camp for um power bi for azure for power apps we're actually i'm going to be creating a boot camp for our for power automate as well that we'll be uh doing pretty soon so we have all these boot camps that are three to four day deep dive in-depth sessions on the entire platform itself on that ecosystem either power bi or the others to really give you a full scale view of everything available to you to really help you understand those specifically we offer those publicly and privately so if you at your organization you might have 10 or 15 of you that really want to get into power bi and go full in on power bi we offer private power bi boot camps that we try to work with you and we'll use examples so try to frame that around whatever organization you might be working with if you're working in real estate i just did one a few weeks ago with the company that that um is in real estate so we try to frame all the conversations of understand real estate data with that we also offer hackathons now what hackathons are are a way for us to really provide you the skills necessary to understand your own data so inside a hackathon what we do is we have you in control of the screen of the clicks every single time you're in control of everything we're here on on our side to help guide you through the process of solving any specific problem that you have we are there to provide you an end-to-end solution in in one day right in eight hours nine hours time um to be able to help you out to fully understand how you can do this again now i'm going to mention we are training company we are not consultants we are not here to just do it for you you move on what we want to do is and we have a lot of our trainers use this teach you how to fish all right we want you to learn this for yourself so in the future you can come back for us a few little questions here or there but you get to do all this on your own you can really build out your own skill base to be able to understand all the processes necessary for this and then from with that what we also have and i'll get my face out of the way here real quick is we also have our customized enterprise training now with our customized enterprise training we really set up classes multiple different courses and classes for your organization specific to you so we'll work with you on setting up classes either power bi or azure or powerapps or whatever you might think t-sql ssis whatever works for your organization we create classes for you we're actually working with a pretty large organization right now uh two actually large we're about 40 000 um members of that organization that we're we're providing classes to on a weekly basis so it's a it's a pretty large scale solution there so if you have a big company you want everybody to get in on this this is a way for us to work with you to develop classes specific for you only for you with your data will create data for you to help your employees get better at that and then finally what we have are is our architect mentoring now for art architect mentoring what it is it's an opportunity for you to work with one of our trainers myself or all the other trainers here on staff to get a way to you know solve problems in right now so with our one-on-one mentoring sessions you can book as little as 30 minutes at a time or as much as three hours at a time as you may need to be able to work with us in that time frame to try to fix a problem right you might not be able to work really hard at something you just can't solve it you schedule a time with us we'll get on a call with you and we'll explain how to fix it and so next time you do it for yourself now for our architect mentoring we do have a minimum three hour purchase and then you can span that three hours out into 30 minute increments however you may want or an hour increment however you want to have that so it is another way for you to be able to just simply get more information to be able to understand the your own skill set to understand your data to be able to really learn more for yourself okay so we are now right at the halfway point into our current session and so what i'm going to do is we're going to take a quick little 10-minute break here and so after that 10-minute break we're going to continue with this current data model with this current example and start to build out our full end to end solution we'll actually add in an additional table here we'll go through some cleansing steps with that other table and then we'll start to talk about how we can bring in power bi and what we can use in power bi to just make this look amazing for us to use all right so we'll take a 10 minute break i'll see everybody back here in 10 minutes time and we're going to just keep on keeping up all right see so all right everybody welcome back hopefully that quick little 10-minute break gave you an opportunity to go grab some water uh some coffee hit uh the restroom or anything needed there i certainly needed to get some water myself so where we left off before our little break here we brought in one table from a web page into the power query editor our hospitals table and here in the power query editor we did some cleansing steps for our hospital table to really set it up to go ahead and to do some of those um you know analysis that we want to see now the next thing we want to do is we're going to add in another table and the next table we're actually going to add it in back from that same web page and one thing to notice and i've seen a little questions here inside of our chat along the way is it doesn't matter if it's a csv file an excel file or even if it's a web table saying like wikipedia or something we have the ability with the power query editor to be able to pull in that data pretty easily no matter what kind of what file type it is now i will say the power query editor here in excel is a little bit more limited than the one in power bi which we're going to showcase here in a moment so just keep that in mind what we do in power bi we have a little bit more capabilities so it is one way to think about you know maybe if you have some other uh sources some other options that you might be wanting to work with all right so what we're going to do is we are going to go back into that medicare. medicare.rt.data.com there data.medicare.gov and i'm going to open that back up i'm going to go back a moment and instead of searching for general hospital information the next one that we're going to want to get is like the the metric data for medicare right so now we have a distinct list of all of our hospitals what we now is to see okay what about the spending per hospital or claims per hospital or all those things that go with you know for you know the organization that we want to you know work for here and so what we're going to do is we're going to search for medicare spending by claim okay so just to get a better understanding of just the general medicare spending right especially with the different claims available and we want to be able to see which hospitals have more spending maybe in different states or cities and also on which claims as well right to give us a better position or leverage a better position inside of you know that medicare ecosystem and so once again we're just going to select that first one in this case it's the only one and once again we are going to select to copy the link address we're not going to download the entire csv file we're going to copy the link address because we want the most up-to-date data we want the the newest version anytime this gets updated so i'm going to go ahead and copy link address and so we'll go back into the power query editor okay and here in the power query editor we are going to add in that additional table now we need to do that at our home tab and from the home tab here the home ribbon at the top we can come on over to new source and we're going to choose other sources and that's where our web option is located now when i select the web option just as we did before we're going to go ahead and just paste in the location of that csv file and hit okay and now we'll get our little preview as we always do okay of about the first 20 or so rows of data that we can pull in and i'm going to hit ok to pull that in here and so now with this data the first thing i like to do again that's a little bit of my ocd i kind of have like these are the steps i take because i like to think things be nice and neat when i work here is i'm going to rename the table and so in this case i'm not going to do it on the far right i'm just going to come over here in our query pane i'm going to double click here and i'm just going to call this our claims table pretty simple pretty easy that's it claims and move on from there and so now we can go through some of the data cleansing steps to set up our claims table to make it actually work for us and so as we go through we could see we have a lot of the actual data that we want to work with in this case okay so the first one we have our facility name we can go ahead and do a quick transform to make that facility name capitalize each word we can then come into facility id now facility id in this case right now we can see our facility id is a whole number data type now if i go back over to my hospitals table you'll notice that our facility id is currently set at a text data type that was done automatically but you will also notice that in the very first cell here in the very first row we have a leading zero for our facility id well if we have a leading zero that must mean that it means something right that's some type of identifier that we need to have so what i want to do is i want to make sure my claims table for the facility id matches that because i want to be able to use this later on to be able to build a relationship between those two tables i want to use this as the key column to allow these two tables to communicate because if you look at facility name here for our very first one southeast health medical center okay we have southeast health medical center we have that match so we now can use this as a way a simple simply to like look up our tables in another so i can go ahead in our claims table and i can change that out to our tax data type okay for facility id now you'll notice on our hospitals table our facility name that was in all caps too we could go through there and transition that to just capitalize the first letter but in this case we're not necessarily needing that because depends on where we pull it from it's okay for what we're talking about if we're doing this in the real world we'll do every single row a column and make sure every single one is set up for the sake of our what we're doing today i think we're okay on that one and so the next thing we want to do is just simply again look at the data we want to focus on the data to make sure we're cleaning it up get rid of extra stuff get rid of if there's any errors or any nulls or things along the way that just doesn't work for what we want to do and if you start to scroll through here and you look at each column here say for period or for claim type what you'll notice is if i just look at the what's inside of that column itself if i come down here to row number 22 on row number 22 what you'll see here for our period column it says complete episode for claim type it says total and then if we come even further to our average spending per episode hospital and state and national that number there if you're looking at that you're like that's a pretty big number it's kind of like an outlier everything around it is much much smaller well what that actually is you can see it here on claim type that's the total so this is giving me essentially a subtotal of this category for that hospital essentially for that specific hospital across the way now if we keep that in there if we keep that in its current state our data is going to be wrong right we have double data this is bad we don't want to have this in there because we put this into a table or chart or anything everything's gonna be all messed up so we need to get rid of that entire total row there to make sure it doesn't mess up all of the the visuals or everything we want to see and you'll notice it doesn't just show up there on row 22 it actually shows up on row 44 every single time we move on to another hospital we the very last row there is our complete episode so we want to get rid of that for each one because we want to make sure our data is actually what it needs to be so in order to do that what we can do is we can filter out that row we can filter out a single cell or the entire row here and so you could choose either do it on your period column or claim type column i like it on the claim type because it looks a little bit better there i'm going to come to the claim type column and i'm going to select our little filter drop-down and i'm going to choose to deselect the total row and when i do that and just hit ok it's now gone and we can see here in our applied steps filtered rows i'm going to rename that okay you can see if i double click it tells me exactly what happened if i right click and rename it filtered out total rows is what i'm going to put in just so next time i can see exactly what that filter was applied on right if i wanted to to do a date filter i can do that as well i can say i only want to see dates between say oh here's our start date end date i want to see after what is this july 1st 2020 but before august 1st 2020. i can choose that and it'll filter out the rows as well i can apply those types of date filters to really hyper focus on a specific time period if i need to right we have all those capabilities here inside of the power query editor to be able to work for us right so that looks pretty good i think that this one is generally set up in a decent way here we can rename some of the column headers here to maybe have it work a little bit nicer like the average spending here it's kind of a a smaller version of that but in this case we're okay with it having it like that it's really average spending per per claim here per episode each episode is a claim just to keep in mind there for each hospital for state and for across the country there you could see for a national okay giving that data and so now we are ready to go we're ready to now go ahead and load this out to our data model to be able to use to leverage and start create some visuals on it so i'm going to come over here and i'm going to choose close and load two once again close and load two now for this we need to say how are we going to do this how we're going to import the data into that data model there okay i'm going to only create a connection and i'm going to add it to the data model hit okay we're now going to see the same section here on the far right and i'll get my face out of the way for a moment so you can see all that okay everything's there we have our hospitals table in our claims table we can see everything that's come in we have all the columns and we have the data source as well so it's giving us all of that information and so now what i'm going to do is i'm going to go ahead and open up our data model okay and inside our data model now we can actually see all of the rows all of the columns here between the two tables now if you want to see the two tables in kind of like a better picture view right and more of a different format what we can do up here on our home tab is we can select the diagram view and in the diagram view we can see our tables here a little bit nicer right a little bit more neat here to be able to build with and so i have our hospitals table and our claims table okay and so what we then have the ability to do is we can go ahead and say hey how are they going to communicate how is this going to work if i want to make a visual that pulls say the facility name from our hospital table or the city state column that we created but also shows the claim type or the average spending i need these two tables to be connected to be able to communicate i need to create a relationship and so what i can do is i can select the columns that i want that relationship to be built on so i will say here in powerpivot or the data model in excel this is actually a little bit more complicated than what we would do in power bi in power bi we can do this much faster much easier and we can really set this up to make it just an easy way for us to do it so we in power bi it's a very easy drag and drop right we can set that up here in excel we can kind of do that the same we drag and drop and we have that there but we have a little bit it's just not as quick not as nice not as perfect okay and so then what we have established here between our two tables is we have this line down the middle that represents our relationship a solid line represents an active relationship we can actually have multiple relationships between tables after an active you have an inactive which is it'll show up as a little bit of a dashed line what we'll also have here is if you see on my screen here we have a one and a little star we also have if you can tell here a nice little arrow there as well now the arrow there is pointing from our hospitals into our claims what the arrow tells us is how these are going to be filtering right called our cardinality how what table is going to filter the other and in this case when we think about our data modeling and we'll showcase this a little bit better in power bi we have our hospitals table here okay this is the one side of the relationship okay the number one kind of gives that away right on the claims table this is the many side of the relationship that star represents many which means we have over here on our hospital table we have the facility name listed one time on that table however that facility name can be listed many times on our claims table right you think about how many different claims types that a specific facility might be offering or might be working with so with this when you think of a one-to-many relationship this is what we want to have when we start to look at our data modeling and so as we build this out further and further and get deeper into data modeling you'll have many of these one-to-many relationships to be able to filter down one to see the data specific for the other and so when we think about the the way that we organize these in our one to many relationship we have in our hospital table we call this our dimension table okay and our dimension table or our lookup table okay is where we are saying how we want the data to be filtered right over here on our claims table this is called our fact table or our data table and so this is this is all of our metrics this is all of our actual like data that we're looking at and so when we think about the relationship between the fact and the dimension table we say i want to see claim type or average spending by facility name i want to see it by state i want to see it by hospital type or by ownership so i like to especially with a lot of the classes that i have people that are new to data modeling reference our dimension table as our buy table so think of it in your other other examples i want to see um our so maybe i'm another table that has all of our like customers or members and i want to see them by gender or by age or age group breakdown or by address or anything along those lines or by a specific month right those are our dimensions our descriptive tables dimension tables describe the fact describe the data the metrics that we're using and if we can set set this up for our filtering to occur it's going to be much faster inside of our data model for us to actually populate those visuals and to do a lot of those amazing calculations that we like to have that we'll showcase inside of power bi all right so now that we have our tables our relationships all set up what we can do okay is we can close out this data model and we can build a pivot table we can just do it real quick right away so i'm going to go ahead and close out our data model and so now i can insert a pivot table here leveraging the data model to be able to showcase maybe if i want to see average spending per hospital by state so let's go ahead and come to insert the insert tab at the top i'm going to come to pivot table and now i'm going to say i want to insert a pivot table from the data model right that's where all the data is now being stored from the data model i'm going to put up my existing worksheet and so now we can see all of the data there that we want to use to populate our pivot table and so in this case maybe we want to see again average spending per episode hospital that's going to go into our values so we're going to get some type of metric a sum a min a max an average uh anything along the way that we might see okay and then i want to see that by let's do it by state and so now we can see the average spending by state and i can say hey over here for our sum of average spending what how do i want to see this data here you know if i want to look at that i can double click and go deeper into it i could see you know now i'm looking at just for florida right we can go through all of these and really filter things down to set up our data even further right we all work with pivot tables especially those of us that live in excel we know how they work well now we've just set that up using the data model setting it up even better for us making it just much easier for us to be able to do okay really good really easy to do all right so now it is time now it is time to take everything we've done here in excel in the power query editor with our data model okay we built out a little sample table there now we can go and take this and we're going to move it into power bi and inside of power bis we're going to start to leverage some of the amazing visuals and capabilities of power bi to really set this apart from anything we could really do in traditional excel so in order to do this first i need to save it then the next step is before you try to import anything into power bi you have to close out this workbook you have to close it out otherwise it won't work i'm just going to tell you right away it's just not going to work if you don't close it up so i'm going to come here and choose file i'm going to do a save as i'm going to put it to my desktop again and i'll go ahead and put it into our folder here and i'm going to call this our um excel 2 power bi model all right and then i'm going to save it and once it's saved i'm going to close it out all together i'll also close out our other demo that we had earlier and then what i'm going to do is i'm going to open up power bi desktop and so as power bi desktop opens up okay what we're going to do is now going to import that powerpivot data model here in power bi now if this is your first time opening up power bi what you are going to get and i'm just going to show up our little splash screen here you're going to get this screen here okay what this screen simply does is gives you an opportunity to see some of things from power bi on the far left you have your more recent sources recent projects okay on the middle you'll have just some some links to videos and things that help you along the way and on the far right you have some places some links to get to the community pages at power bi i will say the power bi community if you hit forums or any of these is very active very very active indeed and so if you have any questions you're confused or anything you can go over to the forum page there and really get a lot of answers like very quickly there it also has one in spanish as part of the forum there too so you can see all those questions and answers in spanish as well so it's i mean it's updated on an hourly basis and people are very active there it's one of the few community pages that i think is just it's set up really well the power bi community is awesome so if you have any questions honestly there's a really great place there or you come over to our youtube page and you can see more videos like this all right so here in power bi what i'm going to do is i'm going to first import that data model that we just made and then i'll showcase some of the things we have here and then get into just making this report look awesome so what i'm going to do is i'm going to come over to file okay hit go from file and now i'm going to choose to import and now i'm not going to import anything a template or all this but i'm going to import from power query power pivot power view and now the only way again this is going to work now here is my excel power bi model is if that excel workbook is closed it's not closed i'm telling you right now it won't show up i actually had taught a class two weeks ago and forgot to close it and i was like scrambling around it didn't work then i was like oh i have to close it for it to work and there it is once you do it it pops up like that quick so go ahead and we'll open that and now you're going to get a warning and every time you do this you will have this warning pop up that essentially tells you you're about to import a data model powerpivot from excel this is a one-way street you do this you can't go back you can't go back into the data model there and work with there it is now in power bi only so keep that in mind okay so this is a one-way ticket here once you do it it's done for good so now i'm going to hit start because we want to do it here in power bi because power bi lets us take that data model and do way more with it than we could back in excel all right so now i'm going to close and so what we can do here with our model is we could just take a look at some of the things that we have currently right now in power bi we are there are three things on the far left here these are the different views that we have we are currently in the report view where we make our report visuals right where we can set up our visuals beneath that we have our data view which allows us to see the tables the columns the rows in that tabular format that we are pretty much used to in excel we can see everything there and then finally we have our model view the model view is very similar to what we just saw in the data model in excel in that diagram view where we could see the tables it's just kind of like little squares there rectangles to see the relationships between them okay so that that those are the three big items here for power bi that we're going to be leveraging and so what we want to do is let's just go take a look at those views real quick and then see everything else that we're looking at so i'm going to go to the data view and inside the data view here if i select the table you can see there's our claims table okay we can see all the columns all the rows all 52 731 of them come into the hospitals table you can see the same thing okay all of them there okay then we can come into our model view oh there is our data model okay we have our if i'm gonna put it in the way that we saw it on the other other view there and in excel we have our hospitals and our claims tables okay we have our one to many relationship notice here it's just a little bit more interactive it lights up a little bit here in power bi now for power bi because relationships here are way easier to create if i did this wrong for some reason this facility id i did it wrong all i need to do is if i right click i can just delete it like that that relationship like it's that easy and you're like oh actually oh i wanted that i made a mistake i didn't want to delete it oh all you need to do is again facility id facility id it's that fast it's way easier to have here in power bi if you want to understand it double click on that line you can see the columns from each table the cardinality and the filter direction as well okay it's really it's actually a much better much easier to do all that modeling side here in power bi than rather in the data model in excel i will say okay so let's go back into our report view and just let's just start building our report let's let's make it we now have the data set up we have done a lot of the the back end cleansing steps let's go ahead and make our report because when you think about a power bi report there are really four phases of everything we go through the first is we are doing our data cleansing right we're bringing in the data our data discovery we're gonna go into the power query editor we're going to get rid of columns we're going to transform and do all that back in stuff then we have our data modeling in our second phase in the data modeling phase this is where we do our relationships where we start to build out hierarchies if we want to where we can add new columns using data analysis expression language our dax coding language here in power bi our third phase is our visualization phase and in the visualization phase what we do is we build out our visuals we actually set them up to like we look at the picture of the actual visual themes of the table the chart the graph all that kind of stuff and then finally our fourth phase is to publish it out send it out to the world at power bi service so others can view it we just hit a quick button there there's our publish button and we can do it that way we've already done the first phase and most of the second phase has been done for us we did that back in excel so now we'll do a little bit of our modeling and our visuals together and then our final one we'll just send things out so let's go ahead and let's do that right now so what i'm going to first do is here in power bi is i want to set up my report now in ahead of time i've decided you know as for my organization for our reports i want to create a background that everybody uses to just insert items it just looks nice and cool nice and neat that everybody can implement into their reports so i created a background i did it in powerpoint by the way and saved it as a file as an image file that i'm just going to import here to then land all my visuals on and so what i could do for our just to edit any type of visual or our report itself i come over here to our little paint roller format icon here okay and so if i select the paint roller icon i can come over here to page background and i can choose to add an image and that's what i want if i want to change a color i can do a color real fast but in this case i created one so i'm going to go ahead and do that and if i'm going to it points right to our folder here that i have everything saved in and there is our medicare claims report background i'm going to open that up and so what you'll see is oh right now it's nothing and this happens all the time if this happens to you don't be afraid all you need to do is fix your transparency right now it's 100 transparent which means you can see right through it but if i bump it down to zero there's our background again i created this in powerpoint it's not perfect it's not amazing but it's it does the trick right it's nice and easy now you can have one really if you have somebody in marketing or somebody in your team that's really good at you know they like all that kind of stuff they can make some amazing background templates for you to land your visuals on and so from here we can start to add in some visuals right we can start to populate some visuals and so for those visuals there's a couple that i want to see okay i actually want to see two different little card visuals a card visual is just simply a visual that has a number of value that just displays that number throughout the process but i want these card visuals to show metrics that we actually don't have like i want one to be like our total claims all together i want to see every claim just on the total amount of claims that number right now we can't really do that because we don't have a column to do that for us so i have to create that what i also want to see is i want to see like the average spending per claim as well i don't really have that i have average spending per episode hospital but it does a sum it does some extra stuff so i want to set something up or it just shows me the average every single time in order to do that what we actually need to do is we need to leverage dax this data analysis expression language okay and dax allows us to create calculated tables like a date table calculated columns like using data that's already existing creating a new column from it like an age group breakdown or something like that and calculated measures which are these aggregations that we automatically set up to be dynamic to change depending upon any filter that might be in place with that specific measure so if i have that measure on a card visual and i click on something else it'll be dynamically calculating depending upon the other visuals any slicers any other selection i may have on the page itself and so that's what i'm going to do we're going to create two different calculated measures to show both total claims filed and the average spending per claim all right so the first one we want to do is total claims just total i want to see all of them what i'm going to do is i'm going to select the claims table here i'm going to place it on my claims table and to create a new measure once i hit the claims table what you'll see is under table tools you have an option here to create a new measure it has this little calculator icon it helps us out you could also right click on claims table it does it too and i'm going to select new measure and for our new measure in this case i'm going to just bump this up a little bit bigger i'm going to call this measure our total claims file okay nice and simple and the function that i'm going to use to calculate the total claims filed is the count rows function and what the count rows function does okay is if i'm going to go back for a second here i'm going to start typing you see our little intellisense here and i can go all the way through what account rows function dot is if i hit the little i icon there it tells you counts the number of rows in a table well if we want to see the total claims filed count all of the rows every row in our claims table represents a different claim filed there it is right that's exactly what we want so we're going to count rows which table are we going to count rows in if i hit a single quote that always calls out to a table using dax i'm going to count the claims table and that's all i want to do close out my parentheses that is it as simple as that i don't want to i don't care about anything else let me hit enter and so now our first calculated measure has been created and so i can create a card visual i'm going to hit card and populate a card visual here i'm going to move it around similar to how we have in like powerpoint you can see we have these little red lines here that tell us from the middle and all that i'm going to put it all the way up here in this corner here and i'm going to drag in our total claims file right on top of that visual nice and simple right and so now what i can do is i can format this visual to show me the actual number so some other ways of looking at visuals here in power bi which is why a lot of us go to power bi because the visuals are so cool we come over with the visual selected to our little paint roller icon and we can decide exactly what we want this to see now what i'm going to do is i'm going to come into data label and i'm going to choose for my display units right now it's at auto like 53k right for a thousand i'm going to change that to none because i want to see the full number what i also want to make sure i'm doing is i want to make sure that our text size in this case i'll drop it down to 30 i think it's a little bit big because i want to make this i'm actually put two cards here what i also want to make sure i'm doing here is this number 52731 i want that little comma separator right i want that to be in here right after for 52 000. and so if i want that here in power bi similar to what we saw back in excel what we can do but here's a little bit faster and easier if i select that column there select the measure in this case what i can do is once it's selected click the word up here in our measure tools all you need to do is choose to add in the comma separator it does it very quickly for us it's nice and simple for us to be able to do okay so there's one let's do our second calculated measure and again this second one is i want to see the average spending per claim all right just i don't want it to sometimes we could see and we've had one by the way automatically created back when we created that pivot table in excel we have a sum of average spending per claim here i want the sum i just want the average right that's it if i this one is going to get added up it's not going to work for me so we're going to create our own calculated measure so i'm going to right click this time on our claims table and choose new measure and for this new measure again i'm just going to call this our average spending per claim nice easy title for us to understand and so this time i'm going to use the average function which your excel user you should know that one right i think most of us would understand what the average function will do just get an average of the number and so now what we're going to do is we're going to do an average of that average spending per episode hospital so essentially it takes an average of that average it's going to give us the same value anyway so we don't have to worry about it so first thing we need to do is call out to which table okay that claims table and you can notice the intellisense here in power bi with dax is already giving us where we're doing it like where which columns we have available to do an average on so we're going to go to the claims table and actually this first one right here so i can just use the arrow keys on my keyboard or i can click on it with my mouse and it'll automatically grab it and then i can close that out and hit enter i now have my second measure so our second measure is created and now let's go ahead and create another card visual and i'll put it right next to that other one i'm going to go ahead and put this measure right inside and so for this one i'm going to do pretty much the same type of cleansing steps here i'm not cleansing the formatting steps here so i can have everything match pretty nicely so i'll come into format i'll go into data label i'll do our display units i'll do it to none okay what i'll do is i'll bump this down to say 30 again so it fits we can already see here we have a comma and a decimal okay we can see that if i select average spending per claim it's seeing hey that's a number there there's a decimal number but we know that spending right is spending money so what we can also do is we can say i want this to be shown as a currency now the currencies here we have far more options than what we would do in traditional excel we can choose all of these different currencies available to us if we wanted to do this so we can do the uk pound we could do the euro the yen we have the swiss franc you know think about all these that are available to us if we wanted to use that currency in this case obviously i'm going to use the uk dollar because we are in the united states but you can use the canadian dollar as well if you'd like so i'm going to have that and you'll notice that throws in the dollar sign and it'll automatically change our decimal places to 2 as well so that's set up ready to go we're good to go with those two measures and so the next thing we're going to do is just create some more visuals here to create our report right to populate this report to really tell the story of everything we want to see okay so for these the next one is i'm going to create a bar chart a clustered bar chart okay and for this clustered bar chart i'll put it right here i'm going to pull that right there okay and i'm going to populate this with a couple options here so i want to go ahead and pull from our hospitals table i'm going to do state and city state okay so we're now creating a bit of a hierarchy as well so we can click in and drill down into not just the state but the city state in there we can set up a quick little one and then in values what i'll do is i'll do our average spending per claim measure that we just created and so now we can see for each state the average spending for a claim nevada has the most spending if you hover over we can see that number right there if i wanted to in my format i can add in a data label and so for that data label now gives me that total here so it's just a quick little blurb there and i can choose for this data label to i can choose the display unit i can choose you know none for that one as well so everything's set up proper there the same way we saw the others things are looking pretty good okay we can do that right we can have everything set up for us um so now what the next thing is let's keep making more visuals right and because we already said this average spending measure we made it an average we made it a currency i don't have to go in and format those every single time i drop them into a visual that same format is going to showcase across the board for every visual which is another great reason why we want to create calculated measures because we get to decide right away what we want all right so another visual we can create is a map visual so i'm going to throw in a map right in the middle here and for this map i'm going to make this really big okay and for that map what i want to do is i'm going to put in our city state in our location and then what i'm going to do is i'm going to go ahead and put our average spending in our uh size there okay so what you'll see is on our map you should see the larger the bubble the more spending but we have a problem with this map we're only seeing three cities right we see in this case niagara falls uh kenkidy illinois probably butchered that and mount vernon illinois right and so the problem is power bi is a little confused because because the city state column that we chose if i select it right now our data category right now is uncategorized powerbi doesn't know like what city state means that we created remember we created that one so what we need to do is we need to come in here and we need to say what type of category our city state is actually going to be and in this case we're going to select a place it sounds a little strange there but once we do that we can see that now we have everything ready to go and so we can hover over and we can see all the different city states in the average spending per claim in that specific city if i select say nevada notice i now only see the data for nevada or if i go to texas i can all only see the city states in texas we have that little bit of cross filtering that occurring there and i can click away now notice it's not perfect we do have some issues there because we're using state abbreviations so we see lebanon new hampshire thrown into uh lebanon the country um that's because we're using the nh instead of the actual full word new hampshire if we did that then we would be able to um we see everything a little bit easier i'm gonna take my face out of there i think a couple you were in the chat there say uh can kiki oh i saw that there you go thank you um appreciate that i'm gonna get my face out of the way so everybody can see i wanna thank anthony for coming in with a whiteboard to tell me that um and so now the next thing we want to do is we're going to insert a a slicer okay in power bi we have all these amazing custom visuals custom slicers custom things that we can import into our reports to be able to leverage in this case i'm going to actually come in and i'm going to insert one really quick to showcase one of the ones that i really like a lot called the chiclet slicer in order to do that i came over and select get more visuals here my little ellipses there get more visuals and i now have a pop-up to the power bi visuals at our app source sites that i mentioned earlier today and this is going to do a kind of the same view of like the microsoft store okay so if you familiar with that you're gonna see this and then you can see all of the different custom visuals available to us here in power bi okay and so anything with this little blue uh ribbon there tells you it's power bi certified so that means that it's past all the tests and things from power bi team at microsoft to be able to be certified i will say some organizations don't let you use ones that aren't certified so check with your company for that but we have a lot of really good ones the one we want to choose again is the chiclet slicer and for the chicklet slicer what i'm going to do is i'm going to select that one and notice it's powerbi certified because it's a microsoft product by the way and then i'm going to choose to add it and it's really simple really fast if i just go ahead and add that one what you'll notice is i now have imported a visual now for you if you're wanting to see all the other visuals just go ahead and browse through all the the custom visuals there at the app store site there to be to be able to see everything um available to you to be able to pull in there's a lot of really cool ones there to pull in and then once it's there i can see there's my chiclet slicer it's ready to go i'm going to go ahead click away from my visuals in the background and add in my chiclet slicer and i'm going to put it all the way over here okay i'm going to add it in right there and so now for my chicklet slicer what i'm going to do is i'm going to actually drop in my claim type on there so now i can filter down all this data by claim type as well and for this to edit this visual a little bit better i'm going to come over and format again and in my general drop down i'm going to make sure our orientation is vertical but i'm going to change the amount of columns i have from three to one and when i do that now we have just a nicer feel okay for this and then from that what i also want to do is i'm going to come into our chiclets here our text size make sure that's good there's our 10 point what i want to do is i'm going to pump this up from 28 i'm going to make it all the way to 80. and notice it fits pretty good into this little slot here now you might say well what how is this different than a regular slicer well the one difference between a chiclet slicer and the generic slicer we have here in power bi is one it's just it looks nicer you have a little bit more like a pill feel and you can do multi-select a lot easier and notice how if i select the home health agency i'm now seeing our measures here that are going to be filtered down okay by average spending total claims i can then do it by you can see this other chart here has been filtered down too now massachusetts has the most spending for home health agency if i go to like inpatient and deselect i can see nevada once again you can choose that and then i can choose in nevada again and i can go there if i wanted to drill down okay in nevada if i'll select here i select this single down arrow and then i click nevada i can now see all of the cities inside of nevada as well so another great feature here our drill down feature that allows us to simply see the data just at a better level nice and neat now one of the other things we have here in power bi is we have the option here if you come into view to set up a theme now what the theme allows us to do is to really um change the way the report looks change the way the color scheme the font all these other options here how they look in order for us to be able just to make it just feel better for us inside our organization so we have a couple of just generic themes here in power bi available to us one of them that i like to point out is the colorblind save theme so anybody you may know that is colorblind i think the stat is like eight percent of all men like all men are colorblind um so if you have somebody in your organization that might be qualified you might not know it or not you can do that and it changes out the theme there to change the colors to make it a little bit easier for them right to be able to see the data better in a different way what you can also do is you can create your own theme right you can customize your own theme to change this up however you want or you can pull in themes from the themes gallery that have been on the power bi at a website there for our app source you can pull in the themes gallery to get more what you can also do is if somebody creates a custom theme you can actually import that theme if they share it with you you can import that file and see all the color schemes and everything for you see a lot of organizations say hey we have our own font we have our own colors we have our own style we want to use those in every report so they create a theme with those and you can bring those in for each report you do so i'm going to select browse for themes because i actually created a theme for us today our medicare claims theme here i'm going to open that up and for this one i just chose some other colors right so now i have like a more of a golden yellow color you see our font color is a little bit different things are just you know i just kind of picked things along the way that i wanted to see and so we can go ahead and choose different things here i'm going to go ahead and see a montana there okay an outpatient we can see total claims filed 33 okay in that six-month period from july to december of 2020 average spending per claim 461 so now we get a little bit more just knowledge of the data that we're working with this helps us make better decisions right that's the whole poor point of understanding our business intelligence so we can make better decisions about the data about what we are doing so we can you know either make more money or just have some type of change right we want to tell a story with our data to do something in the end not just for the sake of having it all right so we are ready for our last step here we are going to go ahead and publish this out to the power bi service and then from there we'll go in full circle bring it back into excel so in order to publish this out to the power bi service what i'm going to do is come to our home tab i'm going to make sure first actually before i do do this i'm going to save this right i'm going to make sure i save as i'm going to go ahead and i'll put this and i'll call this our medicare claims in 2020 okay and i'll save it to the same location of the others okay save it there and so now what i'm gonna do is i'm gonna hit publish when i select publish okay what i now need to do is go ahead and choose where i'm going to publish this report and where you do this somewhere called a workspace now workspace if you want to think of it as most basic term is a landing location it's um a spot right it's a storage unit to save not just the report visuals the report itself but also the data set that populates those report visuals so every time you publish one out you get two things that go into the workspace together now everybody has a my workspace every single one of us if you have a pro or a premium license you also have the opportunity to create your own workspaces that you can share from you cannot share from your my workspace it's only for it's a dev location it's a sandbox if you will to be able to use so in this case you can just use your my workspace for me i actually created a workspace because you can see i've quite a few and i delete these like every month um i created a workspace just for our event right now okay and i'm going to go ahead and point this power bi report to that and so now what is happening is now we're taking a package this entire report and the data set and publishing it out to the power bi service at powerbi.com and that specific workspace and so what i can do is go ahead and open it up online and it's going to pop up of course it always pops up on another screen when i do these and there it is okay i'm now at powerbi.com okay app.powerbi.com and i can see everything inside of my workspace i still have notice it takes a snapshot of the current like location of what i've clicked on i still have the ability to change this make my phone look at hospice and i don't want to look at montana anymore and i want to look at say florida now i see everything in florida and here i am up in jacksonville okay middleburg is right there at orange park there's jack's beach you can see we're looking at just that location there and if i click on say one location on my map notice i'm now hyper focused on that as well so it gives me more just more information that we're looking at but all here at our workspace now for our workspace here we're not going to go super deep into the workspaces and reports here for the sake of this uh conversation but just to give a general lay of the land of what we're looking at here on the report this is the report in our workspace we have a bunch of options that we can take we can enter file here download the file we can manage permissions to our our report and the workspace we can embed this in sharepoint or in a website we can also export this to powerpoint and pdf we are going to do the actual analyze in excel here in a moment we can share this out with others and chat about it in teams and actually have this report in a team's chat and talk about it from there we can have a email subscription to it every single day or a week whatever we get an email to a link to this we can edit this and add more visuals or to take away visuals or we can also see the lineage view which is one thing i really like to show because the lineage view is awesome to tell what this tells me is how this final report got here we have our sources we have our data sets we have our report okay so again that's our sources we have our data set that we worked with this is our model right then we have our report and if we wanted to next we would build a dashboard and a dashboard is essentially a um a bulletin board a a cork board where we can pin visuals and images too that allow us to see multiple visuals from multiple reports if we want and then from there after that we have an option if you are using anything but your my workspace your power bi app right where you can choose to create an app right there there is your full lineage from source to very final product inside of a workspace you can only have one app per workspace but as you need to make sure you have either a pro or a premium license to even get to that point as well okay so the what we want to focus on for this conversation is how we can then take everything we have here in power bi and we'll go back to our report there all the work we've done and then go back into excel leverage everything here go back into excel and see how we can build from that even further especially for those of us that we're just we like excel we're comfortable in excel i want to hold on to that for a little bit so what i can do is if i come and select the actual workspace itself you'll notice here we have our list of workspaces in the far left bottom here i'm going to select the workspace here we have in our options here we have our report and our data set on our data set we have our options to do our data refresh as well right now i can do a manual refresh right now refresh all of it go back to the source give me the most important the most up-to-date data go through all the transform processes in power query everything in the report visual will now be changed and i can see it immediately or i can do a scheduled refresh or if i hit scheduled refresh i can choose to do a scheduled refresh on that data however i want to see it now if i have a pro license i can do eight scheduled refreshes per day per data set eight per day per data set if i have a premium license which is much more you know costly i can do 48 refreshes per day per data set so keep that in mind if you want to go down that road as well and so it's really easy you could say i want you at a time i want to say maybe i want to do a refresh every maybe like 6 30 before everybody gets in the office every day or looks at their reports and just add more along the way if you want to have them and you can consistently have more and more refreshes to keep your data up to date there in here in the workspace so there's a good option all right i'm going to go back for a moment hear me back to our workspace what i want to do is i'm going to select our data set and here on our data set what i want to do is i want to take the data in our startup data set the model and be able to put it back into excel now in order to do that first i need to make sure i have up here on the far right corner i think it's like right above my head here in this picture we have our little download icon here select that and with that download icon i want you to go ahead and select analyze in excel updates because with that that allows you to go ahead and download analyze in excel it allows you to get the newest version of it if you've already done it or the first time and so i'll go ahead and i'll do it real fast i've done this multiple times so i'm just going to put this into my downloads here it's going to replace it and that allows us to go ahead and run our analyze and excel update so mine pops up on another screen there and i'm going to do a repair just repair anything that might have gone wrong i'll quick do a quick install you'll notice how fast it is too and i'm done once that's complete i now can select this option here to analyze this data set in excel to bring it back into excel where i first started and i can build out pivot tables and pivot charts and whatever i might need on this data set all back out all right now i'm going to go ahead and select analyze in excel and what it's going to do is going to create an excel file i need to say well where do i want to have it i'm going to go ahead and put it inside our folder here i'm going to hit save and then once it's done i'm going to just open it open it right away and so once i open it and enable editing and there it is content you notice i've done this a few times i know where it's going to pop up i can now create my pivot tables pivot charts whatever i might want to see here back in excel so let's go ahead and do that let's just make some here in excel to kind of go do that full circle once again to really get you know everything we want to see in the format that we like all right so for our pivot table here we have our pivot table fields okay this is the same thing we saw earlier but now we're automatically connecting everything's done so here uh let's say for our pivot table we want to see um we'll do let's come over here and do state and city state let's see if we can reproduce some of our visuals from power bi we have state and city state a little bit of an uh a hierarchy there i'm gonna just collapse these real quick collapse the entire field and then what i'm also going to do is i'm going to take one of our measures here and i will do let's do average spending per clean okay we'll do that measure there and so what you'll notice here by the way when you do anything from a data model here you have two different sets of options here for your selections here we first have all of our tables okay these are our dimension tables this is these are the tables that we're pulling in okay how we want to see the data we also have our measures okay you'll notice it has a little bit of a sigma there that tells you we have a measure these measures that are put it there are the ones that are created using decks we've we created those either using dax or when we're earlier back using the data model creating our pivot table first okay so any you see the difference between them okay you'll just know you cannot take any of these and add them to values okay the values can only be measured any type of aggregation so keep that in mind as you're pulling through okay so these tables cannot go there you even get this little icon that kind of tells you what you're looking at right tells you what can go in there just keep that in mind and so now we have here is our pivot table cool right that looks fine right it's something we can easily do let's say we want to oh i have an idea let's say we want to see one of those card visuals again remember we had that card video in power bi that showed total claims like all total claims that were filed for each state or whatever we selected on i want to see that but you know here in excel we don't have like a card visual what we can do is we can leverage the data model to actually bring that in here so what i'm going to do is i'm just going to select any cell here say i'm in f4 right now i'm going to go ahead and call this my total claims i'm just going to give myself a just the title there okay i'll expand that real quick okay and then for g4 what i'm going to do is i'm going to use a formula here to call out to the power bi data set to be able to populate and bring in that card visual use that measure we did before to populate in the cell and we're going to use a cube value formula what a q value formula does if i just start typing in it returns that aggregated value from the cube the cube the data model right if you have a background in in t sql ssis or anything of those you can you know a little bit about the cube you could it's the data model we're presenting we're gonna be pulling into here that was automatically created with power bi that's one of the great things it's automatically created for us we don't have to create the cube it's done i'm going to hit tab and so now i need to describe the connection okay here's our little intellisense tells me what i need to have in order to grab the connection i'm going to use double quotes here and once i do that you can see that i have only one connection right now to be able to um have everything uh ready to go for us here okay and so i'm gonna go ahead and hit tab to go ahead and do that all right and so we now have our connection i'm going to close that out with double quotes and then hit a comma now for the commas now saying okay what do we want to bring in okay what is the next expression that we want to see i'm going to use another double quote to call it to the cube and so you'll see that we're now going to populate our items in here okay what do i want to see well in this case i want to see my measures right i want to see the measure i created so i'm going to scroll down there and select measures and i'm going to hit tab now which measure if i hit a decimal there which measure do i want to see well i want to use our total claims filed measure so i'm going to go down and select that one and so now i can close that out with the parentheses the double quotes excuse me and the parentheses and if i select anywhere outside you will now see that as it populates i can now see our total claims filed 52 731. to verify that that's back the same thing we saw in power bi let's just go to our power bi report we can close this i'll deselect our outpatient okay we'll go up a level here total claims filed 52 731. there we have it that's exactly what we want we want that verification we want to see that same value there so now we can see similar to what we had in power bi back here in excel okay cool that's a cool thing to do right that's nice to have well let's say we want to do an additional feature let's say we want to have a slicer we want to be able to slice down by maybe like claim type and so we want to see average spending per state for each claim for maybe for inpatient or for hospice and then we also want to see this number also dynamically change as well similar to what we had back in power bi so let's go ahead and do that so i'm going to come here i'm just going to select a place i'm going to insert in a slicer okay now for this slicer what we need to say is okay where are we going to get the data from right which connection are we going to use i'm going to use our cube okay all right here i'm going to hit open for that and so now for our slicer i need to say okay which column here are we going to be using as the slicer in this case i'm going to use our claim type and then hit ok and so now we can see our slicer right there okay and if we select one item say carrier it should filter down our table but we have a problem and if you've used slicers before you're probably well aware of how to fix this but our problem is they're not connected right we haven't established that report connection here in order for the slicer to communicate with our table and with our measure that we just brought in so luckily there's a quick fix for that okay if i right click on the slicer itself what i can do is i can come into report connections and now i can just say yeah i want the slicer to work with the pivot table and now i can hit ok and what you'll see is oh there it is we've now gone through and now we can have i select each moment each option here things are now dynamically changing looks good we still have another issue this measure here that we created before it's still giving us the total of everything what i want is i want to select carrier not only does it do i want it to slice down here for our pivot table but i want this to show how many total claims for the carrier as well too or for outpatient or hospice as well so quick little fix for that what we can do is we can come in here we can select that cube value formula and when we select the cube value formula we just need to add in an additional field over here right before our closing parenthesis if we just put in a comma okay and with that comma just start typing in slicer what you'll notice is it pops up for us hit tab and there it is so now we want this to work with the claim slicer that we already have if i click away aha now that's filtered down too so it's a really nice little way to be able to identify all these different items here we can bring everything back into excel that we maybe you know we like in power bi i'll be honest right it's a little bit faster a little bit easier in power bi but if you're still holding on to excel and you're still like look i like everything powerbi has to offer but i still i do a lot in excel i want to be able to use some of those features here's how we can use both power bi and excel together to be able to really get to the final solution that we want to see to get to that final product that we know we absolutely want and so things are looking good right we have everything in here if we wanted to add in say a pivot chart we could do that too right we can come into insert we can come over here to pivot chart let's see where is our pivot chart um there it is pivot chart choose a pivot chart okay what are we going to pull from okay i'm going to pull from an external data source and the connection that we're going to choose is from our cube right that we brought in from power bi there it is i'm going to open that i can do it on the existing worksheet here and eventually it'll populate through and i'll get my face out of the way because i can hear people screaming at me to get out of the way i can feel it not here maybe my spidey senses and so now i can um if i wanted to i can just populate a pivot chart maybe i want to see uh total claims filed here and i can do it by um claim type and i can see that right and i can go ahead and get rid of this total because we don't really need the legend there and so i can see everything there as well and we notice and this is a weird little data set it's the same and this is accurate it's just a weird way they're all the same if you if i go through these slicers you'll notice that they're all total claims it's a strange little model here but we could see that that's all now being populated and we can see that there and so if we want this to filter down as well well right click report connections add in the pivot chart there okay one or more slicers that's okay we have to change the olap there we'll have to come in through here and change that later on but we'll have the ability to go ahead and add that in add in data for that pretty easy now i will say for some of us they're like wow do i have to go through the whole process do i need to go through this entire stage step by step from excel to power bi or just all in power bi publish out and come back through this to be able to do this the answer is no there's actually a fast way to do this if i come in here and open up a new tab at the bottom what i can do is if you come into insert if you want to just do a pivot table from a power bi report you can just do it from a power bi report you can just say straight away i know someone in my organization i have one in one of my workspaces that i want to leverage just choose from power bi and again i'll get my face out of the way here you'll see all the different data sets we have options to pull in any one of them okay you can pull in all of these i'll pull in one from let's say one of the the public classes i teach the modern excel analyst class in the day and you can now pull in maybe you know and bring in that here you don't have to connect that to it's already there as long as you're logged in and that single sign-on you know using your azure active directory sign in you can then pull in and populate all these other ones from any power bi data set that you are currently connected to it makes it just a nice way of being able to do all this to be able to see everything along the way so it's really quick really easy way to populate everything to be able to showcase everything as we pull through all right so last phase in our last 10 minutes 15 minutes together what i want to do is i want to save this and then i want to bring this report with these visuals that i just created into power bi i want to go back the other way too i want to take all these visuals and put it to the power bi service just as i did my power bi report so let's go ahead and do that i'm going to do a quick save there it is and so now what i can do if i close this out i'm going to come back into powerbi.com powerbi service i'm going to go to my workspace select the workspace itself and what i'm going to do is i'm going to hit new and when i select new i have a few options here i can create a new report based on any data set so i'll just select here any data set on any workspace by the way which is a cool thing that's a new feature so if i have you know 17 different workspaces i can choose to build a report here from a different workspace if i want so that's another cool thing we can do i can have a paginated report we can do scorecards i can build a dashboard from here which we'll do a quick little one i have a data set a data flow which is like the power query editor in the cloud a streaming data set but i can also at the bottom here upload a file and if i choose upload a file i can then choose what type of file do i want to have well right now the file just say it is on my device right it's a local file where i'm going to go to here is inside of our folder there i'm going to choose that medicare claims on that we just saved and closed and if i open that up i'm going to choose here to upload it right here into power bi and there it is there it is right there i can go to the workbook i can select it here and what i can see i might get a warning here by the way it happens every time okay about the query refreshing the data you can just hit enable them look what i get to see our pivot table pivot chart our measure and our filter and our slicer it's all here it's in the same place so it's now we can do the same thing we've gone full circle once again bringing everything back here into power bi service which is what we want right we want to be able to share this out in a much easier way no longer do i need to email out that file i don't have to send it to people i just drop it in here and so now what i can do is i can say you know what i want to i'm going to pin this i want to create a dashboard to allow maybe my boss or others on my team on our like weekly or a monthly meeting that we all have to kind of go together and talk about what we're doing right i want everybody to see this so if i come over here in the top right i can pin this okay and what i can do is i'll just choose one here i'm going to pin this to a dashboard okay and so right now we don't have any dashboards in our workspace i'm just going to create one for our learn with nerds dashboard and i'm going to pin that visual right there okay it's actually that's only going to give me that i want to take the whole thing because that's there it is i want to pin that one the whole thing there we go so learn with the nerds dash if i can spell there we go i'm going to pin it all right so now i pinned it to a dashboard i'm like well what does that look like i want to see it let's go ahead and let's pin some other visuals from our other report and i'll showcase what that what that looks like here go back to my workspace okay you can see our dashboard is created we have another thing created there i'm going to come into our report here and you know what i'm going to pin a couple of these visuals i'm going to go back up i'm going to get out of our little drill down here for a hospice i'm going to get out of here okay let me see i'm going to go back up why is it let me go back up let's see let's see maybe it's on our visual here nope i'm zooming the wrong way and there it is it should be giving me that there it goes i'm not sure why there it goes finally a little bit of a bug there and so maybe i want to see uh this whole thing okay this entire one okay i can take this if i hover over it you can hardly see it because they're kind of tucked in i can choose in the far right corner here i can choose to pin a specific item now because of the background i have it's a little tough to see but you can see that there and i'm going to choose to pin our average spending or if i then want to say i want to pan out this map too i can take that i want to pin that to our dashboard and i can then say you know what actually i like those but i want the whole thing i want the whole report page itself if i come over to these ellipses i can pin the entire thing in doing something called a live pin and see all of it and so now i've just gone and picked some random ones here if i go to that dashboard i can see all of those visuals right there all set up and now here on the dashboard i can actually choose to move them around i can say you know what i want some of these on the side here i want to move them in different areas maybe i want my live pin to be up there i can see how things are looking here and set up all of these and now if i select one of these visuals i then get to jump back to wherever it was created and so now i'm going to come back into our excel file here once it finally loads through it looks like there it is and it jumps in so what a dashboard is is a great place for us to be able to look at and leverage everything that we've put together inside of a workspace and what we can do with our dashboards is we can have multiple different dashboards inside of a workspace we can have 10 15 if we want and we can update those dashboards pin unpin tiles as we want to really understand you know what we want to showcase and then if we're like okay dashboard's cool we like that but what is like what's this lasting thing i want everybody to see everything inside of our you know our workspace here what we can create is we can create an app and if we go ahead and select app we can just do here's our demo here just you have to create a description here go through the process here i'm not going to go through all of it you can change the color scheme if you want maybe there's our green for excel select publish app we can have this final resting place here for everything we've then created thus far we have our dashboard with all of our tiles okay everything we've pinned we have our reports our power bi report we have our next our excel report our workbook that we had and we can see everything in this one it's just a nice and neat place and one of the great things about a power bi app is there's a mobile feature you can download it for your phone either you have an android an apple or windows phone or anything all you need to do is get the app from your app store download it and you can now if you log in you can see all of this and so especially on the on the dashboard here one of the great things we can do is we can ask a question about our data use the q and a and you see we have a couple different suggestions here what if i want to see total claims by states as let's do a matrix and that shows it right there and i can see all that and it also even has a little bit of a chart inside of it or if i want to do total claims by instead of state i can choose this by city as matrix now i have the same thing and so we have some conditional formatting already set up for us we have a way to just now ask questions in real language here about our data to be able to populate everything in here ready to go for us super easy super nice we don't really have to worry about it it's all done for us and so everything we've done today coming from starting with our demos just looking at the power query editor doing some basic transforms really looking at you know our data and just this this grad this general format here getting everything done we then took it from you know the power query editor created our data model from that data model we realized you know we can do some connections between the two tables we can really have those relationships set up we can export it into power bi add some amazing visuals that exist inside of power bi that will allow us to to leverage what power bi offers us then we can create that report publish it out and do so many things with it here on the power bi service go back into excel if we want some cube values or pivot tables pivot charts come back here and create our dashboards our app really have this full long-term solution not just for us but for our entire organization and often i get the question hey okay now i know all this what do i use like do i do i only use power bi now because everything we did today in excel we can do in power bi in the power query editor we have that option we have that opportunity to do that right we can pull in our power bi report and here in our power bi report if we go to home we select transform data guess what we get we get the power query editor we get everything that we've done today so we have all those options here in power bi what do i do which one do i choose well the answer is both all right it's not one or the other it's both together it's if you want something done ad hoc today tomorrow this week i need something quick i need to get it done excel is probably your answer right that's a quick little solution for you but if you want a long-term solution for business intelligence that you can come back to over and over and over update all the time have this beautiful array of different visuals and tables and things that you want to see power bi is your solution power bi is the way to go because it gives you that opportunity to really make this work for you and because you have the power bi service you have your dashboards you have your app you can use this as a as an amazing feature for the entire team or the entire organization to be able to leverage wherever they are on the go if they need hear all we can work together we can do this collaboratively we can have everything ready for us to be able to go right away at our fingertips so i do want to thank you all for uh joining me today hanging out with me taking this journey from excel into power bi and to really understand some of the amazing features that exist in both power bi and excel the power query editor really our power pivot or a data model and how we can leverage all the existing knowledge base that we all have that we're starting with that you've worked so hard to get you can leverage that and to really take your skill set and progress you can go forward with it you can start to learn and understand power bi and really get it and let it do all the work for you i do want to remind everybody that because you are a participant here today you are going to be given access to a uh 50 off our on-demand learning platform so go ahead and check that out it's it's an amazing place we have um new videos coming out you know just about every month we are updating a lot of our our courses as well so you'll see some of the newer features as well particularly with power bi um so please look forward to that and i again it's going to be an amazing thing that we have we have more and more courses coming out just it's it's a really cool place for us to have um again thank you everybody for joining me uh i did see there's quite a few of us from around the world so that is awesome to see i absolutely look forward to seeing all of you or meeting all of you virtually or in person if that ever happens again um and being able to connect right please feel free connect with me on linkedin on reddit as well here on youtube we have videos all the time i look forward again to working with every single person here um and really you know seeing how we can build out our knowledge base here with both excel and power bi in the future well for now thank you

Channel: Pragmatic Works

Views: 375,614

Keywords: power bi training, data analytics, power bi reports, microsoft power bi, beginner to power bi, intro to power bi, power query, power pivot, excel to power bi, power bi tutorial for beginners, excel in power bi, new to power bi, excel and power bi, excel vs. power bi, excel and power bi training, excel from power bi, power bi excel, power bi excel tutorial, power bi excel tutorial for beginners, excel training, pragmatic works, intro to excel, beginner excel course

Id: gjnnqsdvAc0

Length: 177min 35sec (10655 seconds)

Published: Thu Feb 10 2022