PL-300 (DA-100): Microsoft Power BI Data Analyst– FULL COURSE in 2 hours

[Music] hi there this is tess now in this video we'll cover the entire content of the da100 exam analyzing data with microsoft power bi this video will cover the entire content following the structure outline as given by microsoft my role in my day-to-day work is being a business consultant in a big four company based in belgium i did the exam because i wanted to become better at data analysis using microsoft power bi i actually did the exam twice because the first time basically i was unprepared so i want you to avoid making the same mistake i did and just pass on your first try although i'm not a technical computer science profile i don't think this is necessary for people to learn data analysis or even teach it so without further ado i wish you best of luck with the course and yeah enjoy learning [Music] all right let's get started so the da100xm it's a new power bi certification exam out since july 2020. it replaces the order 7778 analyzing and visualizing data with microsoft power bi the exam is for you if you want to become a data analyst or you want to leverage the available data within your organization or maybe you're just getting started with microsoft power bi and you want to get a better understanding of the different capabilities the exam however is not for you if you've already used or if you're using power bi extensively but you want to get more familiar with dax and kodi m language the exam is 165 us dollars it takes 150 minutes so an hour and a half sorry two hours and a half and you have to get a passing score of at least 70 the exam is also only valid for two years it's important to note here that uh given that i've done the exam myself i had to do to sign a non-disclosure agreement which permits me from giving away any hints regarding the exam structure or specific questions however it's on the side of the microsoft certification site that the exam typically contains around 40 to 60 questions so how do you study for this exam well this video will cover the majority of the content on the exam but it won't cover all of it there's an online learning path provided by microsoft which you can follow but we feel it's a bit elaborate for people who want to get a better understanding and want to have a quick grasp of the functionalities there are also a number of youtube channels out there that stack on specific items such as guy in a cube sql bi which is mainly focused on dex and also also kerbal lastly it's important to note that the fastest way to learn is to just open power bi desktop and try things out yourself so how long will it takes take to cover this exam so i've made a distinction here between experienced so that would take you around three to seven days to get accustomed with uh workspace rules and maybe freshen up some things that you're not truly familiar with for people who are in sales finance or i.t in their jobs it would take about two to three weeks if you're new to power bi it will take around 30 days note that this is all eight hours per day so it's quite a broad exam and you need to be aware of that all right so what is a content outline the majority is in the first three parts so prepare the data model the data and visualize the data model the data prepare the data includes get data from different sources like databases sharepoint lists or excel files analyzing what the distribution looks like and see if there are any errors in the data modeling the data is truly the design of the data model we'll also learn how to configure relationships and create measures using dex model the data is the bulk of the content almost one third of the exam so make sure to pay sufficient attention to this section visualizing the data is creating reports creating dashboards and learn how to add tooltips and books mark bookmarks to enhance your reports filters is also an important part of this next up is analyze the data we'll look at how to enhance reports to derive useful insights and how to make your reports interactive we'll also learn to work with the analytics pane and look at some of the new ai functionalities microsoft has added to power bi finally deploy and maintain deliverables we'll take a deep dive into the power bi service covering workspaces deployment pipelines and how to set up subscriptions we'll also take a look at how we can schedule a refresh or at least set up a scheduled refresh important here as well is to always make sure that you have the latest exam outline on the dedicated microsoft page you can find the link of the latest outline in the description [Music] power bi desktop is where you load your databases transform them in power query editor and create relationships between databases after loading our data and setting relationships we create visuals in the report segment in here we can create all kinds of visuals to show our data in the best way possible and derive useful insights after we loaded and transformed our data we will be able to see all the rows and columns in the data segments relationships are shown in the model segment where we also set our relationship type and storage mode power bi desktop has two models the report view which is the default view and the power query editor view for those who have experience in loading data in excel the editor should be familiar the power query editor shows up when you load a database and click on transform data alternatively you can always go back to the apply transformations by clicking transform data in the home ribbon [Music] all right power bi has two main languages on one side we have power query m on the other side we have dax power query m is over has over 700 different functions and is used in the power query editor every action that you perform in power query editor automatically creates barcodem in the back end it's used to transform data before loading it into your model in power bi desktop the typical structure is it starts with a let statement followed by an in statement as shown in the bottom dax on the other hand has over 250 functions and it's created in the reports of the table view dax helps you create new information from data already in your model it's kind of similar like excel and we use it to create measures and calculated columns some common functions include calculate summarize year-to-date or related below is also an example of a typical dax function calculate where we look at the amount of salt items [Music] so remember we can load data a new and pop-up screen will appear we can either load transform or cancel transforming will open power query editor and will allow us to make necessary calculations and modifications [Music] in power bi we typically use two ways to store our data the import mode or a direct query mode import mode imports the selected tables and columns into power bi desktops desktop and as you create or interact with the visualization power bi desktop uses the imported data refreshing the data will try to re-import the entire data set again direct query on the other hand doesn't import any data into power bi desktop but as you create or interact with the visualization power bi will try to retrieve the data from the original source and it's typically a live connection for example with a cloud database like zur or a typical web tool or use google analytics it's limited to some data sources only now it's important to consider that the chosen option will impact performance direct query is less suitable when there's a lot of load time to get the data from the backend source when there's a lot of users that will use the reports and when there's row level security on your model that being said there are some limitations to import mods there's a one gigabyte limit for data sets in power bi service and the scheduled refresh can only be done up to 8 times a day now for our own models we typically use import mode as this is overall better for performance [Music] loading data through parameters a parameter is an item of information such as a name a number or a selected option that is passed to a program by a user or another program so it's kind of like a variable between different programs why bother about parameters well it allows for easily changing environments um using and reusing values such as countries categories consumers and use cases or that you can switch between development test and production phase and also apply dynamic filtering now it's limited to some data source connections and often it will be used in an sql context now how do we set up a parameter we go to power bi power query sorry and then firstly we have to enable the option always allow within the parameters field we'll connect to an sql server by defining the server in the database and we'll set up the parameter by clicking on manage parameters within those in the field we can indicate whether we want our parameter to be a text or any value or maybe you want a list of values and so maybe you want colors for example and you want to be able to easily switch different colors now it's important to note that this is not a full explanation because parameters is a very broad topic but you can find more information in the microsoft documentation next up we'll go to power bi desktop and we'll click on manage parameters we can change your parameters according to our needs so for example if you want to switch between databases in this instance in sales here we can do just that so quick recap parameters can be used to easily switch data sources or environments and also values they are listed in the query pane and there are also some advanced options such as filtering a data source logic statements applying dynamic names objects and so on power bi has a wide range of options when it comes to connecting to different data sources we can load data in both the desktop view and also the power query editor view let's take a look at what the different options are so we can click on get data or we can go on recent sources when we click on get data we get a pop-up screen of all the choices available to us you can see that there's a lot of options so we have files we can connect to databases to power platform to microsoft azure and also to online services such as google analytics or sharepoint lists when we decide to connect to a database we close this for now we get a pop-up screen we get a pop-up screen asking us to either load the data immediately into the desktop view or we can also transform the data when we click on transform the data it will open the power query editor [Music] when loading data into power bi we want to verify that our data is usable when we're creating reports and that it doesn't contain any mission values or the data types are not captured correctly when opening the power query editor you'll have a range of options under the view tab in data preview the most important segments are column quality column distribution and column profile they can give you more information on the distribution the uniqueness or the number of errors in a certain column note that these options give you information on a column base not on the entire data set now knowing which segment does what is quite important so try to remember the names and the respective function when clicking on column quality you can see what number what number of values are valid are empty or contain an error and clicking on column distribution you see distinct and unique values which we'll cover later on column profile has a wider range of options and it's basically a summary of both column distribution column quality but also some additional numerical information a quick recap here so we have the option to show errors show valid data show empty values in the column quality and the column distribution will show us unique distinct values and also give us an idea what the distribution looks like column profile contains all of the listed details now we also have mono spaced and show white space monospaced displays the options where each character has the same space and show white space is a way to visualize white space in data cells now we've already talked about unique and distinct values but what's the difference the difference so we have a column profile here that says okay you have unique and distinct values but let's take an example right so we have a short example here red jada jade cyan magenta anodic times cn so two times cn now unique values they appear only once while distinct values is the number of categories let's say so cm is is not considered a unique value because it's listed twice however it still counts as a category so if the number of distinct values in this case is 4. next up merging versus appending so we can combine our data in power kodi editor by either appending queries or merging queries appending queries is joining tables together by adding rows as displayed in the picture we have one table above with the same columns on the bottom and then bar bi we'll just add those two rows together you have two options you can expand queries or you can append queries as new it's important to know as well that you can also append a lot of different tables if you want to if you have 10 tables in the same format you can append all 10 of those merging queries on the other hand is a bit more difficult because it has a lot more options merging queries is adding two tables or more um into columns so you you're basically adding the columns together and to the right instead of on the bottom because the columns don't always match now it's in this case you also have to specify a join guides and there are a number of joint cards displayed and we'll cover this in the next segment so we have six types of joining the left outer will use the values from table a it will reiterate this but it will only take the the matching values from table b same goes for write author but in this case it will only take the values from table b and the matching values from table a the full author will not delete any information but it will keep all the data and it will add all the cells together where possible an inner join is a way to only keep data that matches whether it's the same between both tables the left anti will only keep the values that are not in the other table so it will not include the values which which are presented twice left empty because in this case it only keeps the values from table a the left table and right nt will only keep the values from table b [Music] right so next up is transposing pivoting and pivoting data now before we go into the theory let's first look at what we're trying to accomplish by using an example in the first example we want to have the first row as the first column as we want to be able to evaluate the different companies our transpose function will give us the required layouts but note that this function transforms the entire layout so now we have the companies listed in one column and then also the employees and the location just need to change the fact that the first row actually become the headers and we can do so by using this function use first row as headers so now we're all set to perform our analysis let's take a look at another example of the pivot and unpivot function so in this case we have stores in one column we have category in one column and then we have all the different years spread across the table let's want let's say that we want instead to have the years in one column and the sales value in this case these numbers are the sales values in another column we can do so by selecting those two columns then going to transform and then unpivoting the other columns because those are the columns that we want to display have displayed differently all right so now we can see that we have the years in one column and values in another and we can easily perform multiple um analysis based on this data set let's say we want to re-pivot our data maybe we want to perform an analysis based on the sales amounts per store and so how much did amsterdam stores sell in 2016 compared to 2017 not taking into account the different categories we can do so by duplicating this column we will delete the category column as we don't need it then we select the store and we go to pivot so pivot column a window will pop up and it will ask us where are the the values stored so in this case the values are stored in the value column and we can also indicate what kind of aggregation we want to add in this case we want to have the sum click ok and now we can see the different stores as headers and the different years as rows so we can compare for example the first year amsterdam performed the best out of those three and the second here it was brussels so that's it let's move on alright so let's do a quick recap of what we discussed so transposing is moving columns to rows for the entire table pivoting is changing rows into columns and unpivoting is changing columns into rows alright let's move on to the next topic [Music] data types are really important aspects of working with data sets power bi automatically tries to link each column with the correct data type but depending on your data source you might have to make some manual adjustments most of the time this is done in the power query editor data types are changed in the power query editor under the transform ribbon and then going to data type you can change the type to decimal number whole number data time date time values and also duration text and binary values it's important to note that if you select date time here that power bi desktop will automatically display a hierarchy for this value but we'll discuss hierarchies in a later stage now what about handling blanks empty strings and zero values let's say that i want to change this column to a numeric value now it's not specified let's say that i want to instead add it change it to a whole number because i want to perform some analysis later on it gives me errors because it cannot convert this text to a numeric data type now if i want to change this error i can go to replace values and then replace errors it will ask me what kind of transformation i want to perform on the error values let's say that i want to change it to the value of 1 because it should display that the quota has been achieved there we go now i can use this filter in a later stage to make an assessment whether certain store and category has performed according to their quota now this was an example of a cell error there's also a second kind of error called step level errors these type of errors prevent the query from loading and display the error components in a yellow pane examples includes my customer table where it says it can not retrieve the correct source another common data source error occurs when power bi cannot find the column you reference to in a certain power query m code alternatively to replacing errors you can also change uh values in in this segment and so you have to replace error segments and then also the replace values for example if i want to add words but change the the name of words to certain version words updated let's say i can do so and it will adjust the layout for the entire table this is also very helpful to retrieve for example spelling errors all right so we identified how we can evaluate and transform data types let's move on [Music] next we'll also look on how we can add or delete columns and rows let's go over the different options we're still in the power query editor as this is where the main transformations happen let's go to the associated product table deleting one or multiple columns is done to the remove columns option which is can be displayed accordingly now adding columns there's multiple ways so you have the column from examples you have the custom column info custom column conditional index and so on we'll start with column from examples in this case we just have to write what we want and power bi will recognize it and then also perform all the transactions or transformations for every row let's say i want to add microsoft to every product i'll just click microsoft access and then press enter and i can identify that it correctly displays what i want so i'll click on ok next up is the custom column custom column allows you to write the power query m codes directly and also work with tables let's maybe say i want the the same as before so i'll write microsoft and specify that it's an integer so i put it between brackets and then i'll add um and i'll do it like this and um i'll have the product so it doesn't give me any error so i can press ok but i'll also add a name to it merged to there you go now the third option is invoke custom column so this is another option where you can specify a custom function defined in the file for each row so you have to add a function query because we don't have any function queries it's empty for now another method that we'll discuss is the conditional column so in this case you can add if statements like you would in excel let's move over to the products table and illustrate this by an example so here you have the products according to price and let's say that i want to indicate that any products that have a price above 50 should be in the premium segment so i'll add a conditional column i'll add it it's header name premium so if the product equals sorry if the price is less than or equal to 50 it should be zero and otherwise it can be one indicating its premium clicking on ok will give me the correct result now i didn't include 50 into it so it will start from 51. all right an important feature to remember is that for performance region reasons it's better to add columns in the power query m editor than using calculated columns in the desktop view however the exercise we displayed here could have also been achieved through a measure or a filter now the final thing that i wanted to discuss was the index column so an index column is a very useful useful feature for adding a row counter to your data set so i'll just click on it and you can see what it does it starts counting every row this is useful if you want to perform advanced analysis now we can also add or remove rows instead of columns this is done through the home tab where we can keep rows or remove rows so we also have to specify the number let's say i want to keep 15 rows then it will delete the ones after that okay we can also indicate which rows we want to remove on the bottom uh or maybe we want to remove duplicates and so on alright so we've looked at how we can add or remove columns and rows let's move on to the next segment [Music] an important thing to note is that everything you do in power query editor will be shown as an applied step on the right the applied steps are all the transformations that you apply to the data and they are shown in their respective order so the first thing we did here was load the data to a source and change the type replace the value and add some columns now it's important to note as well that in the advanced editor we can see these steps as shown in power query m code and also perform relevant modifications so when opening the advanced editor we can see our steps applied using power query m code and it's it has a typical structure so it it starts with a let statement and it's followed by an in statement now the query m language is a very extensive language with over 750 functions let's go over to the microsoft docs and see what kind of functions there are taking a look at the microsoft documentation we can see a variety of functions so we have functions for data binary functions we have duration lines logicals and so on in fact there are over 750 different functions although you shouldn't know all of these it's worth looking at some of the common used ones so let's go back to our slides all right so this was the first big module prepared data so we got to understand the difference between rbi desktop and power query editor we also looked at import and the way to which we store data using direct import or direct query we've briefly covered parameters we looked at data profiling to column distribution column profile cone quality and so on we've also used a couple of built-in functions within the power query editor so we've looked at merging or appending our data sets we've also looked at built-in functions such as transpose pivoting and unpivoting and then finally we've used how we can leverage the advanced editor to make our model even better up next is modeling our data using the different functionalities [Music] all right we're in power bi desktop we want to see our relationship structure we can see that it's quite complex so we have a lot of different tables and they are connected by these lines the lines they represent the relationships you can also see that there's a one so a number and a star this indicates the cardinality type in this case it concerns a one-to-many relationship in this case it's a one-to-one relationship we can also see that there's an arrow the arrow indicates that the store table is filtering the sales table and the cross filter direction is set to single while in this case it's set to both notice also how when i hover over a table we can see the name the storage mode and the date at least at which it was last refreshed that's it for now let's go to the next segment all right in this segment we'll look at how to design our data model we'll define relationships hierarchies create measures and derive insights from our data using visuals first we'll look at how we should structure our model for optimized performance and usability data modeling is an important aspect of data analysis a good data model contains the right number of tables with the right relationship between them setting the correct relationship between the tables in a data model is important because the relationships decide the direction of filter propagation in power bi a star schema is an example of a data model when you design the data warehouse and it's yeah it's kind of best practice to have a store schema whenever possible it allows for better performance and also it's easy to visual but it's easy to visualize and it's also easier to use dex functions later on now what is a star schema well we have dimension tables and we have fact tables the dimension tables are those on the outside so those typically those typically contain um customer data product data store data values that appear only a few times and ideally only once then in the middle you have our event data so we have our transaction data our sales data which also references to the products or the stores and so on now if you use your imagination we can configure a star in the middle and then we have our dimension tables and our effects same now why would we use a star schema i said before usability performance and also faster refresh the common steps in designing a data model are typically the same we'll select and configure our data source we'll load and transform our data we'll set up a data model we'll configure relationships create measures and calculate columns introduce visuals and then derive insights all right let's move on now i've already quickly mentioned filter propagation now filtering is done through visuals and the underlying tables they are connected using relationships this process is called filter propagation the filters start propagating or moving from the dimension table towards the factory in this case if we wanted to know the sales quantity of products category a in calendar year 2018 we would use a specific measure power bi would first use the dimension tables to filter the data and then get the information it needs from the sales data now when we have our response we can display it in a report we can use a simple card visual or another visual whatever we like now when we discuss relationships cardinality is a really important aspect there are three options one too many or many to one one to one and many to many one to many will take one value in a table and match it with multiple identical values in another table so the one in this case will be the dimension table the multiple will be the fact statement most common type of cardinality has cap so i've already covered this so dimension table filtering defective now best practice is also to keep our tables narrow and long instead of wide and short so better to have little columns and a lot of rows instead of a lot of columns and a small number of rows this is better for our performance and other reasons now one to one is when we have one value in the table that has another value in another single value in another table it's using specific scenarios such as a degenerate dimension from a factoid table or multiple data sources for a single entity now when possible it's better to avoid these and use merge or append functions whenever possible finally we have our many-to-many relationship so we have multiple identical values in one table that match with multiple values in another thing it can be useful in some scenarios but it's best to try to avoid this whenever possible summarization and other numerical functions might not always display the correct results so keep that in mind all right let's move on now next up active and inactive relationships so some data models might initially require you to link to the same column in the fact dimension table twice and in doing so you automatically create an inactive relationship so what does it mean so an active relationship is for example in the picture below is between arrival airport and airports then there's another relationship between airport again and departure airports now it makes sense because often the arrival airports will be listed in the airports table but also the departure airport will be listed in there now in this case we might want to use functions that we could use functions that also call the departure airport but it's typically best practice to have two tables instead so in this case one for the departure airport and one for the arrival airport note that these are duplicate tables so our dimension table on the right is duplicates and now it's the relationship is active so we have two active relationship instead of one active and one inactive all right let's move on cross filter direction we've already discussed it so it's the arrow and it's automatically often dependent on your cardinality type so in one to one you won't have any option you will have to have both as cross filter option in some cases you have the cross filter option to set to both now what does it mean well both is means that it filters in both directions and it's also something that we describe as bi-directional modifying the relationship can be done in a cross filter function which is a dax function now in this case we have a model that uses a bi-directional relationship as you can see by the double arrow now the presence of that bi-directional cross-filter introduces ambiguity in the model which is a problem that often occurs with bi-directional relationships now what is it well it's a case where a model is um yeah a bit struggling or has potential issues that may arise because of the way the data model is set up in this case the problem lies in the fact that the date can be accessed through the purchase table um or it can it can go from sales to product to purchases or it can go directly from data purchases so this is a problem here you have two ways to retrieve and fetch your data now this can be dangerous as it may cause your model to break especially when we're introducing filters so the general conclusion here is to avoid using bi-directional relationships whenever possible [Music] so we've loaded the data in the power query editor now it's time to further develop our model we can easily add additional calculations using measures and calculated columns these are very often used in power bi as they allow us to extract information from the underlying data and help us make relevant numerical calculations such as sums divisions create percentage changes track changes across years and so on after creating a measure or column it appears automatically in the fields pane the fields plane is displayed here and you can see that there's a bunch of different icons to the different columns so this represents a table underneath it's a date which indicates a calendar this icon and the item below that indicates a hierarchy so a hierarchy is a set of sequential columns that are based on a logical order this icon represents a calculated column which we can add to the modeling base and then new column we can also add measures and they will be displayed through a calculator icon to create all of these simply hover over one of the tables and click new measure new column new quick measure and so on it's quite important to understand the difference between a measure and a calculated goal a calculated column will create a new column for your model and it will add cell values based on the function that you specify a measure on the other hand won't do that a measure is like a function that you create and it returns the results in a visual but it will not populate any cell values in a new column the choice between a measure or a calculated column also has an impact on performance the storage will be larger when you use a calculated column so also likely impact on performance but on the other hand um a measure might be harder to compute if you want your visuals to load really fast generally measures are more useful but trade-offs are the performance hits storage space and the type of expressions you can use for example calculated columns are often used when you want to filter on the results rather than just calculate results also in the context of slicer you might want to opt for a calculated column instead of a measure measures can be created in two ways we have quick measures where we can add some quick calculations such as the sum averages year-to-date running totals and so on or we can write our own measures using dax formula by clicking on new measure a window will pop up and i will be able to code the decks myself in this field i can reference to tables to columns or to values and then afterwards when i created a measure it will be added here in the fields panel this might be a lot to take in so let's do a quick recap of what we discussed alright so let's do a quick recap of what we discussed we have calculated columns measures and hierarchies so calculated columns is powerdi desktop which uses dax and creates a value from every row first is a measure who only creates single calculation a measure is a sort of data transformation but it won't create any additional columns in the power desktop data sets it also uses tax and you can write it yourself in the formula page or you can use quick measure in the fields pane the hierarchy is a combination of columns columns in a logical order in this case we have a region product category and a product subcategory it can also be a date which starts by year month day and so on it's detected automatically in the case of dates it's very useful in reporting and drill down functionalities which we'll cover later on alright on to the next section now we've already looked at some dax functions we looked at calculate before but some other common functions which you should take the time to truly understand among some common functions is group by summarize top n class filter and so on so you have statistical functions you have logical functions you have date functions you have functions that are used in relationships such as the are selected um the related the uh use relationship so all kinds of different functions with different functionalities and it's really important that you're that you know these functions and that you can use them now we also have tax statements so these are defined evaluate in order by these are used in specific scenarios an example of a test of a dex function is if you wanted to [Music] look at the last year's sessions on our website you would use calculate and then you would see which column it has to sum well some is default here or the sum is optionally here i would say and then we have the filter uh criteria so we have same period last year and then a date now popular challenge for specific dax formulas includes sql bi gynecube and also kerbal variables are specific dax formulas that allow you to [Music] introduce some complex formulas to your model while not making the formula too long but instead using variables as a way to improve the readability the complexity and the performance of your function so in this case we had a function that follows a lot of different functions after each other like you would see in excel but with variables we're able to write much more cleaner codes and it's nice it looks better but it's also more powerful more performance and it has less complexity from a reader but also for for you as a data analyst quick reference guides to the icons used a desktop you when you're working with power bi you come across these when you're working and building your models but yeah i won't spend too much time on this right now besides the common functions shown previously the dax documentation guide covers around 250 different functions there are date and time functions filter functions you have financial functions and many more that was the overview of the different decks functions onto the next topic [Music] in this segment we'll discuss the core of what power bi does visualizing the data and deriving useful insights in power bi desktop next to the fields pane you have the visualization pane so here we can choose across a number of different visualizations we can also load additional visuals either certified by microsoft or from third-party developers notice when i select a visual that it will have either two or three panes so we have the fields pane where we put in our data axis legend values and then we have the layout pane in the layout pin we typically adjust the formatting and the final one is the analytics pane the analytics pane has a lot of interesting features such as a forecasting feature you can also add trend lines percentile lines constant lines and averages so that's it for the different paints in power bi desktop onto the next topic all right so the common visuals in power bi are listed below we have line charts which we all know but also we have cards or funnel charts waterfall chart three maps we have a lot of them so it's important to know what visual want to use for our specific function for our specific situation because each of them has their own functionality now a line chart will probably or likely present data over time and it puts an emphasis on the overall shape bar and column charts they show the data across different categories a combo chart is a combination of both line charts and a stacked or a regular bar chart we also have donut and pie charts which show the relationship of parts as a whole or to a whole simple cards can be useful too for representing a number funnel charts are really cool visuals that usually display process with stages where items flow sequentially from one stage to the next an example includes a sales process from leads ending with fulfillment next up is a waterfall chart also a very cool visual often used in the business context to get from revenue to profit or from revenue to ebitda three maps are charts of colored rectangles with size representing value they can be hierarchical with rectangles nested within the main rectangles the gouge chart displays the current status of the context of a call the kpi sorry the key influencer charts shows the major contribution to selected results or value last slides the kpr charts displays progress towards a measurable attainable goal a matrix is some kind of table but it has multiple dimension and it also automatically aggregates the data and enables drilling down a ribbon chart shows which data category has the highest rank so they're effective in showing a rank range rank range or change sorry with the highest range always displayed on top for each time period scatter and bubble charts display relationships between two or three quantitative measures a slicer is a very useful useful feature for making a selection and also allowing your users to make a selection that will cover more on slicers in other segments now dashboards are also an important feature of power bi they allow you to create a one-pager that has all the information that your user needs so the way it works is that you create a report and then you can select what visuals should appear on your dashboard so as listed here it's built in power bi service it can only be a single page where reports can be multiple pages you create or you add data visualizations or visuals to a dashboard by pinning it with the icon displayed on the right and it's important also to note that they don't have any filter visualization or field panes and that the user also won't be able to see the underlying data sets and a very in useful feature of a dashboard is that you can set alerts for example if a value should fall under certain threshold an alert can also be followed by an auto-generated email now you can also set a dashboard as featured which means that it's often approved by a certain admin and it's usable for a wider context within your organization now this isn't an option for regular reports we can configure data alerts on a dashboard tile by going to our dashboard and clicking on three dots and then selecting the bell icon adding an alert and then configuring the alerts you can also add let's send the email to check mark and then we will also automatically receive emails as soon as the threshold is hit or the alert is triggered alright let's move on in power bi desktop filters are an important part of power bi in the report builder we can decide what data needs to be included and what data isn't really relevant to show the basic filters are shown automatically for the columns and the measures that we selected in our report let's say you want to change certain elements of a visual for example it might be relevant to show only the best performing categories by sales value using the top end filter type doing so i press the store breakdown and then the store which is currently unfiltered i select the top n and let's say i want to investigate the top five performing stores so i say top five and then i have to add a field to evaluate it so we add the return fields and then we apply the filter so now we can see that the stores or only the best five performing stores are shown and also the category is now being affected by this we'll quickly discuss edit interactions as well when we click on a visual to take some time here we get the format option and we can edit the interactions this way three icons appear filter highlight or none in this case category breakdown is set to highlight store breakdown is set to filter and the q a feature is set to none now let's see what happens if i change this so in this case i will add the default which is the filter and the defaults basically says that if you indicate a value by clicking on it in one visual it will also indicate or impact the other visuals across the page so if i click on pharma here the category breakdown will only show me the results for pharma notice that the q a visual did not change because it's set to none nevertheless it still refreshes let's say that i want to highlight instead of showing only the results for my store in that case i press this button and only the pharma results are shown as part of the total if i don't want to apply any filters and keep my visual unaffected by any other visuals i click on none let's talk about another important element of power bi slicers you can empower users by introducing slicers this way they can choose what data they want to see for example it might be useful for them to allow or to allow them to see values by dates categories regions and so on if you don't want a visual to be affected by slicer remember that you can change this by going to edit interactions and clicking on the icons let's say we receive the request from our marketing analyst and he wants to evaluate the results of a marketing campaign across two weeks so in this case we could add a slicer featuring the two weeks and let him do the analysis so i already added a slicer to our report and now we just need to go to date and add a field and in the filter i'll make sure that he can only select the two weeks that are relevant to him so let's say that those two weeks are the ones that we need and now we're all set so we can choose between this one these dates and also see what the results if is in the other week but the fact that i changed it it's to a couple of weeks here made it a bit difficult but you get a point you can also add hierarchies to this selection and then users just have to check mark certain selections so slicers are really important feature in power bi it's important to note that slicers only affect the page that is currently shown if you want a slicer to affect the entire report you can change this by going to the view tab and clicking on sync slicers that way a window will appear asking you which pages it should sync the slicer on and also whether we want to display the slicer or not alright that's it for this segment on to the next one the next thing we'll discuss are bookmarks and tooltips bookmarks available under the view tab allow you to freeze a certain visual which you can be usual when it can be useful when they're really complex visualizations or maybe you're giving a presentation and you want to focus on certain things so it captures the information that's currently shown and it saves that for later use so let's go to the documentations for this one creating bookmarks as i said is done in power bi desktop and you can create a collection of them and arrange them in any order you want you see that it's often relevant when there's a lot of complex visualizations and you're giving a presentation and you want to quickly be able to solve any questions or show any interesting visuals or let people ask questions and revise later on and so on the selection pane also lets you [Music] set which bookmarks should be shown which should be hidden and so on and you can also have books marks bookmarks for shapes and images i don't think the rest is really interesting but yeah you can go ahead and check out the documentation for this one also for tooltips so tooltips are a way of providing extra information to your user users when they hover over a report and they are created by going to another page and creating a visual and then connecting it to another visual on your reports so in this case this visual is featured on another page but you can see it's really useful because it's hard to do any analysis based on this one but this already gives us a lot of more information again check out the documentation guides for this one as there are smaller segments all right that's it for the layout on to the next one another cool feature of power desktop is the drill through option drill throughs allow a report user to access the underlying data of a report setting drill through is possible in both power bi desktop and power bi service in this case if i want to evaluate the store performance by the different date i can do so by clicking drill down and then it will show me the performance across the different weeks starting on sunday there are also a lot of tools that help me to show where i currently am in my drill through so i can use these to navigate across my reports now we won't discuss the setup in detail so i highly recommend you check out the drill through documentation guide for microsoft just knowing when and how to set up a drill through will be sufficient alright that's all for the reporting tools on to the next section [Music] so what are page native reports well page net reports are structured documents that are used for often a business context and to be able to retrieve information from a database and to display it in a structured template structured format it's built in a different application than desktop so it's built in the power bi report builder it also requires a power bi premium license and it allows you to also set a deployment pipeline so development test production before you release any page native ports to your users to set up a page nails reports we start by choosing our source so in this case we're connecting to an azure sql database and we get a preview of the data and we select what tables and fields we want and then we can start drag and dropping certain items and start creating our page needed reports paginates reports is a separate application in power bi they allow you to create all kind of standard reports and usually it's created in a business context for example to create invoices or other structured documents pagenet reports are built within the power bi report builder it's a separate application which you can download by doing a quick google search and then going to the install center they look like this so you have this report builder and then you can create all kind of templates and then also publish them to workspace all right so that brings us to the end of this section on how to model our data we've covered quite a lot of grounds so we've identified what the star schema look like we can now configure relationships and also cardinality we can add cross filter directions we know the difference between uni and bi-directional relationships we can also add measures and calculated columns in power bi desktop to a report and finally we can use these calculations in our reports or dashboards by adding visuals that suit our data best so next up is analyzing the data where we first look at how we can increase our performance now this is actually a section that was discussed uh according to the content outline uh all the way in the beginning but we've decided to cover it now because it's uh it builds on some of the concepts we've already covered alright let's look at analyze the data [Music] performance is really important when you're building your data model and creating reports we have six golden rules for optimized performance first one is the sooner that you can resolve a potential performance issue the better for example use custom columns in power query editor over calculated columns and try to fix any performance issues in power query query editor instead of adjusting it through filters or slicers when you're creating your visuals also avoid using white data sets data sets should be small in width and long in length so a lot of rows and small number of columns ideally also use star schemas for relationships now don't overdo the number of visuals or tiles on a single report as this also may cause performance issues use measures over calculated columns whenever possible as this reduces computer requirements for daytime variables try to split the date and the time into a different column if you need it otherwise this is quite takes quite a lot of compute power from your model alright let's move on now how can we analyze our performance in power query editor we can go to the view tab and click on performance analyzer here we get an overview of yeah first we need to select our site after our location and then we get an overview of the different performance issues and load times so in this case you see that it takes quite a long time to load a button but um the dax query only takes four seconds so yeah this is just a great way to [Music] analyze what is causing delays in your report we can also look at potential issues when we load our data so what happens if our query is the thing that's making our reports run very slowly so in the power query editor we go under tool step we have the option to perform and diagnose so query diagnostics doing so will create three queries and they will have a lot of information on the different load types of your data and where potential issues might arise so they will help you understand what power bi is doing when you refresh your data sets to identify performance issues and identify what queries take the most amount of time so this is a feature that you have to enable in the options so query diagnostics you can enable it in report and credit editor or only in query editor running as admin or not all right let's move on to the next segment [Music] alright so back to our visuals so what can we do to increase the value for our users well we can add certain analytical tools or also change the layout so we've covered these panes already but now let's go a bit more into detail so the analytics pane has all kinds of analytic and numeric functions such as adding a minimum line or a maximum line or even adding a percentile line so in the example here i added a percentile line for the 85 percent of all cases it can be used in a business context or just when you're performing a certain study the forecast feature is also very cool feature it allows you to extrapolate on the previous data and look at possible ranges of where your next observation will be another cool feature is the anomaly detection so this feature allows you to detect certain yeah anomalies things that aren't really in line with the rest of your data so you can imagine this being really useful in for example anti-money laundering context now next to the analytics pane we also have the layout pane so in the layout pane we can change the formatting of our visuals so for example formatting the title the columns the x and y axis how our data is shown and so on alright that's it for these elements on to the next section [Music] all right next up is ai insights so there are a number of advanced tools that you can use to empower you and your report with the latest ai artificial intelligence functionalities we have text analytics vision and azure machine learning text analytics has three options sentiment analysis where there is a certain sentiment that's being analyzed this is often words so a consumer gives a review and then the model will scale this review from one to z to from zero to one one being positive um and then it's it's useful to extract additional analysis on social media reviews and see what's the overall feedback and sentiment is next up is keyphrase extraction which will extract the key phrases out of a certain long document so it will give you a summary of what the document states another example of text analytics is language detection so language detection will return the language that's being used in your document vision is um for now it's it's only image tagging so image tagging um as you know um it's the model is shown pictures and then it has to correctly identify certain uh text associated with it so a cat or dog or a background and of course there's a lot of use cases in practice lastly is azure machine learning so you can also connect your own machine learning models and incorporate them into your report and and reuse the already done models that you created so to sum up its power bi is already doing a lot to have the state-of-the-art avi capabilities in there it's configured in web service or in power bi query editor and it requires a premium license the smart narrative visual is a way to quickly summarize visuals and reports in text form so it provides relevant innovative insights that you can also customize for example in this case we have a four visuals on the left and they get explained in detail with actual numbers um on the right and on the right the key insights are extracted there are some limitations though so it's currently not supporting pinning to a dashboard publishing to web you can also already or do it for some visuals for now and the there's also no option to cross filter on this smart narrative visual alright so that's it for the section on how to analyze our data so we've started with performance and then we've looked at the analytics and the layout pane and we ended with some ways in which we can empower our visuals using advanced analytics and ai functionalities up next is deploying and maintaining our [Music] deliverables so we've looked at how to analyze the data now it's time to also figure out how we can deploy and maintain our deliverables so it's important that our users always have the latest data available now how do we do that typically we do so by having a refresh functionality to our report now before we get into refresh we need to explain a couple of topics in detail first so we've already covered the different storage modes we covered import and direct query and you also have live connect and push which are used to a lesser extent now regardless of the storage modes there's no option to have a data refresh succeed unless the underlying data sources are accessible there are three main data access scenarios so either it's located on premises and you refresh that way either it's on cloud or a combination of both now depending on your storage modes the refresh will happen in another way and it will also have other functionalities for example if you use import mode a data refresh can be scheduled and on demand but for direct query it's it's not applicable because your data refreshes um yeah the way you set it up typically or by default it's every hour because there's a live connection between your data and your data sets used in your report now there's also other kinds of refreshes so you have data refresh query catch and tile refresh and then also visual refresh so query cache is when you have a certain query and the results are remembered by the desktop functionalities and that way it can easily retrieve the same results should the query be loaded again so this is always the option to be enabled or disabled and it's a premium functionality a dial refresh which you use in a dashboard happens automatically when you perform a refresh of the data set now your report that's an important one it will only refresh the visuals if you perform the manual action in your desktop or your mobile view so in mobile view it's scrolling down and in desktop it's using the refresh button so the visual refresh happens automatically when live connecting um but typically you have to understand that mostly we will use import and direct query and the visuals won't be refreshed as part of a data set refresh next up is the concept of gateways so if we use a data source that power bi can't access over a direct direct network connection for example the cloud or let's say google analytics is also a direct one then you have to configure a gateway connection for this data set before you are able to do a refresh examples include local files data sets and sql servers now you have the options to perform or to deploy a personal data gateway or an enterprise data gateway and it's quite easy to set up so you go to the web service then the settings and then on data sets you have the option to configure a gateway connection now it's in yeah you can look at it on the left here as well so you have the different cloud services um that can be accessed directly through the other service bus and then you have on-premise data that needs to be connected to a gateway now the typical um order in which you will set up your gateway and and the following actions so you will start with the gateway then you'll set the credentials for the data to be accessible by the system um alternatively or optionally you can set a parameter and then afterwards you can configure a refresh schedule so for example if you have a local file you first need to set up a gateway before you can set up a refresh schedule according to that data all right so with data refresh we've already quickly covered how it works um in the the previous slides but i'll go again into detail here so in power bi service you also have the option to use a quick refresh now button or you can use a scheduled refresh and then explain what's the the frequency frequency should be so should it be daily weekly monthly um keep in mind that under a regular capacity you only have the option to refresh eight times a day well under the premium capacity it's 48 times then in power bi desktop you have the refresh button and also the incremental refresh that's a way to to have the refresh done faster when the data is quite large it's quite an important topic so i suggest you head over to the microsoft docs page for this one now a data set reset versus a page reset we already covered so power bi won't refresh any report visuals when there's an automatic refresh now for direct query you can have the option to enable a refresh of your visuals which is called the automatic page reset option and this can be included through the layout pane it can be a fixed interval or a change detection so the fixed intervals just every x minutes change detection is when a certain change appears to your data for example a new row is added now there are some limitations as i discussed already eight refresh per day for the normal capacity 48 refresh per day for the premium capacity this is for the regular refresh the data sets on the premium capacity with the xmla endpoint enabled for read write support unlimited refresh operations and config program so this is basically saying that if you have a premium capacity that has a special connection with read writes supports um then there's the option to have unlimited refresh operations but it's uh yeah a smaller detail which you don't have to remember percy all right finally some recommendations by microsoft um i'll quickly go over this so schedule your refreshes for less busy times keep refresh limits in mind so um this is the the data volume that's uh being loaded into your model so if you do it too often it can be quite cumbersome for your model and it will impact performance now also verify that your data refresh time doesn't exceed a maximum refresh duration because there's a maximum refresh duration and if it takes too long then power bi will cancel the refresh optimize your data sets that speaks with itself and then avoid using dax functions that test every row so the sum x function for example um it is also not the best practice for performance reasons then limit the number of visuals on your dashboards and reports and also as a final hint or tip ensure that power bi can send refresh failure notifications to your mailbox so that's an option that you can enable all right that's it for refreshing and gateways onto the next topic right next up is row level security so we have to in some cases restricts our data sets and the way that the report is shown to some members and we do that by using row level security so in this case certain people which are added to roles will be able to see a limited extent of the data in the left example here we use dax to specify that the role eastern u.s can only see the values east in the column named region so they won't be able to see any other rows that have another region specified in that column we can also test our row if we go to view s and then we select our row now two things also to keep in mind is that um when using docs there are specific functions um basically created for the the purpose of adding more detail and granularity to our row level security so these are username and user principle name and this way we can select the username from when someone is logging in where we see the username and then the data will only be shown if a certain value is met with that username in the column that you specify so that way if you have certain rows that can only be viewed by certain members of your organization you can specify these members in a column use dax function to set up raw level security and then those members will be able to see the specified rows but the others won't all right username returns a name with the domain and user principal name looks like an email address a final thing to note is that you create roles in desktop and service but you assign users or groups to roles in power bi service only so this is not possible in desktop all right next up is adding sensitivity levels labels rather to our data and our reports so we can increase our security or at least make our our users more aware of any confidentiality issues that may arise by using those labels you can set it up by going to settings and then in the sensitivity section uh choosing the appropriate label and then saving and and this will make sure that when users think about sharing any documentation that they get reminded of the fact that some things shouldn't be shared to other users or members outside your organization all right next up is endorsing to promotion and certification so you can promote a certain report which means that yeah co-workers can easily use that document and are also supported in doing so it's easy to retrieve promoted documents um and then anyone can basically that says so he or she creates reports if they think it's it's something that a lot of users will use they can opt to promote that content and allow co-workers for easy retrieval certification on the other hand is a way in which authorized users indicates that a data set meets the organization's certified criteria so this could be for example it's that the underlying data isn't confidential by any means and it can be used by anyone within the organization you can think can configure it in power bi service for data sets data flows reports and apps now in some cases you have very large data sets and this might impact performance now if you're on a premium capacity you can enable the large data set storage formats which will make sure that your data loading and also your limit um is set so that it's able to handle large amounts of data volumes so it's required for data sets if they grow beyond 10 gigabytes per users but it has additional benefits also with regards to specific functionalities you set it up in the web service if you go to data sets and settings and then you enable the large data sets and then you can also do it in a workspace by going to settings and then premium they all speak for themselves so important to note here you have the option to enable the large datasets format all right on to the next topic [Music] next up is workspaces so workspaces are places where you collaborate with your colleagues or other members of your organization so it's set up in power bi web service and you can use workspace to yeah work together and cooperate with colleagues typically you start by creating your reports in desktop and then you publish it to workspace through the publish button on the bottom left here so you have workspace assets you have reports data sets dashboard page and reports now ideally a workspace should have some logical structure for example it can be for certain projects or it can be a workspace for a certain department and within that workspace you can then add users to your workspace who can change reports user reports publish reports and so on now there are a lot of different functionalities here but i won't go into detail of those let's instead look at the different roles within a workspace now as i said before workspace asset access is set through roles so you have the admin role the member role the contributor role and the viewer role it's important to understand the difference between these roles the admin role is the the most high level role and he's responsible for organizing access to the workspace and configuring the access settings the member role is kinda like the admin role but he or she can't add or remove people from the workspace the contributor role can be configured as a member role with limited access to some functionalities as well it's important also to note that the contributors can update an app which we'll cover later on and if only the workspace admin delegates this permission to the contributor now it's also important to know that they can't publish a new app or change wise permission to an app a viewer role allows reads only access to the content so users with the viewer rule can also export data if they have built permissions on the data sets in that workspace i'll quickly go over to the nodes so we discussed the contributor role now you also have the reshare permissions that you can enable you can also have built permission for the data sets and that allows you to copy reports or to create a report based on a certain data set in a workspace now you also need to keep in mind that you need to set up the permissions on the gateway as well if they want to have refreshes or they want access to the underlying data and then also finally if you don't have a power bi process pro license rather you can view and interact with items in the power bi service if it's under a premium capacity all right so the right table sums up these different roles again it's important to understand the difference between these roles next up is deployment pipelines so deployment pipelines are a feature within the web service so here it's often in the context of a product or a model that you're developing and you have your test face your development phase and your production phase so development space obviously coming first um and then you create different workspaces for these different phases and then you can also have test data in your development phase with actual data in your production phase and you can create different kinds of access and different kinds of roles within each phase so this allows for not only working together with different teams but also just a better overview and control over your production pipeline lastly it also allows version control when you set it up to with one drive that it auto syncs okay that's it for deployment pipelines what's up next data flows so data flows are a reusable process in which the data is collected using multiple data sources and allowing reusable transformation logic across data sets and reports inside power bi basically it's the ability to reuse data sets in a structured way additionally it can also serve as a way to prevent data analysts from having access to the underlying data source on the right here is an example of how the structure look like once you want to set up a data flow um also for your information a common data model here is a shared data language for business and analytic applications to use and it makes it possible for data it's meaning to be shared across applications and across business processes so data flows a reusable process in which you can retrieve data but separate the usage of the data from the underlying data permissions all right next step is configuring and updating an app so applications are typically connected to a workspace and they allow for a broad distribution of your content and so distributed to broad audience is done through an app apps are created in workspaces or from workspaces rather in power bi service you can collaborate with your colleague in the workspaces and then you can publish the final reports to an app the steps are as follows followed so you create a workspace and add reports and dashboards and in the right corner you select create app and configure the setup navigation permissions so these are three different things that you have to configure and then you're all set alright on to the next topic next up is configuring subscriptions so subscriptions allow you to receive emails based on a periodic schedule this can be set to daily to weekly or whenever the underlying data is scheduled to refresh you can add up to 24 emails to a report and it depends on how many and for who you want to subscribe that you have taken to look out the premium or regular capacity so for example if you want to create a subscription for yourself this is possible if you have a power bi premium license or if the report or dashboards resides in a premium workspace and you've been given access to that workspace you can subscribe others if you're the owner of the report or dashboard and you have a premium license subscribing to pagenet reports is a little different and you can check out the relevant redox for this one i think most important here is that you know if you have to set it up in power bi service and that you can set up different frequencies for your subscriptions to go out all right so so far in this model we've covered the workspace workspace space access and different roles we've also talked about deployment pipelines we've covered data flow flows rather with covered applications so publishing a workspace to an application and then finally subscriptions hello this is dez again so congratulations on making it through the entire course um it was quite a lot of ground that we covered so by now you should almost be ready for the exam what's left is of course practice you have to practice all the things that we discussed and especially the more complicated box functions like calculate summarize grew by and so on so before i let you go here are some practical tips that can help you to fully prepare for the exam all right so as mentioned before practice is really key for preparing for the exam it's best for knowledge retention that you learn through different sources so either books spotify youtube and so on get comfortable with the microsoft docs as these contain more information than explained throughout this video also try to get familiar with practice questions which you can find online or on youtube or through courses time is critical so try to keep an eye on the clock and skip questions which might take a long time and flag them for review later on below i also listed some items that are not fully covered in this course in case you wanted to check them out in detail now thanks for listening uh to this course and yeah best of luck on vtxm also make sure to subscribe to call for data and then we'll catch you in another video thank you

Channel: Call for Data

Views: 177,195

Keywords: #da100, #powerbi, #microsoftpowerbi

Id: Tfk3ae0qz3A

Length: 108min 20sec (6500 seconds)

Published: Mon Jun 14 2021