ULTIMATE Power BI Tutorial 📊 Beginner to Pro Course (2024)

Hello and welcome to our session today our learn with the Nerds powerbi beginner to Pro my name is Devon Knight I'll be your guide through today's session this going to be a lengthier session as we really walk you through all of the intricate parts of what powerbi can do now you may have seen some of our learned with Nerds powerbi beginner to Pro sessions in years past we've actually done it two previous Years now at this point but as you probably realize for those of you that have been working with powerbi for some time powerbi

changes on a very regular basis so because of those changes we have to re-record this session on a regular basis as well but I have good news for you we've made some additions and enhancements to this year's session to bring you even more than what we have done in the past in the past we've used the exact same data set not anymore We're bringing a new data set to the table through this session so if you've seen our alarn with the Nerds powerbi beginner Pro session in the past guess what this one's going to be

totally new for you and not only are you going to see a new data set that's focused on education we're also going to have different teachers different trainers joining us through each of the components of the powerbi life cycle so you'll get to experience multiple Speakers through each of our individual sections that we're going to be covering now for many of you you're completely new to powerbi so all this is going to be new exploration new things for you that you've never heard of or seen before and the big reason why you're going to choose

a tool like powerbi is to help you and to help your organization make better datadriven decisions any decisions that you make that are significant within the company Should obviously be made Based on data that you actually collect and so throughout this course we're going to be looking at how do we collect that data organize it and then present it to eventually make decisions with it whether that decision is made by you or others in the organization that's going to be super important to what you're going to be doing and solving as you build your powerbi

Solutions now most people when they think of powerbi they Think of it as a data visualization tool and it most certainly is that but there's a lot more that goes into powerbi than just building visuals and that's what we're going to be exploring today so let me briefly go over our agenda for the today we're going to start off with planning and thinking through what is the bigger picture of what we're going to be covering in the design of our powerbi Solutions we'll be doing that with Alison Gonzalez then Next we're going to be covering

our data cleansing or data shaping section of the class with Angelica chukan where we'll be walking through how do we pull in and connect to the data we're going to be using but also how do we make sure that the data we're bringing in is actually accurate following that we'll be jumping into our data modeling section we'll be doing that with Amelia Roberts and that whole section is FOC focused on how do we make sense and collect and organize The data that we just brought in so we'll be focusing on how do we connect the

various data sources together for example and we'll be also getting you a little Peak at how Dax the data analysis expression language works during that section as well then we'll be moving on to the data visualization part as most people again think of powerbi as a data visualization tool we will most certainly be covering that and we'll be doing that with Greg trec where we'll be Walking through a number of different ways that we can build really in engaging reports we'll be covering a few of the data storytelling capabilities with inside of powerbi and you'll

also get a quick peek at how you can do co-pilot within powerbi during that section as well finally we'll be wrapping things up with Allison again Allison Gonzalez is going to rejoin us and she's going to help guide us through the data sharing phase of powerbi so how Do we take the results of what we've done throughout the majority of the day and share that with others as well as how we can do things like schedule data refreshes and we'll talk a little bit about security as as well all right so obviously we have a

lot of head of us here so make sure you get your caffeine we're now going to be jumping into our first session with Allison that's all around planning and designing your powerbi Solutions we'll see you [Music] there well welcome back everyone and I want to introduce you to Allison Gonzalez one of our wonderful trainers Allison's been with us now for 3 years she actually leads our powerbi team of trainers so she is not only an expert herself but leads a group of experts thank you so much Allison for joining us and uh yeah absolutely this

is our our first kind of demonstration a little bit but we're going to be talking about Planning a powerbi project we'll also get you familiar with the things that you need to have ready to go for today's session we'll be doing that towards the end of our time with Allison but first Allison I think it's probably important to talk a little bit about the why like why is it important when you start a powerbi project to have a plan why why why can't I just hop into powerbi and start doing things what do you and

we'll get into the details of this but why is It important to have some kind of plan first so we see this all the time right when we do mentoring and help people out so often we see right they they get some data they've been tasked with building a report so they're kind of they should like well I have this and they're in a kind of semi Built Report their data is there but they haven't really done anything with it because they don't have a goal they didn't have a plan they just opened up the

program clicked a few Buttons and then they don't know where to go from there yeah so every project and this is really the planner in me and I have a million notepads and everything but I think this holds true for everyone who is working on a powerbi report that you always need to think what is the end goal right what am I trying to accomplish with this report what do I need to solve what do I need to find out right you need to know what the whole goal is what you need to do so

that way You can actually do that right no one wants to be given a project and have this ambiguous goal with it right so when you're starting a powerbi project you should always look to the end and think what do I want to get from this what am I trying to achieve and then you can work back from there right so you can understand what am I trying to solve so what do I need to have right if I need to solve this question well do I have the data that will tell me the Answer

to that yeah a lot of times you might want to answer a question you don't have the data for right exactly and so if you go through the process of right well here's my data clean it up bring it and do this stuff to it and then you realize you don't have the data you need you got to go all the way back to the beginning yeah so understanding what you have to do and then what you need to successfully achieve that is so key and helpful to have in the very Beginning so knowing your

purpose really mapping out what do I want to solve so if we think for our report that we're going to be building today okay this is drum roll this is all about Education data which I think is so fitting because we have so many former Educators who are now amazing powerbi trainers that will be going through this process and share little like teacher insights as they go through with this so we're working with math and reading data right yeah math And reading test scores right so think of like standardized testing that you might have and

the bigger picture of what we want to analyze today is uh let's let's give you a little fake scenario by the way all the we're going to be looking at today is fake it's not real so don't overanalyze it from that regard but the bigger picture of what we're trying to solve is we have this National US national um tutoring program that we're trying to launch and we want To determine is that tutoring effective towards better math and reading scores we have a hypothesis hopefully it is but we want to make sure that the data

actually hopefully supports that and so we're going to be going through a number of things to determine that now you mentioned something earlier I think was really helpful is you probably are going to come into this with some general questions that you want answered at the end of it so what are some of the kind Of questions that we're going to want to answer at after today's session yes okay so we've got a list must thing always be prepared so we want to see which school districts are doing best we want to see what state

has the best reading scores we also want to find out is tutoring having a positive impact that's a really good one to know right um I also want to know does tutoring in the morning have a better impact than in the afternoon right if you're offering multiple um Options of something right which one's the better one we also want to know um what impact to extracurricular activities have on test scores which I think is so interesting and that's a whole other rabit for another but I think those are some fantastic questions to that we should

be able to solve with our data set yeah and keep in mind too you know building a powerbi solution is a bit of a process so after our first set of demonstration we're probably not Going to know the answer to these questions right away it's it may not completely uncover itself until we get to the end where we're starting to build data visualizations as we talked about in our intro there's phases to a powerbi project there's phases to the demonstrations we're going to be talking about Allison's going to talk more about those phases here in

a few moments now from a project standpoint let's say uh we're talking to folks that are back at Their work their their place of work uh generally you're going to have someone that's a subject matter expert in the data you're working with it may be you maybe not you yeah so what how do you kind of work with that person to derive the information you need to answer these kind of questions yeah so that's such a good segue into this point because once you have an understanding of your purpose for your report right you know

what you need to achieve you see all Right here is the data I have can I answer these questions well maybe you're at that point where you're looking at your data and you're like I don't really know what I'm looking at or I don't know what questions to ask I don't know what I'm trying to achieve here and that's where that subject matter expert is so valuable now like said sometimes you are the subject matter expert right they're turning to you for your knowledge but other times right you might be tested Especially in larger organizations

like hey we need to solve this build us a report you need to then find who is the subject matter expert and work with them right it's to be a collaborative experience right if we want to have an amazing report as our and output then we need to not just be thinking of oh I'm going to make an amazing report it's going to look fantastic it's going to be great we need to think of okay the people using this report how are they Going to be interacting with it what do they need and really work

with the team to gain those insights from them so that way you're giving them the best output right your data could be coming some from some really obscure locations right maybe it's coming from a company SQL Server maybe you have to go to a specific website download a file and then store that file in SharePoint or somewhere else maybe it is just files just hanging out on your desktop which I Could be you know at fault for a few times but you always want to think through what type of data am I pulling in and

what is the source we'll go over a really cool feature in powerbi that allows you to pull your data from really anywhere right um and we'll be looking at that when we get in but you just need to know where is my data and do I need like a username and password to go to like access that data yeah that makes sense all right so for today's session You're going to need a number of things one you're going to need the powerbi desktop uh we're going to show you where you can download that also make

sure you look at the description of this video you'll see several links that will be helpful to you not just how to where to download the powerbi desktop but you'll also find links to the class files uh a lot of the class files you can also find on GitHub we'll be sharing that as well uh but then long term if you want to Have the completed version of the files all of kind of additional information we're providing there'll be some class files that you can download so Allison let's first start with the powerbi desktop uh

again link is in the description but Allison what would they be doing once they follow that link awesome so you can easily Google like a powerbi uh download you and that will take you essentially to this page which is on the Microsoft website and that Would allow you to go through the process of downloading this I I would suggest though and you can walk through this download process and it'll let you pick the right kind of plan for your computer this is a free download which is amazing right anyone can download this little caveat here

you have to have a device that's running Windows so if you're on a kind of Microsoft computer or something that's natively running Windows you're fine you can download a Powerbi desktop totally for free to do your own you know processes if you're at home but if if you're on a device that's like a Mac for example you would need to be running another program that allows you to run Windows there's a bunch of them out there um and then that would allow you to download the powerbi desktop completely for free we are going to be

in the desktop for this entire version and we will go to the web version of powerbi at the end of our Session but you will need the desktop to follow along now the other option to download is just go to your computer go to the Windows store the little Microsoft store and that will allow you to also download the powerbi desktop the benefit of that download is that it will stay up to date because powerbi updates every single month so it's always good if so for some people though you can't access that you'll have to

go to your organization may have a specific place Right we work with so many private organizations where they're like hey here's our store essentially that has all of the approved programs or you have to get permissions for that so feel free to pause this if you need to go get that downloaded but otherwise you can just click the link that we have for you down below and that will download a version for you and you can always update it later on so once we have the powerbi desktop downloaded you run through the Process it'll have

a nice little wizard to walk you through the next thing we need is our our files so we have these conveniently stored over on GitHub do set this all up so that way we are able to point to data on the web that all of us will be able to access as we're building um and moving through and then of course the full files are also stored um so you can get to those later on um yeah if your organization blocks GitHub for any reason I don't think many do Block GitHub because it's s so

widely used at this point but if yeah if your organization does these will also be part of the class file downloads um again check the description I'm sure many folks are asking right now where do I get the link where do I get look in the description of the video uh you can always pause the video as well remember even though we're we're doing this uh here it's you can um you can always go back and and and Rewind us if needed Here all right so we've gotten a little bit of a peak of where

we're going to be finding the files that we're going to be using we're not actually using these yet so the files that you see on the screen right now the student data student testing data those will be part of our second session that we're going to have with Angelica uh at least the student testing data will be and then we'll be touching on student data later with Amelia but um just hold on to those Links for now until those points but what we're going to do is we're going to flip over to the powerbi desktop

now and get you a little familiar with the interface here uh this is assuming you've already installed the tool again feel free to pause if you haven't gotten the installation done yet but we're going to give you a little bit of a tour and then we'll also highlight there is at least one preview feature that we're going to want to have you turn on so Allison if you could guide us a little bit through the tool get people help help people get familiar with what areas of the tool they're going to be using under different

scenarios awesome all right so welcome on into the power VI up so this is your powerbi home view this is the first thing you see when you open up the powerbi program if you're on an older version you might not see this you might just be already dropped into the powerbi desktop but what we are going to Do is we are going to start off with a blank file right we're going to walk you all through the process in our session today of building a report so we're starting with a blank report and then we'll

connect to our data in a little bit in those next few sessions once we kind of show you what's that right I know the hardest part sometimes of learning a new program it's just figuring out what do all these buttons do a lot of buttons what do I click to Do stuff so in here all you need to click on is blank report and that will drop you on in to the powerbi desktop and you'll have a blank report and then we can move on from there so I'm going to go ahead and click on

that blank report and then this is going to be your screen now one of my favorite saying that I always say in class is Microsoft loves options because you will see the same button multiple times in a lot of different Places so we're going to show you essentially one way to do things just know that there's probably at least five ways to do almost every step in powerbi and that's why we have a lot of buttons because right they're just trying to make it easier for you to connect to so what we're going to do

first is we are going to do a little tour around I'm going to show you all of the important buttons that you need to click on and talk through when in the powerb life Cycle we would want to click on these buttons and then we're going to talk about important feature where if you're looking at this and you're saying my screen looks a little bit different we will cover that so first things first right you open up a blank powerbi project and you're like what do I do how do I get started well your very

first button is always going to be get data that allows you as I earlier mentioned right to go out to wherever your data Lives and this has what are called connectors that allow you you to understand essentially it looks like the back end of your data makes sure it's like hey if I'm going to a Excel file or if I'm going to a data on the web somewhere it's going to understand the structure of your data bring it back nice and intact and then you're able to use it in your report so the first step

first thing you do when you open up a blank powerbi project is bring in your Data we do that with the get data button the next step is cleaning up our data and that is done with this transform data button now the transform data Button as well as the get data button these make up the first phase of the powerbi life cycle and this is the data Discovery phase so in data Discovery this is where you are going to establish your connections right you're clicking that get data button and you are going to find your

data right this Is where you might need to put in a user your name or a password if it's like on you know you might need your Windows login or uh your 365 just to connect to it and then once you've brought it in then you are going to clean it up now Angelica is going to walk you through this in a few minutes but cleaning your data is done in a tool called Power query now some of my Excel people in here you may know power query because power query originated in Excel right Right

and then power essentially came from Excel because we had all these cool components and then they're like you know what let's make a whole program from it so power query came from Excel and now also lives here in powerbi to help you easily clean up your data with simple clicks once your data is all cleaned up then we're going to come over here to our left side kind of we have some buttons here in this navigation now these buttons are called views and then All the way over to the right these are called pains so

depending on The View that you're in you're gonna have access to different pains okay gotcha now for us as we walk through we always kind of want to start bottom and work to the top right so we're going through and we'll see this as we go through today really we're doing a lot of technical pieces and a lot of technical kind of stuff to our data before we ever get to our visuals and a lot of people might think You know I'm in a visualization program I should be doing my visuals first right but this

steps that you're going to be walking through in the order we do it today is really key to making sure that you have a good output with and the easiest time um going through so what I want you to do is you're going to start with these essentially these two bottom views we're not talking about our Dax query view some of you if you're on an older version might not even have that We're not even going to touch it today we're going to start essentially on the third one down and this one here is your

model view and then the one above that is going to be our next one that we go to and that is called your table view now this used to be called the data view which I think that's just really confusing because you have a data View and then all the way over to your right you also have a data pain so I think it Just the name got a little confusing so they renamed that but these two together these make up your second phase of your powerbi life cycle and that is your data model modeling

phase and in data modeling right that is where you are going to bring your nice cleaned up data from powerquery and then if you have multiple data sets you need to build a relationship because you can't communicate if you don't have a relationship so if you have date in one Table date in another table and you want to use it together in a visual if you don't have a relationship it's not going to work and Amelia is going to walk you all through we're we're going to hopefully see that problem there yep all right so

you're going to build relationships and then you are able to do one of my favorite features in powerbi which is Dax calculations so Dax is the language essentially your formula your coding Language that is built into powerbi so remember how we just said powerbi came from Excel Dax formulas are so similar and identical to the process so you through a little bit of writing formulas where you write them right you have a formula bar just like you do in Excel and how you write those out but doc stands for data analysis expression going to emphasize

that X there and that language is what really Powers all your calculations you Can build a lot of stuff with it um and you'll see that a little later on so now at this point you're you have cleaned up data you connected to your data you cleaned it up in power query you brought it in built relationships if you needed to if you have multiple tables of data you then did any analytic calculations on this with Dax right wrote those formulas you needed to see now we get into the say the other fun part that

I find now some people you are all going To have a favorite part to the power B life cycle definitely leave us comments let us know what your favorite thing to do is in powerbi but we have now reached the visualization St agage right this is a part that probably most of you are like I thought I could just come in and do this but we need to do everything else first so this top view which is your first view this is where probably was where we're at right now where probably most of you are

this is called Your report View and here in your report view that is where you're going to see your visualization pain so all the way over to the right side right here you have all of these little visual icons right you have bar charts you have line charts you have pie charts our least favorite you have tables matrices right all these great visuals so in your report view where we are currently this is where you are able to build your report so this is your data Visualization phase and this right is where you build a

report now a really important piece of information I think a lot of people get confused with when they first use powerbi is that your interface and everything with this is all tied to one report so just think right if you're in word or you're in Excel or you're in PowerPoint or any other Microsoft program all of those buttons right that top ribbon is all tied to that one thing so just like with Word if you want to start a new document right you click on a new document with powerbi anytime I want to start a

new report I would start a new desktop so everything is tied to that so inside of here in the power Bay desktop you will be building one single report at a time but to that report I can add multiple pages and on each of those pages that's where I'm adding all of the visuals so I can build a 10-page report that has 20 visuals on every single page if I really Wanted to do that right you can have a lot of fun with this that's helpful understand so the context of what we're building with inside

of the powerbi desktop everything is here is considered one report can have multiple Pages multiple visuals on each page and then we'll talk later about dashboards because they are different than reports so we'll talk more about that later I'm going to follow up with you and we'll do a little combo about what that actually Is so let me ask you this so now that we've got we we've covered these first three phases we're going to pull in the data through phase one we're going to kind of organize the data in Phase number two phase number

three is pretty obvious we're going to build some report visuals then what's last how do I get this to people where they can actually use it yes so our last phase is the publishing phase and this is where we are actually going to share collaborate And work together because everything we've done up to this point we can do totally for free on our own in the power VI desktop once we've created an amazing report we want to share that with others right maybe we've solely been tasked to make a report so we can use this

so how do we get it there right so clicking that publish button that is what takes us essentially our final phase which is data sharing and in data sharing this is where we are going to publish to the Cloud to the web version of powerbi and that is called the powerbi service and so we are going to go to powerbi.com super easy URL nice nice and simple to know and then once we've published it there and we'll talk more again at the end I'm going to come back and I'm going to tell you all about

publishing right you're able to collaborate with others talk about where and how is a powerb service organized right how do you find your stuff on there and then when you're There what can we do right we can share we can collaborate with others we can give people access to certain things right I can set up security if I want some people to see certain things not other things right you can get it really really locked in and then you're also able to set up data refreshes this is a really great thing in powerbi where I

