Learn Microsoft Power BI in One Video | Business Intelligence for Beginners | Practical Course

So, from this video, we are going to start a new tool that is Power BI. When you heard of the word Power BI, the first question that comes to your mind is, what is Power BI? So, a simple answer is: it is a business intelligence tool. Now, if you want to explore that, what is a business intelligence tool, then what we can do is simply go to Google and try to Google Power BI. So, when I Googled Power BI, the results that I got are over here. Power BI is a business analytics service that is provided by Microsoft. It aims to provide interactive visualizations and business intelligence capabilities with an interface that is simple enough for end users to create their own reports and dashboards. In simple terms, we can understand that Power BI is actually a tool that is given given to us by Microsoft itself.

Now, Power BI helps us to get the data from various sources, the raw data, and then it gives us an interface through which we can visualize that data in the form of reports and dashboards. So, why do we need that? We need to actually visualize the data because we all know that nowadays, most of the companies focus on the data generated by the customers, and they want to analyze that data to know everything about the customers or their clients. So, whenever we try to visualize that data in the form of reports or dashboards, then it becomes easier for anyone to take a look at that data and analyze that. So, Power BI is just the tool for that purpose; that it takes up the raw data, it cleans it, visualizes it, and then gives us a full-fledged report that shows what is happening in that data.

Now, if we talk about the components of Power BI, then there are four basic components, and these components only help us to perform the necessary actions on the data. These four components are the Power Query. So, Power Query is the first component; it is used to clean the data. So, whenever we are talking about data from a raw data source, which means it could be in the form of a table that is a structured data or a semi-structured data, or could be even unstructured data. So, whenever we are talking about raw data, it is very much possible that the data that we have can contain some blank records, can contain some null values, or duplicate records. So, we do not want our analysis to go wrong because of all these things; that is why the first step in Power BI is the use of Power Query, which helps us to clean the data so that the data is actually in the form that could be analyzed effectively without affecting the results. First of all, we need to clean the data; we need to remove the blank values, null values, and the duplicate values.

Then, the next step or the next component of Power BI is Power Pivot. Power Pivot helps us to model the data. Suppose we are getting data from various sources, from multiple sources, then that data needs to be modeled perfectly. We need to find out the relationship between these different data sources so that they can be connected together with the help of that relation, and then they could be joined together to give a common report. This is the work of the second component of Power BI, which is known as Power Pivot. After modeling the data and finding out the relation between the data, the next step is to visualize that data; that is, how can we actually represent that data in a visual format that could be understood by all the other people? So, for this purpose, the third component of Power BI, that is the Power View, is used. It is used to visualize the data, maybe in the form of a chart, in the form of a graph, in the form of a dashboard, or in the form of a report. Another advantage that we get here with Power BI is that we get multiple forms through which we can actually visualize our data. There are around 280 types of charts available to us in Power BI, which could be simple bar chart, pie chart, donut chart, to many more complex types of charts as well. Also, with the help of dashboard templates, we can actually represent the data in a dashboard format as well.

Now, after data is visualized or a report is generated based upon the data, then the next important component is to share that data. Once you have visualized your data, you want to share your data with many other people. So, this is possible with the fourth component of Power BI, and this component is known as the Power BI service. So, Power BI service enables us to share data with multiple people; it doesn't matter if the person is sitting over here or somewhere on the other part of the globe; you can easily share your data with the help of this tool.

Now, if we talk about the features of Power BI, that why do we need to study Power BI and not any other software, so the first feature that comes to our mind is its search volume. Search volume is basically the data we have taken through Google. So, Google is one of the leading search engines in the world, and that's why we have taken the Google data. Now, if we just try to show that data, then here is actually a report from Google Trends itself, in which we have compared the data for the past 5 years of the whole world, in which we have searched the two terms: that is Power BI and its competing software, that is the Tableau Software. So, if you just look at this graph, you will find out that this line, red line, is for Tableau, and this blue line is for Power BI. If we talk about Tableau, then in the earlier, in the beginning, that is in the year 2015, it was increasing, but if we talk about the current trends, which is around over here, that is August 16 to 22 in 2020, the searching popularity of Tableau has decreased over time, while if we talk about Power BI, then you can see that this blue line is increasing rapidly, and over here you can see that there is a considerable difference between the popularities of Tableau and Power BI softwares. So, this is one of the most important reasons that is why Power BI is preferred because the people all over the world are talking more and more about Power BI than Tableau.

If we talk about some other features of Power BI, then it provides us maximum features, such as it gives us a large variety of templates for charts; around 280 charts are supported by it, and there are many other features that you will obviously learn later in the tutorial that helps us to make our task very easy. If we talk about the cost of Power BI, then you will find that Power BI is very cheap as compared to other softwares. Some of the features of Power BI are available at free of cost, so you do not need to worry much about the cost in case of Power BI. If we talk about data connection, then Power BI enables that from around 100 sources we can just take up the data. We can take data from all the Microsoft Office tools like Microsoft Excel; we can take data from PDF, from CSV format, from the web directly; we can take from Microsoft Access database, and there are many more databases; you can just name it, and you will be able to import your data source from that particular software into Power BI. This is the power that helps us to actually take up the data from various sources in their own format and use it or visualize it using Power BI.

Then, the next feature is trust. Power BI is a product of Microsoft itself, which gives us a sense of trust because Microsoft itself is a leading company, and it gives us a sense of trust that we are actually using the right software and our data is secure because data is the currency of the 21st century. So, it is very important that you must trust the company or the product in which you are actually using your data, and Power BI gives us just that.

So, how Power BI works? Power BI actually works in two parts: first is the Power BI Desktop, and the second is the Power BI service. If we talk about Power BI Desktop, so Power BI Desktop is a software that you need to install in your computer, and it would carry out all of your tasks; that is, whatever the data cleaning, data visualization tasks are there, it would be done with the help of Power BI Desktop. If you want to generate reports, if you want to create a dashboard, then Power BI Desktop is your weapon. But what happens when you want to share your data with many other people all around the globe? Then the second component, that is Power BI service, is used for this purpose. Power BI Desktop is used to generate reports, but to share those reports with others, Power BI Service is used. If we talk about Power BI Desktop, then it is completely free; it can be downloaded very easily from the official website of Power BI. Uh, how can you download this Power BI Desktop? It is what I'm going to show you now. Power BI has a very simple interface, so uh, if you have worked on any of the previous Microsoft tools like Microsoft Excel, then you won't be facing any problems because its interface is very simple and very similar to the Microsoft Excel interface. But still, we are going to familiarize ourselves with it.

So, welcome back, and let's start that how can we install Power BI. So, this is the URL that you need to follow to make sure that you need to install Power BI correctly on your desktop. The software that we are going to install is actually Power BI Desktop. The reason why we are installing Power BI Desktop is because Power BI Desktop enables us to actually perform all the operations on the data, like cleaning, visualizing, and then generating reports based upon that data. So, once you are on this URL, you will find that this is the download free option. If you just click on it, then you can download it, or this is the second option; this also you can choose. But if you click on the download free option, then it would open Microsoft Store. So, I have already opened Microsoft Store over here, and um, Power BI is actually a Microsoft application; that is why it is available in Microsoft Store itself, and here you can see that where launch is written in my PC, you will see the install option. So, you need to click on that install option, and it would take some time to install depending upon your internet speed, and then it will actually um configure itself according to your system specifications, and then you would be able to launch Power BI. So, since in my computer Power BI is already installed, that is why I'm getting this launch option. So, either you can follow these steps, or what you can do is you can go to this second option, see download or language options. Once you click on it, you will get this tab in front of you. So, here what you have an option is you can select different languages and you want to uh change the contents of Power BI. So, these are all these languages that are supported by Power BI, which is an added advantage; that is, you can use it in your own native language instead of English. But whichever language you want, you can just select that language from here, and then you can just click on download, and Power BI will be downloaded in that particular language. One added advantage of using Power BI software is that it updates itself every month. So, every month there are some new updates that are available to you with Power BI, and with those updates you can easily just make yourself up to date with whatever is going on in the industry.

Now, since Power BI is already installed on my PC, so I'm going to just launch it. So, when you just open Power BI, you are going to see this kind of a screen. If you want to sign up, you can just simply sign up over here. I'm going to skip the sign-up process right now, and I'm going to come back directly to my Power BI software. This is actually the very simple interface of Power BI. So, we need to first familiarize ourselves with this interface. If you see that uh this interface is having these different kinds of tabs and these ribbons, which is similar to the Microsoft Excel or any of the Microsoft tools that we have um seen coming with the Microsoft Office package, then there is this file option, which is also very similar to all the Microsoft Office tools. So, uh, this is similar. Now, the new things that we have over here is actually this filter pane, visualization pane, and the field pane. If we talk about these panes, then right now the visualization pane is something that is to be addressed primarily, and this visualization pane has these different types of charts. So, this is actually an um small sample of the charts that are available for use in Power BI, and if you just click on these three dots, you will get this get more visuals option, so which means you can obviously get more and more visuals in Power BI, but for that you need to sign in. So, I'm just going to skip for the Beginner's Cod course that we are going to discuss; these types of visuals are going to be sufficient. But if you want to add more visuals, I will tell you later on that how can you add these more visuals. Then, after the visuals have been added, you can obviously apply some filters; you can search for fields. Apart from that, if you go to the Home tab in Power BI, you have this data group. In this data group, the first option is the get data option. So, here you will get these common data sources. So, these are actually the data sources from where you can import your data in Power BI. So, uh, actually there are 100s of data sources. So, if you just click on more, then you will uh get a dialogue box in which all the data sources are listed. So, these data sources would help you to get the data. Suppose you want to get data from Microsoft Excel, which is actually a version or uh product of Microsoft itself; you can get data from Excel. If you want from a CSV file, you can get from that; XML, JSON, from folder, PDF, SharePoint, SQL Server, Access, Oracle, IBM, MySQL, and so many other things. Here you can see like Impala, Snowflake, Azure; everything is there. Obviously, you can get it from Power BI datasets as well, and you can just see that how many data sources are available to you. Here you can see Spark, web, OData feed, SharePoint, R script, Python script, ODBC, O database, and like this you can see there are these multiple options available from wherever you want; you can get data for yourself. Okay, so this is this uh added advantage, and there are these options available; you can check these options as well. From a file, if you want; if you want from a database; you want from a Power Platform, that is actually the Power BI platform itself; from Azure, you can get the data from the online services as well, from the other sources as well, um, like web, SharePoint, Spark, Hadoop, etc. Apart from this, if you want to extract the data from commonly used data sources like Excel, Power BI dataset, SQL Server, etc., you can just get it. Then there is this recent sources tab; this is available in my PC because I've been installing or importing some of the data. So, this is a sample data. So, if I click on that, uh, this would be connecting itself to that sample data source which I have already imported previously in Power BI. So, that is another advantage that Power BI keeps the memory of what you have actually um taken or imported in the previous versions, and that would help you. Suppose uh these are all these tables, and I want to just import the sales order table. So, once you click on any of the tables, this is the preview of the table that you will get; this is the whole data source that you would be getting. So, if you want some other table, like say instructions table, so instructions table is there, then there is this table one; you can get that as well. So, whichever table you want, its um preview would be available. I want the sample uh sales orders table to be imported. Then what you can do is uh first of all you need to know one thing that whether your data is clean or not. Right now my data is clean, so this table is completely clean; it is not containing any of the null records, any of the blank values, or duplicate values. I have purposely imported this clean data, so because in the upcoming lessons we are going to work with the clean data, and later on we are going to see that how can we actually clean the data if it is an unclean data. So, since your data is clean, you can just directly click on this load option; otherwise, if you want to clean your data first, then there is this transform data option which you can use. So, I'm going to click on this load option because my data is already clean, and I know that. So, what would it do? Once you click on load, is it would take some time to load that data into your Power BI. So, you can see that some of the changes. I'm going to just um stop right there because I don't want the changes to be applied; I don't want any changes in my database. So, it may take some time; like here you can see it is importing the data. Okay, so now our data is imported, and once your data is imported, you can see all all of these fields are available which were the fields of the sales order table. So, let's just create a very simple chart to say that how it works. So, suppose I want to create a stacked column chart; let's just bring it here. You need to drag that particular visual that you want to use, or you can just double click on it, and that visual would be available for you. Okay. Now, the thing is you want to add some fields. So, here as soon as you add a visual, this thing changes; you have an axis, legend values, and tooltips. So, in the axis, I want to drag this items field. So, from the fields, you can simply drag the items into the access fields, and this total I want to drag in the value field. So, what would happen is it would take some time, and you will get all the items sold; like binders, you have pen sets, pencils, pens, and desks sold by the number of items. Right now it is very small, but if you want to just increase its size, then you can do that as well. So, it is all a part of data manipulation or visualization manipulation, which we are going to see in this video. We are going to see that how can we import the data from an Excel data source in Power BI, then using that data we are going to create a clustered column chart, then we are going to see that how can we just change the way this chart looks through Power BI, and then we are going to see that how can we create the different types of charts in Power BI. So, all this is going to be covered in this video, so make sure that you watch it till the end.

So, let's start with the video, and the first thing we are going to do is actually import our data from an Excel data source. In the previous video, we also used an Excel data source, but what we did was we used this recent sources option; because we had already used some of this data source previously, that's why it was available in the recent tab, and we used it. But since you all are beginners, so it is very much possible that you are using this Power BI for the first time, so your recent tab would be empty. In that case, how can you just import the data into your Power BI is what we are going to see. Since we have already made up our mind that from where we want to import our data, and that is an Excel sheet, so first of all you need to make sure a clear image in your mind that from where you are going to import your data. Okay, so once you've got that, you can check for that option present over here. Since Excel is present, I can just click on Excel over here. But if you do not find your desired data source over here, you can just go to this get data, click on this down arrow, and you will get a bunch of more options that are used widely. If you're not uh satisfied with them, then you can just go to this more options, and there would be a ton of other options available to you. Okay, so I'm simply going to click on Excel. Then what happens is I have got this uh sample data, and then uh when you click on Excel or any of the data source, it would open up a folder from where you can just go to your desired place. This is exactly the place where I wanted to end up; so this is my sample data, and I'm just going to select it and click.

On open now, it may take some time, uh, depending upon the size of your data to get it loaded into Power BI. Now, once it's loaded, you can just go to sales orders. And this is a table actually that is present in my Excel data sheet, so I have just loaded here.

One interesting thing to note is the data that I have loaded is a super clean data; there are no empty records, there are no null values, and there are no duplicate records. Purposely, I've loaded up, up because we do not want our data to get cleaned right now because it is a somewhat tiring process. So you need to be well-versed with some of the concepts before going into that part. So make sure you are getting a clean data, and a good method for that is you can simply go to the internet and download any of the sample data sources. Okay, I have myself downloaded it from the internet, so you can just click on this table, uh, which you want to load, and you can just click on this load button because our data is clean, so you can click on load. Then again, it may take some time because it is loading that data source into the uh Power BI, so it may take some time, and you must see like these kind of things, um, coming up. And the larger your data source, the more time it's going to take. Okay, so now you can just close this warning message; we will understand it later on.

Once your data is loaded, in the fields column, you can see all these fields present. Okay, so now what we are going to do is we are going to create a clustered column chart. So in the visualization pane, you can see the second chart, uh, actually we're going to create a stacked column chart instead of a clustered one. You can create basically any chart, so we are going with a stacked column chart. Okay, so whichever visualizations you want to create, you can simply click on it, and what happens is there is a sample of this visualization available in your Power BI. Okay, now you can just, uh, change its size, but it's going to be of no use because there are no fields in it, so we got to, uh, just get some fields into this axis legion and the values. So the axis, I'm going, going to get region in the legion, I'm going to get items, and the total I'm going to get in the values. Let's see what happens. Perfect. So what we are getting over here is a stagged column chart, which is stagged on the basis of the regions with the different items and their values have been shown over here, right? So this is actually not visible correctly, so what you got to do, if you want to show it in the full screen, there is something given as a focus mode over here. If you just hover to the top right of the chart, just beside the filter option, there is this Focus mode. If you just click on this Focus mode, then what happens is this chart would be fitted on the screen perfectly. And what else you can do is you just collapse this visualizations and the fields pane, and this chart would be then much more bigger and much more easier to work upon.

If you can just look at this chart, what do you, uh, make out is there are these three regions: the central, east, and the west region, and there are these numbers given that is how many, um, quantities of the items were sold. And these different items are actually, um, denoted by these different colors: like binders with blue, desk with dark blue, pencils with pink, penet with purple, and pin with a shade of orange. So if you just hover over any of these colors, what you get is some, uh, information like it is of a central region, the item name is pencil, and the total quantity as well. So these two things, pencil and Central, we can just make out from the fact that it is a part of the Central and the pencil; the color is pink, but the amount that is 154.32 is what we are going to see with the help of this information. Okay, so this is actually the default settings, but what if you want to change the appearance? How can you do, uh? So this is this visualization pane present from where you can just change the appearance or the way your chart looks. Okay, so how can you do that? First of all, uh, you need to come out of the focus mode. So once you are in your focus mode, how can you come out of it? There is this back to report option; you can just click on it, and it would be back into the normal thing that it was previously, and you can again see this Focus mode option once again. So if you again want to go to the focus mode, you can simply click on this option, and you would be taken to the focus mode once again.

Now what we are going to do is change the way this chart looks. So for that, in the visualization pane, there is the second option available, which is kind of a paintbrush option; this is actually a format option. So if you go to it, you will see these different options, and using these different options, you can actually customize the way the chart is looking. Suppose if you want to go to the data tables, and you can just click on on, then what happens is, let's again go to the focus mode to have a better understanding; you are getting all these data labels like 1.5k, 2.4k, .5k, 9k, 5.8k, which is actually, uh, the round off value of the, um, thing that it is representing; like in the central region, 5,762 binders were sold, so what we are getting is 5.8k as a rounded off figure, which helps us to understand it in a better way. Okay, then what happens is, uh, suppose you go to this x-axis, if you just expand this x-axis, then you can just change the color; right now it's gray, you can just change it to black, and then also you can increase the text size. You see, as I'm increasing this text size, I want 13, uh, actually 16. Okay, 16 is good; this is east, west, and central have increased in the size, but that's too much, so I'm going to just keep it at around 13; that's perfect. You can change the font family, uh, you can just change the minimum category width, maximum size, padding, everything you can change whatever you want. Okay, uh, and that's the title, so you can just change the size of the title as well; that would change the size of this region thing, so I'm going with 15 points or 16 points; that's perfectly fine. This was for the x-axis; if you want for the y-axis, you can do the same as well, like you can just change its position; right now it's shown on the left; if you want to show it on the right, now it is present on the right, but that's kind of odd, so let's go back to this left. You can always, always change all these things like the size; I'm going to just increase it to around a 11, all right, and then this title also I'm going to just increase its size to around a 15 or a 16 for a better understanding; 16 is fine. Then the third option that we have is for the legion, uh, where you want to show your legion; right now their position is at top, but if you want to change it to anything like bottom, you can change it to left, you can change it to anything like you want; do it on the right; yeah, that looks good for me. And then, uh, what is the name, name of the legion or the title that you want to give? Item is perfectly fine; you can just change its color to say black, uh, you can just increase or decrease its size; I'm going to just increase it a little bit to around 13 or 14, yeah, and, um, uh, in the general also there is some kind of options available, all this, uh, especially this alternative text, so it is, uh, actually kind of a description that would be visible along with this chart, so I'm just not going to add it, but if you want, you can just add your alternative text, and, um, then there is this background option; if you want, you can just change the background color of your chart as well. Suppose I want to go with this shade of yellow, then what happens is this chart's background color is changed to a shade of yellow; you can just increase or decrease the transparency like this. So basically, whatever formatting you want to apply, you can just apply using the visualization pane; this is this format tab which you can use for this purpose. Okay, so using these options, these are actually pretty self-explanatory options; you would want to explore them, uh, much more on your own to see that how you can change the look of your charts.

In this video, we are going to see that how can we create different types of charts in Power BI. Now the process of chart creation in Power BI is very simple, and it is the same steps that you need to follow for each and every chart to get created; there is nothing extra effort that you want to pick it up into those charts; it is a very simple process to create literally any kind of chart that you want in Power BI with the minimum efforts. So this was the chart that we created in the previous video; in this video, we are going to create different types of charts. The first type we are going to create is known as the donut chart. So first of all, let's click on this back to report option; what would it do is it would just adjust the position of this chart into the screen, and then you can see that there are these different pages available; so page one is where we have this chart right now; page two is an extra page which is currently blank. So if you want, you can just add any number of pages that you want in Power BI; this is similar to adding like sheets in Microsoft Excel. So if you just click on this plus button, you can add any number of pages that you want. Okay, so in page two, what I'm going to do is create a new chart. So we know to create a new chart, we need to go to this visualization pane, and first of all, we need to make sure that what type of chart we want to create; so we want to create a donut chart, so for that you can just go to this donut chart and you can click on it. So this donut chart is, is not, uh, now available for creation; then you can just expand this Fields option; we are going to drag this item thing into Legend, and the total we are going to drag into the values. Now what would happen is a sample donut chart is created like this. Okay, and, uh, if you want, you can just expand it like this for a better view, or simply what you can do is just make it of its original size and go to this Focus mode; this Focus mode will automatically just, um, expand it into the size or into the position which is fitting into the screen. Now here, from, uh, the donut chart, what we can do is we can format it like we did in the previous chart that was a stacked column chart. If you want to format anything for the donut chart, what you can do is go to this format options, and what options all you can format is, um, suppose you want, uh, actually this is showing the value and the percentage of the item, but it is not showing the name; like we know that blue color is noted by binders, so this blue thing is of binders only, but we are not sure about that; we want it to be shown over here. So what we can do is we can go to this detail label, we can just expand it, and in the label style we can, uh, just choose all detail labels. So what would happen is we would get the name of the item, its value, and its percentage. If you want, you can just change its color; suppose I want this shade of black for a better view; you can change its text size like this; I want to increase it; that's perfectly fine. Okay, then we have data colors; so these are these different colors for like binder, penet, pencil, desk, and pen; if you want to choose any other color, you can simply just toggle it; suppose I want this, um, shade of purple for the binder; it's perfectly fine; I can just go with it; for the pen, that's a kind of odd color, so I want the shade of pink for the pins, so that these colors are of the same family, that is pinkish, purplish, bluish family; for the desk also I want to change this color to this kind of an aqua blue, so that's what I'm going with. So basically, you can just change the colors as per your choice. Then, uh, we have this shapes option; in shapes, what you can do is you can just control this inner radius of your donut chart; right now it's this much, but if you want to just decrease it, you can decrease it using the slider, or if you want to increase it, you can increase it like this. Okay, so this is kind of a pie chart if the inner radius is zero, but I want a slight inner radius like around this much; it's looking good to me, so it's basically the formatting options or the visualizations that you can do as per your choice or as per the requirement. The next thing that we have is a title; that's total by item; this is the title given to us, but if you want to change its title, you can simply type anything that you want, like, um, uh, I want Total chart as my title, so you can see total chart is now, or total chart, that's an apostrophe s over here, for the total chart over here. Okay, so you can just change the title; you can change the way the title is looking; it's alignment; I want it to be on the center aligned; I can just change its size as well, so you can see, uh, it's applying these changes, so it may take some time for these changes to be incorporated, and once it's incorporated you would be able to see them. The background, we have already seen that you can just change the color of the background to anything that you like; I want the shade of blue for the background, so here it is blue, but that's kind of odd, so let's just go with the shade of pink; that's looking good. And, um, then we have like borders; if you want borders, you can just click on on, and you can see that the borders are applied over here; you can change the way their colors, you can change the radius of the border; just increase it; you can see the rounded corners present over here; then you can apply shadows as well, so that's the color of the shadow, position outside, preset, uh, like on the top like this. Okay, so that's how you can add the shadows, but that's not looking good, so you can just click on off. Then, um, again if you go to this detail labels option, there is one more thing that you need to know is, uh, where you want this label position; right now it's on the outside, but if you want to change it to say inside, you can go with that as well, or whatever you like; outside is a little better, so I'm going with outside. Okay, now this thing over here is known as a legend, so if you want to just change the way of this legend, then you need to search for an option called legend, like here, and what you can do is change its position; suppose you want it on the right, on the above; you can toggle the title as well, so I'm just going to click on off; you can just change its color, say suppose black or a darker shade, and you can just change the size of the text as well. So that's how you can just customize the donut chart, and there is one more thing; if you just, uh, hover over any of these items, it would get this kind of a tooltip. So if you're happy with it, then it's okay, but if you want to turn this tooltip off, then what you can do is just go to this tooltip option, and you can just click on off, so none of the tooltips are visible right now. Okay, so that's how you can customize your donut chart. Let's go to any other chart; um, let's go to page three, and we are going to add another chart; this is a funnel chart; you can just click over it, and you can see this funnel chart is being added over here. So again, you can add like items and total in the group and the values option; so this is, uh, what a funnel chart is like; you can see the items like binder, how many quantities were sold, penet, pencil, pen, desk, how many quantities were sold, and you can just customize its way through the format options; customize how it is looking. Okay, then, uh, you can just go with any other chart, literally any chart that is available; like suppose there is this line chart; if you want to incorporate a line chart, that you can do as well. So I want to drag in the region; okay, actually I want to drag in the region over here in the axis and the total in the values; so I want the region-wise total; so you can see in the central region we have the highest total, in the east region, and, uh, the west region we have the minimum total of the items being sold; so this is a line chart; again you can customize the way it looks in the format tab. So that is actually how you can just, uh, change the look of the different charts and how can you actually use the different charts in Power BI; any chart that you want, you can simply just click on it, and you can just drag these items over here, and you would be able to work upon them as per your choice. So that was all about the charts.

