Power BI Training | Power BI Certification | Power BI Course | Intellipaat

[Music] hey guys welcome to this session by in telepods business intelligence is a concept of using some methods to convert raw data into meaningful insights and use them to drive businesses to success according to a Forbes survey 48% of the organization's how would head for bi as critical or very important for the operations of their organizations now power bi is a bi tool which is owned by Microsoft and also it is one of the most popular BI tools Dell Hitachi and Wipro are few among the thousands of companies who use power bi for their bi needs now in this session we'll be learning power bi comprehensively also guys before moving on to this session please subscribe to our channel so that you don't miss our upcoming videos now let us take a quick glance at the agenda we'll start off with an introduction to business intelligence as well as so why do we need business intelligence after that we'll learn how exactly to learn power bi step-by-step moving on we'll be looking at a quick introduction to power bi and learn about its features after that we'll learn how to install power bi on a Windows machine after installing power bi will be learning data modeling and data visualization on power bi and finally we'll be doing a hands-on on generating power bi reports also guys if you want to do an end-to-end power bi certification in telepods provides a complete power bi certification training and those details are available in the description now let us begin with this session well what is business intelligence we might have heard the term business you might have heard of the term business intelligence but you might have not heard it together as one unit right I mean sure in a world where we are surrounded by IT many of you guys might have actually worked with bi no bi or ever heard of bi but then to check out the basic aspect of what it does we need to start with the definition guys so here's a very simple definition of what business intelligence is it is nothing but a very simple of architectures technologies and consider processes and what these does is basically it is used to convert all raw unruly data into meaningful information and this meaningful information at the end of the day will make the business profitable in in in some means so think of it this way right so it converts raw data into meaningful information that drives profitable business actions guys so bi basically you know it covers all the processes that is needed and methods that is needed like we know there are some methods of data collection data analysis data storage and so much more so all of these data are collected from business operations or with say any day-to-day activities in a company and at the end of it all of this analyzed collected stored and you know operated upon so that we can get a get an output get a result which will basically help us to optimize performance at the end of it and business intelligence has a direct impact on the organization's strategic operations tactical operations and even the business decisions on a very big scale guys so on that note you know there might be so many business intelligence tools out there if W we are quickly you know we have power bi and so on so on and so forth guys there are multiple tools out there in the world so how are these tools selected by companies well the first thing is pretty much availability and usage they need to check out what tool is available they need to find out what their usages are and if it matches with what the tool is offering then that's a tech mark guys and number two is how easy is it to learn and use because even at a start-up it might be two guys who are learning a new bi tool or two a fortune 500 established form where there are thousands of developers who sit and collaborate and work on a product so they need to cater to each and every one of these guys to see how easy it is to learn and to use at the end of the day to help drive their business to success and then after this it is the quality of data visualization or the tool provides you can have basically elevations you know something which looks intermediate and advanced very beautiful looking data visualizations as well so what does the quality of data visualization that they are looking for as a company how much money are they willing to spend again that drinks is right to point number four which is cost and easy to set up because something which is extremely expensive where the company doesn't make use a half of its features is a waste of manpower it's a waste of money but then if you are picking up a data visualization tool which is just perfect for your needs in terms of cost in terms of ease of use in terms of setup in terms of uses and much much more it's gonna be a perfect hit guys so it's comparing all of these four points it is basically done you're sure it is more convoluted than this but most of these companies start out with these four points as the key foundations to check out what tools they can pick up for their business intelligence requirements case so on that note we need to check out why we require business intelligence okay guys a quick info if you want to do an end-to-end certification on power bi in telepods provides a complete training on power bi with certification and those details are available in the description now let us continue with this session I have a couple of points are down for you guys here but then the most important things you need to know is that business intelligence helps mainly in terms of KPI skies so KPI basically stands for key performance indicators and these are the statistics these are the numbers which help drive a data-driven business to success at the end of the day so ready give you or I know all the numbers from day to day activities profits losses in case you're selling your product how many units are got sold where it got sold why your core product is performing very good why your product is not performing very good so these why questions W questions what why when where the answers to all these questions are basically given by the key performance indicators and bi helps give you an extremely good clarity in terms of that guess and the second thing is process benchmarking every company has a goal that they need to achieve quarterly girls half your goals your Digos ten your goals and whatnot right so all these goals are put into place and then making sure that you achieve those goals so having a road which is you know attainable where you can walk on that road and get to that goal having good girl which is reachable basically is what I'm trying to tell you guys and process benchmarking does just that so with the help of business intelligence you can get to know if you're getting closer to your goal or further away from your goal race and then the next important reasons extremely important reasons are driving sales because at the end end of the day you using business intelligence to drive a business to success entirely you're going to use analytics you're going to use a lot of statistics mathematics and everything are you doing all of these to make sure you sell more of your products in case you're selling a product or understand why you're not selling selling well enough so driving sales is the backbone of I know sales is basically the backbone of any product based company and then helping sales do better to fare better in this competitive world is always an advantage guys and the next reason why you would require business intelligence is because of business problems any business small intermediate sized or large ones face problems on multiple levels so having having the solution to understand what the problem is about and why or the problem was faced by that particular company is extremely vital as I'm stressing for any company guys so understanding that problems and catering to provide a solution is very vital there as well and then for a developer all of these were companies afforded developer how is it good enough well a business intelligence job as a Pinole you can become an intelligence or business intelligence engineer or business intelligence architect developer and whatnot so all of these jobs are extremely high in demand and these are the jobs you know these are the top these business intelligence is one of the top ten carriers for the year 2020 and they're a service would say that you know business intelligence has taken off on a very large scale and it's gonna stay on the top of one a one to five positions for the next five ten years as well guys so it is highly in demand it is trending and I would urge you guys to take a deeper look and jump on the trend train as well and then committee job abundance job abundance again in this corporate more job openings better right so you might be thinking business intelligence might be a niche concept because not many companies are a part of it well let me tell you that almost every big firm almost you know Navy it's not supposed to be big firms it's almost every form which needs some sort of analysis from the data has used or is using business intelligence tools and this opens up a void of opportunity for all of the learners guys so this quickly brings us to the introduction of power bi so what is power bi well power bi is nothing but a bun again a tool which is used to attain business intelligence guys so it is a business analytics solution that lets you visualize your data share insights across your organization in your inside your team's outside your team's you know in fact even to a different organization as well and then later whatever graphs visualizations you've put out from your data you can put it in an application you can pull on a website and show them and you assembly it as well guys so basically it allows you to connect hundreds and hundreds of data sources and bring your data to life with live dashboarding and reports guys so basically this is what you can see right so you have set of numbers which might not make sense but then at the end of the day if you convert numbers to these beautifully looking graphs and visualizations then that has so much impact for a non-technical person for a person who's from a business domain or let's say in a board of directors meeting as well because at the end of the day of visualizations and images attract the human consensus pretty much you know three thousand times faster than numbers as well it's something that I read last year so on that note why do you have to use power bi in particular it's an offering from Microsoft as you guys might know it and the power bi is again a giant in the world of business intelligence guys so the first reason of why you should use power bi is to help you make informed decisions very quickly guys and not just informed decisions not informed in just any simple way you're gonna have all the details you're gonna know your data in and out with respect to visualizations which are to what your data is doing why it is acting the way it is and what is the outcome and how good or bad is the outcome guys poke you guys a quick info if you want to do an end-to-end certification on power bi in telepods provides a complete training on power bi with certification and those details are available in the description now let us continue with this session so all of these will help the company you know in terms of the associate level all the way to the director you know CTO CEO levels and to help them basically taken a very good decision and do this very quickly as well guys so this brings us to the next reason of why you should consider power bi power bi is basically you know this one tool with so many features that it pretty much you know help you connect your raw data connect your models it'll help you model the data that you're connecting and then pretty much you know you can explore your data you can create all these very good looking or visualizations visual reports basically and then you can talk to your multiple teams about it you can talk to just one team design a team a business intelligence team sales team you can have all these guys collaborate pitch in on what they want to see at the end of the day and then publish it to them and eventually share it to all your board members or other members of the teams as well guys so this brings us to the next reason of why you should consider a power bi especially this particular reason guys because you know there's a statement which I read it says that power bi are pretty much is again one of the most very faring bi tools in the world today and I think this reason pretty much this reason of why you should use bi actually connects to that basically what we were trying to say here is that you know you can take data from hundreds of sources like I just said and no matter where your data lives power bi has the ability to connect it and pull it and pretty much give you visualizations based on that as well guys and not just any visualization you can have stunningly amazing looking interactive visualizations which will catch a lot of attention of a lot of people as well guys so you know then there's an other reason which is again very good as a company called operation is a foundation right so all your reports will get published among your teams all your dashboards will get published among your teams basically all if you guys together will come you know share insights about what's going on you or if you will have an idea of how your business is doing and this will help you understand your business inside out thoroughly guys so what Dad further does is pretty much you know that'll help you take you know your desk to wherever you go because at the end of the day power bi apps can talk to any of the apps basically built by Microsoft and all of these will give you very good seamless access to insights whenever you want you know at your home at your office or if you're on the move as well guys so this brings us to the actual process of how power bi works well guys our to simplify it I'm going to take it very slow since the the intent here is to make the of the audience from the you know beginner level to the advanced level understand this so I'm gonna be a little slow here and I hope you guys catch on to what I'm saying so this is how power bi works basically in the chain guys so there are well you know pretty much five major steps of how it works step one is data connection next we have data transformation we have data modeling we have data visualization and data reporting as well guys so coming to the first step which is a data connection as I've already told you you can talk to hundreds of hundreds of resources at the same time and get power bi to pretty much pull all the data from any let's say Twitter Facebook it can be any Big Data solutions Hadoop you know high pick any of these guys can talk to a power bi and get the data into your system where it can be processed and worked on and the best part in my opinion about the data connection part of power bi is that it is extremely simple to import data into power bi or let's say you want to upload a custom file on your own and you don't want to pull data from anywhere else guys Microsoft power bi interface is so simple it actually makes it very easy even for a beginner to get started on this guys so coming to data transformation which is second which is the second basically so after data loading what the data needs to be done at this point of time your data will be very unruly it will be very shabby it will be very wrong there's a good chance it will not be structured as well so basically we do this step which is called as pre-processing and pre-processing the literal term is data shaping or data transformation as well so in this pre-processing we do a lot of changes and transformation in the data so that the next steps becomes you know very easy and maintains very good accuracy as well so you know processing how is it done well basically we can do a lot of changes to the data that's present you know you can rename your columns you can rename your tables you can change the type of data so you can convert text to numbers numbers who takes you can modify the rows modify the columns modify the data with each and every cell in the two dimensional matrix of data that you see and much much more guys okay guys a quick info if you want to do an end-to-end certification on power bi in telepods provides a complete training on power bi with certification and those details are available in the description now let us continue with this session so data transformation plays a very vital role as a second step in to achieving very good visualization and this brings us right to the third step or which is data modelling or data modelling again is a very important step because data modelling will help you enhance your data in a way where you know when you're doing visualization you do not pick up on any outliers or you do not pick up on any data which will basically hurt your visualizations and decrease the accuracy of your model so to do that we need to enhance our data we need to make sure that the data is extremely efficient it is extremely you know to the point where when we go ahead and graph it out it looks perfect guys so basically how is this done well data can be enhanced by doing a lot of things guys you know you can do a lot of calculations on the data you can have some hierarchies you can map your data one to other you can have give relations to your data you can have certain set of measures where you know you're pretty much asking your data or to stay in a certain set of limits you can have we can build certain relationships among a couple of floors couple of columns and much much more guys so the data is enhanced basically to make it more efficient to the next step which is data visualization this is the heart of power bi because this is where pretty much your data goes from looking like numbers or something on a spreadsheet to graphs guys and not just any graphs those are beautiful looking visualization as I'll just show it to you in a moment but then this is a very important step because you will not just have access to one or two types of graphs graphs sorry you'll have a variety you'll have a plethora of visualization types where you know you can go on to use it based on your personal requirement guys so this will feel like a tailor-made requirement to any company who require our business intelligence and then since they're how awful our loads and loads of tools for us it is actually one of the main reasons why power bi will give you these attractive looking amazing and beautiful reports guys so this brings us to the biggest advantage basically is that you know let's say you guys might have questions in the comment section basically which says you might not have any coding experience but then is power bi for you as well guys know that the advantage of power bi is that you know even a person who is an MBA who's a business guy who has never written a single line of code in his life can whip up can come up with extremely good analytics using power bi guys and then this is a very very high paying job as well the average salary is very high and then if you can think about being in the information technology sector without writing code then business intelligence could be one of your ways to actually you know amassing a lot of wealth and at the same time enjoying your job because for me personally I love seeing visualizations as the output of power bi guys and this brings us to the next step which is basically data reporting by data reporting basically what we try to do with power bi comes up with a lot of services there is much more than what meets the eye in terms of Microsoft Services any services of course and then these basically our dashboards are used to create reports these reports can be physically created they can be put on our website they can be published and exported in a very secure manner as well so it is not that you create a couple of graphs and you know anyone can access those graphs guys so these these are done under very high encryption and you can export it and publish it and much much more as well guys so coming to another important point of data reporting is that you know it is not a manual task in fact a power bi supports automatic data refresh which can be set up once and then left as is and what this does is this will give you almost real-time analytics of trends and results so it will update itself on its own and you do not have to have a human there into meaning putting in the new results you know putting in new visualizations and whatnot guys so it supports an automatic data refresh and this in my opinion is a very good advantage of a business intelligence tools case so this brings us to the key features of business intelligence key features of power bi the first most important feature that I could think of was the usage of artificial intelligence guys so basically you can do any sort of text analytics you can do any sort of image recognition by using power bi as well and then this is done basically you know by teaching machines to do or stuff on their own or we basically call as machine learning if you guys do not know the term but then for the people who do then we use a lot of machine learning models you know which has the capability to automatically learn and integrate with their entire cloud platform which is basically as your Microsoft Azure platform and then there's something called as your machine learning as well and then power bi has the ability it is ready and it talks to Azure machine learning in a way where at the end of the day even machine learnings goal is to achieve artificial intelligence so with the help of visualizations you know future predictions trend analysis and much much more guys so the next key feature is something called as hybrid deployment support because you know a power bi comes with these so-called connectors and these connectors will you know allow power bi tools to connect to it I get a number of different data sources again as I've been stressing because it is extremely important which generates terabytes worth of data every single hour yes you heard it right every single hour there are terabytes and terabytes worth of data throughout the world and then knowing that you can probably use it and connect to it very easily and pull the data through is very simple and doable in terms of power bi guys so you can have the data coming from any Microsoft's platform from Salesforce which is a very good CRM tool and maybe many other vendors as well guys and this brings us to the next key feature which is getting quick insights out of your data well one of the most important thing that you need to know is your data might be large and you might not have to visualize it all at once so having very good interest in getting quick insights basically allows you know the users to create let's say smaller chunks of data or subsets of data and then what you can further do is you can apply analytics to those small amounts of information rather than the entire thing because you do not want the entire thing and you want a part of it so Megan power bi has the capability and very easily so to in fact do this as well guys and then the next thing is something called us data model support and data model support is basically you know we're going to use a lot of data again as I've been mentioning and making sure your data schemas are understood by the model you know supporting a plethora of tools again requires a lot of variety of data which can be read and understood in terms of the point of development of power bi so you know you'll have lot of entities lot of attributes lot of relationships basically giving you this entire data schema where you know one common model can talk to the can talk to a large group of data connectors and data inhibitors guys so basically you know some ways you want the data some places you don't want the data that is what inhibitors actually mean so where you know what data we need to pick up from and what data you should not pick up data model support will do just that and the next thing in my opinion is something really nice and I have personally use this as well guys so it's basically Microsoft's NLP natural language processing and Microsoft saw virtual agent which is Cortana so I'm sure many of you guys you scored on a few Windows users or you use it elsewhere as well so this is actually extremely popular on more on mobile devices and this will allow you to basically talk to your computer and verbally you can ask your computer you can ask Cortana you know using a certain set of queries as we call it and then go Donna will give you all of those results and you do I mean you do not have to type it out manually guys so Cortana is a beautiful digital assistant by Microsoft and it's a it is completely supported and talks really nicely to power bi and it makes our task of typing and verbally querying data out are impossible and this basically you know I I know 60 or 70 percent of the users who talk to their computers when they're doing business intelligence and personally they're actually you know going about telling their queries out to the computers with Cortana can hear it convert it into text and actually execute that query case so this brings us to the next thing which is the most important key feature when it comes to power bi it is the customizations thousands and thousands of different types of visualizations different types of reporting aspects hundreds of variables that it can look at at the same time guys you can import any new tool which is from the entire Microsoft saw a domain and this will basically help you change the appearance of anything that you look at on a visualization screen answers this can be only done by the creativeness and thinking the thought process which went into designing power bi guys okay you guys a quick info if you want to do an end-to-end certification on power bi in telepods provides a complete training on power bi with certification and those details are available in the description now let us continue with the session in fact here is a dashboard so this is a power bi dashboard and this is what it would look like on a phone and a laptop as well guys so you have lots and lots of data you have graphs you have a map you know your heat maps and then you can track each and everything that you can think of and in fact to the uninitiated people who might have not done sales it will track everything that you might have never thought of before as well gays so it is extremely intelligent on its own and with all of these insights that you have never had before this will give you a clarity throughout your product throughout your domain your hold in this world of products like no other guys in fact here is another dashboard which is basically from the retail sector of you know it's basically another sales dashboard and this is done somewhere in the and then in the part of the United States of America and then you can get the you can get details such as how much products you sold per square feet of that particular city guys per square feet think about that and then you know you can get the total sales what is the sales per square feet again as I told you what was this your sales how many stores got opened total sales this particular your sales what's the target how far away from the target how to achieve that target pretty much guys everything that you can think of you can achieve in terms of the power bi dashboard again here is another very good-looking dashboard guys so this is again something related to the number of times and requests this website outage and so much more but then again let me quickly head back you can see the difference in between these particular or dashboards and how unique you know each of them look guys so this get this can be catered to the businesses needs and this can be designed to look as beautiful as it can and with all the data that you particularly need in your organization guys now I'll show you how to install power bi on your Windows 10 desktop for that all you need to do is open a browser and I am using Google Chrome in Google Chrome what you need to do is open the Microsoft power bi website so I type Microsoft power bi click the first link over here and this page comes up and here you will always find an option start for free what this means is that you can download the power bi desktop for free and then if you need a subscription account you can continue it further so I will start for free and now I will find this pop-up and now this is another option that I get here download for free so click over here and now this pop-up menu appears and you have two options over here so and as we know that ah bi is a product from Microsoft you can directly download it from Microsoft store so open Microsoft Store by doing this the product will download automatically and install on its own and once that is done you will see this the this product is installed and you can launch it directly from here as I had already installed my product I was not going through all that process so I will launch the product directly now power bi desktop will open now once it's open a menu will appear and you will have to fill in your credentials after that click on sign in' and here you will have to use your professional account I'll be using the account of a friend of mine ok that was a mistake yeah so sign in I'll have to enter the password and fortunately I have a password so we have sign in to power bi desktop and this is how power bi desktop looks like as we are discouraged there are three different views in power bi desktop report view data view and Model View in addition to that you have three different menus in the home menu you have these options you have get data enter data edit Perry's new page new visual buttons and all of that so what you can do is I mean before starting the process of creating a report in power bi desktop you need to import data into it first and for doing that you need to click on get data once you click on get data you will find these menu options now what does this mean this means that power bi can take in data from a huge number of sources I mean it doesn't have to a particular excel file it can be anything as you can see you can click on more over here now a list will appear now these are the different sources which are compatible with Microsoft power bi desktop so you can take it directly from files Excel CSV XML JSON PDF anything you can take it from directly databases all these databases are fine you can directly taken power bi files which have a dot p v IX extension you can take it from us your other online sources and other files that include a script python ODBC spark I mean any kind of data can be ingested over here and this will work absolutely fine now this is the beauty of power bi desktop or you can click on enter data and enter your data manually you will find this option column you can input the column and then enter values as per as your requirement you can simply edit it and then load it you also have edit queries option here you can once you have created a table you can edit queries and you can use queries to extract specific information from that table or you can click over here from marketplace you have to you will find these options these are different visualizations you can take it directly from the Microsoft website it's absolutely free to download or from my organization if see because we don't use power bi as of now we don't have any custom visuals you can use custom visuals and under my organization you simply have to click it and then extract the visual directly you can also switch your themes over here and after that now I will give you a view of all the views that we have first comes report view now this is where actually the report is created in report view you have two specific panes over here the visualization pane and the fields pin in visualization pane you find these many visualization options once you enter a data set you can visualize it with these many options you have different things stacked bar chart column chart clustered bar chart clustered column chart 100% start bar chart as per your requirement you have many other things and you can use any of it you also have line in stock column chart when you have multiple parameters in your table and if you want to show a graph of multiple things you can use all of this you can also use donut charts pie chart scatter chart and you don't have to worry I will tell you exactly how to use these things in our next time you can also take data directly from a script well visuals and Python visual you can create your reports in these visuals also all these options are available to you and all you need to do click on a visual and once you have data in it all your parameters will appear over here in this field spin and you can directly select a parameter and drag it over the axis on which you want to project it there will be two axis as x and y and you can put it directly over here and there are other options also you can add data fields over here add data values over here and these are the filters if you also in addition we have these options and all this will be active when we enter some data set that is the only reason why this is not active right now but I will tell you I will give you an overview you have the title thing over here you can enter a title you can customize your background the lock aspect general things you can give a border visual header and all those things and analytics is not available for this visual as we have not entered any data once that is done next comes date of you here you can edit your data you can manage it with query editing tools edit your queries and once you have parameters in your data you can manage those parameters directly from here and then we have this modeling view what you can do in the modeling view is that suppose you have a big report and you have to break it down to make it more understandable or break down into modules so what you can do you can simply enter the data over here and break it down I will show you how to do that so this is how the power bi desktop basically looks like in our next demo I will generate a report and tell you how exactly can we work in Microsoft power bi desktop now that we have installed power bi let us understand how data sets and data types book the data that you import from other sources needs to be typed correctly in the first place plus you might also have to change the data types as different sources may have different data types now means of question arises where is this data stored in power bi data is mostly stored in fact tables and dimension tables a fact table holds the data to be analyzed and a dimension table stores data about the ways in which the data in the fact table can be analyzed now it is really important to note that these tables are connected and work in sync with each other now when we talk about tables it is a short thing that there will be data relationship straight how can we manage them now don't we think before managing them it is necessary to create them in the first place so if there are no null values and duplicate rows in those facts and dimension tables you can create data relationships and there are two ways to do that in power bi either you can do it manually or you can use power bi is inherent auto detect feature what happens in the auto detect power bi Travis's through your data directly and finds potential data relationships and creates them on its own now let's see how data relationships can be managed data relationships can be managed with primary and foreign keys what are primary key stone a primary key is a special relational database table column which is specifically designated to uniquely identify all table records but a foreign key is defined in the second table but refers to the primary key in the first table and the relationship between these two keys is the stronghold of how tables are connected for managing data relationships even better power bi equips users with cross filtering the cross filter direction of relationships in datasets affects how power bi treats the tables in visualizations and reports there are two types bi-directional and single Direction bi-directional cross filtering allows modelers to determine how they want filters to flow for data using relationships between tables and single directional cross filtering will filter one table based on another table Sam's curious if he needs to shape and transform data before using it in power bi well if required he must do it and he can do it using the power bi query editor here users can load data from a wide number of sources and apply transformation on them directly they have four options Home transform add column and view and when applied in the right order these options can help transform data in a really quick manner let's move on then what is shaping data it is the process of transforming data so that it becomes best for your reports this can be done implicitly in power bi the most common techniques of shaping data are removing columns and rows renaming them modifying data types adding columns and indexes and applying a sort order now Tom wants to know why do we need to transform data if data is not structurally appropriate we might have to transform it and this can be done using the query editor in addition the table group offers functions to quickly transform data so what are these functions these are grouped by count rows split column use first row as headers any column data type transpose fill rename columns reverse rows and place values and errors so using these functions you can directly transform data and these being library functions you don't need any extension for using them now question arises when a data is transformed how do we merge it there are two ways to do this first is merging columns to tables must have adjoining column where values will match and when values match users can easily join both those tables and the second there is a pending rows take rows from one or more table and add them to the first table and both the tables will be connected together now that we understand this about tables let us move on and understand what DX Square is really mean so what are these Dax is basically a formula language which is specifically designed to work with relational data there are many applications of DX basically they are used in expressions to calculate and return values help perform complex calculations and draw specific insights from data also data can be manipulated in power bi using calculated tables and calculated columns what are these in calculated tables one formula is created using a Dax query and that formula automatically adjusts each value in each to similarly calculated tables are created in the same manner but they're useful for intermediate calculations and specific data which use a swoon included in their data models and using all these features intuitive business reports can be generated really quickly now Sam wants to create some power bi reports well we will show that in the upcoming demo but before that he must know the basic steps that lead to creating reports first comes page layout and formatting report pages can be customized and page name page size page background and page view can be modified as per requirement also charts can be created and as we had demonstrated on the last demo there are several charts that are implicitly available in power bi this include bar and column charts lining area charts line and column scattered and bubble pie and donor table and matrix tree maps are visuals and other charts what's more surprising is that geographic data can also be included and this can be done through map charts Bing Maps is integrated with power bi to ensure that users do not need longitude and latitudinal coordinates to include geographic data in their data sets while creating reports and if everything goes as planned you will successfully generate an insightful report on your business data now I will show you how to generate a power bi report for that I need to open power bi desktop first so I will open power bi desktop and you can see this is my power bi desktop and it is open now what I need to do in the first place is start I need to import my data source because without data we cannot create any report and for that I need to click on get data this is the easiest way to import your data and I need to select the file format and because my file or data set which I am going to use to show you this is an excellent I will simply click over Excel and connect once you do that you will find this browse menu or browse option whatever you want to see it and from over there you have to browse and select your file I don't need to browse it because it's right here so I will simply click on it and open once I do that I find these options over here I can see the number of sheets that I have in that excel file and I have only one sheet so it's kind of mentioned over here now I need to select this however you can select as many sheets as you want if there are multiple sheets so once I select this I get a preview of that sheet with parameters and values so this is basically how my data looks like I have fuel economy cylinder displayed horsepower rear axle ratio then I have weight Q SEC number of forward gears and carburetors so what does this basically mean so this is kind of the internal parameters in a car so I am going to make an actionable dashboard or a report using which you will be able to understand this data more clearly so for now I have selected this and I will click on load now I can say that my data is loaded over here and how can you say that I can say that because in this field section I can see my sheet 1 over here and under that these are my parameters so using these parameters I can create graphs and visualize them and how can we do that for doing that or for creating a graph or a visualization in the first place I have to select a visual a out of these options suppose I need I want to create a column chart so I'll simply click on column chart as you can see this visualization opens and when this opens I will simply expand it to make sure that this is clearly visible to you now that this is open I will go on and concentrate on my parameters what I mean to say is that I need to select the parameters using which I will create the graph before doing that I want you to see these two things so this is the field spin and this is the format pin in the fields pane you have different options axis legend value tooltips then you have filters and drill through so what you need to do here is that you have to take a parameter from here and drop it over here basically this axis represents x axis value represents y axis so this is basically an area using which you can control your parameters so we will work on that suppose I want to see a graph between ok let's say weight and number of forward gears so I will take number of forward gears and drop it over here you see this ad data fields your right I simply drop it on top of that so that will be loaded and the next thing I need to do is I will take this weight and draw over here add data fields here under value I will drop it over there so instantaneously you can see that a graph is generated now what does this graph mean what basically means is that the number of gears that I have suppose there are three gears in my car or four or five forward gears accordingly the weight is getting changed as you can see if I click over here this gets selected and this gets these letter so what to do know suppose I am not happy with the title of my visualization what I can do is that I can go to the format pin and in my format pin I can click titles and in place of weight by a number of forward gears I can simply write graph one this is just to show you so I write graph one if I want I can change the font color I'll change it to this color I do not know what we call this but okay I'll change it to this but still my alignment is over here so I will make it Center aligned but still it is not clearly visible so oil increased attack since as you can see now it it is kind of visible I will switch on or enable the borders okay and on top of that I will make sure that data labels are on so what happens with data levels estar you get instantaneous data I mean look at the graph and you can judge if you don't want this you can simply move your mouse cursor over here and you will find data so on taking my mouse pointer over here what I can see is that number of forward gears is three and where it is 50 8.39 over here number of forward gears is four weight is 31 point four zero and that is how the graph is broken but now if I want to see this graph but as a stat bar so what I need to do is that I will simply click on this tag bar over here and I can see this as a bar or if I want this as a hundred percent stark bar so I'll click that and you can see the difference but hundred percent doesn't work because we only have two parameters all suppose I need a line shot click on the line shot and I can see the differences for three it is 58.50 three nine four four it is thirty one point four zero four five it is thirteen point one six so that is how it works now suppose I want to see this as an area chart I click that and it happens so you can customize your visualizations the way you want but these were only for two parameters now suppose I want to add more parameters over here so what I will do is I will select the right option from here let's say I will select line and scat column chart so what will happen over here is start in this fields pane I will get one more option with the name line values now I can add one more parameter over here now suppose I want to see my fuel economy so what I need to do is that take this fuel economy and drop it over here and you can see the fuel economy appears as a graph it's quite clear rate see just because there is no value of weight over here we can see only number of forward gears and fuel economy but if I move my cursor over here I can kind of see everything now suppose I want to add one more parameter over here suppose I want to add number of cylinders with weight so what I need to do is that I take cylinders and put it over here under this weight added so as you can see my graph change so here I can see my cylinder value here I can see my gear to weight ratio and here I can see my fuel economy now I want to create one more draft so this is done I can minimize it take this to a corner and now suppose I need to add an API self like that key performance indicator so what I will do is that once I select that I will simply select fuel economy and drag it on indicator I'll take horsepower and drag it to success now when I added horsepower to trend axis you can see I see a value over here and what does that mean see there are different filters over here also if I want I can apply a filter is greater than then apply filter but there are not much parameters over here so there is no point of doing that so what this basically does is that it kind of adds value you see your sum over here if I want to average I'll click on average you see a graph it is changing suppose I born trunk count as one suppose now I won't stand it deviation so what this necessarily means is that this value or the indicator is the fuel economy and the trend axis that is going in the background that actually means the horsepower and as you can see is continuously changing for these different aspects so count distant it's 1 and this also changes accordingly suppose I need minimum 15 so this is the fuel economy and this is changing in the background the hot spot now suppose I need to create a donut chart or a pie chart let's create a pie chart so I change that for pie charts as you can see you can see different options over here if I select this all of the third be selected so let's maximize this to make it easy to understand for you as you can see in this pie chart different values have appeared so the here the horsepower is 65 minimum of fuel economy is 33 point nine zero similarly it changes so this is again a range chart or a donut chart as we seen again this means the scene so this is how you can work around power bi desktop you can change your values or if I want I can add one more horsepower over here in this values and I will see this so this basically shows horsepower and this shows the sum of it I want average so I click on average and I can see the value changer with all maximized start see now suppose I want standard deviation this change I'll switch on my Title II yeah and now I'll give a title - let's say horsepower indicator fine as you can see my title appears over here I will change its alignment will increase the size so now I want to see my median so I click on median and my median changes so this is how power bi generally acts now I can do one more thing I can simply select any of these values drag it and drop it in this canvas directly by doing that just because this is a single value it column chart is created and power bi does it on its own because power bi has the ability to kind of understand data that we are ingesting in and it creates visualizations on its own now let's see I take horsepower and put it on top of here so a new graph is created so this is like horsepower versus display but now I am not happy with this and I want to change this so I simply select this graph click this watch now you can see it changes so there these aren't many ways how to create visualizations this is how you can do it so we created this graph we created this indicator we have this kpi in this caper you can see all these different values we have this indicator so now what we can do is that suppose I am done with this and this is the report that I wanted to generate so how now I have to publish this but before that I need to save it first so I will save as report 1 and this report 1 will have an extension of PB IX that means it's a power bi file and I will save it save it in my desktop so safe so this is safe so what this means is that I can take that file and click on any Windows 10 operating system or any desktop which has Windows 10 and it and if it has power bi then this will open directly on top of that I can click on publish by doing that I will publish this report to the web see I'll show you I'll click on publish it will be taken to my workspace so I will select my workspace now this report 1 has been published to power bi see now it is published so I can open report or report one dot P VIX and powerplay what this means is that once I click on this I will open power bi service as you see we open power bi service over here so I'll sign in I will use to see him account that I was using I'll enter the password and sign so this is my power bi service power bi cloud service and this is not free actually but force first 60 days you can use it for free so as you can see over here and I have several options I can see shared with me no one has shared in the contact in this account so I can't see anything I can click over to my workspace and over here I will find under reports report one I click on report one and I find my report you can see this report rate and now this is on the web so for sharing this I can either share it with someone by clicking on this option or copy this link and send this link to the person who might want to share this and by simply accessing this on any browser they can open this report directly so in this portion what we can do is start we can pin this pin life page so in pale live page I will click this I will create a new dashboard under the name let's say Roshan or bi demo then open at life now as a it has pen to dashboard so either you can create a phone view or go to dashboard or I can do one thing I can simply or if I want to add more visuals over there I can simply pin this visual existing dashboard Roshan power bi demo pin and once I pinned those things I can find my dashboards over here in the left side under this dashboard I have this Russian power bi demo so I click over here and I find my options I had this display that I had pinned later and I had this whole report in the first place so if you remember we had this power Q&A feature so you can simply ask questions about your data directly over here using simple English and you will get answers so suppose I will write total weight and I find the total weight is 100 2.95 suppose I want to see this play see display and I find it here all I need to do is that suppose I want display - total weight can't display the visual okay but that is how it basically works over here you can find your usage metrics and you can simply click over here and open it that's how it works so this is basically all about power bi and finally if you want to share this simply click over share this dashboard enter an email address with whom you want to share suppose I want to share this with Roshan dot in belly part at gmail.com see now you will find this message one of more email addresses with the following domains are outside your organization gmail.com just because my account was created by the extension in telecom I can't use gmail.com I have to share this with someone who has an extension gmail dot form so I will write Bhalla ad and belly part Losco and it works so I can share this with people who work in my organization that's all on top of that you can provide access also you can once they are your partner's or you are connected to them in power bi you can provide your colleagues access over here also so this is pretty much it about how power bi works now we have created our first basic dashboard with power bi we have a good range of visualizations here so now let's publish this to the cloud or bi service so we are still in power bi desktop as publish this to power bi service so for that we need to have an account so I already have an account I am I have logged in so when you launch power bi desktop it will ask you if you have an account log in so I had an account so I logged in so that's why you can see my name here so now I want to publish this to power bi service I simply click on this icon so clicking on this icon will take that so I already have some file a dataset named Lance Transportation Corp there on the cloud surface let's sleep restart but just this file this this dxp solid this analysis file will be new okay got it so now publish to publish to power bi so if you want to open you can click on this link and you can go there or you can log in to your cloud account and you can see a Raina lytx there so what I'll do I'll click it here okay let's not do this with ie okay first let's get let's say like let it get open there yeah so this is a link copy this link get out of here it close the data file we don't need it now okay so it'll ask for the login so I have this Microsoft account you asking for mobile authentication iego this is power bi cloud service this is a dashboard that I just created okay so this is the actionable dashboard you click on data to data get filtered so you see this is a data so the filter works on all levels of data so if you want to select multiple bars you can control and click you can click you can press control and select multiple bars of data yeah so this is a actionable dashboard now we have in power bi cloud service so this is how the cloud service looks like so when you deploy a report that will come here in this section reports in this section so now from here you can compose a - master dashboard what I what you can do you can select the icons select total revenue you click on them so to ask you to create a new dashboard or to any other existing - but I will select new dashboard a limit as grants dashboard and just pin it okay you see this in your dashboard God got created here and we can see that as a tile here so that visualization is a tile here now if I click on this tile it will take you back to that report the dashboard the underlying report you can pin multiple visualizations there yeah yeah lamps dashboard the affinity I will pin one more KPI and then this map check our dashboard this is all this dashboard looks you can arrange them and we nearly this drag-and-drop so he don't develop any visualizations here so this is pinning from our already existing dashboards this dis acts as a mast master dashboard if you click on that so it's DUSU an aggregated brief overview of the data if we can if we can pin visualizations from multiple dashboards into this canvas okay let's leave it there so the natural great natural language query option that I showed you is not available at a report level is not availability rivet level but it is at a master - model so now when you when we have pinned something here now we see here ask a question about your data I can comfortably ask a question like this total revenue so this is my total revenue you see there that's my total revenue they in August in the select month Butler month it change to say total revenue in this is in August we saw the variance I suggest total variance this is my total revenue in August this is the total revenue in August okay say revenue revenue by source city so let's select see our city you see I will see what top five in you visualisations for all this talk by revenue by source city origins day our origin city okay this will generally come up in descending order of this I can change chart type also here as line chart I can see it as a line chart or a bar chart I can say it just not that once you once you start composed of once you finish creating a visualization you can actually do some more enhancements you can go over to filter visualization feel feels level if you don't want this as a bar you want this has a pie chart here you can convert that into a pie and you want to change color schemes here you can change you can do further further modifications and then straightaway pin that visualization to your dashboard let's say it now get back to the dashboard you have that here you have that sweet pie here so just created by human-computer interaction it's not that tough okay arranging this okay there you go fine so now so this is this is master dashboard so here you can place tiles we can now when you click them you'll go to get back to the original reports you can run natural language queries here you can compose you can create ad-hoc reports like that so that's one ad hoc report that I created that pi there okay so you can start sharing your application from here you can go you can checkout the notifications that you have select a particular visualization and so this menu is getting changed every now and then as I told you read this is highly evolving tool right now and a lot of enhancements improvements that are being made to this application there is this option okay so we have no notifications so I think suggest that share option is disabled here so we can add accounts here so if you have any power be a subscription you use this you can add them you can add the users to this report here I'll show that shortly so now here the another future is the another way in which you can publish this report is by creating it as an embedded copy so when you click on this publish to web so it will create a copy embed copy so now you see this iframe code here yeah you close this now anybody with a link with this link can access can analyze can view our analytics so this can be freely embedded into any application so this is a power bi embedded they say this is a page you don't get all that futures here the settings filters dashboard options yeah at the moment there is no natural language clearly support also in this embedded copy but still this is an actionable dashboard you can still click this view data check-check everything check everything that you are interested in so this this is possible so this is possible with all bi embedded this is the other way so this is an easy integration into other applications your existing reporting applications quickly integratable so yeah so just created that link there okay so we have two data sets for books we have multiple options here we will keep exploring the options as and then we progress in our course so for now so that's an end-to-end flow so we created an now application in our desktop or bi desktop we created a dashboard we published that to the net power bi cloud service we got that here under report section we pinned some within some visualizations to the dashboard we created a new dashboard and we've been actually we can pin the entire page this entire page we can pin into our dashboard okay and there we used some natural query languages and we also show how to publish it to the Internet okay you see you here we have the share option okay we have options share option at a dashboard level this is what I wanted to show you so if you click on this it will give you verse by a panel here so anybody with cloud subscription that email address if you add that here yeah you can grant access so email will be sent to them and once they log in in their workspace in their workspace they will see our - our reports so that's all this cloud so there is no local setup there is no local infrastructure for power bi reports so everything is on cloud the only thing you can setup locally is a gateway or bi Gateway to talk to on-premise databases hi so here we have the basic dashboard build and publish to cloud service so we will go explore further in the next angle assignment on how to perform calculations here we haven't done any calculations we got the data as is and then we represented with some set of visualizations and we published but this won't be the case in real time situation so you often run into need of writing a custom formula or a expression or calculation to represent data in your visualizations right so we have options in RBI for doing that so the next assignment is targeting on the data calculations using Dax expressions or Dax stands for data analysis expression so I will give an introduction on the Dax and then we will do write some couple of formulas on the data existing data and create visualizations on top of them will discuss clear more of Dax in upcoming assignments so for this assignment will target on induction to Dax and also will get two new datasets into the analysis we already have trips data that is that is coming from the excel source now I'll import two more datasets into this power bi desktop so the data sets would be coming from open data feed or data is open data feed so this is a data feed that is hosted on [Music] web so it's like complete data warehouse being hosted on web and we can get data feeds that we are interested in okay so that feed that would match my data or your categories so in this navigator I can you know simply see the preview and select the ones that I need yeah shipper cheapest ok Lord that will also ember directly into my analytics file you can see these two tables now they are added here go to this panel I can see those two tables here okay so the good thing with power bi desktop is the moment you add any new tables into the analysis if there are any relations between the state of two tables they are automatically created like this here you see how trips data categories and cheaper because they already have keys in my data shipper key and category key so they are matched go and check out the definition of this relation you can see here trips category ID categories category ID yes same is the case with shippers so this relations are auto created in power bi so now we'll get back to the trip state of this okay so this is a transactional data I have here if you actually observe here so here there is no option to write any expression for this visualizations you see we can only drag and drop fields here we can drag we can drop we can configure those fields we can format we can set reference lines but we can't actually write any expressions at visualization level all the expression writing should happen to take place at a table level like this here the new measure are a new column at the back end so entire expression writing in spot in power bi is divided into two form either you do a row level calculation that is row context or a set level calculation which is set context row level control level calculation is something like creating a column out of an ax or some existing column okay subtracting these two values and creating a new column so that is you do at row level okay so that is row context then which is no column then what is set context set context is new measure creating an aggregated value maybe I want to find out the maximum of this shift s or my total miles or shape des so that is an aggregated value which is at a set level so that is set context so for such needs will create a measure so we have to decide first what we want to create we want to create a measure or we want to create a column for both they will use Dax okay so that is very simple we don't need to worry about that if you are at least some aware of excel formula writing you can easily pick on that is because Dax is completely excel formula base but it is more than that yeah but still for round we can consider it like that okay now I'll create a column in this assignment I'll only focus on row context I will do row level operations only row level operations so I will create a new column so here I have ship date and then delivery date ship date is when it was started for shipping and delivery date is when it was delivered so there's a target so it's saying that it should not take beyond this so this is minimum expectation set if you see this is January 4th it started for shipping two days so it should arrive for sixth January 6 which is the case here so this is on time delivery okay but there could be some variance so here the June 7 & 8 so this is one day early so 7 plus 2 is 9 ideally should be delivered by 9:00 but there is an early delivery ok yeah there will be cases like this not in India okay so now go and create new column and in this I will write a formula delivery days equal to ship date - okay I'll do it the other that you should be done the other way delivery date - ship date okay we all know a new column created here but now the head notice is in date format but generally this expression will return a whole number because that context is not set data is not set to select the column go to modeling and you can see data type why I am NOT saying that a tab highlighted she is this would get highlighter generally okay here delivery date yeah venerated sorry not derivative - tolerate delivery date - shifted so that's what I did here so I got this date column time okay now you can see here data type and format it is assuming that it is a date column and that it is giving you this format option in date format options okay but that's not the case so let's change the data type change it to whole number yeah okay now you should be saying it an actual form okay fine so this is this is the way it is okay find all values into okay see here huge negative values okay this is because these are the shipments that are never delivered various reasons may be address was wrong people were not there at home okay met with an accident I don't know are not ready goods were not ready for shipping superior reasons the delivery dot are undelivered so the date is null so this is not correct so to show this as a big number here so let's do some more calculation here we'll do the conditional statement here if is blank delivery date then put zero else do the subtraction so if it is undelivered there is no point in calculating the difference let us take it as just zero okay so all this values will change okay there you see okay fine so now I've got delivery days I'll go I'll go the next level and I will calculate delivery status add one more column in your column delivery status so this I will calculate based on the day so here so I so I have delivery days and then ship this right so if both are matching then you are correct so that is on time deliver let's see the combinations if delivery days is equal to ship days then it is on time another if statement if delivery days is less than should this then it is early delivery if it is not on time if it is not early then the last option here on the check we can directly write late delivery okay so we got on-time early and late deliveries we have missed out one more so what about undelivered it will be delivered days would be zero so we have to handle that other so we'll do what we'll put one more if clause on top of this nested if statement if the same thing we did for Dell radius if delivery if delivery date he's blank then undelivered and do all this yes man I dig it okay if I give you a closed bracket here okay now I got this new calculated column which will tell me the delivery status a readable way it will be format I see we can write this expression many ways I can actually directly do on dates like if ship date is equal to so general ship date will not be equal to delivery day you so if you add ship days to the ship date ship date plus ship days if it is equal to delivery date then it is on time if ship date and plus days is less than delivery date then it is early delivery yeah so like that I can try to come calculation so I can write calculation in many ways okay so here I have demonstrated away so I first calculated deliver it is because in delivery days also I can use in some way and then I divide one more column delivery status so these are flow level operations so that we create a new column and these are all row context okay so here we go we have a column here so now let us get back to the report panel create a new page name it as delivery analysis now get that delivery status the column that we just created you know we have just verifying whether we got it correct or not then type ID now say our total to K trips and this is a split yes this came out correct we have a little late on time and undelivered you get this now we'll start building the analytics do control z switch this into a pie chart so this is god I got no heart I'm late early and yeah so change colors a bit No so if colors you know can convey so early means good right so yeah they put some contrasting colors late undelivered they present it in a different color early and good shade okay so fine so we got this here on time deliveries late and we can see that late late sector is relatively small compared to the entire stack you can put a stack percentage bar chart like this and you can analyze data by safety buddy so you can see here so if you take the on-time deliveries compared to on-time deliveries later nothing so just a three person yeah okay so now I will go and convert this into pie chart and in the pie I'll put some more fields some more categories like after finding out that you know undelivered undelivered items are more compared to late deliveries I want to further analyze where the problem is so I will provide some more attributes here so on the legend I will drag and drop company name company name so now this is still above the level to the next level so I go and click so now I can see by company so this is now right now it's giving only volume how many trips were made by each company so these are the vendors for my company so I have types with them they do the shipping for me okay so now I can see that yeah United packed packages have more shipmates and getting more business from me okay so the other way is enable this board drill mode and now if you click on undelivered so it will take you to the data related to that particular undelivered packages so in an ideal world you can see that everyone is contributing equally 25% 25% 50% is 50% because we already saw that we have more chippings here from the needle packages you go lay late and check out so late you can clearly see the difference only united packer sang the federal shipping so the third player the third player is no their same here so who is the third player speedy Express so there are no late deliveries from speedy Express you see you click on late deliveries yeah so we can do we can see why what good they are doing are there no late deliveries and we can replicate that business model in other segments or sectors just not that we can do we can go further after we got elderly we can actually go and look out and check by state maybe somebody is doing good in some state and not in other state you want to check out and you placate those practices so you go check federally is doing well okay again they are doing well in you noise I'll compare to any other state and Michigan yeah so we can see like that so this drill option we have for every visualization we have two kinds of drill at a time you drill down to the next levels like this drill down drill up or you can enable this and drill pie slice the actual slicing so you see this option this will at a time we will put all the data heck ones here late speedy Express by state like that so it will give everything everything much and show it on semi canvas okay so drilling by merging this one this option the newly added option okay so we can do that as well if you do this it will just you will not be able to relate from which like which slice we got this so if you do this we can relate actually so undelivered federal shipping on time United on time speed on time federal you go further again on time United you see the you who hold message on time United Packers Ohio so that is a state we got all three on a single slice this is another kind of railed on we are totally three kinds of drill down plane drill down goal level by level yeah split for every category then go by Harry kill drill down so this is hierarchical drill down are selected drill down you select a particular size and drill only that particular forecast area interested area so we have three kinds of drill downs okay now let's put one more visualization here so which would be stacked bar so in stack bar what I will do I will bring categories that is a name and then total miles versus loaded miles variation between total knives he'll switch these two side-by-side stack I can now see that I can see here that in general total miles are total miles will be more so the difference between total miles and loaded miles will give you dead miles empty miles so the concept here is the total miles will include the miles that incurred in sending our truck to the pickup place and once the truck at the delivery is done again when it comes back empty to our warehouse so all that miles is including he included in total miles loaded miles are the miles only for which the talk was actually carrying the load so the reference between the loaded miles and the summation of loaded miles and empty miles will give you total miles so we said yeah I think I already said this earlier so now I'll put a reference line on this I say set individualization so for reference lines we can add a bunch of reference lines so these are analytical lines so they are available for few visual decisions mainly for bar visualizations so you go and select and average time so I'll put an average line by total miles yeah so you see so these two are almost making the average they are meeting the average of total miles compared to that so they are doing good in this area's dairy products range and poultry we are doing good okay so yeah we can add reference like this we can switch to a full mode like this every visualization has this future you can always switch to a full mode and when you right-click select something and right click you can see data if you click see data it will give you the data of the tired visualization and if you click see records review the underlying data records so this is uh negated data so this is transactional group this transactional you so same same options will get here also see data so this is aggregated data which is constituting this visualization back to the normal moon and right-click on the visualization and see records reading you the transactional you transactional you so if you see actually so go right click and say beverages total miles see see records this data would be actually related to now go le drill down shown SQL all that okay so because this data say I want to show you this is not the entire data set so this is a data set which is constituting this so if you see here ship name would be the same signal federal shipping because this is a data related to only that particular slice so see records will give you the transactional view of only that particular selection whereas see data will give you the aggregated data that is composed composing this visualization okay so yeah we have focus mode and also we have option to view data at record level at an aggregated level so we'll just have to finish this assignment we will get one more analysis will do one more analysis with this data and complete this so here this is a tree map there's a very special visualizations if with this I can do the correlation study between two measures so the study I will do here would be a cost my shipmates across my categories shipping categories okay I want to do study between shipping costs shipping costs and revenue spend versus returns color saturation I would select ring okay change the color pattern a little bit okay and change data level colors so here label colors you can't change here data color so how we can change it a color font of the labels if you want to change you actually have to enable data levels change the color there so you see data labels on screen and then you disable them yeah the colors are changed and so you do it okay so now we have here a tree map so what study I can do here is so you see here this - I am doing - - a correlation between two measures one is shipping cost and other is a venue so if you see the area of the tile so these are tiles each one is a time here so the area of the tile will reflect shipping cost and revenue is the color intensity the more the color intensity the more the profit the returns okay you see these two tiles they're equal in area means the shipping costs the spender on these two types of categories is same but still the value proposition the returns are more for paltry compared to this you see this time this tile has least spending less spending compared to all others the area is very little but still the color intensity is somewhat average means what returns are better better compared to this so here this has least its cost spending and least revenue on so that is okay this to our average sized so their color intensity is also average yeah so the color is getting dimmer so the saturation you can see this here so the highest intensity is this maximum and minimum there is the lowest intensity okay so this color saturation study is for revenue color intensity and size variation of the tile size is well give you shipping costs you can add some more like trip type into groups and go and explore that by specific category or at a time or everything yeah you can get that like this hierarchical okay now we have more intensity coloring color variation in domestic yeah in international shipping rates domestic shipmates like this so we can do a better color study like this maybe these are the small tiles you want to contribute so you can focus on ships like this see actually you can do one more study also if you actually observe one two three four five six this six tiles the six segments are contributing to almost 50 percent of your business the area area will give you the costs right so that you are spending almost 50% on this for so instead of analyzing this bunch of setters you can analyze this so by spending that amount on 50% of your returns you get the more benefit because this is constituting to half of your business these sectors yeah so you can easily identify the more profitable areas also using this tree map may not be fifty percent but yeah approximately 50 percent half the area they are covering this just dis Phi sector segment Stiles fine so what'll we have done we have got data sources from today to new data sources from OData open data the data cloud hosted on website oops with this data we we have then we saw how to do drill down in 311 three ways till down and drill up by Oracle drilled on selected drill down we saw how to add reference lines and focused mode see records record level transaction level yeah see data see records and then this tree map very interesting map to row compare it to quarry to study between two measures okay and the one important thing that I want to tell here is actually we are showing data on all these three visualizations from three different data sets shippers is coming from shippers data set catalyst is coming from the catalytic and revenues find the number of trips is coming from this data set but still we are able to successfully create these visualizations without any much conflict because the relations are well created between this date three data sets so that is a good thing so we can create visualizations from multiple data sets very conveniently in power bi single visualization can be created from multiple datasets very effortlessly in this assignment we will further do calculations using Dax basic calculations using tags okay so we'll be working on the same dashboard that we created in previous assignment on top of that we will compose few KPIs and put some trends so this is a dashboard that we developed so we'll be using this from previous assignment let's create some calculations using Dax functions on this so this is a gate I have trips data so now on this this time I will create a measure create a measure set context ok so the difference is you know previous assignments we use Dax for creating new columns so those calculations will be happening at row level for each row for each change in value that formula is applied and the value is composed and that is what we see as part of the table yeah so this occupies space R on contrast to this measures measures work on the sets of data so you don't see a new column being added to the data set so they will be composed and will be available on the fly let's do it let's create one measure so I am I'll be creating a measure to bring revenue per revenue per load so how much revenue is being generated per load so for this what I have to do I have to take the sum of see as I typed the heck formula so it has intelligence to show all relevant formulas in the drop-down so you can pick a formula from them and then there is help intelligence so that will tell you what it takes actually for the formula column name okay it expects a column name here and now when you type the column name it has again intellisense to pick up the columns that are relevant to the sticks takes your typing here yeah so I typed revenue Rd so now you can see that there it's very advanced formula bar that we got here the sum of revenue divided by count of providing so this will give me for trip so this will give me particular venue is being generated on an average per trip what is a revenue I already revenue so this is a call calculation okay so this is how generally we do so this when at the moment I hit enter so a new measure gives created and you can see that got added here that got added here and you don't see that being added here right so that is because this is at a set level so the calculation would be the would be happening on the fly now you can use this measure very much as any other value in our visualizations I'm just doing this changement little behind in this visualizations okay so let's check that you measure that we created so where is that measure there are no paths Lord it's added as a data card annelids chip chip specific API so per Lord I am getting this much revenue change this [Music] 4k yeah approximately 4k 4.3 carries revenue generated per load this is a KPI let's do some more some more kpi's so I go create another measure revenue per mile what is the revenue generated per mile so here again some off some of total revenue so instead of using a divide operator it's better always better to use divide function so you already use divide operator we saw that how it works right so we using D we have good advantages by using divide function over divide operator the biggest advantage is if it encounters divide by zero yeah if it encounters divide by zero ever then it will not throw anything it will not fail it will not stop calculating it will simply give us a blank value that will give us a blank value so that's one advantage so divided by zero errors can be handled by using this function and you can also set what could be the alternate value so what I want here I want some of total miles I'll take loaded miles because that's what gives me revenue some more floated miles so this would be revenue per mile there are new per mile what KPIs I have revenue per mile okay then I'll tell compass one more variance variance in loaded miles and total miles I'll call them as dead miles admite sasame total miles - some of you read knives okay so now I got some measures here let's go and put them here so I just copy this list just created two coffees this KPI it's a car actually its beta card so as you can see this is a card so you know second one the first one is showing revenue per load so second one let's put okay if you unselect data all the data fields from a visualization visualization will disappear revenue per mile this is revenue per mile not appropriate format so we will show it as remember so that's a revenue being generated for miles okay and then here on this will show variance so that's it miles we have this many good okay so I will do some formatting here see revenue per mile is actually a value a monetary amount right so we will show that in some format so I can do the format by going into modeling so in modeling I have bunch of options here we saw already how to change data type and category so now we'll see how to utilize other format so for this I have to select the column not the visualization column or the calculated measure so I have select this calculated measure revenue for my and I will sell it I will set this as $1 so you can see now $1 dollar icon will appear making that it is a numeric come on yeah these are very much in need revenue come on so it's same is the case with this column so I can change the format and set to currency and currency would be US dollar right okay so we can do formatting stuff like that we can we can set it as percentage yeah so now you can actually do that as percentage not this so this is a dead mile so want to see actually the percentage of dead miles percentage of dead miles over overloaded miles how much percentage of dead miles I have over tot total miles actually yeah so we will do division of divide because that number is actually not in a very readable form so I can't figure out whether it is high low okay okay so I can't make that kind of study there so if I have to do proper analysis yeah being it improper readable format then better show it as a percentage so when I divide this difference by total miles and I divide this difference total miles yeah so dead miles would come up as a fraction as a percentage let's go back and see yeah so this would be now a percentage change the format back to a number don't apply the special formats okay and notice it's not in a percentage so we'll go change the model select the column good miles and change it to percentage okay so that's the 37% so on overall loaded - the 37% is a dead miles percentage little high yeah it should be always in or 15% it's all it's more than 1/4 so need to be taken special care you need to be taken here okay so this is one analysis that I didn't make here okay let's go and see this buy all this information by a shipper I have different shippers right I have outsource I have different shipper so I'll see I'll switch this to a freighter so I can set up a filter like this I showed you already so now these classes setting up filters okay so we have filters here so we can select required what we want to see so if you see United pakka so it is it's not there is no much variance oded miles are same almost same for most of the vendors okay so you can see the revenue is revenue is changed revin there is slight change in revenue but yeah so we can actually convert this into a general properties we can switch this to a horizontal format and we can get rid of header and make it something like this this is a this is also filter looks looking like a KPI in but it is a filter so we can filter on this okay yeah so but header should be there if you want to reset it so header is essential for resetting the selection okay now there I go I have a filter by company name so I'll build one more KPI so that would be the last key player here so what I will do I will calculate percentage of percentage of on-time deliveries on poor old galleries what is the percentage of content deliveries yeah because that would blew me good that will give me will be a reputation yeah so let's calculate that so it would not be a simple straight expression will be little complex but don't worry we will understand that quickly so we'll go and create new measures were here also in modeling tab also we have the option to create a new measure or we can go home and here from here also you can create a new measure or we can right-click on the table and say new measure okay all three same okay now going to right click there here this measure that week added and disappear we'll get it back so all these measures when I add those are set level operations so the formulas will be applied on the fly so you don't need they don't get stored so they don't talk by physical space so here I will right on day on-time deliveries so for this on-time deliveries so the function I would be using is so what I have to do actually so I have to count number of trips yeah so count so we generally count trip ID so yeah so this would give me the overall trips but that's not what I want to do I want to count only those trips who whose delivery status is on on time so here I have the load of status I want to count only those rows whose their status is on time so I will convert this function to count so what will do this will count the number of number of numbers in a column I don't want to count numbers in a column I go I'll put convert this to a count a count a will count the number of values in a column so it will count by value yeah and then I want count a X I want to write an expression inside it and based on that expression counting should happen because I want to count only on time deliveries right so yeah so I need to write an expression so this is a function that I will use count a it says values X X J's it's an expression there was going to be an expression inside this Altima so what would be that expression so that expression is so a filter expression so I will use a filter expression so so the filter expression filter function will expect two parameters the table and then the filter expression table would be here trips so table know that we are that is in use yeah so this table on this table I will write a filter on delivery status delivery status which is on time delivery status content so this would be my filter expression so filter rows in this table whose delivery status is on time okay now whatever is written from this function based on that count the values count a so count values based on that expression in the first part so this would give me count of deliveries made yeah so but now I want it as in percentage so what should I do I should encapsulate this entire expression into divide divide at the end put trips at the end put this sum of trip ID that's it so this would give me the percentage so it is doing what it is filtering only those on-time delivery records and we are adding that and that addition that counting the counting those rows that count will be divided by the total count not some of count of so here I can use just simple count count will give me the count of trips ok fine so now put an enter on time deliveries go put a card here not multiple single row card and drag that newly composed measure onto this where is that new in the compost measure on time deliveries convert this to percentage there we go seventy two point eight five percent of content which is very good which is very good yeah yeah so we are almost one third sorry 3/4 yeah of our trips are compiling these are in compliance I can actually now see speedy Express yeah so this is low so unity five federal shifting sixty-eight low United 73 so United has more on time deliveries no not me right it's a speedy Express has a name such as PDX places most on time deliveries yeah so we can do kpi's like this now that we have calculated KPIs using using measures now we will do some more things here so I have I have shipping days yeah so I have shipping this so each consignment is assigned for a SLA ETA systematic time of arrival in another way if you have to Tom this so here we have preset values so I want to see by category so I want to see number of loads that I am categorizing based on the ETA so one quick thing that I can do is I can get the ship days onto the canvas and maybe I want to watch it by I want to put this on access and I want to see the volume for each category so you see here we have like number so we see sls that's it for two days we are more volume compared to others yeah so one day within one day delivery to de salud three days delivery but this is okay because now it max as a SLA is ten what is they are more like 2025 payment term days one can pay in 30 days 40 days it is like that so I don't want to see so this is down now I am getting into a stage where I want to show numerical values on categories so that's what this assignment is targeting us so how to show measured measures numerical values on category axis so we have certain visualizations built around this to show how to deal with that before getting there yeah I want to show these measures as bucket because that would make moment more meaningful as a category categories as bucketed values so observe how I will do that how I will bucket these values so for that I would need a reference table so far we saw how to create a column how to create a measure now create a column create measure now for the for the time now we'll create a table let's see how we create a table okay so you will shortly understand why I am creating this table okay maybe not in this way I'll not create the table in this way I'll get it in more easiest will lie like this enter data and the data I can keep entering data here if I have a data already in excel sheet I can simply copy and paste here it's as simple as that okay yeah so here column column name be this category this category and next column minimum minimum this maximum this okay now the buckets would be one - sorry one two three days would be one bucket for two four five six six would be another bucket seven to eight and last bucket would be nine to ten so all the values that are falling between these days should be categorized as one single unit that's the aim here okay I don't want this extra column the minimum this here would be one three four Evon seven eight nine and ten there I'll go I have table now so give it a name des categories okay it will shortly will be added here related yeah okay now we have that here they scare to get his hand so now these are used to create the column in my existing data so I will not create a column I will go and I will create a measure and now see how I write this measure so I will name this as I'll be doing this on shift s or heap this keep these categories so that would be so I'll be using a function to right an expression so the I would be using calculate so calculate is in function where you can write expressions and filters and stuff okay calculate will be very handy function we use it more so this session and the previous session was on basics of tax writing okay so we are doing basic calculations really have we have more sessions on advanced tax to understand it better okay so now we will use this function and I will count this term will not use count a or count X I will use directly count rows straightaway count rows by applying a simple filter filter like I used earlier okay filter filter word filter trips table filter trips table so here I would be writing and compost composite function so I will use and condition statement if and I have put one more extra base here so if ship days yeah ship days are greater than equal to I go for ship these categories the new table that we created this categories mean over ready name this function will stay we're not to worry yeah max this mean - if this is greater than this and soon function less than or equal to max this city's falling between mean days and max days yeah there I go count the overall rows count the overall rows on the rows from Crips if the tip ship discs are falling between these values okay now he's throwing some error yeah because they should participate in some aggregation so we know that we don't have multiple records there but still this expression will not understand so we will simply use one dummy aggregation it will be applied at row level so now that it is updated row level we have only one row per category ideally it will not do any max but so we need that to get rid of that warning message okay now the expression looks correct so we're doing the comparison ship this between min days and max days and if it is falling then on that trips found those rows enter there we have so this now I will use it here ship days I will not ship this I will go and I use ship this categories okay should this cat why not to use it here check out this okay as a card shifting status fine so this should be combined with categories the actual categories now we are actually counting only the very difference variation between though unless we bring this don't see the actual category okay yeah now you see B not agree so between one two days two to three days this flip it to bar Chuck okay so clearly we can see now so this is the count of trips count of trips so what we are what we have done earlier we were showing one two three four shipping days in just plain numerical values now we have categorized columns this is also called as binning so we means we have categories like this so we can clearly see that most of our deliveries are targeting for 1 to 2 1 to 3 days between one nine three days then next is 4 to 6 and 7 to 8 9 to 10 so these buckets are almost same okay let's understand it better so now this I will split by trip type trip type in category axis I will go to television yeah I see so this red focus mode see this slice says that these are intercom intercom means within now within a factory within a company ok like a shuttle enter shicken kind of transportation okay then we have domestic then we have international see all our international deliveries are ranging between 7 to 10 yes that is correct right so they will take more days yeah so the data is correct so but there are some internal international tell values which are falling in four to six days to bucket also with which are on par with domestic deliveries means what the missing these domestic deliveries are taking a lot of time this bucket need to be specially analyzed because it's taking more time okay now if you see here there are gaps means what within this bucket within this bucket there are no intercom deliveries within this bucket there are no intercom and there is no to me they are not domestic deliveries that so we see blanks there so that legend is not coming up for all okay so this is a share of intercom so all of the deliveries are within one to three days then domestic are between 1 to 6 days and this is split so this is how we actually can show measures measures when we are showing images we bucket them and bucketing turn in this way in now power bi yeah so that's how we do a bucketing in power bi so we have to create a reference table and on using that reference table we create a function so if you say actually in this function it nowhere gets the categories we don't we are not using these categories in this function we are only using min and Max of this table and we are only getting the trip now when we put it put it on visualization we we use categories in the visualization based categories from the newly created table so that will take care of bringing in the counts party as per the bucket and composing this visualization let's do some more visualizations I want to see the difference between target dish and ship this have been actual delivery dish and ship this okay so I will pull ship this and delivery delivery days ship days versus delivery days I will put this as a line chart okay and on category axis I will again put ship this I'll put ship this and I will do sorting sorting is not allowed so instead of actually discount because this is actually doing the overall count which will not be effect so I want to do average study average of this versus average of this this would be middle corrected data yeah so you see now so I have two measures here so you see the black line is representing the target ship this as an actual ETA actually EPA estimated time of arrival and this is delivery days okay if you see up to four days our target when it is up to four days within four days both are in line means the lines are are going going linearly means there is no deviation means for the time line that we have said the delivery time lengths are matching or setting they are in compliance okay if this blue line is going beyond this black line then we have a problem means our delivery days are more than the actual days and then the ship this but the line is going downwards after that so as the number of days are increasing eta we are doing good so if you see here the average delivery is eight average ship distance so we have set at SLA ship this is an SLA of ten days which is ETA expected time of arrival but we we have delivered it in a different way so that's why this line is downwards which is a good indicator which is a very good indicator if this line is in line to this both the lines are parallel and coincident then it is not good not bad it's okay so we are meeting there is something okay so this is one good way of representing this measures scalar measures okay next thing is this is an overall study we do have late we do have undelivered shipmates late late deliver early deliver so but when you take an on average we are doing good okay good so the another study that we can do is we can use scatter plot we can use scatter plot to [Music] to actually do instead of starting from and so I will copy this visualization and I will switch this into a scatter plot because I want to do that compact study on same kind of data okay okay now this I will add one category so in scatter plot what happens it will lay points for each data point so this would be X shift this instead of shift this I will take it by destination city details average of delivery days and average of shift takes on x axis so there's a correlation study I have and the volume and the volume arm size color saturation color Saturday this is not right so this should also be count off okay so these levels so we could right now we can control the size of these bubbles so we have average of over here we should be having it as average okay so there you go so now here what it is saying is has you know this is on delivery days this scale is on delivery days and this scale is on ship days as ship days are increasing as she so are towards the right side ship days are increasing and towards the top delivery days are increasing see when ship days are increasing yeah and delivery days are increasing so this quarter you see this quarter this is okay this is this is normal because ship days are increasing and delivery day is also increasing that's why isn't upper top quartile but you see this data these these data points you observe these data points these data points which is observe these data points these are doing good these are doing really good why why because these are on the extreme right side means what number of shipping days are integrated so for this point shipping this up more okay the shipping days are more but they are not on extreme top means what delivery days are not more delivered these are less so these are the cities where if you see if you find ship days are four but we have delivered it in three days only okay so Phi actually SLA was Phi but we are delivered in three days so these are the points we'll be doing good so if we studied these points and understand the business model is implemented on in those cities or is it because of the local conditions we can enhance or we can improve our business yeah so we can do colors formatting so all yeah we can do all this bubbles can fill can do colors by bubbles okay you can fill them yeah so this is cata plot scatter plot is also useful for doing correlation study between two measures just like tree map okay but it is more advanced here we can plot the entire data on the visualization and we can study the we can identify the patterns we can identify the four focusable areas you know where we can lay our we can where we can put our focus and do compare it to study like that we did here so these are the cities where we are doing extremely good though that is shipping days are more delivery days are less and here these are okay these are okay but you see there are no there are no points here means we're shipping days are less but delivery days are more so this is a good transportation company so because here we don't they are not sleeping very heavily on the estimated arrival time yeah so I have discussed some this filter so he can actually put more advanced filters here on these visualizations so ill do some squeezing here or squeezing me because i want to do this study based on a state so I can go and I can pick destination state put it here and so now that this is a state so auto auto intelligence we brought it as map but I want it as filter so on this filter I can actually enable futures like such box we actually have more values yeah now it doesn't make much meaning here such box and we can have options like select all at a time you can select all states and unselect the ones that you don't want to do study on yeah your data will get changed accordingly okay so it can put a slicer like this this nation state slicer somewhere in between here arrested okay I can do I can notice the beginning which makes more sense there you go around this nation see to filter based on that I can filter this entire analysis okay in this as I mean we'll be targeting power query so we will be getting an introduction towards how to use power query the benefits of it and make an audit dashboard on this transportation corporation data okay so this is assignment that will be covering as part of this session but to start on I will set the pretty cool so here every every load you know that truck carries every load so each transaction here is a trip right so it has a source city destination city and arrival time all that fine so it has also a auditing mechanism like each each trip should cover certain checkpoints while truck is going from destination of source a to destination B it should cover some checkpoints in this course of journey so like four checkpoints five checkpoints so there would be some checkpoints where it has to get filled a fuel fuel and then refreshments driver change so there would be some compile and compliance issue stuff that a truck driver should take care of okay so here the data is recorded at AB transaction level I mean at a trip level so how many checkpoints each of this trip has covered okay so here we can see the data checkpoints okay how many shipments are then covered but how many checkpoints were assigned for each of this trip each of this trip IDs elephants are suppose this trip okay this trip for this customer from this source to this destination so how many checkpoints were assigned okay now it says that we attend checkpoints were covered but the detention friends with it did it cover all the checkpoints assigned to that or not for that we have to get data from online system so now we will connect to our DBMS data source this is a time will now connect to an RDBMS data source so for that I will be getting data from SQL Server hosted on cloud which is a Jew ok so I will pull data from Azure service so because that recording happens online through to their centralized system so when a truck passes through a checkpoint that information will be sent to that entire system so that is being recorded and or your Azure is nothing but SQL Server on cloud ok so I will quickly first log into my I have set of that so this is as good as connecting to a SQL Server database or a Oracle database so I'll do a quick login just to get these are our credentials we're together this is hosted one can easily set up as your bottle access it's no big deal so it's free it'll give some credit for some time okay so you can try out you can set up a database see that is a free credit that I have which is showing here okay so I am utilizing that I'm on free trial on this so there's a date of this that has been set up okay so that does one you saw earlier was a dashboard see this is the server name so all I need is the server name okay then I can access the database so this is a gator base lands transport cop so this is a database name it got that centralized system tracking data of checkpoints okay so I will select as you click on are your SQL database plan it so I have to give server name here the one just I'll copy it okay so this is the server name which is done as your okay so here now when you get data from when you get data from Azure service you have two options either you can directly import the data into your system or you can run it as direct query so these options will be there only this options will be there only for non file state so for files always it is import if you are getting data from an on file then yeah so you have that option of directly linking it so nothing will be stored in the analysis file so it will have only reference points as and then the analysis gets loaded or refreshed so data will be brought from the live system so that is directquery importance data is directly embedded into this analytics file okay so when we are connecting to our DBM is so data points data sources we also have an option to write SQL statements here so this is a panel where we write SQL statements at command timeout there are some of the advanced properties that we got here we so right now I will import the data okay into the system and so I'll click OK so you might be getting doubt I said all files will be directly imported there is no directory option what if the data changes in the file all you have to do is make sure that file is in the right place we're supposed to be and click refresh here the data latest data in the file will get loaded automatically into the Orbea analysis but unless you click refresh latest data will not reflect if it is if it is file ok so this is the data base this is a server this is the database and database I have these two tables checkpoints and chip and points I will pull these two tables into for Bay analysis this is a different way of getting data in the system this is a real-time data once I get that here I'll tell you the model it's loading the data into the analysis okay so here we go so we got so we got checkpoints so checkpoints are you know the actual designated halts suppose a vehicle supposed to stop there so this checkpoints are there some names and then the IDS then the flags like information like they have fuel station that they have refreshments it's an interchange point they have where how's the service center and it's active the checkpoint is active at the moment or not so this is checkpoint information then we have many to many mapping relation table checkpoint trips so the two tables that we got from our geo database so this will map type ID to checkpoint ID this is many to many a trip will have multiple checkpoints multiple trips can pass through the checkpoint okay so this is a relation how it is created is stable so it will do the mapping it will see that type ID and it will check whether if the trip has passed the checkpoint it will make an entry into this table okay so now you go to relations will you understand the relations here well spend some time on understanding these relations if the keys key namings are correct if they have correct key names then the relations are automatically created okay so as you can see here so these two tables categories and shippers that we got from open data we are not going to open data so these two tables we got from Azure so you can see these are the checkpoints and these checkpoints are linked to our actual data with this many-to-many link table okay so what it will do it will shine trip ID to this trip ID and checkpoint idea to checkpoint ID so you open this relation just to check out you see it here checkpoint trip ID is mapped to two by D in trips and here checkpoint ID in checkpoint table his map to checkpoint ID in trips so this is a and you see here this is many to one through this many to one and this is here many to movement so the actual many to many relationship - here so you can see this is one to many and this is also from one to many so the many too many establish a relation is broken down into a link table the help of link table here okay so this is a kind of normalization is called forth normalization okay so that's how I read the data model is created remember this this is a difference table that we created just on the fly to create buckets yeah so it don't mean we don't need to link this table to any existing tables here okay so now we have this so another important thing that I want to mention here is so when you mark our table as you know cross filter direction as both when you when you set this future that cross filter direction can apply to both okay it means that if you filter by checkpoints if you want to see per checkpoint how many trips have been covered yes you can see for checkpoint how many trips were covered you can see at the same way per trip how many checkpoints were covered yes you can off you can see that so this is both way you know filter can work in both ways relations so that's why we actually by default also all the relations gets created in bi-directional so these are bi-directional relations this is different this is nothing to do with this link this one-to-many many-to-many this is the cardinality actually so the thing I am telling you about is bi-directional filtering so that is like per checkpoint I want to see how many how many trips were passed in on that checkpoint in a day or in a year that I can do so from here to here filtering the same way what tip how many checkpoints were covered from here to here so that is bi-directional elation filtering okay so that that is possible with the power bi you can click manage relations and view all the relations at a time whatever the relations are there okay and yeah now it's a time to get into power query so these assignments were in were targeted to cover power query basics okay so power query here can be browsed by clicking on this edit query so if you click on this edit query it will open a panel and this is a panel where we actually do power query stuff okay yeah you see this all the tables will reflect here okay it has bunch of options like transformation so like a column formattings here in this first panel we do lot of column formattings transformations edit column column transformations column level table level transformations column level transformations so you can do dot lot of data modeling stuff this is different from tax okay so this is different from tax so this is power query so in everything you write here is M is also called as M okay we don't actually write anything at least in this assignment so we will actually use all these options that are available here in this transformations panel okay the first thing is if you want to modify any existing data source pointer so we have like data coming from excel sheet maybe the path has changed yes we can change the source again you can select the relevant part or the credentials has changed or the link URL has changed you can for in this analytics we are getting data from three different sources all the sources we can see here we can set permissions we can do things around setting changing the data source properties okay I do a basic transformation so what I will do I will we have some inbuilt intelligence options here so I will extract from shifted from ship date he'll go to transformations and in date transformations I will get all I will take week of the year what is a week of the year so I just click on that so you check out what happens so ship date now is created as week of the year okay so this is now changed ship ship date is not changed into week off year okay so maybe this is not how I want to do the transformation so the good thing here is so every any transformation that you apply on a table any transformation that gets applied on a table can be viewed here all steps will be listed here okay so if you want if you don't want that calculation to take place you can simply uncheck that and check that and take that transformation out okay so we have that options here we have we can set the data type here on the fly we have all data type options we can filter the data that we don't want okay so extract we can do extraction okay now what I will do I will duplicate this column I will do what I will duplicate this column so for that we have an option format where in home give row split yeah so this is copy of ship date and are this copy I will only show beak of here okay so now I got this as a new column that's what I was looking at in the first place yeah you see it gets added as a new column week off the year so we go to we need to go to add column panel we need to go to add column panel not here in the transformations it will do transformation of the existing column yeah that that's not how I wanted to show yeah so that's one way of - indeed we want to we don't want to show the column the current format we want to flip it all together to a new format we can do this now here I add it as a column so I extracted so this is not Dayak so kay remembers is not back so this is like you can see here transformations go home and choose advanced editor so we'll just show you so this is this is the M power query language here so you can see here so this is add column week off here just extracting and adding as a column okay so that is a step again that we see here we will simply name it as shipping week okay giving it and on addition to that she just like number on a new represented on a visualization will not make much sense so what I will do I will do a little transformations for the transformations what I will do I will format add prefix prefix would be beak okay yeah this looks neat now save this so we'll cover more this transformation small for now I just wanted to show the overall view how it looks we have we got dance emissions we can that we can directly apply on the tables you can add columns with functions so all the steps that we do will be listed here at any point in time we don't certain go down one certain steps we can't remove them and once we are done with this power query thing we need to click this close and apply I just are applied so then that will reflect into our actual development environment okay so we have reports tables and relations we just came out of query window now power query window now you go to tables throughout trips on grapes we should find this so if you click on this column you don't actually see any formula here you click on this you will see a formula here because this column is created by Dax so this is a tax formula but if you click on this it's nothing you see here because this is created by power query ok we can do much advanced things with power query we'll discuss that ok let's build a new dashboard call it checkpoint analysis checkpoint analysis so I will now I stop show you that many to many a bi-directional filter how it actually works and all that things so I will first do what I will first get destination city okay then I have destination city I will change it to a side by side stack bar on this I will show first how many checkpoints each trip has crossed so checkpoints here so the aggregated a number of trips were crossing these checkpoints in these cities this ministry these cities now I want to compare I want to do compare it to study like how many they actually intended to be covered so what I will do I will go to that table where we had many to many relationship point IDs so this checkpoint IDs I will pull into this bar diagram count off check part IDs yeah so count off checkpoint ID so this is my target this is my target so so this would be on trip heart rate per city okay per city per trip so we are adding all those checkpoints at a trip level so that's why count here you don't need to add so at a trip level okay because at a trip level this is already added and we are seeing the data so edit trip level we have this information like this tip ID has covered check seven checkpoints but if you actually go to that table then we see that trip ID if you map the trip ID and we will see how many checkpoints were covered so that count is what we are getting here count of checkpoint ID so this is a target so this is I mean you can spray any name to make it more readable target chip finder so these are the targets that need to be covered yeah okay so this is actual checkpoints are covered and count of targeted checkpoints so this is a variation so you can see here mostly most of the states that is meeting okay so but here you see in some states in some states target checkpoints are more than the actual cover and in some states more checkpoints were covered and then they are signed so more delay in the delivery so these are the states you know trucks are heavily getting high we're getting broken or halted or stop that's why they are covering more checkpoints than the intended actual here this is a perfect in this destination city were a all the vehicles going towards this destination city are my are perfectly adhering to the compiled ends of the checkpoints okay so that like that we can do compared to study of this is a kind of auditing dashboard again now we will do a actual view here we will see D transactional you so for that I will pull a table visualization and in the table visualization I will select I will select fields like dict abiding actual Triple D click select on that trip hiding okay then we have customer origin city destination city targeted ship this tube type and some more fields from other tables like shipper category and finally the auditing information so how many checkpoints this trip has covered and how many were intended to be covered sorry not this yeah so this target should be shown as count okay there you go so we have this detailed visualization it has an intelligence to put up total line for numeric columns okay guys a quick info if you want to do an end-to-end certification on power bi in telepods provides a complete trimming on power bi with certification on those details are available in the description now let us continue with the session here it doesn't make much sense so what I will do I can take that out general we can take out totals bar okay now you can see in detail this data you click on particular stay destination city the data related to all the destination city will be visible here so same single click ascending descending sorting you can do sorting also on this data okay so this is a detailed view we can put details you select this with table visualization but the thing is right now at the moment our table visualizations doesn't support row level selection you can select a row in this table visualization that's not possible you can select that only on bar charts or any other visualizations selection within within the table utilization is not possible only for all filters will Bob okay I'll put one more filter so this time I will do aggregated study not like a transactional study so for that I have to go for matrix so get matrix here and tricks and in this matrix water life--all first I will get the tip type but tip type we have domestic intercom and international and players players in this each category in each category we have okay all three players are doing business in every category okay and then we will put categories also yeah so these are the categories within each in each now on this I will put check might adherence like so actual checkpoints values values would be checkpoints and then go to this and get targeted cheap points see so we have total subtotals for each level automatically I have come up here and we had a like this what we can do we can actually make put this trip type in columns and can view data like this also for within domestic how is the variance okay let's before doing that do some formal naming arrangement of columns yeah then get this into column so this is a normal map generally in Excel also be compose right we have some options we can we have some nice formatting options that are available here like you can go and change the style of this matrix for example this I like and we have also like this black letters are just flashy rows yeah this is good okay okay fine we can add one more column on to this we can put by origin state it's okay yeah this is relation is good so now we do what now we could be remember we have extracted weak numbers we'll see this trend by week this compelling compile ins how they are adhering to the components by week boys so instead of destination city I will use it the column that I created the new column where is it shipping week yeah this is requires adherence we will seal it straight this into line chart okay so the adherence is good so we see always the number of checkpoints covered our are never going down below so here in some weeks we see lot of variance for some weeks the difference is more so the component is less okay and some weeks they are coins are they're aligning to each other so where the components is good so like that we can and this see one more thing I want to show in this modeling is we don't in this general modeling you see your weak numbers are not properly sorted yeah so we want to do that sorting so what we can do we can select that column c1 1011 that's not the way because it's a text column right so that's why it is doing on text based sorting so we can do custom sorting select week number varies week number that's whipping week and we also have beak of the ear right do we have it or will we lost it if don't we we will composite we are shipping week okay I have directly appended that yeah I order it repented that so what I'll do I'll create a new column so just to pull weak number so for this I will write I will go to power K I'll not do again right attacks but that's not what I will do I'll go to our query I'll add a new column and from shifted I will extract weak number so I did this already but what I did I I got I got week of the year but then I selected that and I went to transformation and I prefixed some text like in formatting I prefix some text so that's how I got this no I will not do this will keep this column as is this new column and I will use that column for doing custom sorting say I could have created that column using tax also but this assignment is more often power query usage okay so now we have this column now this column should get sorted based on this column how do I do that select the column that we were supposed to sort shipping we'd select that column go to modeling in modeling where already saw how to change data types categorize them do a dollar symbol or percentage these options right formatting and properties we also have sort option here click on this column we can sell it so this is this is a default sorting happening based on that column we can change we can select on what column the shot sorting should happen so see here not week one two three four five six perfectly sorted yeah based on that column this is custom sorting for this you don't need to go to power query here that we have it in data modeling like a modeling along with adding column or measure we can do custom sorting also we have explored all these features in modeling okay we can add new page from here also instead of clicking tab we can add new page here or duplicate existing page instead of clicking on visualization we can click here and add a new visualization and later select what kind of realization it should be yeah we can add text box images like we do in PowerPoint or God we can do things like that you can go and manage relations from here or we can click here and see all the relation that are there on the table and one go here on this manage relationships window okay so now I completed a proper dashboard analysis now in this for this corporation from the data they have given us we have constructed a dashboard overall overview kind of a dashboard which gives you a brief peek peek into the data and then we have built one analysis file for delivery delivery analysis to do the delivery analysis then also a checkpoint analysis now I will save this and we'll publish but remember when you're publishing don't select any data because it will get saved and published in the same manner and always be on pH one while publishing so now click publish so it will ask us you have to sign him yeah I have to do this I mean yeah so I did the authentication I signed into the application so now it is asking whether to replace the data set yes and now it is publishing this to the power bi cloud service so see the desktop client is free you can anyone can download this and start building application and share the file so when I save this it actually gets saved as like this a file now when I publish this this will go and sit on cloud service so for cloud service you need to take subscriptions so those subscriptions are charger are chargeable so $10 per month right now but we can set up free trial we can set up free trial which is for 60 days so I am actually using a free trial now just to get rid of so it's going for second authentication so we just finished authentication here process so now that DX be the local DX P will be published on to this cloud service okay so now we can see all those three panels here all the three dashboards here from here I can specifically pin visualizations on to the dashboard so remember we already created dashboard in our first assignment lamp Scott - both that we already pinned some visualizations there from our first dashboard now repinning from our second and third dashboards okay and this trendline bring in the string line also okay now we go and check out that dashboard you see more visualizations now here and this panel they had a table instance it's still not very user-friendly arranging disguise here okay so now you see here this is a dashboard we got now we can go into full page mode I look at this dashboard so now this page is coming from a delivery analysis now I click this it will take me to the Lurie analysis tab here yeah that now I have seen this this checkpoint analysis okay and now I click on this I will go to shipment analysis panel so these are the dashboards that we prepare and deploy on to cloud service anybody access to this cloud account can analysis files we can share them we can share these dashboards we can share and we can also publish we can embed the copy and we can publish so I will start our next assignment fie so in this assignment we will cover data transformations more data transformations using power query okay and we will be utilizing a new new data set for this assignment which is State U State University Admissions I will give you a little walkthrough of this data I'll give a brief overview of what this data is so this is the number of applications received by various universities located in USA so we data like the institution ie D institution name school name and the university its affiliated to number of applications received I mean the total number of applications received and then split by foreign applications Hispanic Indian applications Asian black specifics of race wise number of applications received in each university in each state okay so this is a main data set our University of beauticians and then we have a data set which will give us the information like how many universities are there in each state state universities and how much fun they're receiving state fund and how much touched the accumulated fund they have with them okay post it and these are the targets so these are the targets number of applications should should should be received by each in each state for various years okay so now we are in 2010 so the data is from 2010 to 2016 okay so this is a data set that we have so this data set now I will take into power bi okay so this will create another instance of power bi desktop so it will be pay for performing all these transformations all these transformations using power query l+ this okay so let's get data from the University Admissions there is a problem here okay so actually what happens when you are trying to access files which are prior to 2007 release or which has Excellus extension yeah so we need to have special drivers for that yeah there I go so now I got to do it access divers so I will install them so actually a power bi also comes in two versions the 64-bit and 32-bit based on the operating system we will install the 32 beta switch to bit so here city sees or various top 64 bit and this is 32-bit okay I am putting the access drivers this will help in reading Excel files prior to 2007 release so I got this drivers from this website so you can download from here access Shrivers this when you click on download it will ask which version you want this is 32-bit if there is no extension mention is 32-bit generally two-dimension as 84 86 not very sure but for for 64-bit it would be clearly written as 64-bit okay so the installation is over let's get back to the file so you can actually clearly see here the problem you know that why it is unable to read the text file now retry I will get the greater let's get all three sheets into power bi desktop mystic Lord no need to set preview now because we know already what data we have meaning okay so what is happening is now the the data from all the sheets is getting embedded into the system and table schema data model everything is getting created detecting relationships yeah so see now we got all three tables here and then if there are any relations that will be created I don't think now we have any relations here okay yeah let's get back to the data so we have you and your state by Stargate state wise universities and then university applications find so let's let's rename them do proper names to the stables state targets so these are University funds University funds and then applications simple name okay so we got applications we can within the what targets state wise and then we got funds universities was I think even this data is at university level a state level state state targets and state funds and state-wise okay so now after loading this data so this is this is the University data right okay after loading the data I realized that there are some more applications in in a different data sheet okay now I want to get that data as well okay like so so here we have university admissions data right so actually if you see there is another file which got charter charter as in these are state universities but ran by private organizations they are under private management but they get funds from the state okay so here so there they are more accountable the accountability will be high in such institutions fine so now I have data here so these are the applications received by charter universities so I want to take this data into power bi so for excel take charge applications so this version is different xlsx Olivos XLS open once data is loaded into power bi you don't need to bother about which version of excel you are getting that data from okay now this is a table we got here chartered applications right yeah so now I don't want to have to data set separately because both are both contain same data same number of columns or not same data same metadata same number of columns column formats everything just that we have different data sets now I want to append this data set to this want to make this as part of this so what I have to do is get into power query query editor redditor so we'll start with a pending data and then we will explore all possible data transformations that we can do on data using this power query editor okay so we have applications there is a table now on this table I want to I want to append charter applications data so now I say I click I select this table okay and go here so here we have options that you want to merge queries or append queries so in our case it will be append queries append works as Union in SQL Union all okay so here we have options append query our append query as new so it will append that new query and create a new table now I don't want to create a new table I want to append to the existing table so this table itself so it's like two tables are more than two it's just two table what is the table that you want to append so this is a current table now we are in for this table will be appending charter applications stuffing is completed close and apply we'll just verify that open this actual admissions file so how many rows we got here we have approximately 4k yeah we have approximately four carry cards you see here you have four a data didn't get reflect okay actually we have to do refresh from the model yeah so the Refresh has finished so now you can see now the total has increased it's not reflecting 5,000 so earlier it was 4k now we have five K records so Phi K application so that additional applications charter applications have been appended to this original set yeah now that we have appended data here can we delete this now don't need this right we can delete this can we do that try it will throw an error saying that this cannot be deleted because it is being referenced in another application in another query applications and so actually that is a virtual join I mean what you'll append in so there is no day actually data integration into this table it still have data on this table but it has created a bridge so we need to have this table but any expression that we write any function that we write on applications will reflect on both the sets retreat as one single set in applications okay the main table so we have the entire set of Records here then this main table applications so now I'll do a so I will do some pitched functions so text functions like so here you see institution name this institution name is a combination of university name and ether state code CA CS California yeah so here we have state code also clubbed with institution name we don't have that column anywhere else in the data set you don't have that state code again anywhere else that is essential for us because you see the other two data sets they are all led by state code so we need to have that state code separated here so how let's do that here so select the column select the column and select split columns split columns by delimiter so how will we split this so what is the delimiter I mean what bah bah we can see that the each word is separated by space but everybody separated by space so I can't split by space I did a lot of words so we have advanced features here advanced settings that we can relay on so first we select the delimiter the separator words operator in our case it is a space yes space is being and split by what at the leftmost delimiter rightmost delimiter each occurring in our case it happens to be the rightmost rightmost delimiter we can write a more you know are delimiters if you don't have any we can type the delimiters if it is not available in this list so okay so click OK now the column gets Column get splitted yeah we have a new column now let's rename these columns leave it to leave this as an institution name and this one state code and we so this is one of the basic text operations that I have done so we can do more text operations like we go and you see the school name the school name solid small letters light let's increase the readability of this column select this there are more transformations and transformations format capitalize each word I like these options capitalize each word see now it will get camel notation so anything you do on transformations you need to worry it will not create any new column or anything it will replace the data in the existing column and you can see for every action that we are doing a new step is getting created at any point in time you can move back and check what the data was at this point in time what was the data at this point yeah he can actually check if you don't want to but Lester you can get it off that here just click here you be cancel it okay we'll come back here we have more options here like column level options we can format text we can set the type we can set the data type you can strong type you can rename fill empty values you have empty empty empty cells you want to fill with the procedure or the successor of filling down or filling up the place we were you will come you will come and we'll explore all these options in detail first we'll go to other data sets so we here I have state targets right yeah let's start with state funds so this table is good but actually if you see the column names are not properly aligned here so I have an option here so you can go and do use first row as header use first row as adder or use headers as first row so we got this option so I will select this use first row as headers so yeah and there I go so I have columns here and names but this is not correct see right see see we die these are states but under states I don't have universities funds and all so this is this this data is in flat mode so I want to I want to transpose this data then it becomes meaningful so I will take this step temporarily out I will not do that step so before doing that I will do a transpose of this data select the table and say transpose click the data now it is more readable and more convenient we can easily do the reporting we can establish a join between our applications and this table using state code and we can trip and we can retrieve state wise number of universities and the funds they got so we so what are the options that you see here in this way then transform or add column you can see most of the options here also use first row as header ok so this is a valid I mean proper data table best suits for reporting let's do similar stuff here in state targets also here we have to be little careful here you see here we have state courts and then state wise target so what I have to do is here two operations so the first thing is I have to do a pivot pivot of this data but before doing that I have to transfer this data because I want to do pivot ear voice they will understand that we will see first we will do the transpose of this data so I have state names state codes and then ES and EFS targets okay very did state coal go okay so state code will disappear when you do transpose on this kind of data so before doing transpose we actually have to take this out use header row as a column as a regular row header row as a data row so that you know States will also be in column and when I do transpose they will so the state codes will be in a row and when I do transpose that row will convert into a column because only rows will convert into columns not headers okay when I do transpose that's when a lady transpose all state codes disappeared I want to have them yeah so I have state code and ear wise targets now is the time to make this as header see why I did transpose I did transpose because I wanted ears here not state codes I wanted years now I'll go and select all the years I will go and select all the years and then do one pivot you can do one pivot of selected columns or you can do pay and pivot of columns that are not selected so I will do I'll click this so these all gates and pivot now it is a tower now it is easy to use we have state code and here wise targets but I don't draw on previous targets because I don't I don't need to show them anymore on my dashboard so I have data only for current here so let's filter this set so when I filter this set the underlying data I will also get filtered so for the reporting purposes only the data which I select here will be available it is completely filtered okay so now I select 2006 now I have data only for 2016 now that I have latest data only for latest here I don't need this column anymore I can remove this column yeah it is possible so now I have state codes and then targets for that state codes because I have my entire data is for current here the two files that I got there for current year so there is no need of maintaining that unnecessary data so I can filter and then can ignore where this tool is very powerful you can do lot many operations like that okay now let's get back to the applications and not here I want to I don't want to how this is a separate table the state state funds yeah let's leave it as a separate table because we have more columns here okay but this has only one one column values so instead of maintaining this a separate table what I will do and we merge this table into applications will merge that into applications so how will I do so you go to Home tab and here we have an option to merge queries much the existing which is applications the master said master sect so to this I will march state targets on what on state code here with the state code here you remember the state could be extracted from the same situation yeah we are using that now or we are joining these two and the join that we select will have left outer so it will ensure that we will not miss any data from the master data set so if there are no targets for any state code then that those for those states will show it will show blank but it will not ignore those states in our main data set which is very safe okay we should because we don't want to lose any data from the applications if that's not the choice you want only those states with away targets then go for inner join only matching rows okay fine so say for left order select that so that is added now there to select the field that you want so what I want in this I want just the value not the entire is the value value is the target I will rename that column should be target okay so we have statewide target appended here we don't see all the data here it's just a little preview but yeah believe me that much is for appropriately calculated applied okay so yeah so now we finish that we have more options like we can choose certain columns that we want and we can ignore columns that we don't want we can elect Lee remove the columns that just we did in state targets keep pros so we want the remove certain rows from top yeah so this is data cleaning this is actual data cleaning the data in some in some terminology is called data massaging so yeah we can sort a column and then group I and then we do all this stuff let's - let's go and so in transformation also we have a lot of transformations which is like replacing values want replace it in certain value we can do that we were uncovered you have split columns text operations then we have numeric operations is there any date then we can extract time day date week all that date intelligence we have that the add column now we'll explore this tab we'll add a conditional column to this application table so in this conditional column so this is a UI for creating a conditional column I will name this as applications status so what I will do I will create three categories like high normal and low high is for more number of applications low and medium okay so before that let's check out let's so in this I want to just check out applications total applications that are received here so this is this field will give us total number of applications okay total number of applications received from all states so this I will split by institution ID and I will see this as a table so institution already leaders is known to correct so this is the institution name yeah institution name and let's check out the maximum number and we number okay put this has a bar all our I can do better than this so what I will do I will select by institution name not the institution name minimum value I'm still inst institution vice in non-value okay let's just take the count of application system in each user institution and put a reference line the difference line for average everything they have an average line here okay let's go for the line and then we put a average line this act by count of applications okay so we see this is the average line let's show data table for data level for that average length so average number of applications received in each university across universities yeah so that somewhere around 16 16.2 to yeah so that's 16 pointer too so that's the average value okay why I wanted that I will shut leave that you okay let's take this off from here get that to the creditor so now here I will create a new column conditioning column so in conditional column status column name would be total number of applications so the order of this conditions you know is very important because that's that's probably if this gets evaluated this expression gets evaluated okay so if it is less than or equal to we have totaled all racers less than if it is less than say we just checked it's 20 right is average so I will go and I'll put it as if it is less than 20 slightly about an average yeah we will it shouldn't be like average if it is less than 15 low output should be low I can actually type a text or I can select a column if I want to show any column yeah instead of but I can show that or I can simply type a value that's true total applications if it is less than 15 so if so how this gets evaluated so we don't need to check between a range like 15 to 30 like that so if a value is whenever when this collision happens at a row level if value is less than 15 then it will show as low if not then it will pass to the next statement so in this next statement I will check if it is less than 15 if it is less than 50 yeah so this is normal number of applications received normal I had another rule if it is let's if it is not 50 if it is greater than or equal to 50 after reaching here if it is not below 15 it is not below 50 or I can say I can say now I can put any upper limit here I do not want to write again less then I will just say if it is 50 or more than 50 high so yeah that's why this order of evaluation is very important you can actually move this order up or down what you can take out the condition yeah options are there this is a default option if not nothing is passed so you want to have one default values yeah we can put that here okay so you see that new column has been added here okay so you just it you can actually check a syntax for this anything we do in power query what happens actually a power M script gets generated so these are this script so we are using that additional column right this can also be added like this using the function which if each if is the functions each if then else if so it has similar syntax to other programming languages then last is else null if nothing is specified in otherwise condition rates that's when L so this is the aims in that so we just added so the year it will show the last operation we just added the conditional column here okay we have other columns like most of the times we may want to have one unique identifier for each row if there are any duplicates you want to generate a unique identifier that is nothing but an index column index you can start it from 0 lit we can start it from 0 or 1 or any value that we want and a unique key will be generated for each row sometimes it will be very handy yeah and if you want to write a more calm composite column custom column then we can go and write it in M using M we can create more complex column like a date functions we want to extract days and then add it to another field compose a new date out of it any any expression we can write here and compose custom columns so these are the operation these are the transformations that which they did let's check out ready yeah so yeah most of the transformations we covered ate summary tables yeah let's do the summary tables so now I have this data so I want to create a summary table like I want to store applications received actual application received by institution name yeah by institution name the number of applications received so I have I can do that group by so if I do group by this existing table will get modified yeah this existing table gets modified but that's not what I want to do so what I have to do see let's first do that here once like a group I so I can do group by by institution I don't know why I want to do by institution mean institution name what I want to do I want to do count of rows yeah this is what exactly I want to see how many applications received for again situation ok right to select any column here because we are counting row so what happens this existing table gets transformed transformed and a new aggregation table will be placed say this entire thing has been replaced these are the universities and those are the number of applications received so there is a new table that got created but actually this is the existing table got transformed like this maybe this is not how I want to do so what I will do I will create it create a duplicate copy of this table applications right click there are some options here as well I click on the table you get some options say duplicate so this application table gets duplicated now this give a name applications aggregation still loading here it is I got it so on this table I will do aggregation so before doing that I don't want to have all this operations so like adding that conditional column capitalizing the renaming columns yes chain split column says leave it till there actually I don't need split columns also split the delivery append query yes keep data up to here on top of this now go & compose aggregation yeah so this is still referring to this data set now on this I will do group by group the institution ID no not institution institution me okay so this is an aggregation table which is part of our data model now we can make aggregation tables like this so we have covered most of the transformations that are available in query editor so I got back to power bi desktop I exited from power query ok guys a quick info if you want to do an end to end certification on power bi in telepods provides a complete training on power bi with certification and those details are available in the description ok guys we've come to the end of this session I hope this session on power bi was helpful and informative for you if you have any queries regarding this session please leave a comment below and we allowed to help you out thank you

Channel: Intellipaat

Views: 610,590

Keywords: power bi training, power bi certification, power bi tutorial, power bi tutorial for beginners, power bi, power bi full course, power bi course, power bi for beginners, introduction to power bi, what is power bi, power bi desktop, power bi dashboard, power bi dax, power bi intellipaat, power bi charts, intellipaat, power bi reports, power bi dashboard examples, intellipaat power bi

Id: _zioodkDZ4U

Length: 237min 1sec (14221 seconds)

Published: Fri Jan 17 2020