don't want to have to manually come and click that refresh button every time I want to update a Data Maybe I have a massive data set and I need updated insights right I don't want to sit here for 20 minutes while it updates so we're able to set data refreshes so it will just whatever schedule we set it will go get any data bring it back run it through and boom our report is up to date all right beautiful so now that we got these different phases hopefully understood again we'll be exploring these more in-

depth throughout today's session we did Mention this we kind of alluded to this early on we are going to be teaching with a particular preview feature on um mainly because it's a major feature that's hopefully soon to be released and we want this video to have a long lifespan so we're going to be turning on preview features now uh not all of them necessarily are needed for today but Allison talk us through how do we turn on a preview feature and then we already have it on uh so folks are seeing it it May be

different than their screen but after we get it turned on maybe briefly talk about how it's different than the previous yes Method All right so you if you are following along and you see you have your visuals right all of these tiny little icons that are showing right a decm tree versus the pie chart versus the area chart right those are your visuals that we'll be able to put on our page later on you may also notice and some of you may have this this as well That I have a section up in the top

of my home ribbon for them now this is a feature that is a new feature it's in preview mode because remember I told you earlier powerbi does monthly updates so before they just launch a feature out there they'll always put it into preview mode so people can try it out get used to it and give feedback before it's launched for real so this feature is coming soon it's going to be the future of what it looks like some of you may Already by default have it turned on if you don't I'm going to show you

where you can see all of the preview features as well as this one in particular which is called on object so to find your preview features to find all of these amazing updates that parbi is running regularly all you have to do is go over up in your top left corner go to that file button when you click on that file button all the way at the bottom you're going to see options and settings when You click on options and settings all the way back up you're going to click on options so you're going to

go file options and settings and op options and that's going to pull up a window and this has so many settings in it so many different settings there's even like a global setting for all of you know your changes as well as just this current file we're just going to look at one thing which is your preview features here in preview features you might have Different ones turned on than I do and that is totally okay right it your version of powerbi might have different preview features than mine right just depending on where you're at

and the previews that they were offering so the one thing that I do want you to follow along with is to have your on object interaction turned on because that is going to give you your the same exact look and feel that I have it's going to make sure your buttons align with mine And just make it a little bit easier to follow along with all of the trainers as they go throughout the day right um as you're working through the next process so preview features super easy to access from your options and settings you

come in here see the latest great to see all the new cool things that they are trying out um click through some I will say anytime that you bring in a new feature when you hit okay you will be prompted to restart right you just did to restart Power Pi you don't have to restart your whole computer um so just a quick close reopen you don't even have to save anything um and that will have those new updates so that way their screen will match our screen as we go through and have a really good

time yeah and I'll mention this pre this feature has been preview for quite some time so if you don't see it listed here it likely means you're running an extremely old version and again you know look in the Description download the latest version that your company allows at least um and then that way you'll be caught up to what we're going to be showing today don't forget of course this is recorded you can go back pause us watch this another day if you need to come back and finish it later uh we have a pretty

lengthy session ahead of us we have some great speakers Allison thank you so much for getting things kicked off guiding us through how to get started here telling Us a little bit about the interface that we're about to spend several hours in um that's super helpful again thank you Allison for our introduction here and we will see you in our next session with Angelica if you want to watch this session as well as many of our other learn with the nerd sessions make sure you sign up for our community plan to our on demand learning

to have free access to all of those sessions and more and don't forget they are ad Free sh and Sh well hello and welcome back to our next section of our class I'm now joined by Angelica chukan who's going to be taking us through our data cleansing or data shaping if you will uh part of our class and real quickly introduction to Angelica Angelica has been with us for a number of years is an amazing trainer in powerbi and focuses a lot on powerbi certification so if you've explored our YouTube channel before just Explored our

training before you've likely seen Angelica's face as associated with the powerbi certification so Angelica we are shifting gears here a little bit we gave everyone an overview of what we're going to be talking about today now and done some planning as well but now we're going to be getting into the weeds here and really our first Hands-On step with powerbi which is data cleansing so talk to us a little bit about the importance Of that process a lot of people think of powerbi they think of it as a data visualization tool which it is but

tell us a little bit about this data cleansing step that we're going to go through yeah so so inside of powerbi you are able to go through and clean up your data get it in that optimal format for reporting but what we often find working oneon-one or working with others is that this is a very overlooked part of the report development life cycle and so Hopefully we'll make it a bit easier for you to understand and see the importance of that today yeah my big anecdotal story here around this is I was doing consulting at

one point created a beautiful report shared it with some Executives at a company and the only thing they could focus in on was they saw one data point that wasn't right and it basically ruined this meeting uh they focused so much on that that it was a big distraction from what the ultimate Goal of the report was so it just shows you though how important it is to get proper data cleansing steps taken care of first before you eventually go and build some reports on top of the data right right absolutely so important so the

way we're going to get started is we're going to use a special tool right that exists with inside of powerbi but it exists with inside of other tools as well tell us a little bit about the tool we're going to be using inside of Powerbi yeah so the tool we're going to be using today is inside of the powerbi desktop but it actually opens up as a separate window and that's known as the power query editor so the power query editor is the tool you want to harness when you are cleaning and shaping your data

and power query is also built into Excel as it's roots in Excel so if you understand what we're doing here today and you're confident you'll be able to apply those skills into powerquery Editor and Excel as well that's awesome so we know that we're going to be starting in the powerbi desktop you learned a little bit about the powerbi desktop in our previous portion of the class now we're going to be uh assuming you've already got it open right we we got you we got you to that point earlier we have the powerbi desktop open

on our screen Angelica so how do we get started what's the first thing that we're going to do to get started here absolutely so Once you are ready to begin the report develop cycle you're ready to hit the ground running and build this report you're going to open powerbi Desktop and you want to start with this section right here this button is the get data button this button is a two-part button that will allow you to access if you hit the top part a full list of the data source connectors native in powerbi as well

as a list of common data sources that are used so you can see from the Ones here we've got some common ones Excel work powerbi semantic models data flows data verse SQL server and more but if you don't see the one that you're looking for here you can select more and you will get a full list of those data sources that are sorted by the data source type now we are going to connect today into a data source that we have out on the web so we're going to use this web connector here to begin

this process so we'll go ahead and select web And then select connect here now all right so so what we're going to do is that we shared this data source with you a little bit earlier with uh Allison this data source is one that we've put on GitHub we're going to bring that link that hopefully we've already shared with you at this point and we're going to put that into the data connector inside the powerbi desktop right so this is the the data that was introduced at Education data and we're going to get a better

Look at this inside the power Cy editor once we connect this here now so once you have that link pasted in you can go ahead and select okay and that's going to take us to the next step here where we see this authentication screen which sometimes uh is confusing at first to users when they first encounter this but you'll see this authentication screen in a couple of scenarios when you are connecting to data on the web like we are here today and we will leave it set To Anonymous but just to get an idea of

what these other sections are in this window we have Windows which would be where you would go if you need to provide your windows credentials if you're connecting to a database and you need to provide the uh username and password or a the server name and database name as well as if you're connecting into a web API I need to provide that API key and then finally if you have to provide your organizational Credentials here but we're just going to connect anonymously to this link that we have provided you to connect into this data out

here on the web we'll click connect here now and then we'll we'll see a nice little preview of our data here in this window yeah that's helpful so you can get a little Peak at the data that you're about to bring into powerbi maybe you selected the wrong file you can see pretty quickly here before you go too far uh now I do notice there's Quite a few options on the bottom there help guide me through that a little bit why would I choose one versus another down there yeah so we have a couple options

down here and obviously we're going to ignore this cancel button here but we've got load and transform data so load is interesting because load is what we want to use when we are ready to load the data into the power data model so our powerbi file here so we can load the data into our powerbi Data model if it's already been cleaned up and ready to go presented in a format that we wanted in for our report to build out our visualizations okay so that's assuming the data like you said the data is ready to

go but I can imagine under nor normal circumstances there's probably some things I need to fix with the data it's unlikely that I'm going to they rare at least that I'm going to connect a data that's already been per perfectly organized that Scenario I gave earlier where there's issues with the data we want to do some minor data modifications or data cleansing what would I do do if I want to fix bad data I have coming in like Devon said data rarely comes to us in the format we need in for reporting there are errors

and consistencies things we have to do to uh kind of fix the data uh resolve some some issues so what we want to do here now actually is we want to select transform data that's The option we we want to use and that's going to launch the power query editor for us and that's where we are going to go in and go through those steps of data cleansing data prep and make sure that we have the data the way we need it in for the report that we are building out so we'll go ahead here

and select transform data and this is going to as you can see launch that power query editor and it does in fact launch it as a separate window so that's a confusing Experience at first many users when they first see that um sometimes they won't see that power query editor open on another window and they just see this yellow Banner here showing there are pending changes in your queries that haven't been applied and so you'll see that when your power query editor window is open and so remember it is in a separate window all right

so now with the power query editor open you got a preview of the powerbi desktop interface Earlier but let's go ahead and talk about the power query editor interface because it is a little bit different and it be confusing to some at first so over here on the left hand side you're going to see the queries pane this is where you're going to find a list of the queries that are currently present in your powerbi model right now we only have student testing data that one query we brought in that one source that we connected

to but as we begin to build Out we may add additional sources and we'll have additional queries listed out here so that's where you would see them now down here towards the bottom we will see the total number of columns as well as the row count uh here in the power query editor but something that you want to note about the power query editor is that power query editor will only bring in a sample of your data about a th000 rows so about 999 rows and the reason it does that is actually beneficial to you

Because when you're working with larger data sets imagine if you had to apply all these transformation steps to the entire query at once it could be it could slow things down and so applying this to the sample of your data allows power query to operate quickly and efficiently and get those transformation steps applied to that sample of your data now over here on the right hand side we have your query settings Pane and this is going to be your helper your Friend when you're going through and applying those data transformation steps those cleansing steps now

many notice when they first get into Power query it's very easy to click a button and apply a transformation and make a change right but what happens when you apply something and you do that in mistake or you apply a transformation step and you don't get quite the result that you expected uh everyone searches for that undo button right away but you'll notice Power query does not have an undo button so without that undo button how do you fix a error or mistake a step that you applied um and that did not give you the

result that you want that's where the applied steps pane comes in handy in addition to being able to rename the query here you also have the steps that have either been applied automatically by power query we'll touch on those in a second but you also have any steps that you've applied so if you make a mistake And you need to delete a step you can go ahead and come to the applied steps Pane and you can select the X which is going to allow you to delete a step and if you need to modify a

step you can reopen that step by selecting that gear icon and it will take you back to if it had a a window that opened up and allowing you to edit you can go in there and take care of that there up here in the middle of your window just above that data preview you're going to see this formula Bar now this formula bar here is really neat because it contains what's known as the M query language that is the mashup or a coding language of the power query editor and this is the language that's

actually kind of running in the background when you apply those transformation steps all of those buttons that you see in the interface and what's nice about power query is you don't actually have to learn this mcode language right away to begin cleaning up And shaping your data you can apply those transformation steps and you will see the the change take effect in your query but as you go on you can take a look at the formula bar and get a little bit familiar with that M query language as you go so over time you'll you'll

start to recognize the functions and things that are applied to this and we can see right now that the selected step is the change type step and that's what we're seeing here in the formula bar at The top so a couple questions for you uh what if perhaps someone doesn't see the formula bar when they go to open this or maybe they accidentally close the query settings you could see an X where can they go to be able to uh Vis visibly show some of the things if maybe perhaps they don't see them yet yeah

so it's a great question so we sometimes see that when users have power CER editor open their query settings pane is gone or the formula bar is not showing now so let's Say you accidentally hit this x and the query settings pane goes away don't worry you don't have to close the editor and start over anything like that up here in the view tab you'll see that the far left hand corner of this portion of the ribbon has that query settings button and you can enable that query settings pane back again and if that formula

bar if you ever notice that that's not there you want to go here to the view tab as well and make sure that You have that formula bar enabled so that you can see that M code if you would like to yeah a common question that people have also when they get started in this tool is they notice the little highlights below the column names can you talk a little about what that is what is that indicating to us yeah so these right here yeah exactly yeah so these are the data profiling that's the uh

data profiling bar you'll see and if you hover over that bar it's going to Show you a percentage kind of of the data in your column the content and so these are the data profiling features column distribution column profile and column quality so you can enable those you can see column quality gives you a percentage breakdown of the content in the column what percentage the values are valid what percentage are resulting in an error and what percentage are empty you've got column distribution here which is going to provide you a Count of distinct and unique

values and by default it's going to do that on the top 1,000 rows so that sample of data so if you are in here profiling your data and getting started with a new data set or just building this out you'll want to when you first get started go ahead and switch this to entire data set if you want that full profile of your data but you don't want to leave this feature on okay because it can slow down the performance of your model if you leave This running so keep it on when you're in here

and you're actually profiling that data but make sure that it's set to top 1,000 rows when you are not using it and then column profile gives you a little bit more detail similar to column distribution but you also get column statistics that are depend on the column type really so a text column will show you certain statistics here here while a whole number column will give you a little bit different statistics in this Window here well that's probably a good transition there a question I have for you is you talked about column types not everyone

is maybe familiar with what column types are data types you may may call it um can you show us briefly as we look at the columns we have on our screen right now how do you how can you tell what each column type is and maybe there briefly how would you change them if you want needed to or wanted to great question so as you can see so our Applied steps pane we have three steps that were applied automatically Source promoted headers and change type and that change type step there is the last one and

that was done on those columns that you see all 14 columns you can click into your applied steps and actually see what the data looks like how it was set up when it was first brought in you can see initially our columns didn't have those headers you can see them there in the first row Power query is is pretty smart and it was able to detect that and it will detect that by default in the default settings so the next step is where we see those headers promoted on up and if you take a look

at all of our columns here we have all of our columns currently set to a text data type okay that's what the ABC means that's what ABC means and then change type here you can see math tutor hours math tutor period we can see that those were Changed you can see that one two three next to them got so you can change a column data type in a couple of ways one really easy way and probably common way one of my favorite ways to change it it's right here in the column header because you can

go right in and you can see here at the top you have a few of the numeric data types we've got the decimal number we've got fixed decimal number which is the currency data type we have whole number which is what that One two three icon is that we're seeing next to student ID grade math score reading score tutor hours and so forth percentage then you have moving down you have the date and time data types we have a date time combination here we have just date we have just time we have date time time

zone we have duration we have a text data type then we have that true false and that binary data type that Legacy data type so you can change your data types right from the header or You also have that option up here in the ribbon and it's also located in the transform tab so lots of options to go in and modify the data type in your query got it this is great this a great overview I know one of the best practices you've always taught me is I should start by renaming my query right because

sometimes you'll have queries that come in with funky names I think we kind of have a funky name here as well yeah so let's maybe let's go ahead and Rename that right let's call it something like test scores I think that's a good concise yeah and we'll talk a little bit about the data here in a moment and get to the details of it but it looks like if you glance at this we're looking at uh math and reading test scores so yeah let's go and rename it and there's there's a couple places we can

do that right yep you can rename your query on the left hand side by double clicking the queries pane you can Also go to the query settings Pane and in that property section where you see name you can go Ahad and rename this I'll go ahead and rename this to test scores all right so let's get a little bit into the details of the data here so we know that we're looking at test scores data it looks like we're looking at various school districts I can see I think if as you scroll over there's a

student actually it's right there in front of us it's have a student ID that Associates us with a student we'll pull in more information about students later by the way worth doing a quick reminder this is all fake data so this is not real names this is not real people these are not real test scores this is just for demo purposes uh but as we look at the data there's a couple things yeah let's start to scroll to the right we got District City State student ID what grade they're in their math score reading score

whether or not they had a Math tutor and their the math tutor's name how many hours that they did tutoring as well as we can also see the same thing for reading and what what uh period they did it so maybe it might be interesting to see do those that are mentored in the morning and first period do better than those that are mentored at in the evening or in the afternoon so there might be some interesting things there to look at the other thing I notic as we look at this is some of the

Columns have null values like when looking at the math tutor hours and then some of them have blank spaces like blank empty values there so like you're pointing out right now city and state they're just empty there's not it's not null for whatever reason talk to me about that a little bit because that does come up semi-frequently with a lot of folks that are dealing with data uh what's the difference between null and blank and then we'll talk a little bit About why we probably want them to be null because there's some benefits that we

get from that right right so in just a moment we're going to take a look at how to handle uh your data when it comes to you like this and you have nulls and you have blinks and a lot of times these two uh types of values really get kind of confused together or or compared but they are they are different so nulls when you see null in your data that is the the absence of a value okay and when You when you see blank like we see here it almost it's like an imaginary space

if you if you think that's that's an empty VI it's a type of it's a blank value yeah so you could filter on empty blank blank value whereas null like you said is the absence of a value and there's a number of things that we'll experience later on that if we use NES instead of just relying on the blanks that are there in fact we're going to experience that right now yeah having it As a null value will help us right absolutely yeah so when we're working with our data and we we see these blanks

it can be difficult and tricky and and allow us to not be able to filter and slice like you said the way that we we would want to we don't want to we don't want to slice apply a filter on our report for blanks to look at these School District we want to be able to filter by that school district or by the city if we would like to now to try to Solve this though and that's probably what you're going to touch on Devon is we can't solve this yet until we fix or replace

these blank values with something else and we can replace it with null yeah and maybe if you don't mind uh let's slide to the left and let's look at the records that we have on District city and state so we can see District city and state we have a value at the very top then we have a bunch of blanks and then eventually there's Another value again here so it's kind of like a ragged report ragged meaning that there's a there's a value at the top and then everything below it is blank and really what

we would like to do from a powerbi perspective is instead of having those blank values we want Papo Beach and Florida and Alderwood County Schools to be replicated down for all the values that are represented by that and there's like we we talking about the reason we brought up nulls is we can't do that While it's a blank value we need to make it a null Value First to be able to do a special transform we're going to talk about in a moment to fix this issue that we have where it's not replicating the values

down like powerbi would prefer right that's right okay so I think there's a number there's actually some multiple columns here that we want to fix so the the problem we're trying to solve here right now is take those blank values and replace them with null So talk us through that a little bit and I know we have I think a total of five columns that we want to do this with so I see some others on the right there right you got uh reading tutor also has some blanks math tutor has some blanks s and