One interesting thing to note is, suppose we have this stacked column chart; we come back to the stacked column chart, and there is this option of filtering that we need to look at. Okay, so what happens in filtering is that, so suppose you are having this kind of chart, and you want to compare two data; like we have 1.5k and 1.7k; so these are kind of in the same league, or 1.4k and 1.3k; these all four are in the same league, and we want to compare these four values, then what we can do is we can simply click on it, then press control and click on the second region, control and click on this third value, and control and click on this fourth value. Now once you have done that, you can just right-click and go to this include option. Now what would happen is we would be getting only these four values for comparison; now our chart is actually composed of only these four values, so that you can just compare them very easily. Now if you want to go back, you can simply go to this undo, uh, button, and you would be able to go back. So this was the include option; there is the complement of this include option that is known as the exclude option. So what would it do is, suppose you want to just click on these some values, like, uh, 2.4k and 5.8k; you can just control-click on these values, and you can just right-click and go to exclude; then what happens is these two values are excluded from the chart, and you are able to see all the values except those two in your chart. Okay, again you want to go back to the normal chart; you can click on undo, and you would be back to your normal chart. So that's how the things work.

In this video, we would see that how can you actually separate the data based upon a particular value and then export it as a CSV file from Power BI. So welcome back to the tutorial, and let's start with the video. In the previous videos, we have learned how to create the stacked column charts, and then we have seen that how can we import the data from Excel and represent it in the form of a chart. So in this video, we would see that once you have created a chart based upon any of the data sets, then how can you actually separate the values of a particular thing that you want to show into the tables, and then you can just export that part of data in the form of a CSV file; that is like working upon a report in Power BI. So I know, know that may sound a bit confusing when I'm telling...

It, but let us understand it with the help of an example. So I will go to my recent sources, and in the recent sources, I have loaded the sample data. So when I click on it, what would happen is a connection would be established between Power BI and that sample data, because I have already loaded it. So it was present in my recent data sources. Now I can just select any of the tables that I want. So this is my Table one, which I want to load. So I can just, uh, click on Table one. I can just check this box and click on load. This would load Table one into my Power BI, and I can start working on it as soon as I want. So it may take some time to load the table into Power BI, and once the fields column or the fields pane is filled with some of the data, you would be sure that this Table one has been loaded. Now you can see that our Table one is loaded over here. The, the first thing that we are going to do right now is actually create a stacked column chart.

Now, to create a chart, we know that we need to go to this visualizations pane and click on stacked column chart. Now we need to provide it with some of the fields. So what fields we are going to provide is, uh, we want the axes to be filled with region. We want the items to be the legions, and then we want the total to be the values. Now when we drag them, uh, we would get some this kind of a chart. Okay. Now let's go to the Format tab and, uh, make sure that we get proper data labels as well. So you can click on on, and these data labels would be visible to you. If you want to get a better view at the chart, you can just click on this Focus mode and just collapse all these panes, and this chart would be visible like this.

Now, if you just take a look at these values, we can see that in the central region, 1.5k pencils were sold, worth of 1.5k pencils were sold. 2.4k worth of the pen sets were sold in the central region, and so on. So this is the individual data that has been given to us. What if we want to take a look at all the records that are present in the original data set that correspond to this particular value? That is, if we want to show all the records in the table that are, uh, of the pencils being sold in the central region. So this is the portion that has been shaded by this pink region over here, and we want to show the corresponding tables or the corresponding records that lie behind it. So how can you go with it? A simple approach in Power BI is very simple. What you need to do is just right-click over here, and then you will get these different options. So this is this first option, show data point as a table. So this value is actually considered as a data point, or it is called a data point in Power BI, and if you want to show the records behind it, what you got to do is simply click on this first option, show data point as a table. When you click on it, what would happen is you would get all, uh, the records in the form of a report. If you just cross-check it, so if we take a look at the region, these are all of central region, and for the item, these are all of the pencil items. Means we have got all the records over here that are of the pencils being sold in the central region. This is exactly the thing that you would do with the help of the filters in Microsoft Excel, but in Power BI, you have got a much simpler approach. All you got to do is click on that particular data point, and you can just go and see that in the form of a table. The reason why Power BI is more preferred over Microsoft Excel is because of its simplicity, and this thing is a live example of the same. You can click back on report, and you would be back to your chart here. Whatever options you want, you can simply just click on it, right-click, show data point as a table, and you would be able to get their options. Okay. So this is pretty simple.

Now, what happens if you want more than one record at once? Suppose I want these four records. So I have control-clicked them. Then you can just right-click on any one of the records and just go to show data point as a table, and then what you would get is, despite the fact that you have been clicked on the four records, you would only get the records of the, uh, thing that you had right-clicked on and not any other thing. So it is pretty, uh, it is not possible for you to get like the records of all the, uh, of the different tables at once, because it is used for the purpose of report generation, and report generation is generally done for a particular item. Suppose you want, uh, a different approach, like I want, say I want to just get rid of this region from this, uh, or I want to just get rid of the items in this Legion Field. What would happen is I would get the total value, that is total worth of the sales made in the particular region, that is in the central region, in the east region, and in the west region. Okay. Now if I just right-click on any one of them and go to show data point as a table, then I would be able to see all the records of the central region. So that's how you can filter the records in the chart, uh, itself, that is you can just filter the data in the chart, and you would be able to see its report correspondingly.

Apart from this, if you want like instead of region, you want it by the item. So we, we want for the items, that is how many items were sold, and, uh, if we go back to our Focus mode, then we have got for the Blinder, uh, sorry for the binder, pen set, pencil, pen, and this, and I want to show all the records of pencil, then I can right-click on it, show data point as a table, and I will get all the records of the pencils being sold in the different regions, be Central, West, East, any other region. Okay. So that's how it works. That's pretty simple. Now the thing that must cross your mind is how can you actually generate a CSV file out of it? So that's a pretty simple thing to do. All you got to do is first make sure that for which record you want to generate a CSV file or for which record you want to generate a report. Suppose this is my current chart, and I want a report for the binders only. Okay. So I can just go to this data point option by right-clicking on it, and once I am over here, then I have got an option that I can just generate a report out of all this options, that is I can just create a CSV file that contains all these records. Okay. So for that, what I can do is I can just click on these three dots, that is for more options, and the first option that I've got is export data. If I want, I can just export it. So you can just click on it, and what happens is you can just export this data in the form of a CSV file. So I want it like binders. So this data is of binders. So let's just select binders and click on save. So this data is now saved in a CSV file, uh, with the name binders. If you want to just cross-check it, what you can do is actually load a CSV file in Power BI to cross-check the same thing. So what you can do is just go to get data option. Here is text/CSV format. You can just go to it, and here is this binders option. You can just click on it and click on open. Then, uh, of course, you got to wait for some seconds, and then you can see that this whole data is present, and you can just compare it's same, despite the fact that it is a CSV file and this was an Excel file. So that's how you can actually use this data in Power BI again and again. Okay. So we are just going to close it for a while, and there is one more important thing.

So as you can see that this chart is pretty simple, and we can pretty easily extract the records lying behind any particular data source once a chart is given to us. So there is another restriction that we can put on it. Suppose we have, as a developer, we have given this chart to our end-user, and that end-user wants to take out that report. So we can control whether that end-user can, uh, actually view this report or not. So for this purpose, we have a pretty simple approach, a pretty simple option. What you can do is you can go to this file option. Here is this options and settings, and once you go to this options tab, uh, you got to wait for a few seconds to get this option dialog box in front of you. Now in this options, you may find this current file written over here, and in the current file, the last option is of report settings. Now what this report settings allows is it allows whether we want the end-users to summarize the data or not. So right now, if you can see in this export data option, we have allowed the end-users to export the data from the Power BI, but if you do not want them, you can just click on don't allow the end-users to export any data. So that's as per your choice. If you want the end-users to export the data, you can do, or if you do not want, you can just click on don't allow, and whatever you choose, you can just click on okay and cancel to apply those changes. If you want those changes to be applied, you can just simply click on okay; otherwise, you can just remove it. So that's how you can actually, uh, work with the data in Power BI. That's how you can filter the data, and that's how you can actually control who takes a look at your data and who cannot generate a report from your data.

In this video, we would be seeing that how can we actually create a map using Power BI. So for creating a map, there are some things that you must take note of. The first of them is that whatever data set you are using must have the columns like country, state, pin code, postal code, ZIP code, anything like that, or the city, so that the Power BI can actually recognize that what it wants to say, like if you have a column labeled at States, so the Power BI would be able to recognize it and would be able to plot it on the map. So, uh, you have to search for a data set that is containing appropriate records. So the question might come to your mind that from where you can get a data set like this. So I have, uh, downloaded a simpler data set from the, uh, internet itself. I have searched for something like sample data set in Excel for map in Power BI, and on Google, it, I have got the second link that is Power BI course download practice data sets, and on going to this link, this is section 4 maps and scatter plots, and this is this Excel sheet given to us, and if you want, you can just download it from the same link. The link or the URL is given over here. I will also share it in the description box. So you can just go to this link and download this data set, or you can just search for the data set that suits your needs. So I have already downloaded this data set, and let us load it into our Power BI to see what it looks like. So this is my Power BI, and the data that I have downloaded is in the form of an Excel sheet. So I'm going to import it in the form of an Excel sheet. This is the data that is P6 amazing Mt European Union to geography, that is for a European union or Europe itself, and click on open, and, uh, of course, you got to wait for a few seconds till the connection is established with the data, and we have got two sheets, that is list of orders sheet, and if you just go over it, so we have got the regions or the fields like City, we have got country, and we have got State over here. So if you just take a look, so we have got three deserving columns or the three columns which could be used in the map, that is either the city, either the country, or either the state. So this is a preferred data set; we are going to use it. So you can just click, uh, or check it and click on load. Then what we are going to do is create a map out of it. So you must be wondering how to create a map. So there is a very simple method of creating a map in Power BI, just as the charts, a map is also available in the visualization pane as a visualization itself. So you can just simply create it. Okay. If you just expand this visualization pane, and this field pane here, you can see that in the field pane, we have got list of orders with all these different fields. Okay, and here in the visualizations pane, you can see something written as a map. So that is actually what we are going to do; we are going to just click on this map, and a map would be available on our Power BI page. So if you just click on it, you can see a map is available, but we are not able to make anything out of it, what it is. So to make sure that it makes sense, we have to drag some of the fields. So the interesting field given over here is location. In location, you can just drag the City, Country, state, or if the postal code or the ZIP code or the PIN code is given, you need to drag it over here. I want the records to be filtered by the country, so I'm going to just drag and drop this country field into the location, and, uh, after that, you can see that, uh, this pointer is glowing, and after a few seconds, this kind of a map would be loaded. Since this map was related to Europe, so I have got all these kind of bubbles over here, like I have got entries for Finland, Sweden, Norway, United Kingdom, Ireland, Germany, France, Spain, Portugal, Italy, etc. So all these countries which were present in my data set are now being, uh, identified in, in this particular map. Okay. Now what do I want to do is I want to actually take a look that how many orders were placed. So for that, I can just drag the order ID. So this order ID column would be would allow me to identify that how many orders were placed in which country. So this order ID column is dragged into this size field, and after a few seconds, you can see that the size of the bubbles is not distorted. In France, we have got a bigger bubble, and if you just hover over it, there is a tooltip with count of order ID, that is 991, meaning a total of 991 orders were placed from France. If you go to UK, 700 orders were placed. Then there is a smaller bubble in Norway because 37 orders were placed. In Sweden, we have got 100 orders, and in Finland, we have got 34 orders. So that's how you can actually, uh, represent the data in a map. One of the advantages of representing the data in a map is that you are able to interact with it in a better way. If you're showing this data to any of your clients or to your customers, they are going to be attracted more by a map rather than by a normal table or a chart. Okay. Now there is one more thing; we have got a legion column. So luckily, we have also got a region column in our data set. So if you want, you can just drag this region into the legion, and what would happen is now you have got colored dots like this dark blue for the North Region. The central region is colored by a lighter shade of blue, and orange is what is representing the South Region. So if you want the different regions to be highlighted on the map, you can do that as well. Now one more thing that, uh, right now it is a location that is used to represent, uh, the country is used to represent the location, but if you want cities to be represented, you can just, uh, drop this column, and you can just drag the city column over here, and after a few seconds, you would be able to see the different cities being mapped over here. So you got to wait for a few seconds, and yes, you have got all these cities present over here, but it's actually not looking very good because we have got these different cities stacked all over the place because the city's names are identical. Okay. So we cannot get City for a very accurate database. So let's just drag state and see what happens right now. So yes, state is pretty more accurate than the city. So you can see the states over here, and if you want to have a bigger look at it, you can just go to the Focus mode, and you can just minimize the visualizations and the fields pane to get a better look at it. But, uh, there is actually one more thing that is of zooming. Right now, it is set at this zoom level, but what if you want to control the way the zooming occurs in the map? So you can do that, uh, by using this visualization pane and formatting the way the map looks. So you need to go to this Format tab, and, uh, if you just go to this map controls, here we have this Zoom buttons option, which is currently, uh, set to off, but if you click on on, then you get these two Zoom buttons, the plus and the minus one. You can just zoom in, uh, suppose I have zoomed in into Germany, and I can see these different states which have been highlighted, and I can just hover over it, and I would be able to see that which region it belongs, what is the name of the state, and how many orders were placed in that particular state, like this. Okay. So that's how it works, the zooming buttons. Now there is one more thing in map, uh, like you all must have used Google Map, so there different options available in the Google Maps, like there is this 3D mode, there is this street mode. So what if you do not want this a, uh, plain-looking way for the map and you want to change the way it looks? So you have got this option of map Styles and visualizations for yourself. Right now the theme is set to road, but if you want, you can just change an aerial theme, and after a few seconds, you can see that we have got this aerial theme, and now you can just zoom in with all your details, and you would get this like terrain kind of a view. You want any other theme, you want like a dark theme, so the dark theme would be available to you. You want any other theme; it is, um, very easy to set that particular theme. I'm going to go with an aerial theme. Okay. Now, uh, you can also just, uh, toggle with or play with all these options, like, um, I don't want tooltips to be there, so no tooltips are there, but if I just click on on, then this tooltips would be visible now like this. Okay. So that's how you can work with these maps. You can actually go on, say data colors, you can just change the colors with which these regions are being highlighted. I want a crash kind of for the central region. For the North Region, I want, say, kind of, um, again a black for the central region or for the North Region, I want black, and for the South, I want, say, uh, white 30% darker white. So these are the three shades that I have chosen that you can just click, uh, and select. You can choose whether you want to show legends and where you want to show legends, that is basically how you can customize the way that your chart is looking or your map is looking. So that is how you can work with maps in Power BI. In this tutorial video, we are going to explore more about the maps in, in Power BI. In the previous video, we take a look at this map in which we created a map, and then we dragged the different fields into the map, and then tried to show it.

With the help of these bubbles, so this was a kind of map that is a simple map in which bubbles or circles could be used to represent the data. But there is another map in Power BI, and that is known as the filled map. You can see its icon in the visualization pane, just beside the globe map icon that we used previously. This is known as the Fill map, and the difference between these two maps is that while a normal map helps you to show the data with the help of bubbles or any kind of figure like a circle, on the other hand, the field map fills that particular region with the color and then it tries to show that particular data.

So, in this video, what we are going to do is we are going to create, first of all, a field map, and then with the same data set, we are going to create a simple map, and then we are going to compare both of them. So, for that purpose, what I have done is I have downloaded a sample data set from the internet. If you just go to Google and Google this: “simple sample data set in Excel for map and Power BI,” then this link is present that is of superdatascience.com. Here you can see this first Excel sheet that is given to us for a superstore in the US, whose data set is given for the year 2015. I have downloaded this particular data set, and once I have downloaded it, I have got this kind of an Excel sheet in which there are three sheets: AORS, Returns, and Users. I’m going to use this AORS table. So this is my sample data set, and let’s see how we can use it. The reason why I have chosen it is because you can see this “State or Province” column is present over here, so I can use all these states over here, and the country we have got a single country, that is the United States only. Okay, so let’s just load this data into Power BI first of all.

So we all know that to load the data, what do we need to do is we need to go to “Get Data,” and we first have to make sure that which kind of data we are going to load, and since we are going to load an Excel data, so I’m going to select Excel, and this is my data that is “Super Store US.” So let’s click on “Open,” and it may take a few seconds to load your data into Power BI before the preview is generated. So here we have got the “Orders” sheet, and once we click on it, you can see a preview over here, and you can just cross-check that whether the column that you want most, that is the “State or Province” column, is present over here. Then you can simply click on it and click on “Load,” and this data would be loaded into your Power BI, taking a few seconds of your time. Okay, so once it’s loaded, you would be able to see the “Orders” in this “Fields” section, so that would tell you whether this data is loaded or not. Okay, so now we can see the “Orders” over here, which means that yes, our data is now being imported into Power BI successfully, and you can just expand it by clicking on this arrow icon, and you would be able to see all the column headings that were present in your data source that you just viewed in the Excel.

Now what we want to do is create a field map out of this data. So for that, let us just create a new page by clicking on this plus icon, and a fresh page is present in front of us. So to create a field map, the process is pretty simple. All you got to do is click on this fill map icon in the visualization pane. You can just expand it as per your choice. In the “Location” field, you want to drag this “State or Province” column like this, and once you do that, you got to wait for a few seconds, and you can see that this whole United States is actually colored with a sort of a blue color. The reason why is because we have just dragged in “State or Province”; we are not categorizing it; we are not adding any kind of values; it is just recognizing all the states in a uniform color. But if you hover over it, you will get a tooltip with the name of that state like this. You can see Washington; you can see Arizona; you can see California; you can see Georgia; and so on. All these states are visible via tooltips.

Now the other thing is, suppose you want to just categorize it on the basis of something. So a simple option for this is the region. If you just take a look at this region, we have got a “Region” column which would categorize all these states into the four regions that are present. So if we just drag this “Region” into this “Legend” field, then what do we get? Okay, so we have got four colors: purple, blue, navy blue, and a shade of orange. These are for the four regions, as you can see in the label over here, that is the Central, East, South, and West. So here you can categorize the different states based upon region. And now if you hover over these states, previously when only the states were dragged into the location, you were only able to see Washington. But now since “Region” is also been dragged, you are able to see the region as the West region that Washington presents is present in the West region. Similarly, Pennsylvania is present in the East region; North Carolina is present in the South Region; and so on; Nebraska in the Central region; and so on. Okay, so that’s how actually you can work with a fill map in Power BI. If you just want to toggle over any of the states, you can simply do that.

Now there is one interesting thing that you must note that in the “Location,” we have got “State or Province” as the column heading, and luckily this is a valid heading because the heading must be like a state, a country, a city, or a postal code for us or for the Power BI itself to recognize that what we are going to show in the map. But in case the exact heading is not present, suppose in case of “State or Province,” like “State 25” or some other thing is present which is not a valid column name, then what you can do is you can make sure that Power BI recognizes this column as a “State or Province” column by simply clicking on that particular column. As soon as you do that, you will get this “Common Tools” tab highlighted, and then this—it is a data category. Right now it is uncategorized, but it doesn’t matter because Power BI already recognizes it because of the column heading. But in case the column heading is not correct, then you can just go to this uncategorized one, and you can select whether it is a city, whether it is a country, whether it is a state or province, whether it is a continent, a longitude, latitude, postal code, whatever it is. So that whatever you select, then Power BI would be able to recognize that what actually it is, and then the Power BI would be able to act upon it as per its category. Okay, so as soon as you have changed it to “State or Province,” you can see this kind of a globe sign present over here, which means that now Power BI has categorized it as a “State or Province.”

Okay, now this was about a field map. Let us create a simple map with the same data and try to compare the results. Okay, so let us create a new page, and now we will click on the simple map. And now what we would be doing is just dragging this “State and Province” into “Location,” and we would be dragging this “Region” into the “Legend” fields. Okay, so now you can see that—let’s just go to the focus mode for a better view—all the states of the United States are marked over here with circles, like Washington, Idaho, Nevada, California—everything is marked with the circles. Instead of being colored fully, they are just being marked with circles. So that is a simple map that is used to use some kind of symbols like bubbles or circles to represent these states or whatever region they are trying to represent. While in the case of a fill map, like the one over here, if you just go into the focus mode, you can see everything is represented fully in color, like you can see every state that is colored fully. So that is the difference between the, like, field map and a normal map. Another difference is that in a normal map, you have got another field that is of a size. So if you just go to some kind of a quantity, a numerical value, like say “Order ID,” then what happens is you can just get this “Order ID” into “Size,” so these bubbles would differ in size based upon the “Order ID,” and we would get a count of “Order ID.” Like in Texas, we have got 124 order IDs, means 124 of total orders were placed in Texas. In California, we have got a bigger bubble because 214 orders were placed. So this is an added advantage of a simple map that you would be able to just see any kind of a numerical quantity in a better way represented through a normal map instead of a filled map. Okay, so that is the basic difference. If you want some kind of a numerical value to be represented, you can just go with a normal map.

Now there is one more thing: there is this “Postal Code.” If you can just check it, what happens? Let’s see. We are going to just drag this “Postal Code” into the “Location” instead of the state. Now what happens is we got to just wait for a few seconds. Yes, so now you can see that it has got bobbled up all over the place. Why? Because “Postal Code” is not recognized as a standard quantity. You can just go to “Postal Code” and see it is uncategorized. So let’s just categorize it into a postal code, and this may take a few seconds. Okay, now the postal code is although recognized as a valid quantity, but still you can see it’s scattered all over the place. So thus we can make sure from this fact that the name is a better way to categorize our data than the postal code because we are pretty sure that all of this data is for the United States of America, as the data set is for the Super Store based in the US, and it’s not going to deliver in some place of Africa, Europe, or Australia or somewhere here. So that’s how it works. Thus, to categorize them, if states and postal code both are present, it is recommended to use a state to categorize the location instead of postal code.