then the district city and state also had some blanks so if we want to fix these do we have to do it one by one or can we maybe perhaps multi select them yeah so we we actually can go in and multi select these and speed up that Development process which is nice and so we want to multi select the columns that we see those blanks in and so that we can apply a transformation that is going to actually um allow us to replace those empty Val those blank Val vales with uh null which is

going to come in handy when we want to apply the transformation that we'll talk about here in just a second it just be clear to multi select how did can you tell us briefly how you did that yeah absolutely so you want to Go to you know the First Column that you want to select select it and then hold down that control key and then as you go through slide and select those columns and it's worth just double checking that you do have those columns selected there before you go and imply your transformation all right

so now we know we can multi select and we want to do is we want to replace anytime there's blanks like empty text we want to replace those with nulls that's going to Help us later all right walk us do it how do we do that so once you have your columns selected you can do this on a single column of course if you would like but we've got multiple columns here we really want to apply this transformation to so multi- selecting allows us to speed up that development process and so we can select multiple

columns and you'll see Power query is going to be smart enough to understand that so we'll rightclick the column Header and then what we want to do is we want to replace place values that's the transformation we're going to select here and when we do that a new window opens up and you can see that you want to use this when you want to replace one value with another in the selected columns those five selected columns we have District city state math tutor and reading tutor and the value to find we're going to leave alone

um because the value we want to find is blank but We want to replace it with null and so we're replacing with null so that we can then use a transformation that knows how to treat and handle those NES based on the way that our data is set up and sorted and we'll talk more about that so value to find replace with null and then we can go ahead and select okay so now we can see if we take a look at those columns I we're already over here on math tutor reading tutor where we

had those blank values you can see null is Now present in those uh Records in those rows if I scroll over to District city and state you can also see the same thing we no longer see blanks all the way down we can scroll and see nulls in place of those blanks so it replaced all those blanks with that null value that we entered all right there you good so now we can solve the problem we were trying to get to which was pompo Beach Florida Alderwood County I want that replicated for every row below

until There's another one to replace it with this was just how the data was provided to us it's not a very uncommon way to get data you'll see it like this sometimes and so how can I what what kind of tool can I use and I know we only want to do this on these three columns so we may want to unselect by the way if you're watching at home following along you may want to unselect the tutor and the tutor columns but on the district uh what do we have the District city and state

columns we're going to multi select those and what are we going to do with these so now that we've replaced those blank values with null we have the ability now to apply a transformation known as Phil and what Phil is going to do if you right click into the column header you'll see that Phill option and it allows you to like fill down or fill up and so the way that our data is kind of set up right now and sorted we know that we can see that that First district city or state like we

see here Alderwood County Schools pompo Beach Florida that's that first entry there and I know that the way it's sorted all of the values all of those rows below it should also be Alderwood County Schools pompo Beach Florida until it reaches the next value and that's what Phil does is it's going to uh Traverse the column and look for a value like Alderwood County Schools pompo Beach or Florida and it's going to take That and fill it down if we select down if we needed to fill up we could also do that but the way

that our data set up and sorted I know that we need to use that fill down option makes sense and so we couldn't have done this with blanks right that was part of the reason we did that now that we have null values there let's see it in action here yep so we needed to replace those blinks with the null value and now when we hit down we can see that it's filled the data down And we can scroll and see it does actually stop when it finds a new value like Aspen Ridge public education

and Grand Junction in Colorado and then same thing when we get to Birchwood Regional District Gainesville Florida so so I think you mentioned a really key thing there you mentioned it a number of times I'm glad you said it the way you did it was the Sorting of the data is really important to this so if we had come in and changed the sort on our own we Probably would not have been able to use this feature because the data had to be sorted so that all the values below it could be replaced by the

one at the top until it found a new one so if you're ever wondering you know the scenario of when this might not be a good idea if you change the Sorting or the Sorting doesn't perfectly align with that right uh you may not want want to consider using this tool but this was a great scenario we got some kind of ragged Hierarchy provided to us uh maybe it came from a output of some other tool and we wanted to be able to replicate those values down so it worked perfectly right uh that's exactly right

so that was a perfect way to kind of explain that as well so we've now got all of those blank values filled in so we can see the district for every Row in the city and the state abbreviation as it should be all right so next I know that we have some city and state information In here we have some Geographic data in here and one of the common things we talk about in a lot of the classes that we teach is trying to identify unique data and right city city can often times not be

unique we happen to live in a City Jacksonville that is in multiple States y that's right and so we what I would like to do in case you know maybe we do maybe we don't want to do some mapping later but if I want to create some maps on this data I want to make Sure that we're looking at a very specific city state combination so how can I solve that problem what are some of the ways I can solve that problem talk us to it yeah so there's a there's a couple of things you

can do and and like Devin said you want to make sure that you're helping power be out when you can and oftentimes powerbi does a really great job at interpreting and detecting uh where colums of data should go or what type content of data it Really is but sometimes you have to help it out and so that's where one we make sure we have the proper proper data type stored but we also want to make sure the data category um which we'll talk more about that later on but before we can use and apply that

data category setting we need to make sure we have a combination of the data which is going to be city and state combined now there's a few ways we could go about creating that city state column power Query gives you a ton of options more than one way to arrive at a any result so it's nice that you have those options but what I'd like to talk about is up in the add column tab the option to use column from examples this is one of my favorite features and so column from examples is a really

neat feature that allows you to create a new column based on a sample value that you provide and it's also a two-part button like many of the options in here the top part is the Shortcut which will take you to uh selecting all columns and then the bottom will just be from selection so it would just be state in this case but we want to go ahead and select from all columns so you can hit the top part of that button as well as you can see enter sample value to create a new column to

create that column from example so just like the name implies we are going to enter the sample value that we want to see we're providing an example for power Queries to be able to generate this new column for us so I'm going to take the information right here from uh this first record this first row pompo Beach Florida and I'm going to go ahead and double click here into the first row and I want to type the citystate exactly as I want to see it here so if I were to type out pompo Beach Florida

all caps it would populate the entire column just like that in all caps so we can go ahead and start typing out that sample value And so I'm going to start typing out Pomo Beach comma Florida because that's how I want my data to popul into this column I want it to be City comma State um and so as soon as you have it typed out the way that you want it to appear double check your spelling because spelling does count yes if it's misspelled it's not going to pick up pompo beach and you'll find

that out but when you hit enter then you're going to see it's going to populate this new Column here with those values you can cross reference to see it's done that and something's neat about this you can see that now the column headers say merged when before it just said column one so that's letting us know that that's the the type of transformation that was completed here is it was a merged column so column from examples is a is a really great feature because it can help you out in a couple scenarios one when you

know the column you want to Create but maybe you're not quite sure yet of the exact transformation to apply or maybe you know the transformation you need to apply but you know this can get the job done for you much faster which is which is great and that's what we want to use right work smarter not harder as we're going through this develop process yeah I think you like you mentioned when folks are brand new to this tool it's really helpful uh maybe you don't like you you Don't know which transform you use under different

scenarios so you could leverage colum from example you can also see in the top portion the gray box up top you can see the actual M query that's what uh Angelica mentioned earlier the inquiry that's being written for you is all visible at the top of the screen if you wanted to the other thing I think I want to reiterate is that you mentioned spelling so if you tried to follow along with this and it didn't Work as angelic did double check your spelling of Pomo Beach to make sure that you typed it in the

exact same way that she did if you don't it does matter so make sure you take a look at that so I see it like you mentioned it also created a column for us called merged I I don't want my column to be called merged it's kind of silly what would I do to change that yeah so we can rename this and you can rename it right here on the column header and it's actually a Best practice to to rename it here within this step as opposed to waiting and changing the the name of the

column after we click okay so after we click okay it's going to add you can see column from examples in our applied steps pane but when we click okay it's going to change that to inserted text inserted merge column and so if we click okay and then rename it after then we'll be adding an additional step after that and instead of that we can we can Combine those into one and so we can double click merged and rename this to city state and then click away and you can see that column header is updated and

click okay now we've changed the name of that column and it did not add an additional step because we did it within that column from examples that inserted merge column transformation that was applied here all right Angelica I think the DAT is looking pretty good now I'm happy with what you've brought Together but by the way if you really want to change the order of the columns you can drag and move them around no need to actually do that it doesn't impact anything because once you get back in the only thing it does impact is

it does create an applied step for you but there's not really much point in reordering the column because once you get this brought into your data model it's going to sort them all alphabetically anyways uh but if for Your sanity you like to see them in a certain order no problem doing that as well all right so if I'm happy with the data that we have now how do I get this into powerbi desktop I guess it's in powerbi desktop already but how do I get it to a point that it's in my data model

and I can build a visual just to see that the data is brought in properly yeah so once you've you've gone through that data prep all of that data cleansing applying the step steps that That you needed removing unnecessary data uh replacing values filling down like we've done here then when you're ready you can go up to that Home tab and the step that we're looking for the feature the option that we want to select here now is close and apply and you can see that we have another two-part button where there is a drop-

down close and apply apply and close you can also access this from the file menu there you can see close and apply as Well as those options separately now what you have here is a couple options close and apply is the one we are going to use because it combines these two it's going to close the power query editor and apply our changes apply will just apply any pending changes and that's referring to those transformation steps that we have stacked up in the applied steps pane when you hit apply it's going to apply it to

all rows of data as opposed to just that sample of Data the top 10,000 rows that was brought into Power query close would close the power query editor without applying those changes kind of temporary temporary really storing them but close and apply is the option we want to select here to begin to close this window and go back just into the powerbi desktop so let's go ahead and select close and apply here now and you should likely see and you can see it on our screen as well it's going to show us Those lows rows

actually being loaded into our data model uh very brief popup there that we can see but you can also find it on the right hand side of your screen the data pane shows that table now visible with all the columns that we just brought in as well and as you can can see Devon mentioned earlier that you can reorder those columns inside of power query if you would like to but know that it doesn't actually affect the way that you're going to see them here In your data pane in the powerbi desktop now we we

want to take a look at the data and answer a couple of questions that we had okay so looking at these test scores I can see we've got city city state district grade math score math tutor reading score reading tutor and so on and I'd really like to be able to answer a question like what are the average reading scores across the districts that we're that we're working with here and so I'd like to create a Visualization that's going to make it easy for us to understand that and and not have to read our read

over our table of data from our our source file be able just to to quickly glance and and see you know what the average is for those districts across all of those regions so I think we should go ahead and create a stacked bar chart here okay that sounds good to me so we can hover over your visualizations and in the visualization Pane and you can see the different Visualization types so when we hover we see that stacked bar chart is that first one there and I'm going to go ahead and select this and you'll

see that it adds a blank visual to my canvas now you can drag it around you can grab those anchor points in the corner there and resize it you'll notice there are some red guidelines to that appear to help you uh with sizing and centering which is really nice nice um as you're going there you can also open that up in the Format section there and get really really specific with the the formatting and and we'll talk about that more in visualizations but yeah here in the bar chart that we've created added to our canvas

now we want to create that visual by populating it with our data and what we have currently turned on is a feature that is currently in preview at the time of this recording but that may change right so on object interaction is the feature referring to and when you first Get started and you open powerbi you can find that in the preview features in your options and settings and it's really great actually it allows you to work right on the visual itself and start building out the visualizations you need to see so we can go

ahead and select add data and build your Visual and you'll see this little pop out window and so this is very similar to what you're seeing over here on the right hand side and if you don't for Some reason have access to that on object interaction ction feature yet if you're working with a slightly older version of the desktop you will still see your build Paine here in your visualizations pan it looks just a little bit different instead of clicking add data you can drag and drop those fields here so you can see that but

let's go ahead and start creating this visualization here and what I want to see here is the reading scores average Reading scores by School District so on the Y here I'd like to go ahead and bring in the district and then on the X I want to go ahead here and bring in the reading score and so now we can go ahead and take a look at this and we can see interesting so it looks like at first glance here we see it looks like willber height school district is has a reading score of 62,000

62,000 wow will Brooks so we're getting something interesting here we're getting some of reading score By District yeah so I spot I think you spotted as well there's definitely problem here so this can be a little deceiving in that we're seeing willbrook Heights at the top but it's because if you if you look at our AIS in the bottom you can also see it says sum of reading scores we probably don't want to sum our reading scores right useful it might be giving us a little bit of deceiving information but if can we change that

let's say if we wanted to change it to An average I want to know the average reading score by School District how can we do that yeah so we can we can modify the way that this is displaying the data and so what you're seeing here is on the reading score column which we have stored it's a whole number column and so by default powerbi is going to apply summarization okay it's going to aggregate that and doing that is applying an implicit measure and we can see that on all of those columns to the Right

that have that Sigma symbol so that's letting us know that those columns have a summarization it's an implicit measure and the default for that I believe is typically sum so that's why we're seeing some of that reading score but we don't have to settle for that we can change that for scenarios like this one where it would make more sense to look at an average of the reading score so you can click right here into that field you can see what Column we're looking at that reading score but we can change that summarization there from

sum to average which in our case would make more sense absolutely and probably under normal circumstances Su is maybe the right choice but in our scenario it doesn't make sense in fact so much so when we go to look at the data what was the name of the is willowbrook's height was the top school district and now it's not only out of The top 10 it's like in the bottom seven or eight there right so uh definitely there's a couple factors to that right it could be that we just have more students that were cataloging

here from that particular District larger school yeah so we when we go to summarize it it creates this larger value but really we want to look at at an average so when we make that change it's significant difference here in the data so much so so that the actual top reading school District is Hawthorne Valley Unified they may have less students but they have much better reading scores but pretty significantly right right yes is 364 average and down here with below Brook we're looking at 304 almost 305 is the average reading score wow now one

of the things we're going to learn about in our next section this is probably a good transition into wrapping up here what we're going to learn about in our next section is how do we make changes like You just made permanent for the entire model so what you just showed us is a little Peak into the visuals that we're going to learn about later but when you made that change it only made it for this one visual if we were to go to create another visual we would have to do it again right so one

of the things we're going to learn about in our next section is how do we make that as a permanent change on that column so it applies to every visual going forward And we're going to look at a number of other data modeling changes we want to do in the next section so uh Angelica I want to thank you so much of course what we probably if you're following along we probably want to save we'll save our work we have to see her do that there's a save button in the top left of course make

sure you hit that save button uh but Angelica I want to thank you so much for joining and guiding us through this data cleansing section before we wrap up Tell us a little bit about some of your favorite classes that either are helpful towards what we talked about or what are some other pragmatic Works learning CL classes that we have that are ones that you're favorite yeah so some of my favorite courses that we have especially that can help further your understanding and knowledge and skill set of data prep and data shaping uh we have

a specific course on Advanced Data shaping it's it's a shorter course that just focuses In on Advanced Data shaping but to really tie everything in together our beginner powerbi course is a great one that dives deep into Advanced Data shaping but also where it kind of Falls in line with the whole powerbi report development life cycle that's awesome well Angelica thank you so much for joining and we'll see you in our next section see you then well welcome back everyone again to our power Bey beginner to Pro session we are Moving on to our next

section this time with another trainer Amelia Roberts welcome Amelia thank you so much for joining us appreciate that happy to be here yeah Emil is another one of our fantastic powerbi trainers she loves digging into things around data modeling which we're going to be getting into here now as well as many other topics but primarily powerbi is your focus right yep uh actually in co-pilot as well I'm getting into co-pilot yeah got Got to love some co-pilot yeah well ailia is going to be taking us through our data modeling section Mia probably a great way

to get started is tell us a little bit about why data modeling is so important of course there are various reasons that data modeling is important it is really important that you build a nice strong data model in your powerbi report and this can actually be done as you are building out your report it is not a one time do it all at once type of Situation sometimes you can be building this report and realize along the way that you need to continue to build out your data model but I'm going to give you five

main reasons why it's important to have a nice strong data model in your powerbi report the first is performance optimization you want a good structured data model in order for your report to have efficiency there are two main types of data schemas that you want to have the first is a star schema which is is Your basic blueprint of your different tables you have a fact table typically in the middle and a bunch of Dimension tables that are branching off of that fact table there's also a snowflake schema which is kind of a build off

of your typical star schema and it's when Dimension tables are built on top of Dimension tables so it gives that kind of snowflake effect um but the second reason that you would want a nice strong data model is for data accuracy and Consistency if you have a strong data model this will prevent errors from happening if your data model isn't as strong then you could tend to have more errors and more issues within your powerbi report the third reason is scalability so if you want to continue to develop your powerbi report develop that data model

continue to make it larger it will help if you have a strong base data model to build that off of my fourth reason is for maintenance reasons All right having a structured data model is going to simplify the process of updating as you are maintaining your reports my last reason is enhanced usability a nice clean data model is always the easiest to work with I always like to imagine I'm going to pass my powerbi report including the data model and all of the semantic models attached to it I'm going to pass that off to somebody

else at some point how are they going to use it are they going to be Able to use it is it user friendly so investing that time in creating a nice strong data model is very important in this process that's amazing yeah those are great tips and I think really apply to small data models large data models definitely good consistency I think those are tips that can carry out regardless of what type of data model you're working with now um and actually while we're talking about this I think some of those great tips you gave

if You're interested in diving deeper even beyond what we have time to cover in today session we do have a data modeling with powerbi learn with the nerd session you can find on our YouTube channel make sure you take a peek at that if you want three hours devoted to nothing but data modeling that's a great one to go to we won't be quite going that in depth today however we will be giving you some of those great tips that ailia just shared with you so Amelia usually and I think You kind of covered this

here usually data models are going to consist of multiple tables right it's not going to be just one thing you bring in you're going to bring in as examples sales and the customers and the employees that are making the sales kind similar in the example that we've been doing primarily an education example today but we're going to be bringing in student information now right yes so we only have one table right here that Angelica Brought in for us earlier it's got some great stuff in here but it is helpful to have more than one data

set to work with so what we're going to bring in is a second data set that is more focused on our student population and kind of gets a little bit more nitty-gritty this link that I'm going to use is going to be in in the description below the video so feel free to grab it at this time if you're following along with us and I am going to go ahead and do exactly what Angelica did I'm going to go up to get data I'm going to use my web Source here and I'm going to paste

this on in I am going to keep it as basic here and I'm going to hit okay and then a preview is going to pop up here as well you may also get that notification like Angelica did that um is asking about Anonymous or if you need credentials um you can keep it Anonymous it is a it's completely public for you to use this is our preview of our data And just looking at it it's pretty simple um I actually don't want to clean this up as well earlier you were hitting transform data going into