In this video, we will be seeing that how can we create a map along with a pie chart in Power BI. In the previous videos, we have already learned about how can we create a map, either a simple map or a filled map, and try to show some of the data using that map itself. But in this video, we are going to take a step further, and we are going to combine the features of a map in Power BI along with a pie chart. The reason why we are choosing a pie chart is because that it can be represented very easily in a circular form, so it would be easy to locate that pie chart in a map because here, as you can see that we have got all these circles or the bubbles that would be used to represent any of the stuff over here, right? So for this, there are some of the predefined requirements. Let us just get into the focus mode and just minimize these panes for a second. So the reason why we are choosing this map, or the requirement is that you must need to choose a simple map, that is the one that is represented by a globe kind of an icon over in the visualizations panel. So you need to make sure that you are having exactly this kind of a map, and it would be the only requirement because for the pie chart, we cannot use a fill map. The reason being that in the case of a fill map, you do not have any kind of bubbles or anything, but you can just use them in this map. So that’s why we have dragged this map, and what else I have done is I have imported a data source. So if you just go to “Recent Sources,” this one that is of a “Super Store US,” this is the data source that I have imported in my Power BI, and this is the data source which I have downloaded from the internet. So if you want, I will share its URL in the description box below. And this is all the fields that I have got from this “Orders” table from the US Superstore database. Now in this all the fields, we have a column named as “State or Province,” and I have dragged this into the “Location,” and that is why we are getting all these states in the form of a blue color. So let us first understand or perform some of the actions so we are able to understand that what is it going to do in a better way. So we have another column that is known as the “Region” column, and if we can just drag it into the “Legend” field, then you can see that there are four regions now: the Central region, East region, South Region, and the West region. These four regions are actually represented by four different colors, which is actually shown over here. These are the different regions which you can easily see. So it is a way of categorizing the data in Power BI. All right. Suppose you do not want “Region.” Let us just remove this “Region” field. Then we have another field in our table, and that is known as the “Customer Segment” field. If you just get it into the legend, then what happens? You can see this “Customer Segment” is actually that what for what purpose the thing or the product that the person has required or has ordered for is going. It is for customer use; it is for corporate use; it is for home or office use; or it is for a small business. So these are the four categories of the “Customer Segment” that we have got, and as you can see, as soon as I dragged that into the “Legend” field, all our bubbles have changed into some kind of a different icon. If you’re having any trouble in seeing those bubbles, you can just increase its size by going into the “Format” tab. Here is this “Bubbles” option, and here is the “Size.” You can just increase its size a little bit like this. Okay, so now you can see it is all visible very clearly. These are the different states. Suppose here we have Montana; so it has only two categories, means whatever the orders are listed for Montana, we have either the orders for small business or for the home and office. However, in Washington, we have all four of them—all four of the categories or the customer segments have been ordered in Washington. Similarly, in Oregon also all the four customer segments have been ordered. So this is a kind of a pie chart, but as you can see that whenever this is three, there or the three customer segments have been listed, we have got the same angle everywhere. For the two, we have got a 50/50 proportion, and for the four ones, we have got a 25% proportion. So with the help of this customer segment in the legend, we are able to get that how many customer segments are ordered, but we are not very sure on the fact that actually how much of them were ordered. Like we can easily make out from this chart or from this map that in Washington all the four of the customer segments were ordered; that’s perfectly fine. But we cannot make out that how many of the small businesses were ordered or how many for the corporates were ordered; we cannot make out a proportion for that. So for that purpose, what we can do is we can just come back to our place where all the fields are listed, and there is the “Size” field. In “Size,” we are going to just get something, and that is known as the—we must have something called as, say, “Unit Price.” You can just get it into the “Size,” and yeah, you can see all of them. So we are able to classify them that how many total worth of the consumers were or the products that were fit for consumption of a consumer were ordered in Washington, that is around 10,088.3. Similarly, for the corporate, we have some other value that is 5579.6, and for the small business, we have 7709.62, and for the last one, that is home or office, we have 141. We have got these different proportions all over the place, and we are able to make out that from which customer segment we are getting most orders out of the state. Similarly, if we try to go to California, then we have got the corporate customer segment placing most of the orders. In New York, we have got home and office segment ordering the most of the stuff, and similarly, we can easily make out from this chart or this map along with the pie chart that which thing or which, you can say, which customer segment is being ordered the most in which state. So this is how you can just use a pie chart in a Power BI map. This is a very simple process in Power BI, but if you can see it in the competing software is like Tableau, it is a very advanced process; it would not be as simple as we just learned over here. That is why Power BI is more preferred because it is very simple to use. We have used it for, say, “Unit Price.” Right now, what if we just remove it? Then all things would get back to normal like this. And suppose we want to make out that how much profit we are just getting from all these fields. So on “Profit,” we are getting the profit only from the small businesses in Washington. In California, the most of the profit we are getting is from the Home and Office businesses. In, in here we can, like in Oregon, we can get the profit from the corporate sector and the consumer sector. So we are getting profits from three sectors, but most of the profit is coming from the consumer sector. And this is the kind of the data that we have got that from which consumer segment in which state we are getting the maximum profit. So this is the actual use of Power BI. This is the actual use where you can just make out different kinds of stuff where you can analyze the data and gather different kinds of information which was hidden if we hadn’t used this tool. So that’s the actual goal behind using Power BI: to get some of the insights into the data. So that’s how you can just create a pie chart along with a map in Power BI, and I hope you all have understood that how can you just use this along with the pie chart. And if you want to make any other things, like suppose instead of “Profit,” you want “Sales,” that how much sales was done in each and every sector, so you can see we have got all these bubbles again, and they are of different proportions.

In this video, we will be seeing that how can we work with tables in Power BI. Now we all know that Power BI is a software from where we can do a variety of things, and mainly it is used to analyze the data. But since the fundamental of this data is a table, so Power BI gives us the opportunity to actually work with tables in Power BI itself. There are two ways through which we can actually use the tables or create the tables in Power BI: one is from scratch, and one is if already you have some kind of a table or a data set loaded into Power BI, then you can create a table out of the fields available to you. So the first step is creating a table from scratch. So we are going to see that. So for that, what do you need to do is first of all make sure that you have opened your Power BI. Then in this “Data” group, you will find something called as “Enter Data.” So if you just hover over it, there is this small tooltip given to you that you can create a new table by typing or pasting in new content. So this is one way through which you can actually create a table for yourself in Power BI. If you just click over it, then what happens? You will get this kind of an option. So

This is column one, which is representing nothing but a table for you. So if you just can, uh, give it some kind of, uh, a heading like "Region," and if you, uh, see this is the star option, if you just click over it, so you would be able to add one more column. So as many times as you click on "Star," those many columns would be added. So, uh, let's just right-click and delete it. You can just delete any column by right-clicking over it. And let's just double-click over here and just change its name to "Sales." Okay, so I'm going to create a very simple table which is two columns: that is the "Region" column and the "Sales" column. All right.

Now, in the "Region" column, let us just type in the West Region, the Central Region, the East Region, and the South Region. Okay. Now, in the "Sales," I'm going to give some of the values. These are the random values that I'm giving, so it doesn't really matter. Okay, so these are the four random values that I have given. Similarly, if you just click over this star for this row, so a number of rows would be added as well. And if you don't want the rows, you can just right-click over it and delete them. Right now, we have created a very simple table. This is one way of creating the table from scratch. The other one is you can just go to some source, say Excel, and if you have created a table in Excel, you can just copy it and you can just paste that table over here, and you would be able to get that table in Power BI itself.

Now you can give your table a name, so I'm going to give it as "Region Wise Sales," and then you can simply click on "Load." Now this may take a few seconds to just load this table into Power BI. Now one thing you must note over here that this is actually a table that you have created in Power BI, and it has no relation with any of the external data sources. All right. Now, uh, you can just see that we have got two tables in this field section: one is the "Orders" table that we had, uh, loaded earlier, and this is the "Region Sales" table that we have just created. If you just drop it, there are two fields: "Region" and "Sales," which we have just created. So let us just use a chart, a simple, um, say column chart, to, uh, just depict this data. We can just get this "Region" into the "Axis" one and this "Sales" into the "Values." Okay. Now you can see we have got these four options, actually that is not correct. So let's just get this chart again: "Region" in the "Axis" fields and "Sales" in the "Values" field. Right, so you would be able to get this, but I'm not very sure why it is, uh, not getting the correct thing. That is because I think we're getting the count of sales. Yes, so what we can do is just, um, click over it in the "Sales" and just categorize it as the sum. I think it should work. Let's get the column chart again: "Region" is already there and, uh, "Values" for "Sales." Yes, so we have got the sales that is for the West Region; we have got 8,700; sales for the East Region, around 7,969; for the Central Region; and for the South Region. Okay. So this is a very simple way of creating a table, uh, and just using it directly into our Power BI.

Okay, the next method or the next way of creating a table is using this visualization pane. If you just go over here, just below your pie map, you will find something called as a table, and just beside it is a matrix. So "Matrix" we would cover in the later videos, and it is also similar to that of a table, but first of all, let us look at a table. So you can just simply click over it, and you would get a table. Now, uh, when you just, uh, drag it or when you just open up a table in a page, you can see that here we have got a "Values" field. In the previous options or any other options like in the charts or in the maps, we have got a bunch of fields over here like the "Region," the "Size," the "Values," etc., but in the table, we have only got the "Values," and what does this "Values" field expect from us? It expects some kind of data or some kind of fields or some kind of columns which can be added into the table. So for this table to work in Power BI, there is a requirement that at least one table must be imported in Power BI beforehand; then only you can just use this table. So we have two tables actually, so I'm just going to collapse this "Region Wi Sales" and I'm just going to expand this "Orders" table. Now this "Orders" table is a part of the data set that I have downloaded from the internet. So if you want for yourself, you can just download any of these sample data sets from the internet, and then you can use it very easily. So, uh, we have got actually some kind of thing that is known as "State" or "Province." I'm going to drag it in the "Values" field, and we have got all these states over here, and I want to show that how much profit, uh, is done in each state. So I would just drag this "Profit" field again into the "Values" pane, and as soon as I do that, you can see that we have got two columns in our tables: the "State or Province" column and the "Profit" column. Now I can drag as many values as I want into this "Values" field, and it would be added into the table. All right. So suppose I want also the sales of each state or province, then I can just drag in the "Sales" as well, and you can see that if I want, I can just drag these things, and their order would be changed. Now it is "State," "Sales," and "Profit." If I want "Sales" to be coming at the end, then I can just drag it, and its order would be changed in the table itself like this. Okay. Then if you want any other things, suppose, um, you want how many, uh, how much discount was given, you can just, um, drag and drop this "Discount" as well. All right, so we have got all these options, and one interesting thing to note over here is in this table we have got these four, uh, headings, and along with there we have also got a total. So this is the default layout of a table. If you just go to the focus mode, then you can see it in a clear way that, um, all of them are are visible now in a much more, um, good way, in a much more way that is readable, in a readable form. So here you can see that the total and the heading column, that is the topmost row, and the, uh, last most row is fixed; that is, this headings are fixed, and this thing is also fixed. So that is the beauty of Power BI. In Microsoft Excel, uh, if you have used Microsoft Excel, we had to do all these things manually, but in Power BI, everything is fixed. All right.

Now one more thing you must have noticed that in "State or Province," we have got different states like Alabama, Arizona, California, etc., and they all are by default arranged in an ascending order, like you can see, uh, all in the ascending order. Okay. So if you just click on this arrow, this is what is used for this purpose; that is, if your data is not arranged in a pre-fed format, suppose in the "Profit," it's not arranged, so we can just click on this arrow, and you can see that now the table is arranged on the basis of "Profit," that is the maximum "Profit" values we have got over here. And if you just scroll down, this is the value we have got the most of the loss, that is in North Carolina, and it is present at the bottom. Similarly, if you want to arrange with the help of the "Sales" or with respect to the "Sales," you can see that it is also arranged into the, uh, form, and that is the topmost, uh, or the state in which the most of the sales were made; it is present at the top, and at the, um, state where, um, the least of the sales were made, it is present at the bottom. Similarly, you can also sort it with the help of the "Discount." Basically, any number of columns that you have, you can just sort it on the basis of that only. Okay. So yeah, if you want, you can also work with the states by just simply clicking on this arrow. So this is actually, uh, very much simpler in the Excel counterparts or any of the competing software counterparts that we have got in Power BI. That is the beauty of Power BI.

In this video, we will see that how can we actually format the tables in Power BI. In the previous video, we have already seen that how can we create a table in Power BI. Now, once you have created a table using the second method that was discussed in the previous video, then only you would be able to actually format it, because in the first method, you got to simply create a table, and you are not showing it to anyone else, so there is no need to just format that table. But over here, the table is shown to the user, to the end user, so it is very much important that you just format it so that it looks appealing to the user, and the user is actually interested in knowing that what is going on in that table or what data it contains. So let us start with the video. We have already got a table from the previous video itself, where we have arranged the values on the basis of the states or province; that is, uh, they are arranged in the ascending order on the basis of the states or province. Now we have got four values over here, which are originally taken from a table known as "Orders," which is a part of our US Superstore data set, which is downloaded from the internet. So if you want, you can just go to the internet and download any of the data sources that you like. All right. Now I have dragged four values, that is a "State or Province," and I'm getting state-wise sales data, state-wise profit data, and state-wise discount data, because these are the four columns that I have dragged in my "Values" field. If you want, you can drag any number of columns, um, more or less; it doesn't matter; whatever data you want to show in your table, you can simply drag that into the "Values" column, and it would be a part of your table. All right. Now this is already created table. Let us just change something. We want to just increase its text size because it is not very much visible right now because we are in the focus mode, but if you just click on "Back to Report," then you can see it is very small; it is very tiny; we cannot see anything. Okay. So for that purpose, we have this "Format" tab, and there is something called as "Values." If you just expand this "Values" group, there are these different options available, and all these options have a common task; that is, they format the way the values are represented in our table. All right, like we can format the font color, the background color, the alternate font color, and the alternate background color. Similarly, if you just scroll down, there is something known as "Text size." Right now it is 10 point, but if you want, you can just increase it using these options, these, um, arrow buttons, and I want, say, a text size of around 20. Let's wait for a second, and you can see that we have got now 20 for our values, but what about them? What about the headings? So there is a separate way through which we can actually change the way the headings look and their size as well, so we would be doing it afterwards, but simply we are going to be focusing on the values right now. All right. So, uh, if we talk about "Font color," so what font color do we have is black, but if you want to change your background color, right now it is the white and shade of gray, but I want to use blue and its shades, suppose this is this aqua blue that I've got, and for the alternate one, I want this kind of a darker blue. Right, so yeah, these two are present, and when I'm using this, I want actually to change the font color from black to white because it would be more visible; that's what I think in the blue one. Yes, that's correct. And let us just change this alternate font color to white as well, and you can see that, uh, these lists or these data is now much more visible in a better way than it was previously. So that's how you can actually change the way of how your values look; that is the font color, the background color, then, uh, you you can also choose if there must be an outline. So if you just select on "Frame," what would happen is you would get all the four sides of the outlines, but if you don't want a frame, you want only for top, only bottom, only left; these are all pretty self-explanatory, so you can explore them for yourself. And if you want to change the font family, suppose, um, "Consolas," so yeah, it's changed over here as well. Okay. So that was all about the values, that how can you work with them. Then there is this "Column headers." So whatever things you did in the "Values," that is the same things present in the "Column headers" as well, like you can change the background color. If you want, I want it with the lightest or the second shade darker of the blue, and the font color I want to change to say white. Yep. And then what I want to do is actually I want to change its size, let to say 22 points. So yeah, that's looking pretty good to me, and also you can work with the alignment, like I want "Center" alignment, so you can just provide it with the center alignment as well. And similarly, that was for the "Column headers"; there is another section that is known as the "Total" section. So if you want, you can just work with the "Total" section as well. We have got this "Total" section over here; you can control whether, uh, that whether you want to show this "Total" column to the end user or not. If you just turn it off, then this "Total" column would vanish away, but if you want to show it, you can just turn it on, and everything you can change, like instead of "Total," I want the column heading or the heading as "Sum," so "Sum" is present over here; you can change the cosmetic part, uh, you can change the outlines, you can change the size, everything that you want. So that was for a bunch of similar options. The other things that we have is known as a grid, like you can see right now the horizontal grid is on, but the vertical grid is off. If you can just turn on this vertical grid, it is actually, uh, now visible, but since our outlines were already there, so you cannot make it very sure. You can obviously change its color, so you can just choose a black color, and you can see that this vertical grid has changed its color to black. So, uh, that's how you can work with it. You can also increase its thickness to around, say, three points. Similarly, for the horizontal grid also, you can just change its color, um, to black; you can just increase its thickness to three as well, and you can see that the table is looking more clearly. Also, you can just, um, change this row padding as well, like this. Okay, and all these things for the grids. Then, then we have this "Styles" options. So that is a default style, or these are actually how much or what style you want for your table, uh, you want a minimal style, so this is a default style, uh, instead of just working on the cosmetic part, you can simply work with the styles, like you can go with a minimal style, you can go with a bold header style, alternating rows that, um, over here, contrast alternating rows, so these are all these things like flashy rows, you can just work with them as per your wish. So that is how you can manipulate a table, uh, in Power BI. You can just change how it looks.

In this video, we will see that how can we apply conditional formatting over the tables in Power BI. Now, apart from conditional formatting, we will also be seeing that how can we apply some of the aggregate functions over the values on a table. So let us start with the video. In front of your screens, you all could see this table. This is the same table from the previous video in which I taught you about how to create a table in Power BI. So this is the same table that I have taken up; you can take up any table that you want. Now, once you know that how you can create a table, after this what we are going to do is actually apply conditional formatting over the different values in this table. So before working on it, you must need to know a few things about conditional formatting. First of all, the conditional formatting can only be applied over the numerical values in a table. So if we talk about this table, we have three columns of numerical values: that is the "Sales" column, "Profit" column, and the "Discount" column. So only these three columns can be, uh, applied with conditional formatting, and the "State or Province" column cannot be applied with a conditional formatting. The second thing is, uh, what is conditional formatting? So it is actually a formatting that is done based upon some condition. Usually, uh, when we see this table, it is kind of plain. Now, if we just sort this table on the basis of "Sales," then what happens is we are getting the "Sales" value, like the largest value on the top and the smallest "Sales" value on the bottom. We all know that, but there is no visual representation of the fact in the table. So if we want to visually represent something, some numerical value, then we make use of a concept that is known as conditional formatting. Conditional formatting can be applied on a number of criteria. What are those? We would see just now. So to apply conditional formatting, what do you need to do is make sure first you have got a table for yourself, and then in this "Format" tab, what you can do is just scroll down here. This is something written as "Conditional formatting," and this is exactly where we wanted to end up. So when you expand it, you have got these bunch of options. The first one is the heading of the column on which conditional formatting could be applied. Okay. So if you just, uh, see the drop-down, there are these four columns, all the four columns of the corresponding table, but we know that conditional formatting cannot be applied over "State or Province" because it is a textual value. So these three column headings are actually valid to apply a conditional formatting. First of all, let us apply it on "Sales." So whichever column you need to be formatted conditionally, you need to select that first, and these are the different parameters on the basis of which you can actually apply conditional formatting. These are "Background color," "Font color," "Data bars," "Icons," and "Web URL." First of all, if we talk about "Background color," and if you we just turn it on, then what happens? You can see that the "Sales" value is now formatted conditionally based upon blue color, which is by default the color for Power BI. So what does it do is it has, uh, actually, uh, marked this topmost value, which is the highest value, with a dark blue shade, and then this shade is lightening as the values are decreasing. You can see that at the bottommost value, like this one, this is the least dark blue, or it is the lightest of the blue. So this is a very, uh, simple type of conditional formatting in which there are the different shades of the colors available, and you can just apply them over a column using "Background color." Now what if you want to just change the color? You do not want this blue color; you want some other color. What you can do is make sure that as soon as you turn this button on, there is something known as "Advanced controls" is present over here. You can simply click on it, and you will find this kind of a popup menu in front of you. All these things you can just keep the same; the thing we are going to focus on right now is this value. So the minimum, which means the lowest value, would be shaded by this shade of blue, but suppose you want some other color. I want like a shade of red, so I can just go...

To custom color and go get a shade of red. And for the highest value, instead of blue, I want a shade of green. So I again go to custom color and just select a shade of green. Instead of this, I can also enter a particular value in case I want that value to be uh associated with that particular color instead of the highest or the lowest values. Like here you can see either the custom value or the highest value for maximum. Uh, this is the format by option in which the color scale is selected, but there are other options also, like rules or field value can also be selected, which we would be discussing later on. But once you are happy with it, you can just click on okay. And after a second or few, you can see that the highest value is not represented with kind of a green color, and these are the brick rate colors all uh associated with it, and the lowest value is represented by a reddish color. So that is how you can actually work with it to make a contrast very easy. We know that the highest value is uh the green and the lowest value is red. So that was about background color. Let us just uh take a look on other methods as well.

So right now we have applied it on sales. Let us go to profit. And now what we're going to do, we can apply a font color so that uh if you just turn it on, you can see that it is not changed on the basis of the font color. We can sort it on the basis of profit. We can go to Advanced controls, and here is the same thing that we did on the basis of the um background color itself. So I'm not going to repeat it, but you can just work on it similarly as you did with the background color. The third one is the discount column. Now we have sorted the table on the basis of the discount column, and let us just apply some conditional formatting over it as well. So we can just select discount column, and here are the different things like you can apply a data bar, and these are the data bars that can be applied, which is the length of the bar that is showing you that what is actually the uh value of the discounts. Then what you can do is you can just select two bars, either positive or negative, and you can just give its direction. Suppose it's right now left to right, you can just uh change it to right to left. And once you click okay, the direction of the bar changes. And suppose you want to work with the positive and negative values, then for that what we can do is instead of profit, for profit we have to use this data bar option, because in profit only we have negative values; we do not have any negative values in discount. So let's just turn this font color off and uh just change this data bars option on on, and we can just go to advanced controls, and there are this positive bars. We want positive bars with say green color and negative bars with red color, and we can click on okay. Then what happens is all the positive values in profit are um there with green, and the negative values are with red, which means that's a loss instead of a profit. If the color is red, then uh the next option we have is of icons. Suppose um instead of um data bars, let's just turn that off. We apply icons over profit. So these are the uh icons that is um these green ones with a higher profit, these uh yellow triangle ones for an average profit, and these red diamond ones for a loss or a negative value or below average profit. So that is how you can apply conditional formatting for the different options in Power BI.

Now, next thing is how to apply aggregation. So for that we can just go to fields, and uh here you can see in the values where we have dragged these columns, you can see something written as profit. If you just click on this arrow, you can see these are the different aggregations or uh different values that we can apply. So suppose I want to show the percentage of profit, so I can go to show value as and I can go to percent of grand total. Then what happens is instead of the exact profit amount, I'm getting the percentage of the profit uh that how much percent of the profit I have made from the sales done in that particular state or in that particular Province. Um, suppose you want to show something else like uh instead of the percentage of profit you also want to show its numerical uh value and the percentage as well, then you can just drag this profit once more into the value column, and you can see now now the value is shown as well as its percentage is shown. You can just change its order as well. Suppose for the discount you want to show uh like um on the basis of Maximum, then what happens is actually there is no change because um yeah uh there is this change uh on the basis of the maximum discount that what is the maximum amount of discount, and that has been uh presented over here. Or you can just uh go on sum. Sum is by default value which actually shows the actual value. So if you're not sure what do you want to show, you can just go with sum; otherwise you can also go for average, like average of the discount uh would be shown over here, or if you want you can go with the minimum value as well. So that is how you can actually work with um the aggregation functions in Power BI and along with conditional formatting in Power BI also. You can show the percentage of any of the values that you want.

In this video we will see that what is a matrix and how can we create a matrix in Power BI. But before going into the topic of Matrix, let us first understand that what is the need of Matrix. Matrix is actually used to represent data just like a table, but it does in a 2D format while table does it in a 1D format. So that is the basic difference between a matrix and a table. Now the question that must come to your mind is why do we needed the Matrix if we already had the table? So there are a few shortcomings of representing the data through table which were actually solved by the use of a matrix, and that is why we are using Matrix as well as table nowadays. So what are these shortcomings? We will see it with the help of an example. First of all, you can see in your screens that I have opened my Power BI. Now I'm going to create a table. So for that I need to go to this visualization span and click on table. Then I'm going to add some of the columns to it like the region column, the item column, and the units column. So this is my table in which what I have got is the region; there are three regions, the central, East and the west region. Then I have got the items. So if you take a look at these items, then what I'm getting is for the central region I have got the list of binders that how many units of the binders were sold. Then again for the binder there is a separate entry for the east region that how many binders were sold in the east region, and again for the binders there is an entry for the west region that how many binders were sold in the west region. Okay. Now if I just try to sort this table on the base of of the item itself, so I've got these three separate entries for binder, two for desk, three for pens and so on. So there are separate entries for the same product, that is separate rows have been taken. Now what is my concern over here is suppose I want to show that how many total number of binders were sold despite of the fact that uh the region. I cannot make out. I have to just manually calculate the sum, and then I only I would be able to tell that how many total number of binders were sold. But this is only a small quantity, so I can just uh calculate it manually, but this is not the case in the corporate world. There are millions of entries, and you need to calculate the data from it. So manual calculation is just out of context. So for this purpose we have the use of a matrix. A matrix is a two-dimensional form, so it would just organize this data into a 2D format, and this data would be represented in a much more user-friendly way. How so? First of all, uh we will see that how can we create a matrix from the table that is already created. Right now this table is already created, so what I'm going to do is make sure that my table is selected, and then I can go to the visualization Spain and here is this Matrix option that is just beside the table option. And if I just click on it, then what happens is you see that the table that we had just now is now converted into a matrix. Now I can make sure uh that this is my Matrix. I have got the entry for the region, and I have got the entry for the items, and I have got the entry for the totals. So from this Matrix I can very easily find out that how many number of binders were sold in the central region, how many number of binders were sold in the East and the west region, and then I can make out that how many total number of binders were sold, that is 722 in these three regions. Then there is another interesting thing I can find out that how many total sales or how many units were sold totally in a central region, that is 1199 units were sold totally, similarly in the east region and the west region as well. So that is the beauty of Matrix; it gives you insights into Data that were not possible with a table. Some of the interesting insights that table was not able to give you. Now uh this is a two-dimensional format of data. We are getting the entries with the region and the items as well, but suppose you want the heading of the region for the columns and the heading of the items for the rows, then you can uh see over here that in the rows we right now are having region, but you can just drag region into columns and you can drag items into the rows. So basically that is how a matrix works. A matrix has three values: the rows, the columns, and the values. In the rows field you can just get anything that you want, and it would be the rows like this in the columns over here, and the values are what is represented over here. Now here we are only having units. Suppose instead of units you want the totals as well. Suppose you want it to compare on the basis of two values, then what you can do is you can just drag more than one value into this values column, and then what you can see is we are getting two columns over here in the central region, that is a super heading; we are getting two subheadings, that is the units and the total, that is total of 424 units were sold which is worth rupees 57626. So this is the case for all of them like East, West and the total as well, that is how many units were sold and what is the worth of this units. Similarly over here also we can get that how many total units were sold in central region and what is the worth of these units. So this is a beauty of Matrix; it helps you to get a better insights into the data. Now suppose if we just get rid of this totals column, there are all the things that you can do with a matrix that were possible through a table, that is you can just format the Matrix just like a table. You can apply conditional formatting to a matrix just like a table. So let us understand this conditional formatting part with the help of an example. So for that purpose as in the table you need to go to the format Tab, and then what you can do is just scroll down and go to conditional formatting. So I'm going to format it based upon the units because we know that only on the basis of the numerical values or the numerical columns only we can actually format the data, and I'm just going to apply icons. So if I just click on on, then what happens is I'm getting these kind of symbols, that is green uh Circle, red diamond and yellow triangle. But what if I want to customize it? Now we know that to customize anything, to customize the conditional formatting, we can go to Advanced controls, and here you can see that this is for the units. In this we have got rules. So rules is used to format the icons. Suppose uh what is it uh these are the rules that have been laid out over here that if the value is greater than or equal to 0% and is less than 33%, then it is shown by this diamond. But instead of percent you can just go with number, and number over here for all the values actually we can just go with number, okay, and then we need to just uh change it like if it is from 0 to say 400, then we get this; if it is from 400 till say 800 we can get a number; if it is from 800 till uh what value we have, we have actually okay 2,000, so we can just go with 1,000 or 1200, okay. Then we can just change the icons as well as per our wish. We we want a red down arrow. Suppose we want um say this thing, yellow exclamation mark, and we want this thing, and for the red also we want this cross sign. So this is what uh we can do. We can just change the rules; we can change the way the icons look, and as soon as we click on okay, then what happens is we are getting all these values right uh cross, that means they are less than 400. For more than 400 it's like this. So let us just change rule because um we're not getting any kind of a correct tick, so it is from 400 till say 450, and from 450 to whatever it is, we can just click on okay. So we have got this sck for 498 values. So this is how you can just change the rules. This is how you can apply conditional formatting over the Matrix, and this is very similar to a table. Suppose instead of icons you wanted to apply data bars, you can just click on on, and the data bars would be applied to your data. Basically working with a table and a matrix is same; everything that you could do with the table you can easily do with the Matrix, and um just as we saw in the table uh we can apply each and everything in the Matrix as well. Now suppose you are not happy with the way this Matrix looks and you want to change its theme, so for that purpose you can just go to this view Tab, and there are these some uh themes given to you which you can work on, or you can just change for uh the current theme. Like you can go to customize current theme. Suppose you can just go to text, and you can just change its size to say 20, and you can click on apply. Now what happens is by default the size of the Matrix uh the TT size would be 20. You can just uh show these grid lines over here and anything that you want you can just work with it. Or if you want to just change uh the way it is looking like the column headers you want to change, you can just go and change its background color say blue, and then you can see it is applied. So that is how uh what is the use of a matrix, and that's how you can create and format a matrix in Power BI.

In this video we will be working with cards in Power BI. So the first thing that must come to your mind is what is a card. So a simple definition of a card is that it is used to visually classify the information. Uh, suppose there are hundreds or thousands of Records in a table, but you want to show like uh some important stuff to the people, then you can easily use a card. It is used often in dashboards, like you must have seen wherever there is some kind of information being shown to you, usually it is shown in the form of cards, that is the quantity that how much is the total amount or what is the average amount etc, and then there are some of the like graphs or the maps to support that data. So this is what is the usage of a card in Power BI, and we can see that how can we create a very simple card. So let us start with the video, and here is my Power BI page. You can see these different types of grid lines. So if you want to make sure that these grid lines are also visible on your screen, you can just go to the view Tab and make sure these grid lines are on, or you can just turn it off like this. So I'm happy with the grid lines on; I'm just going to keep it on, but it totally depends upon you. Then I'm going to show you that how to create a card. So for that, card is also a form of a visualization that is present just above this R script visual in the visualization span, and you can just click on it, and a card would be added. Now the question is what information you want to add in a card. Suppose I'm working with the orders table right now, then I have these different types of options with me, and I want to show that how many total sales were done according to that table. So I can just drag the sales option into my Fields value, and uh you can see that yes I am getting the total number of sales made, so 1.92 million rupees worth of sales were made, and I'm able to see that information in a classified format, in a like kind of a visual format. So that is a card. Suppose you want to show some other stuff, you can just again go to a card, you can add another card, and you can just add any other thing. Suppose I want to add a profit over here, and you can see the profit is added, that would give me the total number of profits, that is how much profit I have made in 1.92 million sales, so I have made this much of profit over here, and uh suppose I want to add any other card, so you have to click outside first of all. Make sure your none of the card is selected, and then you can click on card, and then only your card would be added. Then suppose I want to show that um how much discount did I give, so I can just click on discount option, and I would be able to see that how much discount did I give in all those sales. So this is the quantity of the discount that that I gave, and this is the total number of sales made, and this is the total number of profit. So these are basically the cards which we have created, and you can see you can easily align them with the help of these guides which would guide you that where you should uh set your cards so that it looks good in viewing. Now these are for the general things, that is for all the records that are present in our table. Suppose we want to show that data in some other format, like uh we want to show this data on the basis of a chart. So let us just add a stacked column chart over here. We can just apply it like this, and then we are going to drag some, suppose um let us drag region and let us drag sales in the values. Okay. Then what happens is we are getting the sales for that particular region. Now the data is associated or it is uh depicted in the form of a region; it is visualized in the form of a region. Now if I just click on east region, then what happens? You can see as soon as I clicked on this east region, these three card values changed. Now it is showing this much amount of sales, this much amount of profit, and this much amount of discount. The reason why we are getting this data is because this chart is linked with these three cards, and since I made a selection in this chart that I only want to see the sales for the east region or the values for the east region, what I'm getting is the data for the east region, that is the total number of sales done in the

East region total number of profit obtained and the total number of discount given in the same way. If I suppose click on the west region, then the data again changes; central region again, South Region again. If I click outside, then also you can see the south region is selected currently, so its data is shown over here. You can just control click on more than one option, like uh, right now I control-clicked on Central regions, so the data that is shown over here is of both the central region and the South Region. Similarly, you can just select three regions or even four regions as well. So that is how the cards are created; that's how the cards are linked with the visual; and that is how you can just create a very simple dashboard for yourself to just represent all the information that is being shown instead of having to write tables and all.

Now this was how the charts are linked with say a visualization that is known as a chart. We can create other visualizations as well. For that, what we are going to do is simply delete this chart and bring out a table. So this is our table, and let us drag some of the values, some of the fields and the values, like we want sales of course, and uh, these sales we want to be associated with the product name. Then what happens? Um, yes, with the product name, then it may take a second or two, and then you can see that these are the sales for the product name classified by the product name. Now if I just click on this first option, then what happens is I'm getting the value for that particular product, like how much sales were done for that particular product, its profit, and its discount. So whichever visualization you bring it on over here, just below the dashboard, all of your cards would be associated with that particular visualization, uh, uh, okay, be it a stack, be it a stack column chart, be it a table, or be it a map; it can also be associated with a map in the similar way. So I'm not going to just reiterate the same thing again and again.

Now, uh, you must have noticed one thing that all the three values that I just dragged into the cards are actually number values, like sales has number data, profit has number data, and discount also has number data. So it is no hard and fast rule that you can only uh just grab the items with numerical data; you can also drag them with some other data, like from a textual data. So suppose we have region for the textual data, we can just grab it in the Field section. Then what we are getting by default is the central region. Uh, suppose we just add any other card, we add another card, and then again we just drag and drop this region field, then what happens is we are getting again the central region. But if you want to change this stuff, suppose you want to change uh the region from Central to say the last region that was present—three regions were present in our data, that is Central, East, and West; Central was C, that is arranged alphabetically, so Central was the first; W for the West was the last in alphabetic order—so we are getting West in the last region. Suppose you want to show some of the uh, say details, and you can just go to count, and what would it do is it would get you the that how many total records are there. So in our data, 1952 records are there, that means uh this is the total number of the records that we have got in uh our data or in our table, that is actually the number of times the region has occurred in our table. Or if you want to uh make sure you want to see that how many regions are there, then you can go with this count distinct option, and what will it do? It will just show you the distinct count of the region, that is how many regions were there. So actually there were four regions, I think, must be Central, east, west, and south; yes, there were four regions that we just got in the chart. So this would give us the count of the regions. If you want to know, you can just get the count of any of the data that you want.

Similarly, we can work with other things as well. Uh, suppose we just go back to our number card and we try to apply some things. So these are the different uh types of the values that we can apply, like the sum, average, minimum, maximum, count distinct, and count. This would again work similarly, that is Count would give you the number of the profits or the number of the total number of the records that have the value of profit, and these standard deviation, variance, and median are actually the advanced values which we would be learning later on. Then uh here we can actually get the percentage of the profit, but that would not work right now because we are getting only a single criteria; there is no criteria, that is we are filtering the records out of the total number of criteria, uh, so that would give you 100% every time, so it would not work right now. But if you want to get a percentage of the profit, then this percent can work very well, and if you want to remove this field, you can just click on remove field, and this card would be by default stored to default. So if you want to just grab something, you can just grab it; suppose I want like country over here, then what happens is you get uh the uh list of the first country that is present over there, and again it is a uh card.

In this video, I'm going to explain you more about the text cards in Power BI. So this is our Power BI, and all the tables that we had imported earlier on, they are just getting loaded right now. So first of all, what is a text card? We have already uh seen the usage of the text cards in the previous videos. This is actually a number card that is uh holding the total amount of sales being done, and this is a text card holding the name of the country, that is United States, and the uh name of the first region, that is the central region, okay. So uh let's just remove this United States card. So we have got four regions in total, and in which the first region by the alphabetic order is the central region, reg. So uh whenever we are using say text card, then what do we do is uh we usually don't search for something that is kind of like the first thing in a category or the first uh thing in a region; we don't want the central region to be shown like this. Usually, we, when we are working with the dashboards, we are searching for a region and that has made the maximum number of sales, or from where we have got the maximum number of orders, or from where we have fetched the maximum profit; that is what is our Center of focus when we are working with dashboards. So how can we work with it? I'm going to show you a simple example. Let's create a new page, and we are going to create a table over here. So that's a table, yes. So the uh table that I refer to in the region section was this OS table, and if we just drag some of these things like say region, sales, and profit into it, okay. So what do we get is region-wise sales and the region-wise profit. Over here also we can just get the count of order ID, so that is how many orders were placed in that particular region. If we just uh sort it with the sales, so we are now getting that east region has given us the maximum number of sales. Okay, if we are sorting with the help of the profit, so again it's the east region from where we have got the maximum profit. Now if we just want to sort by order ID, that is from which region we got the most number of orders, so that is the central region. So this is a criteria which may be used while depicting the cards in a dashboard. So this is what we are going to try to show, that is the region with the maximum number of sales, the region with the maximum number of profit, and the region where the maximum number of orders were placed. So if we talking about sales first, we would be covering sales; we would be getting the east region as our answer, that is what we are expecting. So let's see how can we integrate it with the help of the cards.

Okay, so let's just go back to this cards page where we have got the central region. Now we want to show the region with the maximum number of sales. Okay, so what we can do for that purpose is we can first drag this card over here. Okay, now uh when this card is selected, make sure that you expand this filters spin. Now what does this filters spin do? It accepts these different types of filters that could be applied over the different cards. Since the card that we are using is of a region or in the region card, the different fils that could be applied could be on the number of sales, profit, quantity, orders, etc. So how can we do that is as soon as you expand this filters pan, you can see there are these three things: filters on this visual, filters on this page, and filters on all pages. So filters on this visual can be used to apply the filters on the particular visual that is being selected; filters on this page would be applied over all the visuals of this particular page, that is page number three; and filters on all pages means all the uh filters that are available uh all the pages that are available over here, like from page 1 till page 5, the Fiers would be available. Since we are talking about only the visual uh this card, so we would be going with this region. So what we can do is add the data fields. So what fields we are going to add is the region field; this is the field where we want to apply a filter. Okay, now in the region field we have got these bunch of options; we have got a count of all the regions over here, and and right now we are getting a basic filtering. Okay, so if we just expand it, there are these different options of filters available; we can go with Advanced filter or we can go with top in. So what is this top in? This top in will show us the top amount or the top number of values that is associated with in. So we need to provide a value for n; like if we provide three, so it would show us the top three values; if we provide with five, it would show top five values. But if we want only the topmost value, we can simply provide the value of n as one. So I'm just going to click on top in, so it asks me that what do you want to do—you want to show items of either top or either bottom—so I'm going with top; I'm going to uh go with the region that has got the most number of sales. So what I'm going to do is I'm going to type here one, that is I'm going to get only one region, and y value means we need to add the fields on the basis of which we want to filter it; we want to filter it on the basis of sales for the region which has made the most number of sales. So what I'm going to do is I'm just going to drag this sales field into this buy value, and uh then what I'm going to do is I'm going to simply click on apply filter, and then you got to just wait for a second or a few, and you can see that our card is now changed into East, the region. Uh, the reason why we are getting this is because the east region is the one which has given us the most number of Sals. So that is how you can filter the criteria or filter the stuff. Suppose uh the next thing that we had in our table was for the profit, like uh if we just um sort it with a profit, again we are getting the east region. So let's just just try and apply it over here in this filters pan; instead of sales, we can just remove the sales value, and if we just drag this profit value, then what happens? Now in this card what we are going to get is the east region again. If we just click on apply filter again, it's going to be east region; there is going to be no change because east region is giving us the maximum profit. But the third quantity was the order ID; if we just drag this order ID over here and click on apply filter, then what happens? We get the central region as the answer; the reason why, because central region gave us the maximum number of orders, and that you can consult with the table itself. If we just sort it on the basis of the order ID, then we have get 566 order IDs associated in the central region, which means that 566 orders—the most number of orders were placed from the central region. Okay, so that's what we have got over here. Now there is one more thing; right now we are getting the um quantity for the top items; what if we want the bottom items? There is usually a contrast where you can get like the region which has got the most number of autos and the region which has got the least number of os. So we can work with it as well, only with this stop end filter; only we can just go to the show items, and instead of top we can simply select bottom, and now what we are going to do is since n is set to one, we are going to get the region which has the least number of ERS. So see this region is changed to South, and if we just consult back from our table, so yes, that is correct; South has given us the least number of orders with a total of 442 orders only. Simply uh that is how you can work with the cards; that's how you can just filter these cards; and if instead of the order ID you want to make sure to know that which region has generated the minimum amount of sales, you can just drag this sales field again and click on apply filter, then what happens is uh you would get the region which has given us the minimum number of sales; that's again the South Region, I think; yes, that's again the South Region. So the south region has given us the minimum sales, the minimum profit, and the minimum orders. So if you want to show the maximum stuff, you can just go with top m in the items for the top end filter; if you want to go for the uh least amount of the least value, you can just go with the bottom filters, and that's how it works; that's how you can just work with the filters uh using the cards. Also you can just um actually work with more than one value, but uh since the card is used to store or it is used to show only a single value, say suppose if you just provide it with three and apply the filter, then there must be no change because um see here if we just go with sales, okay, what happens right now? Okay, so the east region has got the maximum number of sales, right? If we go here, then in the top three we are getting uh central region as our answer, and if you see the central region has the is on the third spot of the sales, so it's giving us this as the answer; that's the central region, so it is not very much reliable to work with more than one values with a card, so it is advised to just go with a single one, and you can simply go and apply filter; that's the east region. So that is all that you need to know about the cards and the filtering that must be applied on the cards in Power BI.

In this video, we will see two very important concepts of Power BI, and these concepts are known as drill through and the slicers in Power BI. Now both of these concepts are used uh for the purpose of filtering the data on the visuals, and uh how they are different from the traditional filters that we have already seen is what we are going to discuss in this video. So let us start. First of all, I have opened up my Power BI, but this is a new sheet, so I can just go to file and open this uh file file 03, which is the existing file in which we have worked out on the previous videos. Okay, so uh it may take a few seconds to open up; in the meantime let us just discuss that what is drill through. So drill through is kind of a filter which is applied over multiple visuals that could span through multiple pages. Right now what we have seen is we have seen actually that how can we apply filters over a simple or single visual. Now we are going to see that how can we actually apply or select some value in a visual that is present in one page and how will it be applied over the visual present on the second page. Okay, so we are basically going to connect the two visuals present in different pages and apply filters with the help of drill through. So for that purpose what we are going to do first of all is um a simple thing; let us just create a bar chart, and let us open up our orders table. Now in the orders table we have different things, and uh one of them is known as region. So what we are going to do actually is we are going to just make use of this region thing, which we just scroll down; so here is this region; we are going to drag this region into this AIS field, and the values it is going to be of sales; so sales will be dragged in on the values. Okay, so what we have got in our bar chart over here is the region-wise sales of the different items right here. Okay. Now uh this is what is a simple bar chart. What we are going to do next is in a separate page we are going to create a visual; we are going to actually create a pie chart. Okay, and in this also we are going to add region, and we are going to add sales. Okay, so right now what is happening is we are getting all the four regions and how many sales were done in all those four regions. Okay, so this is a simple approach uh how you can work with it. Now with the help of drill through, what we can do is we can actually uh just use this kind of the bar chart that we created; we can select any kind of region from there, and only the value of that particular region will be shown over here in this pie chart, or vice versa, that is uh we can just select any region from the pie chart over here, and that corresponding sales value will only be shown over the bar chart. So what we can do is uh first of all we need to go to that particular Visual and that particular page which we want to filter. Okay, so we want to filter the values of the bar chart, so we need to just select this bar chart. In the visualization Spain there is something written as drill through, so you need to go to that; just scroll down, and you will see there is an option called add drill through Fields here. Here, so we need to add the fields through which we want to do the drilling; so the field is going to be the region field; you need to just select this field and bring it over here in the region column. Okay, so that is it, but right now you cannot see any change; how you can just see the change, you can just go to page seven where our pie chart is residing. Now what you can do is if you just hover over any of these options, you can see east region is given, sales is given, and another option that is right click to drill through is also given. Now this option ensures that um actually we can right click on any of the options, any of the regions over here, and we will be able to drill through them, why because we have added region as a drill through field in that um bar chart. Okay, so if you want to drill through, what you got to do—suppose I want to drill through South—so I can just right click over here simply, and I will get this option of drill through. When I expand it, there is the name of the page that is Page Six where the bar chart is.

Present so as soon as I click on Page 6, then what happens in an instant? I get to page six. This bar chart is now only having this South as the value; there is no other value present. Only the sales of the south region are shown to me, so this is the advantage of drilling through. Uh, actually, the main thing about drilling through is that it helps you to create links between the visuals present in different pages. But now what happens if you want to do a similar thing, like drilling through, but in the same page or on the visuals that are present in the same page? For that purpose, uh, we have something known as slicers in Power BI. So let's see how can we create slicers.

Let us just create a new page, that is going to be page 8, and what we are going to do is, uh, actually we are going to create a table. Okay, we are going to create a table over here, and what values are we going to add? We are going to add region; we are going to add sales, that is region-wise sales. We would get this data, and um, we are going to add like quantity. Okay, so let us just expand this table. So this uh table or this data, this visual tells us that um, in which region how many sales were done and how many orders were placed in that particular region. Okay. Now, uh, if I just sort it out on the basis of sales, that is in the east region, maximum sales were done; in the South Region, minimum sales were done. So that's a simple approach. Now what I can do is I can just add a slicer. So this is a slicer; its icon is present just beside the table icon in the visualization pane. You can simply click on it. What the slicer will do is it will just hold uh the different types of values. You can add fields in the slicer, and it would hold these fields. As soon as you add the slicer, it is connected to the table. Whatever field you select in the slicer, it would be um, this table would be just filtered according to that particular field. So suppose uh this is the field option of the slicer. If we add like, say, region over here, then what happens is in this slicer we get four things, that is the four regions: Central, East, South, and West, along with the check box. If I want to show only the value of the east region, then you can see this table is filtered itself; only the east region values are shown. If you want to show only for the south region, only South Region values are shown. Similarly, any other regions, you you can work upon. But if you want to show more than one value of the region, you can simply just control-click on that particular region, and then two values would be uh visible, like just control-click. So East is visible now, and South is visible, but there are obviously some of the modifications that are possible on a slicer. How can you do that modifications? You can just go to this format tab of the slicer, and here is the selection controls option. If you just turn this multi-select with control option off, then what happens is you are able to select these multiple options once again. Like if you just uh uncheck all, now none of them are selected. If we just click on South, then the south is shown. Now if we click on West, then both South and West options are shown. So this is how you can uh just go with multi-select. But if you do not want to go with multi-select, you can just go to again this selection controls option, and you can make sure the single select is turned on. Now this all turns into the radio buttons, which means you can only select one option from the list of the available options. Now you can either select South Region, west region, east region, or the central region.