the power query editor we're not going to do that this time we are just going to go ahead and load this data set as is so you can go ahead and hit load although it is best practice to go into to the power query Editor to start it off there if you know that your data set's already cleaned up there's no need to go into The power query editor if you don't want to and you always have the option to go into the power query editor at any time during the process oh that's a good

point I was just going to interject yeah go for it I don't know if we covered this before with Angelica you mentioned you can always go back into the power query editor if needed can you show where would you do that so if I want to do that I am just going to go up here to my transform data button up at the top So I can hit that button and the power query editor is just going to open right back up it even has this drop- down menu for you you can hit that drop

down menu and hit transform data right here and that second screen like Angelica was talking about will pop right back up in front of this and then when you're finished you hit close and apply and you're back in your report perfect now I see so you brought a second table and we have student data now has been brought In I can see it on the right hand side talk us through what we're seeing on the right now Okay so we've got our student data table right here and it looks like it's got three columns that

I can see I'm going to go ahead really quick because since our data is all nice and clean I kind of want to change the name of this because it is it doesn't have any it doesn't have any spaces I don't love that so I'm going to change this really quick to just being students Because that's what the data set's about I like to keep it short and sweet and to the point proper capitalization spelling punctuation spacing is very important when building out your data model may I add real quickly uh this is kind of

a silly best practice but some people sometimes ask about pluralization of tables should you do it should you not honestly I think the answer is it doesn't matter as long as you stay consistent so if you're going to add an S and make it plural make it make test scores students plural across the board so I I think it's perfect point we have as I said three columns here we've got it looks like extracurricular activities um it looks like names possibly and then student numbers let's actually go into our table View and see what this

table fully looks like so we have our student numbers which if you can tell it's not in order we're going to fix that in a second we have names right here which Looking at these names if you can't tell we are working with a fake data set here these are not real students these are not real test scores these are not real people so no need to worry about privacy here um we did make this all completely fake and then we have an extracurriculars column which looks like it has nothing in it but if we

drop this down we can see that it has extracurricular activ that students are a part of debate drama Music other sports um but I'm going to go ahead and actually fix this sorting here because I do want it uh to go one through whatever the last number is we can tell it's not sorted by this column you can sort ascending or descending depending on whatever you want ascending makes most sense to me so I'm going to go ahead and change that now so now that we know the data that is in this table let's actually

go in and let's make a visual one one thing that I love to do When I am building on my data model is I like to test everything that I bring into my report so anytime I'm adding something to my data model I'm going to test it out and see what it looks like with a visual and I'm just going to make a nice quick visual here I'm going to use my stacked column chart I'm going to add that I'm going to make it a little smaller this is a little aggressive of a chart and

we are going to add let's do let's answer a question here I always Like to ask questions while building on my data model So based on what extracurricular activity students are in what is the relation or the correlation between that and let's say they're math test scores so I'm going to go ahead and I'm going to select extra curricular activities I'm just going to check that box and then I'm going to go down and choose math score as well and we have this gorgeous visual that has just come to life and we can use our

tool tip Because it looks like everything is kind of close our tool tip is saying that um for the other extra c activities we have the like 61 one94 for our MTH score it's saying the same for debate and drama so it looks like all of these columns are showing the same amount so clearly there something not right here there's definitely something that is not right in this situation so if I was new to powerbi and I saw this pop up for me what what is this an indication of this Is a very common problem

that I see all the time it is an indication that you do not have relationships built between your tables ah okay so we're definitely going to have to head over to our model view and see what is going on all right let's do it so over here underneath my table view is my model view so I'm going to go ahead and go in there and this is where I can check the relationships between my tables and looking at it right here if I have a relationship Between my tables there should be a line connecting these

two boxes here and there is not so I am going to need to manually create a relationship between these two tables now the key to any relationship building in powerbi is just like starting a relationship in real life you need to find something that these two tables have in common all right they've got to have the same data type in order to build a relationship between them and the columns need to be Very similar in what is located within them so looking at my two tables here I know that my student number and my student

ID are going to be the same based on what we saw in our data earlier so I'm going to build my relationship between those two I'm going to quickly check to and make sure they have the same data type they're both summed which means that they're probably numbers uh my stud ID is a whole number I can see right here In my properties Pane and then my student number is also a whole number so they have the same data type I'm going to go ahead and build a relationship between these two columns and the way

I do that is simply dragging and dropping so I'm going to pick one up I'm going to drag and drop it directly on top of the other and when I do that this box is going to pop up this box used to only pop up if you made a mistake but now it pops up every single time and it's kind Of asking you are you sure you want to make this relationship and is this the relationship you want to create I simply do a a few things while I'm looking at this box I check and

make sure that the two columns that I want to build a relationship between are the ones that are chosen and then I check my cardinality which is the relationship type that I have I have a many to one relationship which is the type of relationship that I want to have between My columns and then I make sure that my relationship is active that this box is checked if all of that is good to go I'm going to go ahead and hit save now generally uh not generally but a lot of times when you're working with

different tables it may automatically create the relationship for you right corre and I think we kind of purposely set it up so that didn't happen here so we can go through this experience showing what it's like um a lot of the automatic Relationship type has to do with uh is it the same data type like you checked a few moments ago but also if the columns happen to have the same name then it'll it make some assumptions there but this is great so we have this is what's called a one to mini relationship right correct

yeah what is that so A one to mini relationship just depends on the amount of the values that are in each of the columns for instance here in our two columns for example my student my Students table the student number column in there has one exactly one of each value for the student number whereas over in my test scores the student ID can be replicated multiple times so you'll see the same value various times in that table hence the one to many you can also see here in your data model there will be a one

on one of your tables as you can see here and then there is a many on our other table as well or the little star asteris is what Kind of identifies as the many side I see there's also this little arrow in between I know that I I can uh tell on the far right in the properties Paine there's this section there that talks about cross filter directions very briefly a little bit more of an advanced thing but just to describe briefly so that's describing the direction that filters will flow from so you can filter

a student by you can filter a test based on a student but you're not going to Filter a student based on a test right you know the whatever Arrow Direction you see is the direction filters will flow and sometimes depending on where your table is kind of sitting it will kind of face different directions like up and down that doesn't make a difference Point kind of what it looks like so if your tables are just in a different area and you're like mine's facing up that's okay yeah good point good point yeah it's just something

I Like to point out I come across that question often so with this change that table you created that visual you created earlier it wasn't a table you created a column chart earlier this should fix that right if we go back and look let's find out all right so I'm going to go back to my report View and look at that this is much better looking and if I look at my tool tip and scroll over I can see these numbers are not all the same anymore thank goodness they Have all changed which looks much

much better than what we had before so that big uh column in the column chart that's blank is literally they don't have any extracurricular activities it looks like if you're going to be part of one music might be the one that you're going to have the most benefit from now there's some other things that are a little odd that we need to tweak in here we'll talk about right now it's summing the math scores we probably shouldn't be summing Math scores but we'll get to that here in just a moment so let's head back to

our model view and just make a few adjustments in there um so head on over to your model view and the first thing I want to do in here is I don't want anybody if I do pass off this report I don't want them to utilize the student ID and student number columns um they can just be in the way and kind of confusing so I typically like to hide my key columns because you're not actually Going to use them very often in your visuals so I'm going to go ahead and hide these by just

selecting the little I next to them so I like that point there you need the columns because it's the basis of your relationship but you don't want anyone actually building reports off those no I do not makes sense definitely okay and while I'm in here we can as Devin said we can see that we have some different columns that are summed even though we probably don't Want that so you can see this little Sigma sign next to all of these columns you are able to change these if you so choose for example my reading tutor

period here I do not want all the periods for the reading tutor to be summed up for any reason at all so I'm going to go ahead and select that I'm also going to select the math tutor period and then I'm also going to Select Grade cuz I never need all the grades summed up together either you know what Might be interesting actually show show if you don't mind in a quick visual what it looks like if you did summarize grade so they so they understand the such a good idea and then we'll go back

and we will change it and you can see the difference so I'm just going to make a simple table and let's put in our grade for instance this is the sum of the grade so think of this is nth grade 10th grade 11th grade all of those values in that column added up for every single Studio pretty much so that's the that's the problem with the auto sum or the sigma symbol you're seeing there is automatically assuming you want to summarize things when in some cases you do maybe more often than not you do but

in this case it doesn't make sense to do that and powerbi is just doing it because it's a number so it's assuming that's what you want to do it automatically assumes it it's not always right it is helpful sometimes as I said But necessarily all the time so here's some of math tutor um which we're also going to change so let's actually leave this here and let's go change those and then we'll come back and check it out so I'm going to multi select these I'm going to select one and then hit control to select

my other two and then I'm going to go over here to my properties Pane and down at the bottom I have this Advanced option I'm going to drop that down and here I have sort by column data Category and this last one summarize by I'm going to change that from some To None I don't want any summarization to happen here um you have other options in here we will talk about that in a second but I'm going to choose none for now for this it's going to change it and then you're going to see those

sigmas disappear this is one option let's go back and just take a look at this visual really quick yeah and I think if I remember right you have To refresh yeah maybe delete the visual let's redo this visual completely and see what happens so let's create our t table and we're going to add the exact same things we're going to add our grade notice the sigma's gone here too and already so much better yeah that makes lot more sense so so much better and our math hours look at that much better giving the individual periods

instead of adding it all together that makes much more sense perfect so there's actually Another two couple columns that I want to change as well because they're summarizing and I think that might be a little silly so let's go into our model view and these are our scores so our math score we have score I do not want those to be summarized right we do not ever want to summarize scores you don't want to add them all up there's just no purpose for that so and I was just to interject for a moment so they

they saw this with Angelica earlier so Angelica Built a visual we saw that it was summarizing reading scores and it actually showed a problem with the data it showed that one district school district was made it appear as though they were the best in Reading when really they were like 13th because we were summarizing and they just had a lot of students whereas a smaller School District had better averages so this while the thing that Angelica showed fixed it just for one visual you're Showing how to fix it permanently for all visuals going forward right

yes especially something like this that you know you're never going to want it summarized you might as well go in and change it permanently instead of doing it on a base by- Bas case so I'm going to go in I'm going to select math score I'm going to select a reading score and then we're going to go in to advance again and let's change this one to average because finding an average score That's going to be much more beneficial to me than a summarization of scores for sure makes total sense definitely awesome so now that

we've gotten that taken care of that'll help us as we start to build visuals later on the next thing that we're going to be looking at is hierarchies right yes we will be definitely going into hierarchies and talking about why those are important so let's head back actually to our report View at this point I don't need to be Here in my of you to do this you can do this very happily in your report view hierarchies are important in powerbi if you want to get more detailed information regarding your data so what a hierarchy

is is it is a natural way to group fields of information but we do it for a specific purpose here in powerbi and that's for the drill down interaction and what the drill down interaction is is you start at a large level of granularity and then you're Able to go further and further down into your information into smaller levels of granularity for example I'm going to give you a nice easy one that everyone knows and that's a calendar year you have an entire year so all of 2024 and then maybe you want a little bit

more information on specifically July of 2024 so you drill down into the month maybe you want even more information on a specific day so you drill down into a specific day of July in 2024 that is a Typical hierarchy going from calendar year down into month down into day so I was going to ask what would you say this is considered one of those usability features it's not going to make or break but it from a usability standpoint makes uh quality of life within your for your report developers a lot easier right def definitely like

I think the most important part about hierarchy is you need to think about that very end user the last person who's going to consume Your data do you want them to have that capability to dive deep into some information or do you not so it's completely up to you if you want to add a hierarchy to your data model it is beneficial in a lot of different ways and there are so many different hierarchies you can really make that can be very helpful and it all is based on what your organization does and what you

want your end user to do so the one we are going to create today is going to be A pretty small one and it's going to be kind of geography based I guess you can say we're going to make a hierarchy with our state data and our district data so that we can start at a state level and then drill down into the specific districts of each state makes sense so let's head over to our data paint over here I'm going to go ahead and Shrink this table up just a little bit I may change

it around I might add to it here in a second but what I'm going to do is I am going to find my state and I am going to rightclick on state when I do that this little bar is going to pop up and I'm going to find create hierarchy once I hit that create hierarchy the hierarchy is going to start to be made with my largest level of granularity so I'm going to go ahead and hit create create hierarchy and you're going to see it develop right there I can drop down this little arrow

as well and see as I add things to my hierarchy you can see Them just join in on this so started with my state now I'm going to find my next level of granularity which I'm just going to go to the district I don't want anything else in the hierarchy that's going to work for me so I'm going to rightclick on district and this time instead of create hierarchy I'm going to do add to hierarchy and I'm going to add it to my state hierarchy so I'm going to go ahead and choose that and you

can see District added to the hierarchy down Here now in theory you could have multiple hierarchies different hierarchies that use the same columns even so you can if you had a need to maybe went from a separate hierarchy that went from the district down to the school down to the city whatever it may be uh you can have multiple hierarchies in here even here right exactly like I could make another state hierarchy and it could go state city if I wanted it to so it's completely up to you and really What your in user is

going to end up needing and what questions that you have and that you want answered by using the hierarchy so once again I just added something to my data model so what do I love to do I love to build a visual to test it out the visual I'm going to use this time is I'm going to use my Matrix Visual and it's not just a place that keani Reeves has been to it is a visual that will show us a really Clear Vision of our hierarchy and it's kind of like a Pivot table so

if you have experience with that it's a great visual that you'll know very well and to add this I'm going to select my Matrix and then I'm just going to select the check mark next to my entire hierarchy and I can see it appear it looks kind of tiny here and that's okay but we can see all the states that are within our column here and then if I want to drill down into the next level I'm going to hit this plus sign right next to the state and Down is going to drop the district

that goes with that state so let's find let's go to Ohio my home state here drop it down and look there are two districts in Ohio that we have data on right here I also have the ability if I want to close all of these at the same time I can rightclick and collapse all there are so many different things you can do with this Matrix Visual and this is just the start with our hierarchy in here all right very interesting I love the Hierarchy I love how it organizes things for you speaking of organization

I noticed you're starting a get a lot of fields in your data pane over on the right hand side so are there any methods or or ways that you can organize those fields kind of group them together what do you have for me there there are a lot of different ways that you can organize and it's all dependent on preference but something I like to do is I like to create folders for my specific different Columns especially if I have something like we have a lot on math it'd be great if I had a math

folder we have a lot on reading it'd be great if I had a reading folder I love a good nice clean organization over here in my data pane it just makes it easier to create what I want to once I get to that visualization stage so to do that what I'm going to do is I'm going to go back into my model view and over here my model view once I do this I can actually multi select and Then put each thing inside a folder if I want to so I'm going to start with all

of my math um columns I'm just going to select all of them by hitting control and selecting them all so I have all of these math um columns selected I'm going to go over here to my properties Pane and you can see where it says display folder I am going to enter a display folder name I'm simply going to call it math makes sense keep it nice and simple don't overthink It too much and then once you hit enter if you look over in your data pain it's a very quick and Sudden Change and if

you blink you're going to miss it but I can see this little folder called math has been developed and all of my math columns are underneath it I'm going to do this as well for reading actually while I'm in here because why not we can't leave all the reading columns out on their own I know you were a math teacher Originally but yes I am a little partial but I understand the importance of reading as well so I love that I think it's a great way to kind of organize things you know this is still

a fairly small data model but things can become unwieldy very quickly once you start to bring in five six seven dozens of tables yep um I know one of the other little tricks we don't have to show it but just to mention you can have subfolders so you can have a folder inside of a folder If you use a backslash in there you can create multiple levels of folders I love it I think that's great I also know we have all these columns the math score the math tutor hours yeah we talked a little bit

about that little Auto sum or Sigma symble next to it those are a certain type of measure you'll talk about that here in a moment but I know there's this formula language as well that's worth spending a little bit of time on we have some other lengthier Sessions on that if people want to dig deeper into that but talk us talk to us a little bit first about what that Sigma symbol actually means and then take us on a journey to work our way towards Dax definitely so our little Sigma symbol means that powerbi has

created an implicit measure so it's kind of an automatic thing that powerbi does where it does sums or averages on its own we can also make explicit measures where we can create using Dax which stands for Data analysis Expressions we can create our own type of calculation that is explicit and on its own and it is more efficient and quicker to use and once you get comfortable with DAC you will end up falling in love with using it for a variety of reasons so what we're going to do is we're going to create a couple

explicit measures um to be able to use within our report and to do that I'm going to head on back to my report view because you can use Dax right here in Your report view to create whatever it is that you want to create so what I'm going to do is I'm actually going to create a couple averages of our test scores so we are have the implicit measures of them but if I wanted to calculate a little bit quicker then I can also make explicit measures of it as well I want to make sure

that these measures are going to be stored under my test scores so I am going to first start by selecting my test scores table and Then from there I am going to go up to the top and I'm going to hit new measure once I hit new measure my Dax formula bar is going to show up at the the top of my screen and let's talk a little bit about this formula bar before we get too involved in creating decks so we have a couple options here we have our first number that's showing here all

this is is a line number so it's not going to affect your Dax formula at all but you can have various lines of Dax For organizational purposes you'll see here in a little bit we're going to create a little bit more of an advanced formula and you're going to see the multiple lines that are involved with that it's nice to have that organizational feature if you so choose after that we have where we are going to put the name of our measure if you ever create a table or a column with dacks this is also

where you would name both of those so whatever I end up calling my Measure that's what I'm going to type there everything after my equal sign this is where my function or my formula goes and this is what I like to call the definition of my measure or the definition of my column or my table so that's where the formula is going to go let's go ahead and create one and see what happens here as I said I am going to do a reading score average measure so I am going to call that measure reading

score Average nice and simple to the point and I am going to first use my average function and let's actually look at intellisense here one thing about intellisense in Dax is it is so helpful let it guide you use it to your advantage lean into it especially when you're first starting out because it can really help you along the way in learning the entire process of writing Dax I don't know about you but I I don't memorize every Dax function there is out There I might vaguely remember some of the functions I don't remember memorize

every column I have in my data model so that intellisense that pop piece you're going to show it here again in just a moment it's really helpful for typing Dax Expressions faster right exactly it kind of speeds up the process it also kind of gives you a little bit of help for instance here I typed in my average function and it's actually giving me a definition of what that function is Along with what to do next so it's saying Returns the average of all the numbers in a column that's exactly what I want so that