Okay, so this was for about the text data, that is we had this text data in the slicer. Let us just delete the slicer, and our table would be restored to all of the values. Now what we are going to do is create a quantity slicer. Now is for this quantity, we are going to create a slicer. So let us just again bring up a slicer, and now instead of region, we are going to drag our quantity in this field. So let's just click on this quantity, and we have got this quantity over here. Okay. Now this quantity is between 1 to 167, that is because it is for the uh orders, not uh on the basis of the region. So basically what I wanted to tell you is if we just um uh use the slider, this is a slider that is available in a number or in any of the quantities that we get, so we will be able to get the filtering of this um slicers with the help of the slider, like you can just select any of those values, and the um corresponding values would be filtered. So this is how the slicer works. Uh, we have seen the text slicer; we have also seen the quantity slicer or the numerical slicer. In this video, we will be seeing that how can we insert the different types of buttons in Power BI, and what are the different types of actions that are possible through these buttons. So basically, the different actions are possible, but we will be focusing mainly upon uh the web navigation, the page navigation, and the back or forward buttons. Okay. So first of all, let us see that how can we insert buttons. So buttons are basically um, as you know, are used to accept the user uh events, that is the click event, and are used to perform any action. So to insert a button, you can simply go to this insert tab, and these are the different elements that you can insert: either text box, button, shape, or image, but we are going to work upon buttons. So you can just click on this drop-down, and these are the different types of buttons that you can actually insert in your Power BI, like the left arrow button, the right arrow button. So if we just click on the left arrow button, then what happens? This left arrow button is now present in our Power BI. We can just expand it. Now what we will do is, uh, if we just click on this um button in the visualization pane, we have got something called as action. So this action is triggered in response to the user click. If the user clicks on this button, or in the case of Power BI, if the user control-clicks on this button, then in that case this action is triggered. So what will this button do? It is what we are going to define with the help of this action. So let us just go onto this action and make sure that it is turned on, and then you can just select what type of action it will perform, like it can go to back. Back means that um whatever page was previously selected, it will go to that particular page. So you can just select back, and there is a tool tip, so you can provide it with the tool tip, say, go back by one page, or simply a go back message, you can give it. Okay. Now what happens is now our button is confirmed or configured of an action. If we just hover over it, we get go back. Okay. So for that purpose, let us first define a path. So I have created a new page, that is page 10, and then I came to page 9. So for me, the back page or the um immediate back page is page 10. If I just control-click on this button, you need to control-click on this button, then what happens is you come to page 10. Here you can see this: you have uh stepped backwards; you have come to page 10. That is how this back action works, and that's how the different types of actions can work actually.

Okay, so this was the first action. The second one is a web URL. You can just go to your button, and these are the different buttons you can add. So I'm going to add a blank button. So this blank button is actually a button that does not contain anything right now. So what I'm going to do is I'm going to add some text to it. So let's just bring it over here, and let's add some text. How can we add the text? Make sure that your button is selected to which you want to add your text. You can go to this uh button text option, and then you can just turn this button text on, and if you expand it, there is this option of button text, which is right now blank, but you can add any text for your button over here that you want. I'm going to add a simple URL uh which is nothing but uh say YouTube. So I'm going to add uh I'm actually going to type YouTube first of all. So this means that in my button uh if someone clicks on this button, he or she is sure that they will go to YouTube. Now this is a text, but there is no action associated with it; it's only just the text. Okay. So to associate an action, we need to go to this action uh pane or this action option. We have to first turn it to on, and then we can just expand it, scroll down, and the type of the action that we're going to choose right now is this web URL. So we can just select on this web URL, and then you can see we have got another option of a web URL. So you can just type the web URL that you want to follow. So I'm just going to type a simple https://www.youtube.com. Okay, that's the simple URL that I have typed, but you can obviously copy-paste the URL from uh to which you want your user to go, and then you can um just configure your button to that particular URL. In the tool tip, I'm going to just give it that it is going to be YouTube website; that's simple, and that's it. That's how your button is now associated with the YouTube. If I just hover over it, what do I get is YouTube website, which means that now my button is configured to the YouTube website. You can simply just uh control and click over it, and then what happens is your default browser window will open, and you would be taken to this particular URL. You can see the URL over here, that is youtube.com, but right now I'm not connected to the internet, so it is um not opening, but as you can see that the URL is now copied in the window, and you would be taken to the page of the YouTube if you are connected to the internet.

Okay, so this was about the second type of button, that was the URL action button or the web URL button in Power BI. The next button that is very important is known as the page navigation button. So as the name suggests, the page navigation button is a type of a button that helps you to navigate between the different pages. Okay. So for that, what do we need to do is we are on page 9, and we will be adding the page navigations button on this page only. We have created a page 10, which is a blank page, and we have again created a page, page 11, again a blank page, and page 12. These three are blank pages, and we will navigate to page 10, 11, and 12 using the navigation options or the navigation buttons present in page 9. So for that purpose, you need to go to buttons and again add a blank button. Now what you can do is uh you can just change its text slightly. So first of all, make sure the button text is on, and I'm going to just select or add the names of the pages, that is P10 for page 10. Then what you can do is you can simply just copy this, and you can just simply paste this. So what happens is this uh page is or this button is duplicated. Now all you got to do is just simply change 10 to 11. Okay, and then for the 12th page, we are again got a paste it; it's already copied, so we can simply just paste it, bring it down, and just change the button text to P12. Okay. Now these are the three um blank buttons. Now we are going to connect them to the different pages. Right now they are not connected to any page, so I'm going to show you for page 10. If you just click on this page 10, go to action, turn it back to on, then we can just scroll down, go to type. Now instead of back, we are going to select page navigation. Okay. Now as soon as we do that, you can see we have got another option in the web URL part; we got this web URL over here, but here we have got destination, which is the destination page where you want to end up when you are clicking on that button. So what is my destination? It is page 10. It is providing you a list of all the pages available in your current file. So I'm going to go to page 10, and the tool tip is simple: page 10. Simply you can just click on P11 button, and you can just change its type to say page navigation, select the destination as page 11, you can or skip the tool tip right now, and then you can just go to P12, uh again turn this action button on, the type is going to be page navigation again, the destination is going to be page 12, and you can skip the tool tip for now. Now what happens is if we just hover over it, so we have given the tool tip for page 10 or P10, and that's why we are getting this tool tip. If we just control-click over it, then you can see we have navigated to page 10. Again we can go to page 9, and now this time what we are going to do is we are going to go to page 11. So you can see as I control-clicked over it, page 11 is what we ended up, and again back to page page 9, and this is page 12. Again you can just control-click over this page, and you can see that now you are on page 12. So that is how the page navigation works; that's how you can navigate through different pages. Okay. Now this was about the buttons. All of these options can be done with the help of shapes as well. So let us just take a look at this. Suppose I enter an oval shape. Okay, that's simple. While it is selected, we can just format its line, fill everything. We can provide it with the title. Suppose we just provide it with the title, say oval like this, simple title. Then there is this action button present. If you just turn it on, all the actions that you performed with the help of buttons are possible in this shape as well. You can do everything through this shapes as well, and similar is the cases with images. If you want to insert any image and you want to perform any of the actions that we just saw, you can do that with the help of images as well.

In this video, we will see that how can we create a report in Power BI. So this report is going to be a very simple report. This is basically the application of all the things that we have learned up till now in Power BI. We are going to add some simple charts like the donut charts, pie charts, bar charts, tables, matrices, and make use of the slicers. Okay. So with the collection of all these things, we are going to actually create a report format in Power BI to make sure that you all understand that what is a kind of a report and how can it be created. So for this purpose, what I have done over here is I have already imported a table known as the orders table into my Power BI, and the next thing that I will do is I will be uh just adding some of the charts. Okay, but before that, we need to make sure that this thing is actually uh recognized as the report, so we need to give it a title. So for that purpose, we can just insert a text box. In the Home tab itself, there is this option of this text box. You can just go to it, and um you can enter any text that you want. So this is going to be a text box right here like this, and you can make sure the title is turned on, and this title is going to be Sample Report. Now you can uh make any changes that you want um like the alignment. I'm going to select a middle alignment for this, and the text size I'm just going to change it to say 32 like this. Okay. So this is my text box, Sample Report. Now what I'm going to do is I'm going to create this report on the basis of region. Okay. So so first of all, I'm going to create three donut charts. These charts will be about the region, and the thing that I'm going to show is uh the sales by region, the number of orders by region, and the quantities of the items ordered. Okay. So first of all, for that we can just create a donut chart like this. We can just uh resize it like this, and then we need to drag in the region field over here. Sorry, uh make sure that your donut chart is selected, and then you can just drag your region field, and the first thing that I'm going to add is sales, so region and sales. So this is the donut chart of region-wise sales. Uh, this is looking good, but I'm going to just change something, that is I'm just going to turn this legend off, and in the details labels, I'm just going to select all detail labels. The color is good; I'm just going to increase its text size a little bit by scrolling it down. So let's just change it to say 14 would be good, right, or um we can just uh resize it like this. Okay, 14 is too much, so let's just change it to 10. Okay, yeah, that's looking good, and I'm happy with this chart. So what I can do right now is I am just going to uh change its title, or I'm just going to Sales by Region is good; I'm just going to align it to the center. Okay. So you can just go to this title option, align it to the center, and increase its text size to say 15 or 16, right. So this is my donut chart, and I'm just going to uh bring it over here like this. Okay. Now what I'm going to do is I'm just going to actually okay, that is something that I must get rid of. Uh, this is the donut chart that I have created, and I'm just going to actually duplicate it right now so that I'm can just uh change. Instead of sales, I can bring up uh the quantity and the orders. So once it is selected, you can just press Ctrl+C and Ctrl+V; it would be copied and pasted. Then you can just drag it upwards like this. Okay. Now instead of the sales field, you can just uncheck the sales field, and you can just uh just select or just check on this quantity field. So Quantity Ordered by Region. Uh, I just want to get rid of this new thing, so Quantity by Region, that's what I want, not ordered, not new. You can just change the title to Quantity by Region. Then you can just uh again it is copied, so no need to copy it again; you can just simply paste it, and you can just um um drag it upwards like this. This time it is going to be the Order ID. So I'm just going to change its um let us just resize it a little bit. So these are the three donut charts: Order ID by Region, Quantity by Region, and Sales by Region. Instead of Order ID, let's just change its title uh to say that it is Order by Region. Okay. So that's Order by Region. That's the three donut charts. Then I'm going to add a bar chart, which is going to show the same thing, that is region-wise um sales, all right, like this, and let's just arrange it a little bit. Okay, that's perfect. Then what we are going to do is we are going to add another thing, and that is known as the line chart. This time I'm going to add actually the profit for the region, that is how much profit was done. So the fields are going to be the region field and the profit fields for this line chart. So we can see that in which region we got the most profit, in which region we got the loss, and everything about that. Okay. So we can just arrange it, and um after this, what we can do is we can add a

Table. So this is the table that I'm going to add over here, and this table is going to hold some of the values like, um, instead of the region, or instead of the table, I can just add a matrix in which I will be adding cities; I will be adding some kind of regions, and I will be adding the sales. Okay, so what is this uh matrix going to contain? It's going to contain City. Okay, so that's the thing; here is you need to select this Visual, and then you can just click on City. Uh, you can just click on region, and then you can just scroll down and select on the sales value. Okay, now you can just expand it a little bit like this so that you are getting everything over here. Uh, scrolling is possible; everything is there; that's perfect.

Now what we can do again is, um, we can just simply add a slicer; that is, if we want to just slice something, if we want to just um show something. So I'm just getting rid of this bar chart right now, and I'm just going to add a slicer. Okay, so let us just add a slicer. This slicer will be uh for the city, so we will be just um covering the city, or basically for the region; region would be good. So let's just create a slice for region. Now what happens is we have got four regions into the slicer, and let's just arrange it back like this. So this slicer will help us to just slice these regions. Okay, now whatever region we select, only its stuff would be visible; like if we just select the central region, only the central region would be visible in all of these, but that is not looking correct. So what we are going to do is, instead of region, let us just create the slicer for something called as City. We have got the list of all the cities in the slicer, and we can just select or view the values in a city-wise fashion; like if you want to uh view the value of any of the Cities.

Now another important thing in a report uh is that of a cards. So what I'm going to do is I'm going to get rid of this slicer because cards are extremely important; they are more important than the slicers. So I'm going to add some of the cards in the report as well. So this is a card; I just can click on this card, and what I'm going to get is the total sales, so uh or the profit. I can also create a card for a profit, so that's the profit card. Let's just uh resize it like this and change its uh position. So that's for profit. I'm going to create another card; I can simply just copy this card and paste it to duplicate it, and instead of profit, I'm going to show the total amount of sales. So this is going to be the sales value that is going to be added in this card, but before that we must remove this profit, and this sales would be added. So it is showing me that the total amount of sales done, the total profit obtained, the line chart for the profit, orders, quantity, sales through donut charts, and this whole table from where I can get any information that I want. So this is a kind of a report which you can create very easily in Power BI based upon all the things that you have understood up till now.

Now what is the next step? Is once you have created the report, you need to publish it. So how can we actually publish our report online? Uh, this is something that we are going to see in the upcoming lessons or in the upcoming videos. But as you can see that this is a simple report; if you want, your can add many more visualizations that you fancy. Uh, you can create reports for yourself, and you can apply as many things in it as you want. In this video, I'm going to show you that how can you publish a report using the Power BI service in Power BI. So this is our report from the previous video in which we uh created a report composed of a table, a line chart, two cards, and three donut charts. This was a report about region. Now in this video we are going to see that how can we actually publish this report to Power BI service. So for that purpose we have an option called as publish right over here; we can just click on it, and you required to sign in.

Now one important thing that you must know is that for signing in purposes you need to go with a corporate email ID, and you cannot go with like a Google ID or a Yahoo or Gmail or anything; you need to have a corporate mail ID for that purpose. So I have got a corporate mail ID just like this, and um this is for if you have already signed in. So have not signed in, so I need to go for a free account; I need to create an account in Power BI, and here we can just provide with our corporate email ID. I can click on sign up, and it would ask me to wait. Okay, so I can click on yes, and you need to just provide your details. So what I'm going to do is uh just provide programming knowledge; programming here and last name as knowledge, and the password I'm going to create a simple one, and again a password. So let's just confirm it; a verification code, so that is um 854 is my verification code, and you can just scroll down like this, and you can just click on start. Now this would help you to create your account, and you can either save it or not save it. So I'm not going to save it right now, uh, and if you want to invite more people for your workspace, you can just write their email ID, and you all would be clustered together; either you can just click on skip if you do not want to send invitations, like I just did; I clicked on skip. Now what I'm going to do is um just wait for a few seconds while this sign up is getting complete, and once this sign up gets complete, uh, you will be able to see something like this for yourself. Okay, and this is from where I just got my uh mail ID, that is a corporate mail ID. So you can just follow this link as well to get a corporate mail ID for 10 minutes. Then uh this is how your uh workspace is going to look like. Okay, now you can go to workspace or my workspace; this will show all the work that you have done up till now, but since I have not uh done anything, I have not uh got anything, so it is not showing anything. Okay, now I can go back to my Power BI, and I can again sign in by providing my email ID, clicking on sign in, and uh it may take a few seconds uh before it asks you to sign in for Power BI, and after you are successfully signed in, you will be able to publish your report into the Power BI service. The okay. So we can provide a password over here, and then click on sign in.

Okay, so what happens after this is you will be able to publish your report; like you can see uh first of all it asks you that where you want to publish. So yes, I want to publish to my workspace, and that's the only destination that I have got, so you can simply click on select, and uh there it would take some time to publish your file. Now one interesting thing is whole of your file would be published, which means uh if you contain a number of pages, all those pages would be published over the Power BI service. So you can see that uh we have got a success message, and if you want, you can just open your file from here in Power BI service itself, and it may take a few seconds, and you will be able to make out that um actually your file is now published; actually your report is now published like this. You can see all of your report is available in in Power BI. Now why we have done this? Like why we have published this report in Power BI service? The reason is when you are working in a cluster or with a number of people, then you may require to share your reports with others; it is not mandatory that you are the only person working on the report; you may need to share it with multiple peoples, only um with a few people, whether inside your department or into people uh spanning through multiple departments. So this is the purpose by which the Power BI was set up; this Power BI service was set up; it allows us to do different kinds of things with these reports; like it allows us to download this report; if you just click on download, you have got these bunch of option; like you can uh download in Power BI Desktop; this means that if you have got this report, if you have not designed this report, but if you have got this report from any other colleague, you can simply download it in Power BI Desktop, and then it would be available for you; or you can go with Power BI for mobile, which will help you to just view that report in your mobile phone itself.

Now another important feature or another important thing is this report is kind of interactive; like uh right now you see all the regions are visible, but if I just click on this east region, then what happens is I'm getting uh in these three donut charts only the data of the east region; in these two cards I'm getting the sales obtained from the east region and the profit from the east region, and in this table I'm getting all the records for the east region itself, and actually this uh line chart is distorted right now, but I'm getting the profit for the east region, which is 85291. Simply if we can just click on South, or if you can just click out, all of the regions are there, and if we just click on South, then we get all the reports or all the things about the south region. Simply we can go with the central region; we can go with the west region, or any region that we want. This means that this report is active as well or it is interactive as well. Okay. Now another thing is if you want to rep uh just edit this report, if you want to do anything with this report, you can do it very easily; you can just go to this edit report option, and there will be a uh possibility from where you can just edit your report; you will get all these things, that is the fields, this orders table, and this visualization pane, just like you got one for yourself in the Power BI Desktop. This means all the features of the Power BI Desktop that were available to us are now available through this Power BI service; all the um possibilities that were there are now possible through Power BI service with an added advantage that you can uh just save it, you can share it, you can uh perform as many operations that you want on this Power BI report; you can just perform them very easily.

In this video we will see that how can we use Power Query. So up till now we have seen how to use this Power BI Desktop app to use some of the basic Power BI controls, and then using this Power BI service account we have also published one of our report online uh which was created in this desktop app. Now the next thing that we are going to start from today or from this video is the Power Query. So the first question is what is Power Query? According to the official Microsoft documents, Power Query is a data transformation and data preparation engine; it comes with a graphical interface for getting data from sources and a Power Query editor for applying the Transformations. So in simple terms, Power Query is nothing but it is a method through which we can transform our data with the help of a GUI interface, and then there is something known as the Power Query editor which helps us to apply the changes or the Transformations over the data. All right, so uh before going into that Power Query stuff or seeing its practical example, we will first take a look at our data source which is going to be imported into our Power BI. Okay, so I have this data source which is um named which is in the form of an Excel worksheet or an Excel workbook; the name of this Excel workbook is Data Source Core Unclean, and I have two sheets over here; the first sheet is by the name of Concat, and the uh second sheet is by the name of Split. So if we take a look at this Concat worksheet, then we have got three columns; the first column is First Name, the second column is Midn Name, and the third column is Last Name. I have got eight entries of the different names like this over here, over here, and what I'm intending to do is uh no matter that these all columns are actually separated in Excel, but when I want to import them in Power BI, I want to transform them as a single name column, which means I want only one column of name in which all these three uh columns, that is First Name, Midn Name, and Last Name, will be concatenated or merged together. This is what I'm intending to do with this data. Then in the second sheet I have some data; um um the second sheet by the name of Split, I have some data in which there are two columns, Address and Quantity. So here you can see that the address is given in a comma separated values; I have the name of the state, the name of the country, and the name of the continent. So these are the three separate fields which are present over here, and these are all separated by comma, or these are comma separated values. For this data source, what do I want to do? When I want to import it into Power BI, I want three separate columns to form; the first column should be of State, the second column must be of Country, and the third column must be of the Quate, and the fourth column Quantity will remain as it is. So this is what I want to do with my data, and um this is how you can actually work with Power Query. So let's see that how can we import this data and how can we work with Power Query using Power BI Desktop app. So for that purpose, first of all you need to open up your Power BI Desktop. Then since our data source is from Excel, so you need to click on this EX button, but if your data source is from someplace else, you can just go to this Get Data option, and here are these tons of data sources from which you can import your data. So here also I'm going to select this Excel option, and here a folder will open; uh by default this folder is set for me; this path is set for me, so I am just going to click on it, which is the Excel workbook by the name Data Source Core Unclean, and then I'm going to click on okay. So after this you got to wait for a few seconds because a connection is going to be established in the form of a tunnel uh between Power BI Desktop app and the Excel sheet. So we have uh this data is now imported. First of all let us work with this Concat worksheet, so we can just click on this worksheet, that is Concat. Now you must notice one thing that we have imported these uh different things, that is we have imported a table, we have imported another table, and we have imported the Excel sheets as well. So what I'm going to do is first of all I'm going to show you that what happens if we just click on this table and what happens if we import this whole sheet instead of a table. All right, so this is the Table3 in which the First Name, Midn Name, and the Last Name columns are separate, which we are trying to concatenate. So I'm just going to import it first of all. Now what happens uh in general sense or up till now we've have been just clicking on load data button because our data was clean; we didn't require any kind of transformations in it, but in this case we want some transformation; we want this data to be concatenated first and then loaded into Power BI. So for this purpose we have to select on the second option, that is the Transform Data option, and when we click on it, then we get something that is known as the Power Query editor. So if we just click on it, then it may take a second or a few uh for processing the queries, and after this you will see something known as the Power Query editor. Now this was the Power Query editor that we were talking about earlier on. Okay, so Power Query editor provides you a GUI interface which is similar to any Excel or Power BI software itself, and you can just do any kind of Transformations on your data with the help of this Power Query editor. Okay, so if we uh try to work upon it, then what we have got is whole of a table is present over here with the uh different columns and all the data; what is the table name or whatever is the entry that we are working upon is present over the left side; over the right side we have something known as the Properties in which the name of the table is given as Table3; thus if you want to just change it, you can change it, and then there is this Applied Steps option which will show you whatever steps you have applied onto this table, and if you want you can just change the or delete any of these steps or repeat any of these steps as per your choice. Okay, so now what I'm going to do is I'm going to merge this data. So for that purpose what I'm going to do is this first column is selected, and I'm going to select these two columns as well. So for that purpose I'm going to use control click; actually select this whole column, then control click on the second column, and then control click on this third column. Okay, so when these three columns are selected, and uh make sure one thing that you need to select these columns in the straight order; like first the First Name column, then the Midn Name column, and then the Last Name column; then only this will work; otherwise the results would be distorted right. Then you can go to this Transform uh option or this Transform tab; here you have uh this option of Text Column; in this there is this Merge Columns option; if you just hover over it, there is the tool tip; Concatenate the currently selected columns into one column. So that's exactly what we wanted to do. So you can just click on it, and then uh what do you see is uh you need to use a separator. So uh generally a space is a separator, um but I don't think we have space in our data, so I'm not sure about it. So let's just go with space, and then uh the second option is which is optional; the New column name. So if you want to give your column or the new column that is formed a name, you can just give it; I'm just going to give it a simple name, and then you can click on okay, and here you can see that uh instead of those three columns, now I have got only one column by the name of Name, and whatever the data was there in those three columns, it has been merged into a single column here. All right, so this is how it's done, and now after you have just uh made some changes into your data and you have merged the columns, you can see in this Applied Steps option that this Merged Columns is now present, which means that yes, you have applied this step over your columns. Then you can click on this option Close & Apply. Now what will it do? It will just close your Power Query editor, and it will apply those changes onto your table. Now when the table is loaded into your Power BI Desktop app, this table would be merged into a single column instead of three separate columns. So that's exactly what we wanted right. So that is what we will get here. You can see that since this table is loaded, Table3, and we have got only a single column, that is the Name column; if you want to uh just view it, you can just uh select a table visualization over here, and you can just display this Name. All right, so it may take a few seconds, and if you just change its size, or you can just go into this Focus mode, you can see we have got this Name column with these uh entries that we just created. Okay, and since we just imported it into Power BI Desktop, it's all ready uh just um sorted into the ascending order, so that's how the Power BI works. In this video we are going to...

Learn more about Power Queries. In the previous video, we saw how we can use Power Queries to transform our data that was unclean, or if we wanted to just perform some operations over our data, then how can we do that with the help of a Power Query Editor? So we just combined the different columns of data into one in the previous video. In this video, we are going to learn some more functions that are possible on data with the help of Power Query.

The reason why we are stressing so much on this, or why we are covering two videos over this, is because Power Query is a very important feature of Power BI. Whenever you're working with data in real time or in any kind of organization, then it is a 90% chance that your data is unclean. It is not going to be like cleaned, with all records present, normal values, or everything in a proper format; that's rare. So you should know how to work with unclean data, how to clean it first, and then how can you load it into your Power BI. That is why we are going to cover this video also with Power Queries. So let's start.

First of all, let us take a look at our data source. So these are the three sheets on which we are going to work today. "Split" is the name of the sheet, okay. So here we have some five records, and all of them have a uniform status, or a uniform data is present on them. There is first the name of the state, then is the name of the country, and then is the name of the continent. So these are the three values or the three fields that are present in this "address" field itself, and then we have quantity. So what we need to do when we load this data into a Power BI is that we need to split them into three different columns: the state column, country column, and the continent column, okay. And then, um, this quantity column is going to be as it is. So let's see how can we do that. Let's go to Power BI and import our data from Excel. This is my data, that is "data sourcecore unclean," which is just an Excel workbook, the name of the Excel workbook. So you got to wait for a few seconds until this data is loaded.