looks good and then it's telling me up here that I need to put the column name coming up next first I always need to reference the table in which I am pulling the column from the information from and so to reference a table in Dex I use single quotations so I'm going to start with a single quotation and in tell Ence to the rescue Again it is giving me options from here so it is saying oh which table do you want to use students or test scores I want to use test scor so I'm just

going to go down here and choose test scores and then after that it's continuing to help me it is saying okay now which column do you want to use in test scores to refer to a column when you're using deck you are going to use square brackets so let's use our reading score column right here we're going to go Ahead and go down to reading score and select that and one thing I always like to tell myself is if I see red when I am writing decks then I need to get rid of that red

so I am going to put a closed parentheses to get rid of that red parentheses that I saw early on in my function that's pretty helpful right it tells you that you're missing something right so it kind of highlights areas where you need to close out a bracket or you need to close out a parenthesis I Love it it makes life easier definitely so now I'm going to hit enter and we're going to see what happens and it is so fast and happens without you even knowing that your measure has been created a couple ways

we know did this work is do I have an error message I don't see a yellow bar you'll typically see a yellow bar underneath your dexs um that will tell you there's an error there's something wrong with it or over here in your data Pane you will see your measure created and there will be a yellow triangle next to it if something is wrong and of course the last way I like to check always is in a visual because I did just add something to my data model so I'm going to put this in my

Matrix visual because I already have it created and I love just creating a matrix with all of my my measures to kind of see them right next to each other as I'm building them out so I'm going to choose my Matrix Visual and then I'm going to put a check mark next to that reading score measure and there is our reading score average per state which is really cool actually to see I love it yeah yeah so you can as you go to drill into different states if you drill into Ohio again or Florida again

you'll see that each of those are broken down so right now you're seeing an average at a state level but then when you drill in you see it at the district level Very cool very cool another great reason for a hierarchy right there we know what I always say practice makes perfect should we do it one more time let's do it one more time definitely make sure we're learning here so I am going to once again make sure you select your test scores table and then let's head up to new measure and we this time

let's do math scores because we did reading scores let's go ahead and do math scores too so Math score average and we we will actually do pretty much the exact same formula we're just going to switch out obviously breeding score with math score so I'm going to use my average again and remember we have to refer to that table first so single quotations test scores you can even hit enter as you go you don't even have to use your mouse as you're doing this and then single or Square brackets for your I love that intellisense

oh so helpful it really just makes it go so much faster I see red so I close out the parenthese another little helpful tip as you're learning this is to read things back to yourself when you're doing dacks so I always like to say I'm finding the average of my MTH score in my test T my test scores table that's exactly what I want so I don't want to add anything to this so that's when I hit enter now this Is obviously some basic dacks right where I'd love for us to get much more in

depth we only have so much time and Dax part of the data modeling experience that's why we're covering it during this section we're giving you some exposure to that you'll obviously think of other Dax functions as you go to build your visuals you'll probably think of other ones that you might need so there'll probably be a lot of back and forth with the data model and what you build out Here now to give folks a little bit of exposure to what more advanced dacks might look like we're going to show a particular feature that makes

hard dacks a little easier now it doesn't write the most perfect functions sometimes there's problems with it in fact we going to show an example here where it's going to write a bunch of dacks for us but it's not going to do perfectly but the feature I'm talking about is called quick measures tell us briefly what's Quick measures so quick measures are just an easier way to write decks it already has some calculations formulas in there all you do is kind of plug and chug so if you know what you're looking for you can try

quick measures and see if it's something that's going to work for you this is as Devin said great for more advanced decks especially if you're just starting out in the deck world so let me give you a scenario here then I want to be able to know on the reading Score average that you have right now I want to know are we improving year-over-year so I want to know and maybe we can bring the the school year column into that Matrix you have but I want to be able to tell the difference is their Improvement

you know we have these tutors that are design that are hopefully helping students get better and hopefully that's also showing on their standardized testing that they're doing so I want to be able to see that And see like what percentage better are the students actually doing now so how uh maybe let's first start let's bring school year into that Matrix visual you have sounds good yeah let's see school year down here Perfect all right school year is there all right so now if I want to know what is is 2023 2024 school year better how

can we use this quick measures feature to create a calculation to tell us that so let's try it out I'm going to Go and make sure I select my test scores table again and then up at the top right next to New measure is quick measure it has the little light bolt a part of it so let's go ahead and select that and when that happens we have the option to select a calculation so I'm going to go ahead drop this down and you can see the various options that you have here you have Aggregates

you have filters you have time intelligence totals I mean the list goes on here and by the way it's Worth mentioning you'll also notice the option on the top right we don't have to go to it today but there's an option there suggest with co-pilot there is a lot of lovely co-pilot integration into powerbi now the reason we're not going to highlight that feature too much today is because it does require some additional levels of Licensing that you may or may not have and so we don't want you to hit a roadblock there today but

just know that you could use co-pilot to Write some of these measures for you all right keep going of course so we are going to choose the percentage difference from Filter value percentage difference from filtered value try saying that five times best let me tell you from here in my base value I am going to put my average I'm going to put my reading my average reading score reading score average right here pull that in and then I'm fine with produced blanks and the output here and Then filter here I want to filter by the

school year so I'm going to pull my school year on over here as well and then I'm going to change this value to 2223 is what I think I'm going to yeah makes sense yeah now by the way there's other ways we could have written this we're we're trying to show you a little bit of a cheat code to learning dexs again as we mentioned it's not going to write perfect dacks there's going to be some problems with it but again it's It's a little bit of a guide to show you one how you can

learn some dacks by using this calculations tool you can read it and figure out what it's doing but it's also can be some shortcuts because sometimes it does it right sometimes it does it wrong but you should always look at what's created for you yes which is why I love to test everything in a visual as soon as I make this I'm going to do the same thing but but let's go ahead and hit add and see What happens here oh my that is quite large let me shrink this down a little bit for us

and you can see all of the dacks in here now this is quite a larger Dax function compared to what we just created and it has a lot of different layers to it it's really cool to see it all and we're going to possibly I'm going to show you too how to make you can make adjustments to this if it's not not exactly to your liking if you want to tailor it a Certain way you're able to but let's test this out first and then decide if we want to make some adjustments from there so

in my Matrix here I'm going to cool I'm just G to yeah we're losing real estate yeah it gets a little a little small there and then I am going to add my reading score reading score average percent difference from and so that's quite yeah quite a long name for a measure there um you can obviously adjust that if you want to you can Change the name of it it won't affect the formula at all and once we add this in we can scroll across I can see my radi score average difference from 22 23

but where and we can see it's all zeros here because it doesn't have prior year to look at it doesn't have 2122 so it has nothing to compare to but the magic that's happening is down here reading score average percent difference from 2223 we can see see the elevation here from the prior year so there has been Slight improvement from the year before which is great to see and what I actually want to do while I'm here is I kind of want to hide this column because it is just comparing it to itself so I

can select this I can rightclick and I can exclude this from this Matrix so I'm just going to go ahead and exclude it and there we go all right cool so now we can see the last year of school what we have information for the reading scores and also the percent increase so as far From from taking this back to the business scenario we can see that the tutoring is helping it's it's minor increases but we're seeing overall test scores are increasing uh year over year here so very very cool now some of the other

things that we could do in this data model we're trying to keep this data model kind of simple because this example is uh meant to be small because we're trying to learn powerbi but if we had something like a date table in here We could do some other more Dynamic usage of analysis over time but this is perfect this answers the question did we improve year-over-year and the answer looks like it's yes yeah well things are looking better now um I know GRE Greg is going to help us through and guiding us through some data

visualization stuff later but I noticed that there's a little bit of repetitiveness with that visual it's showing us the total again at the end how is there a way to get rid Of those totals on the tail end it just seems a little repetitive yeah we can definitely do that so this is just altering the Dax a little bit so let's go ahead and I'm going to find that quick measure that I made to make sure it's the right one here all right here we go and as I said you can alter these it's not

like a oneandone like you put it in there and that's all there is to it you can go in here and add and subtract things that you want so I'm Going to actually add something right here I'm going to get rid of this comma and I'm going to paste this little bit in and then I'm going to hit enter so real quick summary of what this is doing this is saying you know you had the zero sents there earlier yeah you you filtered them out so we're not going to really see the change here so

no problem there but what this would do is if we're ever looking at the year 2223 it's not going to display a percent because There's nothing to compare against it's comparing to itself so this the point you're showing here is you can modify the Dax that's written through that quick measures you can make small changes or or big changes if you wanted to but overall you can see it didn't impact our visual too much because we filtered that we excluded a moment ago the prior year yeah but if you wanted to get rid of that

in a different way there you and you can also modify Dax it Worked perfectly fine and went really well so if I want to get rid of those column subtotals because I have on object on right now I am going to go ahead and go to this little paintbrush right here where it says add or remove visual elements when I select that I can get rid of this colum sub to those by just oh nice look at that how beautiful that's great I love it and br will kind of highlight a few of these data

visualization things which by the way You know you mentioned this in the very beginning a lot of times when you go to build out your data model you'll end up throwing a lot lot of visuals out there for doing testing Greg may come in after us and he may kind of wipe the board and start new but it you'll do that this is reality right in reality you're going to build visuals test build a visual test modify your model make changes and it's a very common practice right definitely you're going to have questions along the

Way that you're going to have to answer with your data model so the best way to answer those questions is to build a visual to see if what your data model is providing is going to answer that question a visual is able to be processed 60,000 times faster than text is so best way to answer your questions as you're going just make a visual oh I like that little fun fact there at the end too well Amelia thank you so much for joining this has been awesome before You go though can you give the folks

that are watching any tips on other classes more in-depth data modeling classes that you would recommend what what do you think folks should do after this this was obviously a little bit of a taster to get you familiar with what you can do on the data modeling side but someone was looking to go far deeper in data modeling what would you suggest so on our on demand learning platform we have a variety of classes Devon Mentioned earlier there's a great three-hour class on there on the introduction to data modeling where even though it's an introduction

it goes pretty in depth into Data modeling and powerbi but one of the ones I suggest more than anything is any of our Dax classes I always tell people there's one thing that a lot of people are afraid of in power beon it's Dax and you shouldn't be afraid and it's definitely something you should learn over just about Anything else because it is so helpful in your data modeling process that's awesome thank you so much this has been great yeah uh everyone you'll see us come back here in just a few moments with Greg Greg's

going to be taking what this this data model that Amilia did an amazing job on and he will be building some beautiful visuals with us on top of this so we'll see you in our next part of the session see you [Music] Then welcome back everyone to our next section of our class and in this section we're going to be focusing in on Data visualizations to talk about data visualizations I brought in the master Greg trec also know known as young trezy that's for those out there that know that nickname uh Greg real quickly for

for those that maybe don't know Greg I think many of you do Greg's a former educator but has been with us for a number of years now training on powerbi Has a special fascination with data visualizations so you're really passion about that area you teach things like our powerbi boot camp our advanced powerbi boot camp you do a bunch of stuff with powerbi and you've created loads of content in our ond demand learning oh yeah yeah but today we're going to be looking again at data visualization so maybe the best way to get started Greg

is let's hit that why again why is it so important to get the Right kind of visuals why is it important to take what we've talked about in our previous SE sections on data cleansing and data modeling and now bring it to data viz it is is absolutely crucial and it's really based on a little bit of you know traveling around teaching doing the boot camps and then of course in Virtual mentoring and other sessions people hey can you look at this and sometimes it's like last five minutes of class hey could you just take

A peek at this real quick and I have seen million-dollar companies with some amazing reports but then I've also seen reports that they have very low interaction if you go to the powerbi administration you look at the usage nobody is truly using the report so it falls flat I kind of make a joke of it but if a data tree falls in the woods and no one's around to hear it it never I mean that's that's kind of what I go to but it really is the case here If we don't have good visualizations we

don't have engagement and we're not making datadriven decisions which is what this is all about is making those informed decisions with this amazing tool that we have so that's the big thing that's awesome now knowing the data we're working with we're working with Education data you're a former educator so was Amelia so was Angelica talk us through a little bit about where do we get started how do we get started With this kind of thing so big thing right two perspectives and all my Educators out there you can think about an admin perspective there's a

lot of things that Administration might care about there's a lot of thing that the individual educator might care about and there's plenty of areas where they do kind of interact I think the data set that we're working with has a pretty good represent presentation of some sample things that you'd see we've got Reading scores how are we averaging we're looking at a district level maybe you know we could eventually go to the school level even the class level this is data that admin's going to have the individual Educators going to have so I think this

is a pretty good reflection of a data set like that it's new it's refreshing it's a good good look into that space for sure and there's a couple of problems that are associated with this data we're going to Fix visually that I think you'd find out in the wild in the actual classroom in the actual School District as well that's great so when you get started let's say you've created a data model you've gone through the steps that we've already done together as a class and you're sitting down to start to build some visuals on

top of that model how do you get started do you just start poking around in powerbi and slapping things out there or do you have a little bit More of a plan than that so I think that's a big mistake it's so easy when you get the hang of this tool to go in and just wildly like Wild Wild West like I'm going to make this and a part and a lra and a pure you're going to make all these things but I really think data storytelling comes into play here that's probably one of my

favorite classes that we have on the OneMain learning because it's all about framing your report to convey a certain message so typically What I do and I always instruct people on this is I focus on maybe three to four questions that I think are absolutely essential to the data right something that we have have to convey so if I'm looking at this data here's a couple sample questions I want you to just kind of ruminate and think about right now so one of them would be okay what school district is doing best easy one we

kind of already got a good visualization letting us know Hawthorne Here is doing pretty good so that's the first one another thing is I'd want to know okay what state has the best reading or the best math scores so a visualization could come into play there another one I might do is what about tutoring is it having a positive impact does it matter if we get tutoring in the morning later in the day and then finally maybe a little bit of extracurriculars how do those play a role that's like three to four questions That I

can focus in on and I'm going to start building my visualizations in my report to follow that to convey what is the answer to that another perspective if that's a little bit too much for a data set just ask ask yourself what is one Trend that I know about because I'm I'm you know you're you might be the data expert at home you know your data set what's one thing that you really could convey and convey it so well that it was unmistakable everyone would know About it if you can do that I think you're

way way you're going to have a much a much more effective report for sure I like that yeah I like the idea of starting with those questions those are some of the same questions we talked about earlier with Allison coming back to play here as we go to build our visuals if you can think bigger picture what is those those questions that you want answered then you know I I've seen too often with folks that are new to Powerbi they just start poking around dragging visuals oh what's interesting this might be interesting but if you

ultimately have that business problem you're trying to solve it kind of brings it back to ultimately why you're doing this and sometimes there's just so many things it's overwhelming for someone especially that's just consuming their report they don't know how to build they're just consuming the data on the powerbi service or someone said hey Check out this report and they're going to get lost so we definitely don't want that not not a planned question for you so not rehearsed here but how many visuals on one screen do you think is appropriate oh to to your

point about it could be too much and you get lost I typically go with the format of the top ribbon across the top we can have slicers and a numerous amounts of those those not too much of a limit but I think the main body of a report page I'd say 3 to four is still a good metric if you're having card visuals gauges something smaller sure but if you're bringing in something heavy duty scatter charts line charts Maps those require that attention in that space welld deserved I might add they take up a little

bit so IA 3 to four is probably a good metric I'm looking at a report right now and I'm saying we're right about four I think that's pretty good and actually I've got got a special Theme background we're going to apply today that is preset to kind of help everybody with that design process how to visualize where typically I would actually put stuff in action so well before we get started I got one more of your classes I want to plug because I think it's a really good one is you have a data literacy class

which I think also ties really well to this because as you mentioned too many things on the screen you can get overwhelmed not just you as A developer of the report but your your consumers of the report can be easily overwhelmed with too much so I like you're kind of rule of thumb of three to four is what likely makes sense and you make exceptions you're not a hard hard Tyrant when it comes to number of visuals but uh kpis card visuals those you can make room for more so all right I love that that's

a great intro let's get started so how are we going to get started with our solution today okay so Now that we have those questions in mind I want to bring everybody into visualization mode so I want to get us into that visualization mindset and start by having a coners ation about how do I go about formatting my visuals so that's going to be our Focus here but you'll notice that modeling can come into the conversation we can also have a bit of a talk of uh what type of data we're bringing in measures decks

they all still play a role here so we're not Forgetting those things we're just adding and expanding onto them right now a reminder for for those that maybe just jumped half doing this video with us there's lots of visuals on the screen right now these these were part of the modeling experience because as you kind of lightly mentioned there when you build your data model often times you're having to build visuals to test out your data model so we might ditch a lot of these and that's okay or a couple of These or modify a

lot of them but that's really part of that modeling some work some don't work you know when I'm taking a look at this report immediately I'm looking at a couple of things just getting us into that mindset right one thing I'm saying is like okay I don't mind this but we're going to have to have a conversation about those blanks right there another one is okay table hanging out having some fun I like that it's got good data it actually lets us Know kind of a little bit about when people are getting tutoring we could

add to it but maybe it's better suited in other locations I agree yep so where can we start well one thing that I would say is let's tackle this first visualization that was created the goal here is a sum of math score by extracurricular activities so this is letting me know okay how are people scoring how are my students scoring based on what extracurricular they have I can assume Right now that my blanks hanging out right here are people that just don't really participate potentially in those extracurriculars that can be a wide range of people

not everybody's doing those so if I want to focus on those extracurricular activities one thing I can do to immediately apply a filter onto this report is I'm going to go in hover right over the blanks I'm going to stay there and I'm actually going to do a right click the moment I do a right Click you see my cardinal rule of powerbi you can click it you can hover over it you can right click it and then you can see stuff so this is letting us know right now boom I can exclude this right

out of the gate now what this is actually doing in the background is it's applying a filter which will show up over here on my filter pain but it's nice and easy to rightclick go in and have a little bit of time with it and I'm actually going to get rid of that Right now because I'm more concerned about the people that do extracurriculars I want to see them in action and there we go I can tell right now music is holding the lead with MTH scores a lot of research about hey what type of

activity you're doing is it going to help you out with other things that whole right brain left brain so I can kind of see that in action right now and if I can interject for just a moment I realized we fixed this in the previous Sessions but I think we were just doing a little bit of testing can we change that from sum of math scores to do an average just because knowing the sum it could be overly influenced with the number of people doing those activities absolutely let's go ahead and change it here on

that data options now you see here it's just tucked into the corner right there I can still eek it out and grab it and I'm going to do that and we'll go ahead to our field here we can See summarization we can see field we're going to leave our math score right here and we're going to go to summarization and we can change that right into the average there now this is using the existing y- AIS here but if I believe we actually have over here a math score average measure that we can bring in