Okay, so we have got Table Two. If you just select all this, okay, not this one, not Table Three, not Table Four, it's actually Table One, I think. Yeah, so Table One is what we are going to work upon. So you can just click on this table like this, and you will be able to see its preview and can just check it and click on "transform data," so that we can go to the Power Query Editor window. If you directly click on "load," it would be loaded into Power BI, and Power Query Editor would not be visible, so you would not be able to make any of the changes in your data, right. Okay, so here what we have is this data or this column which you want to change, which you want to split up right now. Um, one interesting thing to note over here is the format of this data. We have got what, like text over here, right? So you can see "ABC" written over here, which denotes that yes, this data is of textual type, and when in quantity we have 1, 2, 3, which means it's of number type. So that's like a quick fact. So in "address," what we can do is we can either go to "transform" or "add column"; both of them have the same functionalities or the same features, but the difference is when we click on "transform," this column will just remove itself and it will be replaced with three different columns, while if you click on "add column," three new columns would be added along with this column. Okay, so we do not want three new columns to be added; we actually want this "add column" thing, um, to be kept on for side; we want this "transform column" thing to come up. So let's click on "transform," since we want to split up this data, and this is a textual data, okay, that's why we just, uh, checked "ABC." So we need to go to this "text column format" or this "text column group." Um, make sure your column is selected, and there is a "split column" option; you can click on it. There is this number of options that you can choose from, but since in our data we have comma-separated values, so we can take comma as a delimiter or a separator; that's why we are going with this first option, that is by delimiter. So, U by default, a comma is selected as a delimiter, but in case you're wondering, there are these different signs that could add as a delimiter, or you can also add a custom delimiter for yourself. I'm going with comma and "split at each occurrence of the delimiter," okay, because so I want three separate columns; or if you wanted only like, uh, the state to be separated and the country and the continent to be crammed up in the same column, then you can just go with the leftmost delimiter or anything that you want. Then you can just click on "okay," and you can see that we have got states, countries, and continents. We can just double click over here and rename them as "States," double click "Countries," and double click for "Continents," right. So that's how it works; that's how quick it is, and you can just go to "Home" tab and choose on this "close and apply." So what will happen is in a few seconds this table would be in front of you; Table One would be in front of you and with the changes applied to it. Okay, so you got to wait for a few seconds, and yeah, Table One is now loaded. If we just expand it, so you would be able to see the columns, okay, like, uh, "Continents," "Countries," "Quantity," and "States."

Now let's work upon the next sheet. So we can go to, uh, "recent sources," um, that is "data source unclean." Here what we have is a Table Two in which what we are going to work; we have a product ID that is made up of like two letters or two alphabets, then there are four numbers, and then there are these, um, six alphanumeric characters. So what we want to do is we want to separate these values; although delimiter is given to us, but we do not want to work with the delimiter; we want some other way to separate them. So how can we do that? Let's just select this table, click on "transform data" to transform it or to, uh, make sure that the Power Query Editor is opened. Excuse me. Okay, now, so we have this "product ID" column. If we just go to this "transform" tab, so we have, okay, so, uh, before that you must make sure that your column is selected, and since it is a text column, that's why we are going here into the text column, uh, you can go to "extract." So how many characters you want to extract by length, uh, first characters, last characters, or range of characters, whatever you want to extract, you can just go with it, but since there are these three values, so we need to go to this "add column" option. If you just go to this "add column" option, you will find that there is this same function available for the text again. You can go to "extract," and let's see that how can we extract the first characters. If we just click on the first characters, then what happens is we get a count, that is how many characters starting from the left you want to extract. So I want to separate these two, uh, alphabets, so let's just say two and click on "okay." Now what happens is I get two characters from each record. Similarly, if you want to extract like the last characters, so last six characters I want to extract, I can simply click on six as count and click on "okay." Okay, so that is kind of a problem. Let's just, um, again work with it. I want to extract last characters. Oh, sorry, I just gave two. So if you want to just undo your work, you can just go to "applied steps" and undo it from here. I want to extract the last characters; how many last characters I want to extract? Six last characters. So yeah, uh, six last characters are present over here. Similarly, if you want to extract, uh, between the delimiters, this middle portion could be extracted between the delimiters, okay. So you can go to "delimiter." So the start delimiter is a hyphen, and the end delimiter is also a hyphen, and you can click on "okay." Okay, so yeah, it's not working. Let's just, uh, check it once again. Start delimiter is hyphen, and the end delimiter is hyphen, and click on "okay." Okay, so I don't know why it is working, but, um, yeah, it should work. So you can just go with, um, if you want to just check this middle thing, you can just go to "text between delimiters," and usually it works; I don't know why it is not working right now. And once you've done that, you can just go to "close and apply," and these changes would be applied; or if you want to just discard these changes, you can just exit this Power Query Editor and you can click on "not now" if you do not want to work upon with these changes.

Okay, now again to the recent sources, that is the "data source unclean." We have another sheet available with us, that is, uh, or another table available with us, that's Table Four, which is having, uh, like kind of names, the first name, the last name, but the difference is that we have not a proper case; they are not in a proper uppercase; they're not in proper lowercase; they're not in proper sentence case. So we have kind of a problem here. So for that purpose, what you can do is you can just press Ctrl+A to select all of this table, go to "transform," and here you have like "format." So, uh, in the format, a common convention for a name is capitalize each word, that is the first letter must be capital and all the rest are small. So you can just select on this one, and you see all of your, uh, names have been changed. If you want to just convert them all to uppercase, you can just click on "all to uppercase." Then we have "trim." What does this "trim" do? It would just remove extra spaces from the text, okay. "Capitalize each word" is what I'm going to go with. So this is how you can work with Power Query.

In this video, we are going to see that how can we use the Power Query and the Power Query Editor to manipulate the date and time data in Power BI. Okay, so the reason why we are using these date functions or the date data is because using the dates we can extract the different types of information. What all we can extract? We can extract like, um, the month, year, day, the day of the week, the week of the month, the week of the year, the current age, etc. There are a bunch of things that we can extract from the date data. So whenever we are trying to work upon Power Query or the Power Query Editor, whenever we are trying to practice it, then it is always advisable to work with the date data because it can give you so much information, and this type of information is not possible without any, uh, with any other data source but the date data. Okay, so for this purpose, we have got an Excel workbook by the name "date data source," okay, and the sheet that we have got is "data s" or "date Source" actually, and we have got at least nine dates over here. These dates are from different, uh, like, um, years, different months, different quarters, okay. So we will be using this data source. Why we have used this is because we have the variety of data over here, okay. So we would be able to make out a much more information from this data source than from any other. So this is going to be a data source for this video. Let's just close the Excel file. Why we have closed this is because whenever we are trying to use the Power Query Editor or whenever we are trying to import the, uh, Power Query, um, or the data into the Power BI, any of the data, then its original source must be closed first, okay. Then you can just, uh, go to this "data" group and go to "Excel," and this is the "date uncore data source source" that we are going to open. So we can click on "open," and you can just wait for a few seconds because this Power BI is going to make a connection with our Microsoft Excel workbook, and then once the connection is established, then when we, uh, then we would be able to see each and every table or each and every sheet that is available over there. So I had three tables and three sheets there, but in this video we are only going to talk about this Table One. You can just click on this Table One, and you will be able to see its preview for yourself. Okay, so this is the table; we have got these different types of dates, and you can just check this table and click on "transform data." Why we have clicked on "transform data" is because, uh, we want to open this Table One into our Power Query Editor; we do not want to load it directly; we want to make some changes to this data. So we can just open up it in Power Query Editor.

Okay, now you can see that, um, once we have opened this data in Power Query Editor, you can see its type is changed to kind of a calendar, okay. This means that this, um, column is containing date type of data, and it is automatically something that is taken out by the Power Query Editor; it is a smart tool; it, uh, usually for itself recognizes that what is the type of data, and it could just, um, change that particular data, okay. So if you just take a look at the "applied steps," so these are the three applied steps, that is "changed type," which means that it recognizes that it is of date data type and changed its type to date. Now, uh, we have two options over here: either we can transform the data or we can add the columns; both of the options are same, um, both of their functionalities are same; the options they provide to us are same, but the functionality difference is that "transform" is going to transform this column, while "add column" is going to add a new column. So we can just click on "add column," and what will it do? It would add new columns. Since our column is already selected, this "date" column, and you can see that this "date," um, from "date and time group," this "date" option is enabled, which means we can apply any of the date operations over this data that we want. If we just expand it, we have got these many options. So the first thing we are going to do is we are going to extract the year from this data. So how can we go? We can just go to "year" and select "year." Then after a few seconds, you can see we have got the year 2010, 2020, 1998, 2020, 2018, 25, uh, 2005. Okay, so these are the data that we have got. Now we can just again select this column; I want to extract the month. So you can just go to "month" and select "month." So you've got the month, that is 12, 4, 6, 3, and so on. Similarly, if you want to extract like day, so we can just go to "day" and "day" or click on "day," so the individual dates would be extracted. Now you can again go to this "date" column, and you can extract the quarter, that is which quarter of the year is this date belongs to. So you can just go to this option of "quarter," and there is this option of "quarter of the year," and if you can just select on it, then you can see we have got like the fourth quarter, that is December is in fourth quarter; we have, um, some 13th April, which is in the second quarter, and so on. So these are the different quarters that we have got. Okay, now there is one interesting thing to note; whenever we are talking about month, we have got the integer values, and if you just see its type, it is 1, 2, 3, which means it is a number type or integer type, okay. Now if I again go to this "state" column and I want some other data, like if I want the name of the month, if we talking about month, I want the name of the month; I don't want like 12, 4, 6; I want like December, April, June, etc. So for this purpose, I can just again go to this "month," and this is this last option over here, "name of month." If I just select over it, and you can see we have got the name of the months, that is December, April, and so on. So we can just, uh, delete this column. Okay, this is this "inserted month" step; we can just, um, cross it and, um, delete, so this column would be deleted, and we would be getting the name of the month, which is more readable than the number of the month, okay. Similarly, instead of this "day" column, uh, let's just get rid of this "day"; instead of getting the individual days, we can just get the, uh, day like a Sunday, whether it was a Sunday on that day or a Monday or whatever it is, okay. So for this purpose, you can again select this "date" option, go to this "date," and, and here we have "day," so "name of the day," the last option, that is "name of the day." If we just select on it, so we're getting Friday, Monday, Friday, and so on. If you want to cross-check it, so, uh, like 21st August, so we can just go to this calendar, uh, on 21st August; here was a Friday, okay. So what we are getting on 21st August is a Friday; that is absolutely correct, okay. So this is the accuracy of Power Query. And there is one more, uh, important thing that you can again go to this "date" and find out the age. What is this age means that whatever the day is mentioned, from that day to this current date, today is third of October 2020, so from that day to the current day, how many, uh, days have passed, how many months have passed, or how many years have passed. So this is, uh, what we can make out from this age factor, okay. This "age" option, you can just select on "age," and you can see that we have got these different types of, um, things. This is like the duration, which means in days. So if you can just go there and, um, actually I want years from here, so you can just select this "age," go to "duration," it is currently in days; if you want years, so I can just go to "total years." So now I'm getting the years option, okay. So, uh, if I want, I can just, uh, delete this "age" column of the days, and I can just get the years, okay. So we cannot just delete that column because we are getting that from here only, so you can just go to "date," uh, again select "age," "duration," and we get "total years," and, uh, you can just see it's a decimal number. So if you want, you can just select the whole number, so that is, that means someone who was born in the year 2010 is having his age as 10 years, and 2020 is having age at zero years. Why zero? Because it is some days which is not present in the whole numbers. So this is how you can just get the age of the person, um, like this, okay. Apart from this, we have other options as well. Suppose, uh, you can just go to this "date" option; there are these bunch of the options that you can just get, like if you just select on this "date" only, then what happens is this exact date is, um, again shown over here, uh, actually this works well when there is a combination of the date and the time data present, uh, so you can just extract only the date data or you can only extract the time data. So that's how it works; that's how, how the different types of information or different data you can just get from your date data in Power Query Editor.

In this video tutorial, we are going to take another look at Power Query, but in this video we are going to perform some of the different operations over the date and the time data using Power Query. Okay, so we have seen some of the date operations that we can perform through Power Query Editor in Power BI in the previous video. In this video, we are just going to extend its functionality and see that what all other operations are possible. Okay, so mainly we have two...

Sheets: the data and time sheet and the difference sheet in our "date uncore data source" workbook of Excel. If we talk about the date and time sheet, so we have two columns over here; a two-column table is present. One is for the time, and the other is for the date. So, in this, we will be seeing that what all operations can be performed over the date data and the time data in Power Query Editor. The next sheet we have is a difference sheet, which is basically, uh, just the two dates: like date one and date two. So, basically, we are going to find out the difference between the two dates, okay, or how many days, how many months, or what is the duration between these two dates. Okay, so that's the simple thing that we are planning to do in this video. So let's see how can we work with it.

First of all, let us just close our Excel workbook and open up Power BI. Let us just import this Excel data as we have recently imported it, so it is present in the recent sources; that is, "dataor data source". So, you can just click on it, or you can just click on Excel and then select the data source from the folder location where it is present to just import it. Okay, so if we just click on table two, so this is the table for the time and the date column; this is what I'm going to import first. So, let us just check this table and click on "Transform Data". By clicking on "Transform Data", we are ensuring that uh we can open this table into a Power Query Editor for making some of the changes, as we wanted to make some changes over here. All right. Okay.

So now, the first thing is let us just check its data type. So, it is a kind of a calendar with a clock; so this is kind of a time data type, if you can just say it's date-time type. Okay, and if we just click on uh this one, it's a date data type. Okay, uh we can just change it to the time data type and click on "Replace Current". So what would happen is uh the dates that were present have been gone; actually, we didn't write any dates in the Excel sheet, if you just recall it. But the reason why we were getting it because it was kind of a datetime data type uh taken up by Power Query Editor. As we already know that the Power Query Editor, being an intelligent tool itself, finds out the type of the data, so it found out to be a daytime data type, and by default, it added the first date value that it had. So uh that's why we were getting it, but we didn't want the date data because we had another column for the date data, so we have just omitted it by changing its type to time.

Okay. Now there are two options: either "Transform Column" or "Add Column". Both of them are used to perform the functions over the time data, and the difference is the "Transform Column" would transform or change this particular column, or the "Add Column" option would add a new column. So, as soon as we clicked on "Add Column", and since our time column is selected, you can see from "Date and Time" group, this "Time" is enabled; all the other things are disabled, like we cannot perform any of the numerical functions over here; we cannot perform any of the date functions over here, or the duration functions over here; that is another advantage of Power BI. Now, if we just click over it, we can see there are these different options available to us. Okay.

Now, one interesting thing to note: if we just uh remove this step, then what happens is we are getting uh this date and time both in this uh field. Okay, we can just change its type to date/time, so we are getting date-time, that is 31st December 1899; that's the first date in Power BI by default. So that is why we are getting it, and we are also getting the time, but we only wanted time. One step I showed you that you can just change its type to kind of time, or what you can do is you can just select that column, go to this time column, and select on "Time Only", or what you can do is you can just go to "Date Column" and select on "Date Only" to just extract either the date or either the time only thing. Okay, uh so that totally depends upon you, whether you want to extract the date or whether you want to extract the time. So I want to extract the time, but I don't want to add a new column; I just want to transform it. So I can just go to the "Transform" tab. Here also you can see the time function is present over here, so I can just click on uh "Time Only", and you can see that we have extracted time, which is uh shown over here in the extracted time steps. Okay, so the uh step is applied, all right, and now we have got the time data. So now let's just go to the "Add Column". What all operations can we perform on the time, like R? So if you just click on R, what happens is we get a new column uh by the Rs; like it was 12:30 p.m., so we are getting 12 I; R 2:21 a.m., we are getting 2 I; and 10:45 a.m., so we are getting 10; and so on; we're getting all the Rs mentioned over here. In the same way, we can also extract like um this is a time thing, so we can just click on it, and we can also extract like minute. So if we just click on it, we are getting like 30, 21, 45, 21, 34, 12, 12, and two for each and every one of them, which is absolutely correct. And the third option we have is for the second. So we can just click on this, and you can see that we have got the individual seconds as per written.

Now, one interesting thing is uh okay, so for this date, let's just change its type to date, and let's just control first; I have selected this time option, and now let's just control-click on this date column. Okay, so these two columns have been selected. Now if I just go to this time option or this date option, and I'm getting this common option "Combine Date and Time". So if I want uh to combine them if they are splitted into two columns, I can just combine them into a single column as well. So I can just click on this "Combine Date and Time", either from the date column or from the time column. Since I was working with the "Add Column" option, so a new column has been created uh whose name is taken as "merged", and um its type is the time type, and I have merged the date data type along with the time data type for all these values. So if you want to just change its value, you can just double click over here, and you can just change the uh heading of the column as "date time". So what would happen is now we are getting date-time types over here. Now uh this is the operations that you can perform over the time data. As far as this date data is concerned, uh these are all the operations which we have already seen in the previous video, so it is no need to reiterate the things. Okay, so uh if you want to and just go to "Home" and select on this "Close and Apply" option, so that all the changes that you applied in your table would be uh applied over there, and then the updated table would be loaded into the Power BI Desktop app for usage.

So this was one table; we have another table uh that was the difference table or the difference sheet in which we had the table. So we can just go to the "Home" tab, "Recent Sources", uh "dataor data source" once again, so that we can perform over our op operations on table five. So this was table five with two dates given: date one and date two. So let's see what happens; we can just click on it and select on "Transform Data". So we basically want to find out the uh difference between two dates. Okay, so how can we do that? Let's see. Okay, so if you just take a look over here, we are having a mixed match of values. Okay, uh so what we are going to do is uh first of all make sure that both the columns are selected. So what we can do for this; this is very important, sir; we cannot just select all these columns using Ctrl+A like this; no, this would not work. Why? Because um for any kind of difference thing, whether whenever we want to find out any kind of difference thing, then one interesting thing to note is you must know the series; like uh firstly we want to select this column, and then we need to control-click this column. Okay, so this gives us a kind of a series; like from this we want to subtract this. So this is very important whenever we are talking about subtraction and stuff, that you need to know the order; that you need to communicate the order to the Power Query Editor that this is the order in which you want the operations to be performed. All right. So we have taken up the order; let's go to this "Add Column" option, and here in the "Date", you can see that there is this option "Subtract Days"; there are very least options available; so one is the "Subtract Dates" option. Okay, so if we just click over it, then what happens is we are getting some of the negative values and some of the positive values. So if we just change its type to a whole number, okay, then what happens; basically, we are getting the values; that is what is the difference between them; how many days um difference from uh first um sorry, 4th January 2019 to 3rd March, or the thir December 2010; we are having the difference of these many days, and whatever it is, we are getting the difference between the days um between these two dates. Okay, uh another thing is that you can just select these two columns once again by control-clicking them; select this whole column, control-click this whole column, and if you go to this "Date", we are having two more things: like the "Earliest". If you just click on it, then what happens is, out of these two columns, whatever is the earliest column is listed over here; like if you can see uh in the first row, this column is the earliest, so it is listed; in the second row, the first column is the earliest data, so it is listed over here. Similarly, there is another option that is the "Latest". So if you just go to "Date" once again and click on "Latest", so the latest uh column or the latest date would be listed. Now, if you want to get only the positive values, you can just select both of them and go to "Subtract Dates", and then you can see that you're getting all the negative values basically, or you can just uh change the order; that is "Latest minus Earliest" to get the uh correct or the positive values for subtraction. So you can just go over here and control-click over here, go to "Date", "Subtract Days", and you can see we are getting all the positive values. So that is the power of uh the Power Query Editor.

In this video, we are going to see uh that what are all the Power Query operations that we can perform over the the numerical data using the Power Query Editor. Up till now, we have used the Power Query Editor to transform or um basically manipulate the data that was of textual type or the date or the time datas. Okay, but uh in this video, we are going to see that uh what are all the Power Query operations that we can perform over the numerical data in Power BI. So for this purpose, we have taken up uh sample data set; that is, "P6 Superstore US 2015"; it is in the form of an Excel workbook, and we have three Excel sheets or tables: that is the orders table, the returns table, and the users table. If we just go to this orders table, so we have three numerical columns that we are going to act upon; there are numerous numerical columns, but we are basically going to act upon these three numerical columns; that is the quantity ordered, the total sales, and the profit. Okay, so basically this is what we are going to work with; these three numerical columns are going to be our point of concern. So let us just load this order table, but um using this "Transform Data" tab. Now, whenever we are using this "Transform Data" tab, the table would be opened in the Power Query Editor, so that we can make some changes to it, which is exactly what we wanted, right? So let's just see how it works. Okay, so this is our data set that's been loaded; if we just hover over it, so these are the three things, right: the profit, the quantity ordered, and the sales data. Okay, so what we can do is we can just select these columns using control-click, control-click, and control-click; uh these three columns are selected, and we can go to "Choose Columns" and select on "Choose Columns". Okay, so basically we can just uncheck them; we are going to choose this [Music] um basically profit, quantity, and sales, and click on okay. So what happens with this is these three columns would be extracted. Okay, then we can just go to this "Add Column" option or "Transform Column" option; both are giving us the same options; the difference in the functionality is that "Add Column" adds a new column while "Transform" changes the selected column. So you can go to this "Add Column" option, and as soon as we just click on this any of the numerical value, we can see that from "Number" functions are um highlighted or they are enabled; we can perform the operations like all the standard operations are possible; the some of the the scientific operations are possible; the trigonometric operations are possible; the rounding of the functions are possible; and uh some of the information is possible; like uh you need to find out what is the sign if the number is even or if the number is odd. So for the profit column, we can just go to this "Information", and we can just click on this "Sign". So what happens is we are getting the sign; this is positive, positive, what is negative, whatever it is negative or positive, we are getting this output. So basically we can find out that from where we are getting a profit and from where we are getting a loss; a negative value in profit, we are sure that it is a loss, right? Now what we can do is um suppose we want to round out the profit, or basically we want to round off the sales value. Okay, so we can just go to the sales column, and we basically want to transform it; we want to change it to a rounded-off value. So we can go to this "Rounding" option, and we are going to "Round Down". Okay, so you can see now we have got the whole numbers, or basically these values have been rounded down. Similarly, we can also round off this profit thing by rounding it to down values; rounding down means the lower value would be taken; otherwise, the upper value would be taken. If you want to just check it uh you can see that um 4.56; if we just clicked on this "Round Down", then what happens is this 4.56 changes to four. Okay, but if if we just remove this and we go to this "Round Up" option, then this 4.56 changes to five. So this is the difference between "Round Up" and "Round Down". Whenever we are working with the profit or the sales data, general purpose is to round down the values so that we are getting a rough estimate and um basically um the lower profits. Okay, so that is what we have done; we have rounded down the profit values; we have rounded down the sales values uh basically. Okay, so we need to just round down the sales value as well because we just undid the step, so the sales values rounded off was also undone. So we can just again round down them, and with this the "Sign" column is also added. Okay, so we can just say that how many quantities were ordered and the total sales value of this particular quantity; that is for one quantity, and this is the total quantity order. So we can just multiply both of them to find out that what is the monetary gain that we obtained; like uh one quantity or one thing was sold for like um 13 rupees or 13 whatever it is, and uh there were the four of total being sold. So for that, if you want to find out that what is the total amount of the um four quantities, so we can just write 13 into 4, and we would be getting the output as like 52. So that's a simple thing, and for that purpose what we can do is we can can just select these two columns by control-clicking them, and we can just go to this "Add Column" option because we want to add a new column of total sales or the total um monetary gain whatever it is, then we can just go to this "Standard" option, and we can just select this "Multiply". Now what happens is we are getting the multiplication values of each and everything; that is um what is the value that we get when we multiply these stuff with one another; like this you can see. Okay, so this is the multiplication value, and the uh name of the column that we have got is "Multiplication". If we want, we can just change it to say "Total", "Simple Total". Okay, so this is the output that we have got of multiplication. Similarly, if we just um divide uh like or uh this is the profit for one quantity, and if we can just multiply this profit and quantity thing, so we would be able to get a total of profit as well. So we can just control-click them once again and multiply them together uh like this, and let's just change its name to say "Total Profit", "Total Profit". Okay, so that's absolutely correct. Now we can see here. Okay. Now what we can do: if we just uh like subtract the profit from the total value, we would be able to find out the cost; that what is the cost that we are getting that commodity at. So for getting the cost, we can just write like "Total minus Total Profit". So this is the subtraction operation. So for the subtraction operation, you need to make sure one thing: that whatever the value of the column is, whatever the larger value is, you need to first select that larger value, then you need to select the smaller value; like first the larger value, and then you need to control-select the smaller value, because in Power Query Editor, the sequences in which you have selected the columns, they also matter. So the larger value and the smaller value; you can just go to "Standard" and click on "Subtract", and you can see that we have got the profit 36 rupees for the first one and some 23,000 for the second one. Now the question is over here: we are getting a total of 4,642, but we are getting a negative profit, which means we are getting a loss. So automatically what happens is this value is added to it, which means the cost uh that uh we bore was 5,830 rupees, but we had to sell it for 4,642 rupees, which means we are having this much of loss. So we can just give it as the cost price like this, so we would be able to get the total cost price of the things that we have um uh got, all the different things. Okay. Now um basically these are the different ways through which you can just perform the operation. Also there is one more thing: like this is the cost price, and if we just divide this cost price by the quantity, so we would be able to find out the individual cost price; like right now we are finding the cost price of the whole; like if there are these four quantities, so we are finding out the cost price of these four quantities; that is this 36 is for four. So if we want to find out the individual cost price, we can select this cost price and uh we can then just control-click on this quantity thing, and we can go to "Standard" and click on "Divide". So here is the division performed, so we are able to find out the individual cost price; that what is the individual cost price uh of the items. So we can just rename it as "Individual CP"; CP is for the cost price. Okay, so this is how you can just get the individual cost price, and these are basically the different uh operations uh possible over the numerical data in Power Query Editor. In this video, we are going to see that how can we use the Power Query Editor to just uh