that actually be pretty efficient that is our explicit measure with that awesome calculator versus what we have right now that implicit measure both are Pretty great ultimately I stick to the explicit but as long as we've got either of those situated inside the Y AIS you are totally good to keep cooking Perfect all right so what are we going to you said we're going to add some labels to this one I think we're going to add a little labels and I think that's an easy one for people to add on because it adds actually a

lot of information and I'm going to let you in on a little bit of a secret in one of the you know few months Ago updates we have the ability to add additional details so here's what I'm going to do I have right over here my button to add or remove visual elements I'm going to go ahead and select that and now I have the ability to look at some of the options but also hit up more options more options is also reflected here right this button this format button I can go here I can

add that in and very easily see all that this form pain has to offer I always joke it's an Old timey paintbrush when I was learning powerbi I don't even know if you know this when I was learning powerbi I got a TV and it played Bob Ross 247 free Channel and I just thinking that's the Bob Ross button because it's this old school oil paintbrush that I'm going to stick to so we are literally going to be Bob Ross Report designers right here you have a lot of options but we're going to move down

and look for dat labels this is a feature you have to turn on in your Format area you're either turning something on or you actually have to hit the Chevron to expand it we're not going to hit the Chevron right now we're just going to open up our data labels and there we go right out of the gate I can see that it's letting me know the exact value right here so I started out looking at okay well 322 that's the average score and then as we go down music's pretty close and a few more

well what if I don't like the way this looks The greatest thing about formatting visuals and power API is you can go right on in and change whatever you want for the most part and I guarantee you if you don't if you can't do it right now you will be in a few months in some of the new updates that are coming out so first things first I have some options I can apply it to just this series for now it's the only thing I'm looking at I could make these vertical thank you please don't

do that I always J anyways yeah just just just don't just don't that in pie charts no um but I have the ability to do that I also have the ability to make it on the inside on the outside whatever the base I can change the locations right there under that area called position so if I go to position I can do that I actually don't mind the auto for now because it helps us with spacing so I'm actually going to keep that up and I'm going to move down A bit here under data labels

notice this whole area these are all specific settings inside data labels that I can adjust and I'm going to move down and I'm going to find this area called detail I'd like everybody to turn detail on with me for just a moment because I think you're going to like this feature if I add on details this this takes one additional step it actually needs me to place more data in here when I do that that number will be reflected Alongside my other item so I've got average of math score what I also might say is

okay I want to look at the number of math tutoring hours alongside those average scores based on extracurriculars so what am I going to do I'm going to need to go ahead and open up that math area find those math tutor hours and I'm going to drag and drop that right into this detail pane so not over here to the build area leaving that alone and right into the format Pane boom now if I take a peak this is actually going to Auto but if I had Millions I could change it to millions billions trillions

of dollars thousands whatever you want to do you actually can edit that I think Auto is actually okay and let's take a closer look right now I can notice H well some of these extracurriculars are getting more tutoring hours but they're not performing necessarily the way I would have potentially expected some are Actually getting a lot in this other category but their numbers are down so this is a question I would say Okay I want to start investigating this area in particular also note format pain I could have just selected data labels here gotten my

initial data labels and had some good fun with it you have other things too backgrounds I can give them a little bit of a background so they stand out I could make it Pink I could make it yellow I could make it orange whatever You want to do with it you can trying to go Gator colors on in there get out CH so I have plenty of options there I think data labels are a nice nice and easy way to amp up your reports for sure that's how we format one visualization at a time as

much as I would love to sit here and click on every single one and add all these different changes you don't always have the time to do that so what I want to introduce to you now is a section called themes themes are a very Nice and easy way to AFF change to your entire report just like that I absolutely love being able to do it it is going to be located under the view tab here so what I'm going to do is actually I'm going to deselect this for now just so we can focus

here on our themes and what I've got is View and themes themes has its entire space where you can open it up and visualize everything so go ahead and take view here and then open it up with that Chevron drop down take a peek as you expand here oh my goodness we have a lot of great things to do as much as I would love to tell you these are all amazing here's your Barney theme all you have to do is click a pre-selected theme and now it's love you okay so not not what we'

need as much as I love Barney not working here for our data I have some other ones this is the blue one I call it it's storm but It's I call it the Frozen theme it's actually all shades of blue I've got plenty of other ones here but I got to show you just one get out your floppy discs oh goodness you can't even like get the full experience there you go that's a better do you hear it I think it's dialup okay not really what we need here but it does have some validity there

are some things you can do and bring in here That are helpful if you are ever lost now you are found because boom right here here this is how you go back to normal this one is your default theme you can always click this and go back those are some generic ones very nice and easy to do the section also reminds me you have another course called Universal Design right where you talk about some accessibility features things that you should be considering that a lot of people forget about um you I Think you spend a

little bit of time here but you talk about a lot of other things so definitely worth exploring that course as well for sure and one of the things actually was these accessible themes now Microsoft has done a great job of providing accessible themes and powerbi itself is very very accessible as a program but in that class I actually worked with a group out of Japan on how to bring color blind themes or color vision deficient theming Upgraded a little bit so I share that research with everybody in that class and I I think it's worth

investigating because again one in 10 males have color vision deficiency in some form or another so I know it actually impacts your organization tell you the truth someone in my advanced boot camp uh was like oh my gosh thank you for this theme they are actually colorine so there you go it it's it happens more often than you and you had 10 people in there Didn't you did I did so I hit the I hit the threshold now I'm going to go back to my default theme Here for a moment and I actually want to

showcase how to create our theme now at the bottom you're going to see a browse for theme theme gallery and a customize current theme if you want to make your own theme follow along with me here customize current theme this is we're going to make your own if you've got any branding right if you need to follow any branding Guidelines you need to have a certain color because that's your Department's color whatever it is you can adjust for it right here in customize current theme that is where we're going to go and you know what

we're going to have a bit of a competition oh I like competitive things here here we go so I want you to follow along here and I'd really love for you to engage with us and share your theme but we're going to make an ugly report theme here as much as reporting should Be good themes I know not everybody's that great at it right now so I want you to have the opportunity to try it out make an ugly theme grab your Neons grab whatever just make it atrocious and we're going to experiment with it

here but along the while I want to provide to you two particular tips that I have picked up with my time here at pragmatic Works traveling all around some great techniques that will instantly make your themes way better we actually had Someone make a theme in class once and all of a sudden the boss said hey guess what you're the powerbi person and before you know it they were emailing me oh my gosh Greg I I need help I'm I'm apparently the power VI guy at my office so let's actually get to it first tip

I'll tell you color one boom that's your main theme this is your biggest change so if you want to have the biggest impact it's going to be color one so if you're utilizing Branding guidelines you better be making sure color one is something very impactful we're going ugly though right yeah I'm going to go I'm going to go for the ugly neon toxic waste green if you have branding though you can actually grab the hex code or the RGB values paste it in there and you can utilize it copy paste it will work the other

colors don't really matter here since we don't have visuals that utilize multiple we don't have a Legend in place but if we did this is something I would come back to so really that's your main stuff right there let's move to text text has some additional features that will make this super super ugly uh General here that's one of my you know pet peeves is there's not a ton of fonts here I'd really like more but you have the Microsoft family of fonts if I'll give you a tip here if you're looking for a very

readable font for Dan it's the most readable font that is out There a lot of studies show it's the most readable if you want to go super ugly I would say let's go with wing dings yeah I know and I'm gonna make my wing dings font really big and I'm gonna make it uh let's go let's go let's go fire engine red magenta e wow nice perfect wow it's going to be super awesome you have outdone yourself a friend chef's kiss this is awful so now I can change titles cards kpis tab Headers but you

know what you can go and explore here if I want to make any changes I certainly can easy to do so these remember are going to be saved as you move forward up next is visuals let's go ahead and navigate here folks to the visualization part of your theme now when we talk about visuals we are talking first about background background is going to be everything that is behind your data so everything behind There is going to reflect that color so I've already got green data bars I've got red fonts purple o like a neon

purple I think that's going to be just perfect now another thing on note here is you have transparencies these will make it seethrough or not you can mix and match and have it just a slight purple slight blue whatever you think is the most effective you have full rain but I do want to give you a really big tip here Which is going to be border border is a big one to turn on so again visuals then go to border and go ahead and turn those on the reason is you can make them disappear but

a border will literally draw a line around your report visuals this will make it stand out make it more neat you can always make it the same color as your canvas background and call it a day and it will disappear but what we're really after is this right here Radius radius is actually going to take your visualization let's say we got a nice little visual here you know just chilling little little column chart and what we're going to be able to two is round the corners on that visualization give it a little smooth edge I

like it something different you know you you know you you all remember early '90s 2000s computer games computers very sharp edges clippy popping out in the background saying hello can I help you With your emails but this rounding is way easier to do anytime you look at teams you look at any messages you're going to have this radius I have made so many radiuses here's the magic number 25 pixels okay that is the go-to you put 25 pixels instantly looks really good I know we want to make ugly themes but I do encourage you to

put 25 pixels here just to try just to give it a peek if you go more it looks like a fun house mirror if you go less you don't really Notice it so I really like that right there and you know what I'm going to I'm going to make them uh a nice little teal kind of Aqua eish yeah perfect holy cow last but not least is Page under page you're going to have a wallpaper that you can apply this is going to be everything beyond the space here right that also applies to page background

which is some of the area over here so I see that dotted line on the bottom behind it's kind of things Beyond the wallpaper beyond the dotted line right indeed indeed so we can AFF change here I'm just going to go with one keep it simple go with a nice orangey yellow and now we're going to cross our fingers hold I mean brace yourself everyone this is going to be pretty awful close your eyes I'm going to press apply now oh forgive me oh my word oh my word I I think Greg you have send

beyond the data visualization God's Real to forgive here I I have really really have done myself on this one kind of McDonald e a little bit little bit gri Shake yeah a little Grimace Shake going on yeah holy cow one thing that is nice I do have these rounded Corners right here which are very very impactful so that has made some good changes you like that that's the only thing I like about it yeah I don't like the rest so make sure if you can share what you created with us oh goodness um I'm Worried

what we you can email gor.com your terrible theme have so many emails oh jeez so if you really like this your organization said wow this is so good you could go back to themes scroll down to the bottom and you will have an option here where you can now save your current theme I I hope you know don't ever with something like this but you can you can save it let's go ahead and try it if I want to save this theme I can go ahead And oh my goodness I'm just going to go my

very ugly theme now this is a Json file it is a 1 kilobyte JavaScript derivative file very easy to share put in a one drive put in a SharePoint have it ready for your organization along with logos and other things people can use as they design reports I'm going to save this now with it saved that means if I go back to my boring report just White black and blue I can Then go back to themes find browse for themes in that same area it's nice cuz that whole theme ecosystem is in this drop down

so you don't really have to go too many places go crazy with it now if I go to browse for themes what I should find is voila oh my goodness it's I can open it all I got to do is Select it again look at that folks 1 kilobyte boom right there I'm going to go ahead and open it up bra yourself again oh it hurts it's Very quick very instantaneous it is something that now for you know all of your reporting days you can have a preset theme that is your go-to every day you

can change it yeah there you go you know if you wanted to uh you could have one for every day of the week you could have one for every organization or whatever you'd like uh all right but in in reality how are people really using themes right it's there there's got to be a to this that's beyond being silly With it tell us a little bit about how you would really use this definitely so I think the one of the best use cases is an organization a department can set up a nice preset theme good

branding everything's there and this helps if someone's not you know comfortable with visualizations something that needs to be there that needs to be done they can just go in grab the theme from a SharePoint one drive save it onto their their desk Des toop and when they're Report building just apply it as they're going through and then that limits the amount of things that they will have to do towards changing the visuals adding data labels and things like that sure they'll need to go ahead and do that that's not too difficult but the general design

has been taken care of okay good now I'm assuming we're not going to leave this report in this state right I can't read the data uh so how you've shown this before but how can we kind of Go back into reset mode here easy way again go back to our themes as we go to our themes we're going to go right into default default boom you're back to normal you can take a breath I think the anxiety in this virtual room just got a little bit ease for everyone I'm going to be teaching like this

the rest of time you're sweating well needless to say I actually did create something pretty cool for us here which is going to hopefully help You understand kind of that design mindset and get in that mentality of what to do and this is actually where you do not have to utilize a theme it's great it's nice it affects a lot but you also have the ability to bring in your own custom backgrounds nice it's pretty great setting so what we're going to do is we're going to make sure that nothing is selected so I don't

want any visual selected I don't want to touch anything right now and I want to start thinking About my class files in your class files you are going to note that there is actually two things that we're going to look for here I've got my very ugly theme but I've got this prgy you theme this is going to be for pragmatic Academy the premier learning Institute where all this data is coming from and it's already designed to help us visualize where things go so you know what we're going to do we're actually going to go

and with nothing selected We're going to go to our format area and on that format pane we're going to move down to wallpaper and under wallpaper here what we can do is look for this image category wallpaper image and I'm actually going to select browse because I want to find that prag you theme that is just a actual image now I'm going to have it right here pagy you theme I'm going to select it and open it up once I do that oh my goodness let's take a peek this is actually pretty nice And you

know what oh my goodness this is so I'm so glad this actually happened if you look here look where I actually applied it I plac this on the wallpaper the wallpaper is going to Encompass that area in which I canot actually put things I can't move those images down so you know what very easy delete it let's move over to Canvas background and apply the same principles here right to Canvas background can do the same thing but now this image will be inside of that box Hold on Craig I'm not saying anything yeah why is

this blank this is where it's kind of an odd thing but it's important to note is this transparency area transparency at 100 % I'm going to put this down to zero and now it's here right so that image is there this is one instance in which that happens but do be aware it can change right if I put this up to 50 or so you'll notice starts to be a little bit in the background that can be Used to your advantage not only on the visualizations but here as well now Devon what I've actually done

with this is I've designed it to help everybody get in that mindset of where we should space out our images now every report is different but I think this is a really good use case of what we could do so let's actually do it I'm going to start by grabbing my average reading score by district and I've actually preset this image so you Can move them around and actually fit inside of the boxes we're going to do that for each of these here I've got another one down here place the table into this bottom one

that looks pretty good I actually have a confession to make I only gave us three boxes that's okay which means I forgive you we're g to get rid of this small table here I'm going to show you how we can get that exact same information back in a little bit but for right now we're going to say Bye bye bye and we're going to delete it I can click it press the delete key you also can use your ellipses so now this gives me the opportunity to space things out the way that I want and

you know what what do you think how does this kind of vision coming together so far as far as spacing is it less overwhelming than it was I I love it I love it and you know what I also like that you purposely made the mistake of doing the wallpaper I think until people see the Wallpaper they don't realize what wallpaper actually is so they can see now that that kind of overtakes the dotted line overtakes the Canvas even and takes them beyond that so yeah back canvas background here makes sense I love it this

looks great yeah big big thing same thing with the transparent CES now if you're wondering how did how did you create this image this is actually a PowerPoint background that I saved as an SVG file it's a scalable Vector graphic fun way of saying this is just a basically a PowerPoint background that we've saved in the right format and here we go and I know one of our colleagues who's on been on this uh class with us as well Allison Gonzalez she also uses things like canva so if you've used canva.com you could use that

to be able to design backgrounds for powerbi I think she even has some YouTube videos on that as well for sure and Allison's the one that taught me Scaler vector graphics from PowerPoint so kudos to uh her and the data storytelling class on the on demand learning go into that as well there so pretty cool stuff okay how do we enhance this well I did something on purpose right I actually left this whole kind of area so we could place information and you know what I've actually got for us today is a actual image for

pragmatic Academy where we're going to actually put that Into this visualization to make things look a little bit neater a little bit more polished and on brand so how do we grab images that's going to be in the insert area right over at the top ribbon insert and from there you'll notice there's a whole host of elements you can bring in one in particular is an image so I'm actually going to go to insert and then image and in my class files I have a pragmatic Academy logo which I can bring in I go to

image and boom Pragmatic Academy open it up oh look at that love it oh per peral the parrot hanging out and I'm going to go ahead and just make this a little bit smaller it's a little small but he you know what doesn't have to be the star of the show it can hang out right around there and just fits to the guidelines pretty nice nicely other things that I can take into consideration when I insert are text box if I want to give this a nice an easy title I can insert and then a

text box And very easily give this a name maybe this is my report summary this is just pragmatic Academy anything whatever I want to do so I'm going to resize this real quick go ahead and move it through make it a little bit smaller and then I'm just going to go ahead and type into here pragmatic Academy make it nice and large I'll select everything and let's go with maybe 32 looks pretty good yeah Center it out maybe go bold voila so I least have a title here that's starting to look a little bit better

now one last tip here is these images are items that can become buttons so they could bring you to another page they could bring you to a special bookmark tons of great things we're going to keep it easy today and what I'd like you to do is select the pragmatic Academy Logo with the pragmatic Academy logo selected in the format pane you're going to see it's now noted as format image and we're going to have the ability to give it an action these are all the different events that we can trigger when someone clicks on

our logo so I'm going to go ahead and give this an action and scroll down to the Chevron and open it up and now I can see a whole wide range of things this can do it can go back it can be a bookmark a navigator Q&A a URL a apply slicers a clear slicers we're going to keep it nice and easy and we're going to put our very own pragmatic Works website logo our web URL right here so we click on this and we're going to go to the pragmatic Works website so I'm going

to select web URL and simply I can paste in the URL I've got it right over here I'm going to go ahead and copy it and paste it right there and there we go now I have an action that will occur whenever someone Selects this now if you hover here it'll say control click here to follow link that's only cuz we're in the powerbi desktop I'm going to do that right now control click and oh my goodness look at that it brought me right to our website any action you want there you can customize you

can go there and you can check everything out so now this is a actionable button all right so awesome stuff we've got a clickable logo button We've got a nice Title Here here one thing that I can suggest is sometimes when you have a very awesome background like this it is a bit hard with these white kind of backgrounds it doesn't seem in place so I'm going to give you a quick visualization tip here go ahead and select any of these and what you'll be able to do is on the size and style find the

background with the background you actually can make these instead of that Pure white background semi-transparent I'm going to go with maybe 50% and now you'll notice that this is blending into the background a bit it's still apparent but it Blends in how do I transfer this over Greg do I have to make a theme that's probably what you're asking you don't have to do it you can simply click on the item and find a very very familiar tool your format painter under your format painter you can select And then add that to other items before