Merge the tables, the different tables, into one another. Okay, so basically, uh, what we have taken over here is, um, some of the tables, uh, in Excel. We have created some of the tables in Excel. There are these different tables spanning across the different Excel sheets, and then we are going to see that how can we merge them all into one using the Power Query Editor, which is a part of Power BI.

So first of all, let us take a look at our data. So this is Quarter 1 data; that is an Excel workbook. It is actually our source data, which we are going to act upon. So over here, you can see that we have three sheets available: the Jan 2020, the Feb 2020, and the March 2020. So basically, this is dummy data; uh, this is not the actual data. It is of three quarters of the first quarter and the 3 months of 2020. Okay.

So if we take a look at this March table, we have three columns: the date column, the product column, and the quantity column. So basically, this is dummy data; that is why, uh, what we have taken is the three, uh, basic columns: the date column, product, and the quantity column—that is a text, date, and a numeric column. Okay. And what we are, uh, trying to do is we have listed the seven records for the, uh, seven days of March, and and um, uh, basically, uh, what we are trying to do is we are just, um, trying to get the quantities of the different items. Okay. And, uh, the format of the date is DDMMYY; so it is the date first, then the month, and then the year. Similarly, for the February month also, we have the same kind of data, uh, but we have the different quantities and the different dates—that is for the Feb, we have the dates right now—and, um, that's the products; the products are the same, but the quantity and the dates are different. Similarly, for the January also, we have the data for the month of January, where the quantities are different. So these are the three, uh, sheets and the three tables that, uh, we have created, or we have in our Excel sheets. And what we want to do is, in Power BI, we want to import all three of them, but we, we want to get the data of the whole of the first quarter. Like, we do not want these three different sheets to be visible to us; we don't want these three different tables to be visible to us. We only want these, uh, all these to be, uh, clustered together in the form of one single table, and using that table only we are, uh, trying to just view our data. So this is what we are trying to do. Okay.

So let us see that how can we do, do that in Power BI. So first of all, let us go to Power BI, and what we can do is, uh, since our data is from Excel, so we can just go to import data from the Excel, and this is our workbook that is Quarter 01 data. You can just click on it and click on open, so that this data is loaded into our Power BI. Now, for here, you need to wait for a few seconds till the Power BI loads that Excel data into itself, and once it's loaded, you would be able to see the different sheets and the different tables. Now, if you just, uh, click over any of the table, you will find the table over here, and if you just click on the sheet also, then since in one sheet there was only one table, so we are able to find the tables for ourselves. Okay. So whatever you want to do, uh, whether you want to import the tables into your Power Query Editor or whether you want to import the sheets in the Power Query Editor, in this case when there is only one table in one sheet, then there would be no difference. So if you want to import the tables, you can go with it; if you want to import the sheets, you can go with it. So I'm going to go with the tables only. So let us just select these three tables; these are the three tables which I'm going to import. Now, what is, uh, the goal over here is I want to make some changes to the way the data is loaded in these tables; that is why I need to click on Transform data, because this Transform data thing will help me to just go into the Power Query Editor. Okay. And you need to wait for a few seconds till this data is loaded. So you can see now the data is loaded; we have the Table 1 that is for January month, because we are having all the, uh, like months for January, then we have the February month, and then we have the March month. Okay. So the format of the data is DD MM YY, uh, that is the date first in the month and the year. Okay.

Now, what we are trying to do is we are going to append these three tables into one. So how can we do that? So for that purpose, uh, what we can do is we can go to this Add column options, and here we have the option, or we can just go to this Home tab only, and in this Home tab we have this option of Append Queries. Okay. So, uh, this is actually a part of the Combine group. What is this Combine group? This Combine group actually helps us to combine the different tables or the different like data into one another. Okay. Now here we have two options: the Merge Queries or the Append Queries. Now, whenever we are trying to like append the queries or append the tables into one another, we can go with Append Queries. What does appending mean? It means that you can list one table, then we second table, and the third table one after the other. So that's exactly what we want to do, right? So that is why we are going for Append Queries. If you just click on this drop down, we are having two options: the Append Queries and Append Queries as New. So what happens when we click on Append Queries is an existing query would be appended, and the existing query would be changed, which we do not want. We want to create a new query because we do not want, uh, our original data to change; that is why we can just go to Append Queries as New option. If you just click over it, and um, then you need to wait for a few seconds for this dialog box to appear. This is this Append dialog box which allows us to concatenate rows from two tables into a single table. But the question is we are not having two tables; we are having three tables. So, uh, not a problem; we are having the data, uh, or the option button here as three or more tables. So we can just go to this option, three or more tables. Okay.

Now, as soon as I click on that, what happens is, uh, you must see that over here in our Power Query Editor, this Table 1 was highlighted, or we were just viewing that Table 1. So this Table 1 is present by, uh, default over here. Okay. Next, we have Table 13, that is having the data for the month of February. If you want to just cross-check, you can go to Excel and go to this Feb 20 sheet, and, uh, here you can see that Table 13 is the table which is containing the data for the month of February. So you need to make sure that the correct order of the things is also known, because then only the tables would be appended in the correct format. Okay. So we know that Table 13 is the second table for the month of February; we can just click on it and click on ADD button. So this table would be added in this column, that is Tables to append, or you can just double click over in the available tables option, and that table would be added as well. If you want, you can add any table multiple times, so that that table would be appended that number of times into our final query, but if you don't want that, you can simply just delete that particular table like this. Okay. Or if you want, you can just change the order of the tables as well using these, uh, arrow buttons over here. So I'm happy with the order of Table 1, Table 13, and Table 134—that is for the month of January, February, and March—and you can just click on okay. And now you can see that we have got a total of 21 records; that is because each of the sheets had, or each of the tables had seven records each, and there were three tables, so we were supposed to get 21 records, and that's exactly what we are getting: 21 records. First seven are for the month of January, then the seven are for the month of February, and then the seven are for the month of March, and we have got all the products listed over here, and we have got all of their quantities. Okay. Now, if you want to, uh, make any changes to this data, like you want to only show the, um, binder data or the bean back, back data, binder and the bean back data, you can just select that and click on okay, so only that data would be visible. So all the filters, etc., could be applied, right? And, um, uh, basically, this is the query that we have created. Now, if you want to load your query, so you can just click on Close and Apply, so this Power Query Editor would be closed, and whatever changes you have applied into your query, it would be, uh, applied, and, um, it would be loaded into your Power BI. Like you can see, we are getting Table 1, Table 13, Table 134—that was the original tables—and the Append1; this Append1 was the name of the query that we had just created by appending the tables. So you need to wait for a few seconds, and then you can see that this Append1 is present over here. So if you want, you can just drag this, uh, you can just expand it, and then you can just drag it, okay, like this: date, product, and quantity we are having. So if you want, you can just get this date here, and you got to wait for a few seconds till it is loaded. Okay, actually, it has by default created a chart, so that's not what we wanted; we wanted, uh, to create a table, right? So you can just click on Table and drag like date over here, the product over here, and the quantity over here. Now we can just, uh, get it into the focus mode. So you can see we have got all the quarters; we have got all the days, and we have got the month: January, uh, February, March—whatever the month is, we have got that.

In this video, we will discuss about, uh, the Power Queries only, and how using the Power Queries can we actually, uh, like work upon the data. We have seen that how can we merge the different tables into our, um, Power Query using the Power Query Editor. So we had the data in our Excel sheet that consisted of the different tables, uh, spanning through different sheets. However, in our previous video, the data that we looked about was uniform data; means we had three columns in all the sheets, in all the tables—that was the date, product, and the quantity columns. Okay. But now this video, what we have done is we have some, uh, somewhat changed that data; we have updated that data; we have added some columns in the data in some tables, while some of the tables are as it is. Like if you see this January 2020 sheet is having the first table, that is Table 1; it is as it is, like date, product, and the quantity columns. Then if we take a look at this February 2020 sheet, so we have added the sales column, which is showing the total sales done in that particular day, and, uh, we have added this column. So the data is different now. Okay. And similarly, in the March, um, sheet also, in the Table 134, we have created a new column that is of returned items—that is how many items were returned on that particular day. So this is the numerical column that we have added. Now the question is when the tables have like different columns, or the different types of columns, or the different number of columns, then also is it possible for Power Query Editor to just append those tables into one another, or will it give us an any kind of an error? So well, uh, this is the question that we are going to answer in this video. So let's see its practical example. Let's go to our Power BI once again, and we are going to just import our data from Excel once again, or what we can do is, so we can just, yeah, we can just import the data from Excel once again. This is Quad 1 data only; let's just click on open. So it may take a few seconds to open, and you can see that we are having this sheets over here with sales data; this is the quantity, and this is with the returned items. Okay, that's the updated data, and in the tables also you can see the data has been updated. All right. So we can just select the tables which we want to import—that is these three tables—we wanted to import, and let's just click on Transform data. Okay. Now what happens, uh, it may take a few seconds to open it up in the Power Query Editor. Here you can see that we have Table 1, that is the original one; Table 13, that is having the sales column; and the Table 134, which is having this returned column. Okay. Now let's see what happens if we try to, uh, just append them together. Okay. So we know that to append them, we need to go to this Home tab, and here in the Combine group we have this Append Queries option. So we can just go to Append Queries as New, and, uh, okay, this dialog box is in front of us; we can click on three or more tables. We have Table 1; the second instance that is already added, so we do not need to add it again; we can just add this Table 13 and Table 134, all of the second instances. Okay. And then once we are happy with it, we can simply click on okay. Now let's just see what happens. Okay. So in Table 1, uh, we had the three columns, okay, the three original columns—that was the date column, the product column, and the quantity column. Okay, the sales column was not present, and the Returned column was not present. So what the Power Query Editor has done is it has, uh, updated those values with null; means, uh, whatever the number of columns is like in the second table, we had a sales column, so the sales column is added over here, and in the third column or the third table we had a new column called returned, so this returned column is also added, but only for the values that it had; like for the March value or the March data, we had this return value, so it is added over here, and all the rest of the values are termed as null. Similarly, for the sales column also, only the February, uh, days have the data for the sales, and all of the rest of them are appended or provided with a value of null. So this is possible in Power Query Editor that, uh, we can actually, uh, combine the data of the different columns, um, of the different sheets or the different columns or the different tables, and it would just append them all together and replace the values with null. So that's how we can work with, with it. Right. Now there is one important question: is this the query named is Append2? Okay. Here you can see that in the property settings its name is Append2. So if you want, you can just change its name to like Merged Columns or Merged Queries. Okay. So what would happen is now we would be getting our new table; this is this whole data with a name Merged Queries or Merged Table, whatever name you want to provide, you can just give it any name, then you can just click on this Close and Apply option, and the changes must have been applied. Okay. So you need to, uh, just wait for a few seconds, uh, until and unless this Merged table is created. So, um, after this, there is one interesting thing to note that, uh, we would be just, uh, getting this; if you just take a look at this field table. So let's just, uh, get rid of this table first. Okay. So here we have this Merged table, and apart from this Merged table, we are also having the second instance of like Table 1, second instance of Table 13, and second instance of Table 134. Now once we have merged the data from these three tables, we do not want this data to be visible anymore; we do not want our raw data to be visible to anyone to whom we are sharing our Power BI report; we only want the final data to be visible, because we do not want to, uh, them to know, or we do not want our clients to know that what was the original data and how we have changed it to the one that they are getting right now. So for this purpose, we only want this Merged table or this Append1 table to be shown to the user, to the end user or to the client, and not the rest of the tables. So how can we work on that? So for this purpose, you again need to open the Power Query Editor window. Now the question is how can you open it? So if you go to this Home tab only in Power BI Desktop app, you will find this Queries option, and in this Queries option there is this Transform data option, and you can see that it would open the Power Query Editor. So we can just click on it, and it would again open the Power Query Editor. Okay. Now if we talking about the Merged table, now in this Merged table, Table 1, Table 13, and Table 134's second instance was the source data; we do not want the source data. So if you want to remove the source data in the queries span on the left hand side, you can just right click on that particular table, and here is this option of Enable Load that is shared right now. So you can just uncheck it, and, uh, basically, you can just click on Continue. Uh, similarly over here also, you can click on Enable Load, uncheck the Enable Load option, and click on Continue, and here also. Okay. So what would happen is you see these three are changed to italics, which means they would not be loaded; we have unchecked the Enable Load option, which means they wouldn't be enabled; now, their load is not enabled, so they won't be loaded. So now if you just click on Close and Apply, then you can see that, um, after a few seconds after it's been applied, the changes have been applied; you would be able to see that this Merged table is only present, and the second instances are no longer present, why because we have changed it. Okay. Now one important thing to note: if we just get our table, uh, over here, and if we try to just view this, uh, table Append1, so we getting only these three columns. Okay. But if we getting this Merged table, so we are getting these five columns over here. But if we just take a look at Table 1 or Table 13, so you can see they have not been updated, although we have made some changes into our Excel sheet, but this Table 13 or Table 134 have not been updated. Okay. So, um, if you want them to update, you can just go to this Queries option, the Home tab, the Queries option, and can click on Refresh. So what would happen, uh, when you're, uh, trying to do that is the relation, uh, the basically, uh, connection would be established with the original Excel sheet from where you have got the data, and now you can see that in Table 13 we are getting the sales column, and in Table 134 if you see we are getting this returned column. Similarly, in this appended query also we are getting this returned and the sales column as it is, why because we have, uh, made some changes; we have refreshed the changes as per our Excel sheets in Power BI. So in order to work with data like this, you can simply click on Refresh, and all of your data would be refreshed automatically, and that is the power of using the relationships, because Power BI creates a connection with your Excel workbook or whatever the data you have provided; it does not copies the data; it just loads the data as a part of a connection; it establishes a link. So whenever you try to click on Refresh, all of your data would be updated.

As per the changes that you have done in your original data source, in this video we will see that, um, how can and we just, uh, merge the different Excel workbooks into one in Power BI. Why we are working with Excel? Because, um, in Excel there is a huge possibility that your data is in Excel sheets that you need to import into Power BI. Okay, so there are these different options or the different instances that are available with the working with the Microsoft Excel data. We have already seen that what would happen if we just, um, merged the different tables or the different sheets' data into one another. Then what would happen if we just merged the different tables having the different number of columns into one another? In this video, we will be basically seeing that how can we merge the different workbooks into one another.

Okay, so if we take a look at our data distribution, we have this folder called Quarter One Sheets. Okay, so this is the whole folder that is containing all of our data that upon which we want to work. Okay, so if you just double click over it, we have three workbooks: Jan 2020, Feb 2020, and March 2020. These are these three separate workbooks; whatever data we have is organized into these separate workbooks, which we want to merge together. Right now, if we take a look at these three different workbooks together, we have first this Jan 2020 workbook. It is containing a sheet called Jan 2020, and in this sheet we have only one table that is containing the three columns: the date, product, and the quantity column, which we have already seen in the previous videos. All right, so these are the three columns. Similarly, we have the second workbook known as the Feb 2020 workbook, which is also having this Feb 2020 sheet, and the three columns are there. And similarly, in the March workbook also, the March 2020 sheet name is March 2020, and the three columns. Now we will be seeing in Power BI, using the Power Query editor, that how can we merge the data that is spanning through the different workbooks into one table or one query. So let's just go to our Power BI and let's just load it. So we can just go to Get Data and click on More.

Now, basically what we are going to do is we are going to load our data from the multiple workbooks. Okay, all these three workbooks are present in a single folder. So what we are going to do do right now is in Power BI, we have to select that we have to, uh, just get our data or import our data from the folder. So basically, we have to select the folder as the option, and from that particular folder we can just load our data. So that's what we want to do. So it's going to take some time, right. Okay, so you have get this folder option. We can just click on Folder and click on Connect and wait for a few seconds, still it asks us for that which folder we want to, uh, load. So this is the folder path, so you can just go to this Browse option and you can just select it from here. Or what you can do is, since we have already opened our folder, we can just copy this path and in our Power BI we can simply just paste this path and click on OK. Now you got to wait for a few seconds till that particular folder is loaded. Okay, so what we are getting is, uh, we are getting these six, um, basically workbooks. Why we are getting six? Because these three are opened up right now. So what we can do is we can just close these workbooks. Okay, Jan closed, Feb closed, and March closed. Okay, we can just click on Cancel, and we need to again just load this data. Why? Because there were three instances of the open workbooks already present there and three instances of the closed workbooks or the original workbook. So we don't want that; we don't want duplicate records to be, uh, loaded into a Power BI. That's why we have to cancel that operation. So one important thing that you must note is whenever you are working with this, um, type of data, you need to just close all of the data that you have opened. Like if you're trying to work with the workbooks, you need to close that workbooks. Now you can see that since we had closed those workbooks, now we are only getting these three instances: that is the name, extension, date accessed, date modified, date created, and everything. Basically, this is all the metadata, that is the data about the data. So this is what we have got. Let's just click on Transform Data. Uh, so what will this Transform Data do? Uh, it would open up the Power Query editor. Right now you can see we have got Quarter One Sheets, this whole folder, and, uh, the sheet name. Now, um, since we have got these three sheets' name and the Content column. Okay, so basically what is this Content column? Is whatever the data is present in these sheets is actually consolidated in the format of this Content column. If you just click over it, so you can see we are getting the name of the sheet or the name of the workbook along with it; we are getting the size. So it is basically the content; all of the content is present here that is compressed into the size or into these number of bytes. It is compressed and been stored into the this Content column. Okay, so now we are only concerned about this Content column and the Name column. So what we can do is we can just control click over, um, both of them, and, uh, what we can do is we can just right click. So there is this option of Remove Other Columns. Since we are only concerned about these two columns, we are only concerned about what data is lying inside the workbook and not the data about the workbook. So you can just click on Remove Other Columns, so that now only the data of the workbook would be visible to us and not any other data.

Okay, now once we've got that, what we can do is in this Add Columns option, we can just go to this Custom Column. This Custom Column helps us to add a new column as per our choice. So we can just, uh, change its name to Data, and we can use a custom column formula. So basically there are these different types of the formulas that you can use as per the data source that you are trying trying to, uh, import and whatever you are trying to extract from that particular data source. So what data source we have used? We have used an Excel data source. So we can simply write like Excel, and you can see the first option that is Excel.Workbook. If you just click over it, so Excel.Workbook is written. Why we have used this Workbook function? Because, uh, this workbook is where our data is originally stored, and when we are talking about this name Feb 2020, Jan, and March, these these are the names of our workbooks; that's why we have used this Workbook. Okay, so what we want to do is we want to extract this data that is pointed to by this binary format. So for this, what we can do is we can just write these parentheses, and inside these parentheses you can see we have a list of available columns from this particular source. So we can just select this Content column and click on Insert. Now what we can do is we can just click on OK, and you can wait for a few seconds to see that we have created a new column by the name of Data, and now this Data is containing these tables. So if you just click on this kind of an arrow option, uh, make sure that all of them are selected, and we can just click on OK. Now what happens is, uh, we are getting these six names, uh, we are getting its data, we are getting the items like what are the items, different items like Feb 2020 Table 13 and so on, and we are also getting its kind, that whether it is a sheet or whether it is a table. So these are the different types of data or the metadata which is about the particular sheet or the particular table. Now we do not want the data about the sheet or the table; we want the data inside the table. So into this Kind column, what we can do is we can make sure that only the tables are visible. We can apply a filter to make sure only the tables are visible, and you can see now we have three tables: Table 13, Table 1, and Table 134. All right, so basically we do not want this table data; we only want the data inside the table. Okay, so what we can do is we can just take up this Sheet column or the Workbook column, that is the name of the workbook, the Data.Data is basically containing the original data, and this Data.Name is containing the name of that particular table. Okay, so we want these three columns. Right now we can just right click and click on Remove Other Columns, so all the other columns would be removed, and we are left with the these three columns. Right now we can just rearrange them. So first we are getting the Name, then we are getting the Name, the Table, and then we are getting the Table Data. Now if you just click over this Table Data, then you can see we are getting the original data of that particular table. Let us just undo that step, or we can just select any of the, uh, cells, and you will see on the downside that all of the data, uh, is previewed to us. So now what is our purpose? Is we need to extract this data. So how can we extract it? We can just select this Data column, uh, make sure we click on these arrows; these are the different columns; these are actually the columns of our tables only, okay, like the Date, Product, and the Quantity column, and, uh, make sure this option Use original column name as prefix is unchecked. Otherwise, whatever the columns we are getting, like first is the Date column, so its name would be changed to like Data.Data.Date, which is not what we want. Okay, we just want the Date column, the Product column, and the Quantity column. So you can just select these three and click on OK, and you can see that now, uh, the different columns we have got that is the Date column, the Product column, and the Quantity column. The total number of rows if you check over here is 21, which is absolutely correct. Now if you want, you can just remove this column by just selecting that particular column, right clicking, and click on, uh, Remove. And similarly, you can also remove this Name column by right clicking and removing it. So we have got this table; these three tables that have been extracted from the different workbooks. And now if you want to load this data, you can just go to Close & Apply, and you will see that this whole, uh, data would be loaded up. So you need to wait for a few seconds till this Qu One Sheets is, uh, loaded into Power BI.

Okay, so now Qu One Sheets is now available in the form of a table which is containing the three columns: Date, Product, and Quantity, and that's exactly what we wanted. In this tutorial video, we are going to see the concept of Column From Example in Power BI. So, uh, first of all, let us understand that what is Column From Example. This is our data source, uh, where we have a table, and in this table we have two values or two fields: the Product Code field and the Product Name field. Okay, so if we just take a look at the Product Code, then we can find that whatever the name of the product is, the first three characters of that product name are used in the Product Code, and then there is some kind of an ear, and then, uh, separated by hyphen is some kind of a number. So this is the whole, uh, Product Code, right. So, uh, what does this Column From Example say us is that if you want to extract something or or if you want to perform any of the operations over the data in Power BI, then you can simply just give us a pattern or provide us with a sample pattern, and we will perform the same operation over all of the data. So this is what the Column From Example stands for; means if you want to do anything, if you want to perform any of the operations, then in that case you do not need to provide it with a function; you do not need to write any function; you do not need to write any code. All you could do is just provide with a pattern, and Power BI itself will learn from that pattern and apply the changes over all the data. So this kind of a feature in Microsoft Excel is known as the feature of Flash Fill. Let us just see that with the help of an example. So this is my table. Let us just extend it and delete this couch. Okay, so what I'm going to do is I'm going to just, uh, get this 2017. I'm trying to get this 2017 here. So I've typed this 2017, and now I bring my cursor to the very next cell. In the Home tab, I can just go to this Fill option in the Editing group, and there is this option of Flash Fill. So if I click on it, you can see that all the rest of the years are filled automatically by themselves, like 2017 and so on. So this is the feature of Flash Fill, which is possible through Power BI as well, and this Power BI feature is known as Column From Example. So let us see that how can we actually, uh, work with Power BI. So the name of the workbook is Column From Example. This is our sample data in which we are going to just split this data into multiple things. Okay, this is one table, and the second table is in this Dates sheet, where we have got this date, and we are going to see that what all operations can be performed over the date data. Now what operations can be performed through Power Query? We have already seen, but, uh, let us, uh, see this example in which we do not want to use Power Query; we do not want to apply any date functions; we can simply just provide the pattern, and Power BI will do all the things for itself. Okay, so let us just open up our our Power BI, and, uh, we need to just close this Excel sheet because, um, if it is open, then the Power BI may throw an error when we are trying to use it. So let us just import our data from Excel, which is this Column From Example workbook. Let's click on Open, and, uh, we, uh, need to wait for a few seconds till this data is loaded into Power BI. Okay, so Dates, uh, what's the name of the sheet and the splitting text for the sheet, right? So if we just go to this Date1 table and I think Table1, yeah. So these two are the, uh, tables, so Table1 and Date1 Table are the two tables that I'm going to import right now. Okay, so let us just click on Transform Data, why? Because we want to open it in Power Query editor before loading it into the Power BI Desktop app. Okay, so here our tables have been loaded, the Date table and the, um, Product table. Okay, let us just, um, see the values; we can just check the values. Okay, that is correct. So this is the Product Code column, uh, which we are going to act upon. Okay, so for this purpose, uh, we have two options: either the Transform option or the Add Column option, but we are going to add a new column. If we go to the Transform column, then we had to use this text functions, which we do not want to use; we want to use the Column From Example option. So we need to go to this Add Column option, and here you can see there is Column From Examples. So you can see that From all columns or From selection means, uh, how many columns are in the table? There are two columns. So if you want to create an example or a pattern from both of these columns at once, you can go for the first option, or either you can go From selection option, which means the pattern would be taken from whichever column is selected by you. So that is exactly what I want, that the pattern must be taken from this Product Code column. So let us just select this From selection, and over here what we can do is we can just provide a pattern. So suppose we want years, so I can simply provide it with 2017, and as soon as I press Enter, you can see that all of these years have been extracted like this. Okay, and, uh, here you can see that, um, this is the, uh, transform function that is being used; it's between delimiters from the column Product Code, and what are the different delimiters? It's written, and you can just click on OK, and as soon as you do that, you can see a new column is being added with all these years. So you can just write like change the column name to Years, and it's done. Okay, similarly, you can again go to Product Code, Column From Examples, From the selection, and suppose we want to, uh, just extract these four, uh, code. So we can just double click here, provide the code, press Enter, and you can see in the light gray option that all these codes are written, which means this is what the Power BI will extract. So you can just check the data, cross check the data once, and if you're happy with it, you can just click on OK. Now you got to wait for a few seconds, uh, okay, so this is applied, so we can just change its name to Code as well. Okay, now if you want to see that which function is been applied over this particular column, what you can do is go to this Applied Steps option, and here is this cog sign; you can just click on it, uh, so you will be able to see that it is a Text After Delimiter; that's the text function that has been applied, and the delimiter has been automatically provided from the start of the input, um, means the advanced options have also been used, and everything like this. Okay, so basically you do not need to go to the Text, um, uh, group and apply the text functions over the data if you are working with the Column From Examples option. Let us take another example that is From selection we want to do. Okay, so we need to come to this step and then we need to go to Column From Examples, otherwise that would be added, uh, before that, before renaming it, so it would cause a problem. So we can just write, suppose we want T Cha, that's the first thing that we want; we can simply write Tab over here, and if you want you can just change your column name from here only, like, um, Name. Okay, that's the simple name that I'm writing; it could be anything. Then here you can just press Enter. So it has not understood it; may be a problem or it may a possibility that sometimes Power BI does not understand that what it needs to extract, so you can provide more more than one input in that case. So you can see it has not still understand. So let us just write another input. Okay, so, uh, this is like it has given us, uh, this function that if 1123 then Tab, else if Code is 11189. So it is trying to, uh, get it from the Code; the reason why our Code column is selected. So what we can do is we can just click on Cancel, select this Product Code column, go to Column From Examples, and click on From selection. The reason why I showed you this was because the selection is very important; like right now if I just write Tab, Tab for Table, and as soon as I press Enter, you can see all these values have been taken by it automatically, and you can just click on OK, and after a few seconds you will see that a new Name column has been added. Okay, similarly, let's quickly see these date functions. So since there is only one column, we can simply just click on this because there is only one column, so there is no possibility of any kind of an error. Suppose we want to extract month from date, so we have seen the, uh, date function separately through Power Query; let's see how can we extract the month. So suppose you want to apply the month function, you can simply write M in, then what would happen is, uh, it would automatically give us all the month functions. Like I want the month name, so I can just click on this and press Enter, and you can see that automatically, according to the data, it has given me the

Different months, and I can simply click on okay. So the month name column would be added automatically. Similarly, you can just uh go with other examples. Like if you want to work with the year function, so it would give you the year as well. Okay. Uh, basically, you can just select this and press enter and click on okay. Similarly, if you want like quarters, okay, so quarter was also a possibility in the date function, so we can just simply uh click here and write like quarters. We want the function related to quarter, so quarter um of year from date, so which quarter it is, we can just write it. So automatically it has taken up the quarter values, and you can click on okay. So these are some examples of the column from example in Power BI, and how can you use it.

In this video, we will continue our discussion on the column from example, uh which we started from the previous video. If we talk about column from example, uh then we have already seen that it is a powerful feature in Power BI. And if you're already familiar with Microsoft Excel, so this feature kind of is similar to The Flash Fill in Microsoft Excel. But of course, since Power BI is more advanced software, so applications of column from example feature are far more better than the Flash Fill feature in Microsoft Excel. In this video, we are going to see one more example, or basically two more examples, of the column from example function or the column from example feature, whatever you want to call it, of Power BI.

So first of all, let us take a look at the data that we have got for ourselves. This is the data that we have got, uh which has four columns: the first name, mid name, and the last name, and then there is this year of joining. So basically, this is the data, and basically the names of different people and how uh in which year they have joined a particular organization. So this is what we are going to take into account, and we are going to see that how can we merge the data. Like if we're trying to work upon the name, we may want to uh just merge the First, Mid, and the last name together into a single column of name instead of getting out like um the uh different names or the different columns of the names. We are going to just merge all these three names into a single column and name it as name, okay, or employee name, whatever uh heading you want to give to the column, or whatever name you want to give.

Then the second sheet is of alphanumeric characters. If you just take a look at this, so you will see that we have some data like name, code, city, and ID. So basically, it is the names of the different employees, uh their employee code or the city code, whatever you want to say. Then there are the cities from where these employees reside, and then there is some kind of an employee ID. So these are the four fields we have got, and they all are merged into one another, and apart from that, it is a combination of the alphabetic and the numeric data. So we will be seeing that how, using the column from example feature, we can just change this data; we can split it into different columns as per our choice. Okay. So this is our sample data. Let us just close this Excel sheet and yeah, open it up in Power BI. So we can just go to Excel. Column from example is what we want to load, and let's click on open. So this may uh take a few seconds to apply some of the um or to just load this data. So if we just take a look at our tables, we have got this alphanumeric table and this table two. So this table two is for the employee data, and this alphanumeric is for the alphanumeric data. We want both these tables, so let us just check on it and click on transform data. Why we are clicking on this transform data? Because we want to open it in the power query editor first of all before loading it into the Power BI desktop app, because we want to make some changes to the way the data is stored in the tables. So whenever you want to make some changes to your data, you make sure that you open it in power query editor using the transform data option; otherwise, you can simply click on the load option, and it will directly open it in the Power BI desktop app.

So let us first work with this table two data where we have got three columns: the first name, mid name, last name, and the year of joining. So let us just multiple select this column, so we can just press control and click it. Uh, this is the column selected, so we can just control click and select another column, uh control click and select the third column. Okay. Now we can just go to this add column option, go to column from examples option, and here is from selection. So from selection, what do we want is uh first of all, we have to give like uh the sample data, okay, that what pattern do we want. So we can just write like Harry, put a space because you want a space between the three names, then write the middle name, that's James, then again put a space and write the third name, that's Potter, and we can just click on enter, and you can see that automatically Power BI has applied a function that is text.combine function in which it has combined the three names with a space with one another. Okay. So you can see we have got the results. You can just cross-check these results, and if you're happy with it, you can just um click on the column name if you want to change it. So yeah, I want to change it to name, okay, and you can just click on okay, and all these would be applied here. Now when it is applied, we don't want these three names, so what you can do is you can just select the columns which you want to just keep by control clicking it. Excuse me, uh you can just select them, and you can just right-click, remove other columns. Okay. However, I'm not uh right now removing these columns, um because I'm going to just uh take another look at what we can do with it. So what we can do is we can just select these two columns basically, and what we can do is we can just click on column from examples from selection, and we can add a new column, and that is like um employee name, or we can just simply give it a name as EMP name, and what we can do is we can just merge uh like the first names and the last names together, okay, uh so like this: Harry Potter, and we can just click on enter, and you can see all the uh columns or all the rest of the things have been um merged, okay, and you can just simply click on okay, and you can see that uh it is merged as well. So basically, this is how you can work with merging the columns in Power BI using the column from examples.

Then let us take a look at the alphanumeric characters. Now basically, what we want to do is the just opposite of which we did right now. We merged the data in the example that we just saw, and in this case, what we are going to do is we are going to just split this data into the different columns. Okay. So let's just click on column from examples. Since we have a single column, so it's the only one that's going to be selected, and we are trying to just uh remove this name from there, so we are going to just click uh create a new column and click on name, and we can simply write the name of the person like John, and we can press enter. So you can see it has already uh automatically taken all the names, and you can just click on okay. So we have got a column uh after a few seconds uh you can see we have got a column which is the name of the person. Okay. Then uh we want to extract this code thing, so we can just again select this column, right, uh this time and go to from selection because there are two columns present right now, okay, and we can just type in code, and let us just give the code as 231, press enter. So you can cross-check that all the values are correct, click on okay. So you will see the code data would have been extracted, right? Then again we can just uh like extract the city, so let's click on City. So first case the city is Mumbai, uh okay, Mumbai, right? You can click on enter and see all the cities are correct. You can just change the name of the column to City, click on okay, and after a few seconds the city would have been extracted. Right now uh we want to just uh remove or extract the ID from here, so we can again go to from selection, and we can uh extract the ID, like we can provide 12, uh change the column name as ID, and you will see all the IDs have been extracted, and click on okay. So this is how you can extract the characters. Okay. Suppose uh you want to extract a different pattern of the data. Suppose I want to extract the name of the employee, then I want to provide a hyphen, and then it's City. So this is the kind of the data I want to extract. So how can we do that? You can simply go to column from example from selection, and here you can just provide the sample data like John, put a space or hyphen, again put a space, and then the city in which John lives, then we can just press enter. Okay. So Power uh query or the Power BI has not understood it, so what message it is giving us that please enter more sample values. So we need to provide some more samples. Let us just provide another sample and see if it has understood it or not. So Catherine, then space hyphen Delhi. Okay. Now if you press enter, you can see it has understood Bonnie, Indore, Ivan, Bangalore, RoR, Johannesburg, and Caddy, Mumbai. So yeah, it has understood it. We can just uh write like name City or whatever the column name you want to give, you can just click on okay. So basically, this is what is the power of the column from example feature. You can simply provide a data, you can just train your Power BI that what it is, and it would automatically work for you.

In this video, we are going to learn about a concept or a feature known as the conditional columns in Power BI. So what is conditional column? It is a type of a column that is generated on the basis of condition. If you're familiar with the concept of programming, then you must have heard of the conditional statements like if else statements. So the conditional column works in the similar way. If you are an Excel user, then you must have heard of the conditional formatting or the if else statements in Microsoft Excel. So the concept is kind of similar. If you're a total newb, then don't worry, I will explain the conditional columns from scratch in this video. So basically, let us take a look at our sample data. Uh, this is the points table named sheet, and the name of the table is also points table. We have the names of the different employees over here, and how many points they have scored is given. So the criteria of the company is that uh over the month it gives some points to the employees, and on the basis of the points it gives them remarks. On the basis of these remarks, they are credited their bonus or whatever it is. So if we take a look at the points, then there are these negative points possible; positive values are also possible, and some of them have even scored a zero. So the criteria of the company is that if the employee or the person has scored a negative point, then the remark would be like bad work or below average. If they have scored a zero, then they would be given a remark of average. But if they are given a positive score, then there would be like good work or above average or excellent work, whatever it is. So um basically these are the values or this is the condition on which these things work, and this is exactly what we are going to see that how can we write a new column or create a new column called remarks on the basis of these values. Okay. So this is our one data. The second data is from this date table. Uh, we have got the different kinds of dates over here. So what will this dates do? It would just um provide us with a criteria, like we can uh take 4th October 2017 as a criteria, and whatever dates are appearing before it, we can just compare them, and we could write like okay, these dates are before uh our specified criteria, and if the dates are after this, we can give a simple message that they're after the specified criteria. So basically, this is the kind of the uh thing that we are going to do. Uh, this is um usually done with like uh date of joining or date of selling or whatever it is in the databases. So we have taken a simple example just to explain that what or how it works with this date data. So this is the two operations that we're going to do, and these operations are possible through conditional columns in Power BI.

So let's just take a look at it. Here we have opened up our Power BI, but before that we need to close our Excel sheets in the background. Then we can just extract this Excel data, which is this conditional column workbook. We can click on open and wait for a few seconds till that um sheet is loaded into our Power BI. So once it is loaded, we need to just select the tables which we need to like use in our Power BI. So yeah, uh we have these tables like the points table for the points and the date table for the date data. So we can just check them both and click on transform data. So why we have clicked on transform data? Because because we want like to open these tables in power query editor before loading them into the Power BI desktop app. Now once they have been opened, okay, the points table and the date table have been opened, so we would first work upon the points table, and we know the condition: the positive value, the negative value, and the zero value. Okay. So we can just go to this add columns option, and inside this add column there is this option of conditional column. Okay. We can simply click on it, and after it you will see that a dialog box will open up like this. Okay. The first thing it asks us is a new column name, that whatever new column you are going to add, what is going to be its name. So I'm just going to uh change its name to like remarks, okay, because I'm trying to give remarks to the employees. Now there is this if condition that is being applied. So if the column name, so we are going to just check upon the column name called points scored. So let us just select this points scored column from the dropdown. The operator is equals. We do not want equal; we first want to check for the negative value. So if um the column name is less than zero, so if any value is less than zero, that means that particular value is a negative value, right? So we are putting this condition: if points scored is less than zero, then what output do we want? We want um output in a value, in a kind of a value for this column called the remarks column, that is below average, below average, right? A yeah. Then if I want to add another clause, which I want to because I want to check for the positive values as well. This, all the negative values, I want to check for the positive values too. So I can simply click on this add Clause option, so it would uh write like else if, which means that if this condition is true, that is if the value is negative, then we would get below average; otherwise, if this value is not negative, then you need to provide another condition, that is if the points scored is greater than zero, uh which means that greater than zero means a positive value. So what we can do is uh good work, so that's the remark, good work. Okay. And if both these conditions are not satisfied, which means if neither the value is negative, neither the value is positive, which means the value is zero, so in this case what remark do we want? We want average to be the remark, and we can just click on okay and wait for a few seconds. Okay. So if you just uh take a look at the results, so this the column name is remarks, absolutely correct. Minus 18, below average; 33, that's a positive value, good work; below average; zero, average; and so on. You can just check all the records if you want. So uh this is the correct output that we have got. This is exactly the output that we expected, and that's exactly that what we have got. So this is a simple example of how you can work uh with the conditional columns in Power BI.

Let us take a look at another example. Now we have a date table or the date data. So we have taken this 4th October 2017 as a criteria. We are going to mark all the days uh or all the dates before it, and we are going to check all the dates after it. So let's see uh why we have chosen this date data as an example because this works in kind of a different way. We have to apply some more things, some more concepts. So that's why let's see. So let's just go to conditional column in the add column, and let us just provide a custom column name, so that is again again going to be like a remark. Okay. So if you need to select the column name, which is only one, the date column, the operator is equals. So since the value is a date column, so we do not need to check uh or select like is greater than or is less than; we have is before and is after. So if it is before, what value do we have? Uh, we can select uh we can either enter a value or select a value from the column. So we do not want to select a value; we can just simply enter a value, or we have got this calendar option from where we can just um choose a value because it's a date. So we can just choose a value from the calendar itself. So what we are going to do is we are going to hover over to the year 2017. Okay. So that's 2017, and October, we wanted 4th October 2017. So yeah, that's the data. If the date is before this, so what we can do is um like before criteria, that's a simple message that we want to give; otherwise, so we are just focusing on one condition only, that if it is before it, so we want before criteria; otherwise, if it is not before it, what we can do is we can just go with after criteria. In the previous video, we had two conditions, that whether it was a positive value, whether it was a negative value, or whether it was a zero value. Okay. But here we have only one condition, that if it is before or if it is after. So after criteria and before criteria, you can click on okay and see that 2019 is after criteria, 2018 is after criteria. This is also taken after criteria, why? Because we clicked on is before, so that's why uh this exact date is also taken as after criteria. Okay. Then 2019, after criteria; 2017, before criteria; 2, before criteria; after, after, before. Absolutely correct; all the results are absolutely correct, and that's how uh you can just work with the conditional columns with the help of conditions, the different conditions in Power BI. Also uh this is just an example; we will see some more examples of conditional columns.

In this video, we are going to see another look at the conditional column feature of Power BI, but this time we are going to use different data sets. First of all, we are going to see that how can we apply the conditions that are spanning through multiple columns; means whatever the condition we are trying to look at will not be concerned with only a single column, but it would expand through multiple columns. So we have our sample data over here. This is our table uh which is going to act as a data source, and what criteria do we have over here is the names of the different employees, how how many points they have scored in our company over the month, over the past month, which means they could be positive, negative, or zero points, and

What is the distance of the company from the home? That is, uh, wherever they have provided their address and wherever the company office is situated. So, what is the distance between these two? So, on the basis of the points scored and the distance between their home, we want to provide a confirmation that whether they should be given the transport allowance or not, or the bonus for the transportation or not. Okay? So the criteria is that, uh, only if they have scored positive points, then uh, they would be given the transport, and if their uh distance is like less than uh, say 50 km from the home. So, basically, this is the distance in kilometers. So we would be seeing that if they're given the positive points, which means Susan, uh, Daniel, and Paul are the three ones that are eligible for a Transportation uh bonus, and they have to check; we have to check also the distance from their home that if it is less than 50 km, and only we can give the transportation bonus; otherwise, we would not give. So it doesn't matter if your uh performance criteria is negative or if it is zero; you would not get the bonus. Only if your performance is above average and you are living within 50 km, then only would be getting the transportation bonus. So that's the policy of the company, and that's exactly what we want to take out from this data. So this is one data set in which we are spanning through multiple columns for the conditions.

The second option is this. Um, this is the items table. Basically, there are two employees, Susan and Joe, who have done the auditing of the items, uh, who have checked the quantities of the items or the different items like table checks, binders, desks, etc., all the things that are present in the shop or in the company, whatever it is, and they have given us the report. So we want to check these different values and find out that how many um items they are giving the same value and how many items they giving the different values. If there is a difference, then we need to; we may need them to cross-check these values once again. So we need to check that which values are same and which values given by them or which stock items given by them are different. Okay? So this is the criteria. Um, in this case, it is also spanning through multiple columns, and this also is spanning through multiple columns. So we have the distance table sheet and the items sheet in Microsoft Excel that we would be uh seeing right now. Okay? So let us just close this Microsoft Excel and open up our Power BI. Let's go to the recent sources and click on this conditional column sheet or the conditional column workbook because we just opened it up in the previous video, so it would be faster that way. So we have this uh distance table and the item table. Yeah. So let's just click on; let's just check them and click on transform data to open it up in the Power Query Editor. Let's wait for a few seconds till it's loaded. Okay? So, uh, once it's loaded, we would be able to see the Power Query Editor, and in this both of the tables would be present right. So you can see that um this distance table is present, and the item table is also present. So first of all, let us take a look at this items table. Okay? We are going to check that if the value they have provided, Susan and Joe, whatever value they have provided, if it is same, then uh we want to just give a remark that it's same; otherwise, we want to give a mark that it's different. Okay? So let's go to this add columnal column, and we may need to wait for a few seconds. Okay? So that's the new column name that we are supposed to give, and it's going to be like the result. So we can just go with result. The first thing is we need to uh select for the Susan column that whatever the value Susan has provided, so the column name is going to be Susan here; operator is going to be equals that whatever the value Susan has provided, if it is equals to the value that Joe has provided. So we cannot give the value for Joe; what we can do is select this column called Joe. So uh we need to select a column instead of providing a value, so that's kind of a different approach for this. What we can do is go to this drop-down and instead of enter a value, we can just go with this option that is select a column option, and what column we can select this is the Joe column that we can select; means if the value of the Susan column is equal to the value of the Joe column, then we need to give the output in the result column that it is same; otherwise, we can just give the output that it is different. Okay? And that's the criteria. Uh, I hope you all have understood it because it is pretty simple. Then you can just click on okay, and you can see that Susan has provided that there are 890 tables, but Joe has said that there are 860 tables, so we are getting different. Amount of the chairs is also different; amount of the binders is same, 457 for both, so we are getting same. So you can just check the whole criteria uh one by one, all of the records one by one if you want, but if you do not want, you can just trust Power BI. So this is one way how you can um apply a condition uh spanning through multiple columns. Like uh, why is there; there is another condition. Let's just go to this distance table and where we have the points scored and the distance value. Okay? So we can just go to conditional column; the new column is uh transport; we can just provide with like yes value or no value. So if the column name that is points scored is uh greater than zero, okay, if it is greater than zero, then uh we need to check the distance, or if it is uh greater than zero, then only we need to check the uh distance, and if the distance is less than 50 km, then we need to provide the transport value. So these are the two conditions that you need to check, but the other way around is that if the distance is less than zero or if it is equal to zero, then we do not need to provide the distance; sorry, we do not need to provide the transport, and we do not need to check the distance at all. Okay? So instead of going for multiple conditions, we need to go for the thing that is providing us only one condition. So instead of is greater than, we can just go with is less than zero. Then in the transport column, we get the output as no, and uh we can add another clause because that if the points scored is equals to zero, in that case also we need to provide the output as no, which means if the points of score is negative for the first clause and if the points of score are equal to zero as per the second clause, then we can just provide a simple no; otherwise, if that is both of these conditions do not um match, then we can simply just go with the thing like uh okay, um you can just go with distance, or we can just go with another clause, add clause, else if. Now instead of the points of scored column, we want to check for the distance column; else if the distance is um less than or equal to; less than or equal to the 50 km. So if it is 50, we can just provide the value as yes; otherwise, we can provide the value as no, which means this third clause, this um third clause will work only if the points of scored are positive. So we would check that if the distance is less than equal to 50 km, if it is, then we would get yes; otherwise, if the points scored are positive but the distance is like more than 50 km, then we do not provide the transport value, so that is no. So this is kind of a condition that we can provide. This is a messed up condition; that's why we have chosen it in the example. And you can see negative, so we can just check for the positive values like uh Susan, it's 33 and 32, so yes; then we have Daniel, 38 and 2, so yes; and for all the other values, we have no. So that is how the criteria is applied, spanning through multiple columns and through multiple conditions. So this is something that you must know for yourself by practice that how can you just work upon the conditions and how can you just apply those conditions, and this would help you to just solve any kind of query that you want through Power BI. So this is all for this video, and thanks for watching.