you know it now that really cool background is actually showcased here oh now look there though dag that's a good it's actually getting some extra stuff there that's interesting I didn't expect that so what's happening here and I'm glad you mentioned it is I selected this and then did my format painter so it copied over the background and my tool tip guess what if you don't like that you've got a back button right here undo The last action and I'm back to normal so GRE what if we wanted to maybe change all those visuals at

the same time is there a way to format them all at the same time there is format painters not going to be the best use case for this so instead controll click to multi select your visuals you'll then be able to still go to the format Pane and from there adjust the transparency for both of the visualization I see okay no makes sense yeah for sure I like that you give Me a couple different options sometimes the format pan is going to be best because there's maybe more than just the background color I want to

change and other times I want to just multi select them all and do the background you have some flexibility you just got to go with what you know what you're comfortable with and know there's a couple of tricks that you'll pick up over time and that's one of them that makes sense so Greg I do notice you have this Matrix in the Bottom Matrix looks pretty good but I want to be able there's really not a drastic amount difference between each of those it's hard to tell right right I want to add a glance be

able to see the difference between these different percent increases or the amount of score increases that is going to be a job best suited for conditional formatting okay and you may have used it in Excel it's going to feel pretty similar here it's very easy actually to apply it all we Need to do is select the visualization we want to tables and matrixes especially are going to be the best candidates for this but you'll notice that there is this FX button hanging out around multiple areas that is going to denote for us that there is

some conditional formatting that can be applied but traditionally we want to see like a gradient view or if it's above a million dollars we want it to be green and anything less is red those type of Strategies so how do we do that well luckily we want to apply this to reading score average let's do with that one here and I'm just going to go ahead and find my reading score average so now that I'm here I'm actually going to go ahead and do a right click this is a big change with on object interaction

usually you have this little drop down you can go there for data options if you can't rightclick and you'll see a conditional formatting you'll also see This if you need under your cell elements in that format pane so if you're following along with me can't really find it it is there in two places I'm going to conditionally format this background color easiest way to go about this is a gradient so I can say hey my low reading scores they are going to be red I'm going to add a middle color here select this so we

can have a a little bit of yellow here actually going to make it a little bit you know not as Jarring let's go like a yellowy green type deal not too too bad that's a little bit better and then I'm going to go into the green at the final element just select a nice bright green in there we go so this is going to look for the lowest value and the highest value and apply that gradient you have other options you have rules you could say if it's this then make it green make it purple

whatever you'd like the easiest is going to be this one here With that gradient and I'm going to press okay there we go ah there we go so now we have a gradient if I want to see this right I'm going to need to actually filter this in a different or excuse me do a different sort access so I've got to actually get rid of my columns here UND school year to see that nice and fast so I'm going to remove school year and then select my reading score average as I select reading score Average

now you'll see it is sorting properly that big green is popping through and if I actually look here I can look back and I see Tennessee Georgia Georgia with the failed Banks and then also with these School dat is here no we're missing but really Wyoming is in the lead with this reading score average and now I can visualize that a lot neater by the way worth reminding this is completely fake data this is this is autogenerated data so don't take Too much from this but it is interesting especially you can see such a drastic

uh Improvement in the reading scores for those districts in Wyoming compared to the others so very cool okay now Greg I noticed in the the build menu over on the right the visualization area that you have a set number of visuals are you just limited to those visuals or are there others that you can bring in there are a wide range of visuals you can bring in there's so many you can bring In that we actually went ahead and created a little bit of a cheat sheet for people to help you out with the initial

visualizations so I'm actually going to pull this over for a moment just show everybody what you can find on the pragmatic Works website on our cheat sheets this is actually going to have a full visualization guideline where you can talk about comparisons Trends statuses locations flows tables filters AI parts to a hole it has a use case and Exactly what to look for on those images so I definitely suggest this but to your point are we truly limited to just these we are not we have a wide range of custom visuals that can be found

under this option right here it's called get more visuals this is a area where we're going to find hundreds of other visualizations some provided by Microsoft some that have been submitted by the community so you can be prompted to log in here and in fact some people May not be able to see this part because organizationally it can can be restricted but if you're able to let's select get more visuals and take a brief tour here you're going to find all these visuals I mean we could scroll there are so many there's a fish tank

one there's a flow there's a slicer there's a word clouds there's so many amazing things here one in particular that's worthy of note is this one right here and I want everybody to look up the text filter the Text filter you'll notice is made by Microsoft and it is certified that means that blue check mark means it's verified Somebody from Microsoft has verified it and checked it out so with it selected I'm going to actually get to see the ability to look at a sample or I can add this right on in here so what

I'm going to do is add it in all I need to do is press add to bring this to my powerbi report I'm going to add it in and it's going to Showcase itself right Here in just a moment in my visualization pane import successful oh yeah and there it is right there is my text filter so I'm going to make sure I have nothing selected and I'm going to grab this text filter and I'm going to bring it into this top Corner that I I kind of reserved on purpose for our custom Visual and

what I'm going to do is feed it a field the field is going to be what I'm going to be able to type in search Do I have any text based values that would be perfect for this yes what if I were able to actually view this information by department so what I'm going to do actually excuse me District here so District I'm going to plug that in drag and drop to field and now I can simply search Hawthorne's the first one popping up there I'm going to go with Hawthorne literally now you just have

a search bar I look for Hawthorne and there we go nice that's a great one just Like that I love that now one thing you mentioned there as well that's worth kind of lightly bringing back because we we talked about it earlier is you said you reserved that spot for that visual very much so so that kind of goes back to the beginning where we talked briefly about you should have a plan when you go into these things right you should wireframe things up you kind of did that where you had this background ready to

go where you had these boxes talk about That for a moment if you don't mind so we thought about those questions three to four questions to guide us but we also want to think about instead of building everything right here all at once actually drawing it out whiteboard it out that's part of wireframing is seeing where things might go where am I going to put the bar chart where is the Matrix going to go that is why I built this out in a certain way I kind of baited a little scraggly so it's got Actual

wire framing uh so you know where to place things I did reserve space here because I know that I'm going to want to filter and I can do so right here with type in Hawthorne if I needed a search by grade level I still have additional space there as well well great I love that I think the custom visuals are really neat and there's loads of them there there's hundreds of them like you mentioned lots of other ones that are worth exploring but let's move on to Another type of visual that I think is particularly

interesting uh it's called Q&A stands for question and answer and so what does this one do talk to us a little bit about this one this is from the future it is an AI no it is an AI driven visualization called Q&A which will allow powerbi to utilize a AI driven visual to actually take plain English questions and Spanish and preview and be able to you ask a question and you can get a result and it Will ultimately also give you suggestions of what to build out here which is pretty great as well so let's

see it all right what I'm going to do is I'm going to name this page one here my report summary nice easy name just so we can keep track and what I'll do is go to the next page here and all I'm going to do is just double click anywhere on the blank area of my report here we go boom do a double click I was clicked out of it so I'm going to do it again boom boom and what's going to happen is you're going to get a text box kind of popping up I'm

actually going to have to go over here my double click wasn't working for me and and it's right over here as well it's a standard visual in the suite here Q&A and if you can't do the double click hack right there all you got to do is Select Q&A looks kind of like that chat box and there we go and you'll notice here what this is going to do is it's Actively going to give me suggestions to put in okay so I could say the top cities by reading score average let's actually try one of

the default ones and see what it does so if I ask this question what answer would I get and there we go so now I get top city states by reading score average and I can see there hey in this fake data set Wyoming again popping through as that city state and that state with a little bit more success with the tutoring program Perhaps so we get to see that here now if you want to standardize this you actually can click right here and make this into a standard visualization I really like that I've actually

made entire reports this way in a pinch and there you go now this is no longer Q&A it is actually one of our bar chart options so if you're new to powerbi this could be a great way to get started if you don't know how to use all the settings within the visuals start with Q&A and then there it's a phenomenal way you can start taking that list of initial questions and framing them as well into here so if I come back back into Q&A I'm going to make another one here I'm actually going to

type out a question I'm going to move it over here and let's try it I want to ask it a question so I'm gonna say I'm not even gonna be nice about I'm gon say show me down tutor oh look I'm gonna even go math Tutor periods and let's do BU average math score so oh got misspelled there let me fix it there we go all right so show me math tutor periods by average math score let me get math as well right and then I'm going to hit play here hit the submit button and

there we go so now I'm looking at all of the math tutor periods by average math score now powerbi is defaulting to a Particular visual so it's saying Hey do you mean this but let's take a look right what was our Trends first period second period third period um maybe that's right around Lun time yeah then end of the day we see this drop off so we could tell our tutoring program works best right after lunch people are not hry uh as well as in the morning then we get diminishing returns so we can start

to say okay maybe some of these time periods would be better than others Again I can make it into a visual leave it and then try another one now one thing while you're here which I don't think we've even really talked about is we don't have to get too deep into it here but the interaction between these visuals if you wanted to look at one city is Cheyenne uh kind of Follow That Trend or is there some differences like if you could actually click on and interact with the different visuals you have here you can

kind of see how they Work together just by default and we can do that right here clicking on Cheyenne I can say oh well second period in Cheyenne Wyoming tends to be actually very good what about New Haven Connecticut oh great pizza right I could see that as well but actually second period still shining through what about BR Junction well they're better in the later part of the day so I can actually multi select with control and start to compare okay so if I look at some of These regions right that are really high performers

second period fourth period it tend to do quite well and I can edit this change the interactivity to be whatever I'd like to as well which is pretty great one more here if I put in Q&A I also have the ability to tell it what visual I want so I'm going to come here and say show me reading score average by state as a map and what it's going to do for me here is start to create a map now I can Adjust this by state is a little bit tougher so I might want to

use citystate but I can actually request certain visualizations just like that to be ilz that's great I love that now some of like you said you can adjust the dots some of them are very similar in size because the numbers are similar but man look at what you can do there with just typing a little little phrase there you're able to get results back and see a nice visual built for you I love it This how I built a lot of my early reports yeah nothing wrong with that I don't if you [Laughter] noticed so

I'm going to go ahead and just give this a quick name Q and A and that is looking super super swell an additional feature we could bring in here is something known as a tool tip and the reason I suggest it is as you get more advanced building real estate is going to be key you're going to start Running out of real estate as you can see here if we add too much more we're going to overwhelm it so what can we do remember I said click rightclick or hover if I hover over my data

I'm actually going to see this this is a tool tip a tool tip is a reflection of that X and Y AIS so I've got average reading score and I've got districts there what if I want to make this customize I can but to do so I'm going to need to make an additional page down Here so go ahead and select a new page for me now this is an empty page which is good because I'm going to tell powerbi that this empty page is in fact a tool tip under format that page info now

here I'm going to have the option to change the page type currently it is a standard page I want to make this into a tool tip and that's what I'm going to do here I'm going to say this is a tool tip under this area powerbi immediately is going to go oh you want a Tool tip so you just wanted a smaller Page by the way D while you're here I happen to realize while your double click wasn't working for Q&A there's a setting right below that that says allow Q&A is turned off so that's

why it wasn't working earlier sorry to bring that back up it's true it's true but now this tool tip this idea looks cool so I can replace that popup you were showing a moment ago with whatever I want is that what you're saying 100% you can Customize it you can edit the size but for now I suggest keeping it small so it doesn't take up too much and block the entire got so I'm GNA actually go ahead and under this area start to design something and what I want to do is make a tool tip

that shows me what are some of the maybe tutoring hours by grade so let's find a visual that's good at showing grades a lot of people always say I want to use pie charts I want to give you my ultimate Alternative which is a tree map tree maps are the better pie chart they are great at showing and comparing items right how are what are the parts of the the hole kind of made up by so what I'm going to do is make this tree map I'm make it nice and big and under the tree

map what I'm going to do is I'm going to give it some categories and values well I'm going to take grade level into my category and I'm going to be able to see that by 9th 10th 11th 12th things like That and I want to see my math average or let's do math tutor hour let's take that in how do those play into here now you'll notice there's a bit of a change between 9th grade 10th grade 11th and 12th so I can see that my 10th graders they're actually number one when it comes to

math tutoring 9th grade not so much maybe we don't have as hard of the math class in n9th grade maybe you know pre kind of college submissions we get there so we could see an increase I Really love your Square pie chart by the way that's what it is but it can handle a lot more categories compared to a pie chart so it's kind of my go-to in this case here it's pretty similar but it looks actually pretty nice now what I might do here is actually get rid of the title on it this main

icon actually let's do that right over here under title and I can just have to keep that in back of my head or maybe make a new title and now what is left is I need to Determine when I should see this okay so that is going to be under that same page info show tool tip on so I want to tell people in some way when I hover over something this is the key to see this certain custom Visual and I'm actually going to make that District so all I need to do is drag

and drop District right into here whenever I hover over District I get to see it okay but it will filter so it's actually going to Filter to to the school or the district in this case that I'm hovering over I like it so any visual that has the district field in it will be able to filter this tool tip I like it sure now what I can do is nobody wants to see this backend development stuff so I'm going to right click it and hide it which means nobody's going to know it's there except us

or people that can edit I go back to report summary I find District data if I hover over anything Else I get a regular tool tip but I go to District data and now I can see individually the actual tutor hours by the counties by the districts here I love it oh oh look at that one o Cypress Creek what are you doing with you know 12th grade no tutoring yeah they're missing out so maybe that's why they're so low yeah so that's some of the trends that you actually can visualize right here and this

is really a good look at visualizations but as Devon knows in a Lot of classes that you've been teaching recently there's actually a way we can have powerbi build this for us yeah now co-pilots all the rage right so one thing before we get into co-pilot we should mention briefly this does require an elevated license uh there are some settings to turn on this part is optional many people won't have these capabilities so no worries this is also a preview feature uh that we're going to be highlighting here but to be able to Do co-pilot

and powerbi you need to have a fabric enabled workspace uh to be able to do some of the things we're about to show so again if you can't do this part there's likely a licensing reason why you aren't able to do that your administrator Also may need to turn on the fabric capability sorry the co-pilot capabilities uh we are going to be jumping over that a little bit we do have some other videos that we can show you for how to turn these features on But we're giving you this little bit of a a warning

ahead of time that if you can't follow the step don't get too hung up on it we're showing kind of the the next generation of powerbi here right it's too cool not to show you need to SA got you need to see it the the use case here Greg is I don't I'm new to powerbi I'm new to data analytics I need to take your data literacy class uh and so because I don't really know what's important in this data I'm going to ask Co-pilot maybe to build a report for me just based on nothing

I'm going to I'm going to say give me some suggestions right literally as long as you have a pretty good looking data model things are structured well which we have we could go right into co-pilot this additional button here and I'm going to enover cover insights just like that and these can get pretty you know these can get pretty wild so I'm going to click on co-pilot and now I'm going to have a Co-pilot preview option here show now I can chat I can add some things I can have it suggest content for this report

but we want to show you one here that I think is really awesome which is create a new report page I'm going to start with this suggestion and with that suggestion now what I'll do is okay what I create a new report page let's say about let's go about tutoring hours okay go into something Like that and we'll let it ride so it's going to think about things it's going to relook through the data set and what it's going to do is actually create the entire page for me right here so we're going to let

that run for a second while we're here if you've enjoyed a lot of what we've been able to discuss today I think it I think I want to give some of my top three class suggestions so I think three classes if you were in love with what we Just talked about you should check out number one the intermediate powerbi class with Allison on the onand learning there's some great things that you can observe there oh keep going keep going we'll hold it we'll hold it we'll pause it other thing that I'll build anticipation here other

thing is going to be Universal Design and accessibility not only can there be actual legal requirements for you to have accessible reporting it's also the right thing to Do and if you design with everybody in mind regardless of their accessibility needs you will make a much better report and last but not least is my personal favorite which is the data storytelling class where we go into the psychology color theory so many great things it is so so worth it definitely the three that I would suggest and worth uh worth peeking at awesome well looks like

co-pilot gave you a response right so let's see what does it do so what I'm Going to do is actually minimize these for a moment so it created this and I'm going to minimize some of these other windows so we can see it a little bit bigger this is now a whole page called tutoring hours and literally it's got kind of the general structure that we have here right that whole design kind of top layer everything situated it's got the average math tutor hours average reading time tutor hours how many tutors we have for math

how many tutors we have For reading now we can also see by District how many tutors are on hand that's a pretty good one to see and how many it use your Custom Tool tip too no I am constantly amazed at the functionality of this I mean there are some here average reading we saw a drop here that's kind of a gimme visual but this did a great job in the fact that it pulled through the tool tip oh I love it now we're with anything with co-pilot trust but verify don't assume what it Provided

you is perfect you're going to want to look at the results that it provides before you just accept it as gospel right but it looks pretty good it does look pretty good it does look now I really want to try that other option that you had as well the suggest content what would happen if we did that let's plug that in here I'm going to go ahead and select that and let's see this is also great because we're going to get some ideas of maybe what direction we Could potentially go some other addition points we'll

let it run here and there we go so here's a suggested outline create a page to analyze test scores across different District cities and state I could create it right here or a tutoring impact page grade level performance yearly performance comparison it's literally got four separate ideas of things that it can build for me right here that'll help me navigate and interact with the visuals On my report so honestly you could say you're not a visuals person but there is way way too many tools you have the them at your disposal now with some of

these features that's awesome I love it I love it Greg well Greg thank you so much for for joining us for this this has been amazing lots of really cool things that you were able to show us today from the basics of modifying a report visual to more complex things like the Custom Tool tips and custom visuals all sorts of Things that you showed us you highlighted some of the great classes that not just you but some of our team has developed and uh I appreciate it so much thank you for hanging out with us

for this session sign off with our group here and we'll be back for our powerbi services section next with Allison but Greg sign us off here hey you know what visualizations are the spice of life they're the spice of your powerbi reports I hope you take Even just a brief moment to invest in better report visualizations you'll see better engagement and hey looks better anyway so hey stay pragmatic everybody [Music] and well hello and welcome back to our last demonstration of our Workshop thank you so much for hanging out with us this this time obviously

there's a lot to cover with powerbi we could been weeks and weeks talking about powerbi I have Allison of course joining us back again You met her earlier yes uh and we are going to be covering the powerbi service Allison so this will be all around how do I take what we've designed and share it with others so tell us talk briefly first if you don't mind why is this part so important in the process of a powerbi project so everything we've done up to this point is all of the behind the scenes work and

it's very vital to our whole project right we have to make the report we have to bring the data in Clean it up go through the whole process but up until this point this is just a solo report this is just for us and maybe that's sufficient for some of you where you're just looking for your own insights but the beauty of powerbi the full power comes from publishing to the powerbi service which is powerbi.com and that is where we can share and collaborate on the web with all of our colleagues so they can get

the insights from this amazing report that we've Designed here that's awesome yeah and so we can once we publish to the web not publish to the web that's a bad phrasing that's a bad thing don't do publish to web but once we publish to the service we're then able to like you said share with others we can set up security we can set up data refreshes there's lots of stuff that we can do we won't necessarily be showing all of those things in today's session but what we will be showing you is how you can

Publish how you can share and then we'll kind of point you in the area of the data refresh we're not going to go super deep into it for today so Allison yes how do we get started here so let's take a sore screen here okay so we have gone through this report and you've seen this report move all the way from the very beginning stages that we talked about right planning out what we want to have bringing our data in doing our data modeling or cleanup and then of course Building out all of our visuals

now it is time right we want to take make this to the web right so we have our finished report we have all the pages everything is done so now it is time we get to click on this publish button right here so when we click on this publish button a few things are going to happen so first off the majority of the time it is always going to prompt you to save your changes even if you're like I didn't do anything if you have like one minor Click between things it will prompt you to

save because it always wants the latest and greatest going to the powerbi service so go ahead hit save now a little Pro tip whatever page you're on when you hit save is going to be the first page of your report so you want to make sure that whatever page you have selected is the one you want to show so for instance page one down here is hidden so I would not want to be on page one when I go to publish because then That would be my first page so once you hit save the next

thing that's going to come up is a window that's going to ask you where you want to save now with this we have a new terminology that we're going to talk about which is called workspaces so when we publish to the powerbi service so the powerbi service is really just the web version of powerbi right it's safe secure but just like there's Excel for the web there's PowerPoint on The web there's powerbi on the web now inside of powerbi.com you're essentially in the cloud right and we don't want to just throw all of our reports

randomly up into the cloud we want to have some form of organization so that is where workspaces come in so inside of a workspace this is where you are able to publish all of your reports you're also at that time going to publish the data as well so when we hit publish we are going to publish two things our report With all of our data and we're also going to publish just our data and that is called now the semantic model now that includes any measures you've written all of your data modeling and the beauty

of that is that now other people will be able to build reports in the service as well as here in the desktop off of that data the other things you have to consider when you're publishing to the powerbi service in powerbi.com is your license level so There are two main license levels to have which are powerbi pro that is a lower level it's pretty sufficient for everything you need to do and then there is powerbi premium and that is a higher level you just get some more options so generally if you have a really large

data model where you got some really special features you might need to upgrade to premium every now and then Microsoft will give you free trials Because I want to get you hooked on that so Pro generally is pretty sufficient for the majority of organizations with what they would need to do but premium is higher level there are some other license levels that you may see for a really really large Organization for example they give you a deal um for buying a lot of licenses but those are the two main ones that you need to consider

so we are going to be choosing a workspace and we did we did a little Cheat we made our workspace prior to this because your workspace has to exist up in the cloud to be able to publish to it but once we publish we're going to go up to the cloud we're going to show you how you can also make a workspace where that's at give you a whole tour around and by the way many people will probably see on the licensing front that their license if they if they are a premium capacity user and

we don't have to get in the Weeds on this but just recently That's been renamed to fabric so if you're seeing fabric start to show up they they've kind of shuffled some of the licensing around you may still see some lingering premium stuff but uh fabric is kind of the new new premium uh inside the service all right so when you publish to the power VI service right you're putting it into a work space which is just just an organization form for it I like to think of workspace as like a manila folder right we're

in a Giant file drawer we have this nice manila folder that has all of those like items together and that's a workspace right it just makes it really easy to keep stuff contained together so you can find it right and you can tell certain people that they have access to that workspace just to keep it organized now another little cool element with workspaces is that everyone gets their own workspace and that is called a my workspace so everyone has this Individual place where you are able to publish your reports your data and that is just

for you to try things out to practice with track reports it really only matter to you now there's no sharing right the whole point of the powerbi service is collaboration sharing with a my workspace there's no sharing from there it is truly a just for you space so if you are following along with us you can publish to your my workspace now remember you will want to be signed In so if you are not signed in you will be prompted to sign in at this point so that way you can see all of the workspaces

that you have access to now we are going to publish to an actual workspace that we did make for this class because there are some minor differences between your my workspace and an actual workspace really along the lines of what you can do in that sharing and collaboration ation so you can search at the Top If you have a lot of Workspaces but I'm going to go ahead and click on the one that we made for today's class and again you all if you're following along you are publishing to your my workspace feel free to

pause us if you need to to sign in um and to find that to follow along now we can see we don't have anything in here currently which is perfect we just made that so I'm going to hit select and then that is going to run through the process of sending this report up to the Cloud so when this is done we're going to have two cool links that are going to be there the very first link this is going to give you an easy way just to get to your report rate it's going to

take you right to your report on the web so you don't have to search around and find it the other one is also really helpful now powerbi has a lot of AI features that are built into the program and have been for years with co-pilot's introduction they've really been adding A lot of complexity to that and a lot of really great elements that are different um it's just making it smarter AI um Devon of course has told you a little bit about co-pilot and powerbi and some different things um and how that's been introduced but

with quick insights this is one of those elements where essentially it'll just run through your data and find some data points there uh sometimes those are really good insights and you're like w why didn't I think of That other times it's just kind of nonsense but I will say it is always beneficial to check these out when you publish your data set that way you can see if there's something that you missed so I'm going to click on that one first so that comes up and we'll take a look at our insights so once you

click on that it will prompt you to it'll pull up a screen this is actually in the powerbi service right now we're in powerbi.com and it always Gives you a little warning that it may take a few minutes to find your insights but we have a pretty small data set so it happened pretty quickly now I can see I have a variety of different visuals here and looking through them some of them I could think would be interesting like maybe debate and drama have noticeably more reading tutor hours for Birmingham Alabama maybe that's interesting if

I care about Birmingham Alabama but we can see we've got a Variety of interesting data here now if I like one of these and I was like this is an amazing Insight I want this on my report well you are going to then have to go and build this exact visual for yourself for your report if you want it to remain interactive where if you click on one data bar one data point this will also adjust and move with all of your data if you're like I just like it it's an interesting Insight I probably

want to Keep track of it but I don't really need to you know have it moving around well in that case it's the first instance here where I'm going to point out this lovely little push pen icon now Microsoft loves this push pin icon it is you will see it in pretty much every Microsoft program you can pin in team there's pin features all over the place but pin features here in parbi have a slightly different meaning in this sense because when you click on that push pin That allows you to add this visual to

a different ele elent that is called a dashboard no Devon a lot of people get the terms dashboard and Report confused like they'll be using those kind of interchangeably but do we want to talk about what is the difference in powerbi lingo with a dashboard versus a report yeah it's probably worth mentioning that right so what we've done so far with inside the powerbi desktop has been considered a report so everything we've Done thus far is a report when we went to publish that we published a report and you kind of documented that earlier where

you were drawing on the screen one of the really interesting benefits that you have with inside the powerbi service is you can create this kind of higher level dashboard where you can have multiple reports all feed into the same dashboard so say for example you work in finance and you have some payroll reports and some uh benefits reports and Some general ledger reports you can have one single Finance dashboard that your Finance executive looks at that pulls in the most important information each of those reports so if you like this visual right in this instance

you would be able to click this push pin icon it would pin this visual to the dashboard we'll build a dashboard in a little bit just so we can show that process at the end but you always know that you can come back here to your quick insights to grab any of These visuals once you make dashboards this is stored and you can access it through the workspaces as well so now that we're here in the cloud we the web version powerbi.com right you may hear a lot of terminology but it's really all the same

place powerbi.com let's take a tour around now if you did not click on this and you're just kind of following through you can again just click in your browser open a new window open a new tab type in powerbi.com it Will take you right here you will need to sign in for that but I'm going to click up to home so we are currently in the homepage for powerbi so when you sign in you'll always be directed first to your homepage so we are going to move through kind of our left navigation just to kind

of show you a few different elements over here and then we'll talk about some important features along the way so first up in home this is where you can See all of your recent things any reports any uh workspaces any data sets that you've been in recently or that you favored right you're in a lot I don't know about you Dev but I have so many that just in the past few days from teaching classes non-stop there's a ton so being able to favorite thing makes it really helpful to find the ones that you like

really fast and all you have to do is hover over an item and hit the little star we also are able to build a report Right here from our homepage and this is kind of reinforcing that ability hey You' published a said it's all cleaned up you did that hard work already now you can just relax just build the reports off of that um and you can do that right here we also have the ability in our create tab to do the same thing so Microsoft has definitely been working hard over the past year years

really to get the web version of powerbi as close as possible to the desktop version I Wouldn't say it's there yet yeah not quite but it's getting there it's getting there it's I say in the past year they've done a really good job there but now we have the ability I can build a data if I didn't have a data set I could build a report here with Excel CSV um even manually entering it and then here's the option right if I wanted to build a report off of the data set we just published out

I could do that right here in that create tab moving on down Let's take a look at our actual report so to find your report if you go to your work spaces button and this is always at the bot bottom of that left navigation when you click on that you're going to find a list of all of your workspaces here for all of you following along you all publish to your my workspace remember so you can go ahead and now click on that my workspace and that will open up and you will see your report

right there I'm going to go to the Workspace that we published to and we will have that same V so now opening this up I can see that there are two things right we published those two elements we published our report we published that data set which is called the semantic model we can take a look at our report right here and then we can discuss how can we share this with others right so when we open up our report let's see this kind of final view so remember whatever view whatever page You're on when

you click that publish button that's going to be your first page so I was on two during hours so I can see this amazing interactive display right I click on and any one of these data points this is awesome Greg did an amazing job with this we let copilot do this one actually see me look at our report summary that's that's the nice one that Greg had this beautiful background to look at that I love much better yes I love the little fraggy logo Up there with the parrot super fun but as we can see

if we click on any of these points right it's interactive that is still remaining right all the stuff that you set up in the desktop version that is still remaining we could search here if we needed to find specific districts all of that is working great and that tool tip feature that was built in right that page is hidden we don't see that over in our page list right so that's truly behind the scenes feature That we kind of worked on in the desktop and we now can see that here so now you have this

amazing report and Devon we want to share this out right so let's take a look at our options along the top here now you will see I have an edit button if you need to make some changes you can always edit right here in the service you're not able to do anything with your docs right now um you have very very limited data modeling capabilities and That most of them don't really honestly have that but you will see right I can make any visuals if I wanted to add a new page if I wanted to

change something on here if I wanted to modify let's say see if I can get rid of the background on here look at that you can go through make any changes that you need to super easily and then you can just go back to your reading view to get out of kind of edit mode hit save on my changes and Boom you can make those quick tweaks now I personally I don't know about you Devon I like to make my edits in the desktop I just like having the full abilities in the desktop versus the

service but it is Handy for those quick changes yeah I like the desktop too because otherwise your file that you have saved locally may get out of sync with what you have on here so generally if you if you originally developed it in the desktop I'd probably say stay in the Desktop yes but to that point if you are working on a report maybe I make some tweaks in here and you're like you know what I actually I need to go to my data model or I need to go make some dacks or I need

to get into Power query you're able then to download the file and you're able to then that gives you that PBX file so if you don't have access to the original file you're like I published it it's good it's gone I don't need this one you can always download a Copy to make those changes that you need to in the last tab now when we discuss sharing right that's over here in our export tab so first off with export I can analyze an Excel and this is a really cool one I will say some organizations

do have this turned off so if it's great out for you just know that's kind of an organization setting but that would allow me to see all of the underlying data and if I want to get into Excel build some pivot tables do Some extra and Analysis I could do that Excel I could take to PowerPoint if I want to give a presentation and incorporate that into my presentation we do that here with a bunch of them super easy but we're in sharing so with sharing I have the ability to give access to this report

to really anyone I want right and I can control that here with people in my organization specific people I can allow them to share this or build content right you can really hone In exactly what you want to put in here yeah kind of looks like the sharing from SharePoint or one drive right it has that kind of similar look and feel to it yes Microsoft there's so much going on there right they don't want to reinvent the wheel every time they come up with a new program or a new feature so they really and

you'll hopefully have seen this throughout powerbi that they really borrow from so many programs so a lot of The things you see like the pinning feature is going to be the same um across those all right so when you decide who you want to share it with right people in your organization people who are already in this workspace with access when I go to put them in right I can either put in an individual name so let's say I'm going to share this with Angelica right you worked hard on it we can share with Amelia

right I could keep going on well that can be a little bit tedious right because you're sharing and I have to type about every single person right if I want to share with every single trainer wa if we add new trainers which we're doing a lot then I have to come back in here and manage who has access to it I don't want to do that right I want to be able to set up my report kind of like set it and forret it in that sense so you're also able to send this To groups

so these are based off of azure active directory which help me out Devon I know this changed names recently Azure onra they wanted to make it hard for you exactly for this reason for you to struggle to say it so when you set up those groups on the back end you're also able to send that so that way then I can set to a whole group versus having to type everyone's name out and then check on that but I could also and I could Send it directly to them or I could also copy a link

here email it rate it to Microsoft so it works great with teams as well I'm going to close this out because I don't want everyone getting an alert that they they have access to this report and need to check on it but that's how easy it is to share and collaborate with others right I can give them access to the report say hey I made this awesome report I think it's going to be really helpful for you if you Check it out yeah and then easily send that over them now remember you cannot share from

your my workspace so if you publish to your my workspace you will not be able to um have that collaborative experience but when you're in a standard workspace that is built specifically for sharing and collaboration that's how easy that is to do and not that we're going to have time to show this today but there's other ways of sharing as well we cover this in Some of our other classes we can share using powerbi apps uh we can share at the workspace level again no need to necessarily get into that right now but there's lots

of other ways that you can share uh but this was a great kind of quick way to see all right here's my report how do I now go share it perfect thank you that's awesome all right so now the big question I have next is how do I make sure this data actually stays up to date so you took us back to the Workspace here and I can see even where we're looking right now that it was last refreshed at a certain point the next refresh time is na so there's no refreshed uh scheduled time

so how do we fix that you can get back always to your kind of workspace homepage by over on the left navigation that kind of sticks there whichever one you're on so it's really easy to navigate through so I'm on my homepage I can see those two items right I have my report we were just There looking at our report and we also have our data now at the data level this is where you would set up that refresh right because I can build multiple reports off of this I don't want to have to set

up my my refresh every single time so they make it simple right all of the data is coming from the same place so right here on your semantic model this is a little icon you are able to click on to the left of the type column where you were able to go and set up and Control the times the days all of that great stuff to set up your refresh so when you click on that it takes you into really your settings there's a lot of really good information in here to cover about a little

bit down the page we have a refresh option so you can open that refresh option up and then this is where you would set that on so if you turn it on you can see your frequency now a little bit about your data refresh right when you're setting up your data refresh You are essentially telling powerbi hey I want you to go back to the source of my data at these particular times go see if there's new data if there is new data I want you to bring it back and they are going to run

through your whole process in the order that you did it so it is going to take it into power query first it's going to add any of that new data in run it through all of the changes that you made in power query bring it into the desktop it is Then going to add it into all of the visuals so essentially it's making your report just like if you have built it with all of that new current data so we can set that refresh up to go on a daily or weekly basis so if I

needed this to be refreshed like maybe my source I have a new report and that new report drops like one a week right I don't need to do it daily because my data is only coming once a week right so you can set up your timing for your refresh based on how Often your actual data source gets new data right no need to have it just constantly running refreshes if you know there's nothing there so when you set up your times this is where that licensing comes into play if you're on a pro license you

get eight refreshes per data set though that is important right so if I have 10 different data sets I publish a bunch of reports a bunch of data sets every single one of them I get the ability to set the refresh for so all You have to do is Click add another time as many times as you need if you're on Pro you're going to be capped it eight times if you're on that higher level license though you get 48 which is every half an hour because you can pick on the hour and on the

half an hour are your options for setting up that refresh if your data like ours comes from the web this is all you have to do it's that simple right you just pick your times you put in hey if something breaks who Knows about it and hit apply and boom our data is updated if you're pulling your data let's say from an Excel spreadsheet that's saved on your computer in that case you would need to get a Gateway we're not going to talk about gateways that conversation can take us far to left field but just

know you would have a few additional steps added to your process if you're pulling data from your desktop versus from a web source which is why we always suggest Get your data from the web right even if it's a Excel sheet in one drive or SharePoint exactly yeah have it stored somewhere because you're essentially Cloud hopping so if you're a data store right we in the cloud right now in powerbi and if my data is also stored in a cloud it can just like fly on over get it and bring it back versus having to

travel all the way down yeah so that gate way is essentially creating kind of the secure connection between the cloud And your desktop right exactly it's just like a bridge to get to your desktop give it like permissions to get in there and you're good but yeah now we have updated data right I don't want this to go out at 1:00 a.m. so I would definitely want to change all of those times and then if something does happen if it breaks for some instance you'll get a nice little email letting you know so that way

you can go and fix it and see what's wrong well thank you so much Allison for joining us back again as we went through all of these different sections of working with inside of powerbi you came back here showed us how we could then share our results with others how we could then make sure our data stays up to dat how we can view it from within the powerbi service and interact with these reports I appreciate you coming back as you as we wrap up here though share with us if you don't mind what are

a couple courses Additional courses that people can go deeper into these different things we talked about good question because we have so many I would specifically say for this section there's a consumer class that is great right so it just covers using the powerbi service using data being in workspaces so that's a really good one that goes more in depth than we you have in this section uh and then there's also admin so you can get into more of the administration side on The powerb service if that's also you and you need to handle those

settings you can see those aspects as well that's awesome well thank you so much Allison and thanks everyone we'll see you in a bit to wrap things up hey thank you so much for hanging out with us for the day I know this is a lot to learn so don't forget this session is recorded you can always go back practice again what you learned on your own time uh as a special offer as a special thank you for Watching us today I want to make make sure you're aware of our OnDemand learning community plan that

you can click on right here and that will gain you access to all of our free courses and learn with the Nerds ones that we've done in the past including this one but as a special offer our paid powerbi classes will also be available for free for a short amount of time you'll see the details of that on the screen right now as a special thank you for attending And learning powerbi with us again thank you so much and I hope you enjoyed today's session and we look forward to seeing you in another one in

the future