[00:33] file, clean messy data, build powerful dashboard, track business performance, and even use AI to understand what your numbers are really saying? That is exactly what Microsoft Power BI helps you do. Hey everyone, welcome to this [00:46] Microsoft Power BI and AI full course by Simply Learn. Businesses are not just the data. They need professionals who can understand data, explain it clearly, and use it to make smarter decisions. Sales teams use data to track revenue. [01:00] Marketing team use data to measure campaigns. Finance team use data to check profit and cost. Operations team use the data to improve performance. But spreadsheets, reports, and different systems, it can quickly become [01:15] confusing. That is where Power BI comes in. Power BI helps you connect different data sources, clean and organize the data, create interactive dashboards, and present insights in a way that business teams can easily understand. And now [01:28] with AI features like Copilot, Smart Insight, and natural language analysis, Power BI is becoming even more useful for faster reporting and smarter decision-making. In this course, we'll start off from the basics and slowly [01:41] move towards the dashboards, visual interactivity, Power Query, data transformation, and AI-powered analysis. So, whether you're a beginner, a student, a working professional, or someone planning to move into data [01:53] course will help you understand how the end of this course, you will not just know how to create charts, you will understand how to turn raw business data into clear reports, useful dashboards, [02:07] and meaningful insights. So, let's get started. Also, just a quick information, career in business analysis, do not forget to check out the AI-powered business analyst course. This course is perfect for professionals looking to [02:20] enhance their skills with the latest tools like Power BI, Excel, and SQL. All real-world projects. You will also learn how to leverage generative AI for smarter, faster decision-making. Our [02:33] smarter, faster decision-making. Our program is IIBA BABOK V3 aligned and helps you prepare for certifications like CBAP and CCBA. You'll engage with 10 plus industry projects, 40 plus practical activities, and benefit from [02:46] live online sessions led by experts. Plus, with Simply Learn's job assist, you will get the support you need to land your next big role. So, business operations is very important because when you understand [02:59] creating a dashboard will not be an easy task. Understanding the business is also equal to understanding the data. BI, or business intelligence, it is referring to a technology or different [03:13] tools or different processes of organizations to analyze the data better. As I already mentioned, people are taking decisions by looking into the data. So, to extract meaningful [03:27] information from the data, we are, uh, you know, creating these dashboards. So, business intelligence is nothing but the same business operations what we are doing manually. We are slowly moving towards a [03:39] digitalized platform. We are making use of the AI technologies, AI tools, which next level. It's very simple. I'll give you a very raw example, I can say. [03:54] Before 25 years before, when you go to a bank, it was called manual. Even if you to check the balance, everything was manual. We used to go to bank, stand in the queue, wait almost for more than two to three hours just to do withdraw the [04:09] to three hours just to do withdraw the cash. But today's generation where if you want to withdraw a cash, we have ATM options. I mean, where we use credit card, debit card. But now people are not even going towards ATM. You all have [04:22] online transactions available. We are slowly moving towards, you know, >> [cough and clears throat] >> online platforms, digitalized, and AI tools integrated because data security is also very important. Because we are [04:37] moving towards the digitalized platform, data is getting generated maximum. We are using the same data maybe for future innovation or to have a very good experience or to generate new such tools to enhance the knowledge and [04:53] also to retain customer in this domain. We are working with the data. We are analyzing the data. We are visualizing the data to move towards, you know, to you know, to move towards AI, not only AI trend, but also to create uh you [05:08] know, a safer environment for all customers to use the application. It's all about understanding the business, understanding the data, invoice, so improving the offer improving the [05:23] operation, for identifying the trend, for gaining a competitive edge out of very good service, customer will come back to you for sure. If you understand the customer better with the different trend, what is the trend of customers, [05:38] how the sales are running from so many years, how can I manage, you know, the chain? For everything, we need a data. To take a decision we need a data. So that's what the business intelligence [05:52] data from a domain that is maybe a net banking or a banking domain. You have a data with you. So you are creating a dashboard out of it to understand your customer of the banking button. [06:06] Okay. So to concentrate on business intelligence, there are few key components which we need to understand. [06:18] That is nothing but working with the data is very important. When I say I work with the data, from where are we getting the data? From where we are collecting the data? [06:30] Data is collected everywhere. Websites are collecting the data. You are If you want to use an application, you will register yourself. application, you will register yourself. Data is getting generated almost 95% of [06:43] online platform. Gathering data from various sources, including internal systems, external databases, social media platform. This involves identifying relevant data sources and developing strategies to [06:58] collect and store the data efficiently. For us to work with the data, we are collecting the data from different sources. We will clean the data. I mean, after collecting of course we need to clean the data and we are getting data [07:11] from different sources. We will collect it. We will import the data to Power BI it. Data is collected from different sources, I can see. And I can see some question in the chat. [07:24] Power BI will only work for bank No, no, no, no, no. Not only banking domain. Power BI works in supply chain. Power BI works very well with e-commerce, banking works very well with e-commerce, banking industry, all government officials, IT, [07:38] non-IT, all companies can go and make use of Power BI. If yes, then how far else can I do it if it's important? To answer this question, almost 2 years domain. It was a company related to health care [07:53] domain only. So, where there we are there we were using Power BI tool >> just to maintain the health care the cost of health care product. If a customer is buying one product, what is [08:07] the cost? If a customer is ordering in bulk, then what discount I'm giving to them? If customer is very important to me, if customer is engaged with me from discount. I need to give a very good customer [08:20] service to retain the customer and I want customer to buy product from me only. When I talk about health care industry, the health care industry means chemicals, medicines. There are so many [08:33] products which will be, you know, produced and that product shelf life will also be there. To understand the shelf life of the product, to maintain the customer data if they're buying in bulk. See, especially the product, you [08:46] bulk. See, especially the product, you know, like 0.5 I mean 0.5 ml, 0.5 mg, 1 mg, 2 mg. There are so many products. I can say, personally, I worked on 9 lakhs can say, personally, I worked on 9 lakhs of product just for one customer. They [08:59] are ordering 9 lakhs of product with the minor change in the dilution I can say, the chemical dilution. And the complete 9 lakhs product and the cost of it, the discounts of it. In discounts also there are so many types of [09:14] given. So, I mean all these are depending on per my experience, I'm just mentioning what I experienced with the health care So, giving them different discounts. So, maintaining this data and giving this [09:30] data to a customer for them to understand clearly, for them to understand cost of each product clearly, you can prepare a dashboard. You can discuss that with your stakeholder. That means your customer. And you can give [09:43] them the complete insights what best you are giving to them with the help of dashboard. Especially for stakeholder management, this tool is used to maximum. So, healthcare domain or supply chain or biotech, even government [09:56] officials, all the industries can maintain data, can represent the data using this Power BI tool. Data integration. As I already mentioned, data will be available in different formats. [10:10] Always Power BI considers structured data or tabular data. When the data is in structured form, when the data is in a tabular format, structured or tabular, [10:22] the meaning is same. When the data is in structured format, then only you can take that data to create a dashboard out of it or to create a visual out of it. So, maintaining data format is also important. Imagine when the data quality [10:37] is not correct. Of course, we cannot expect a good result from the dashboard. Dashboard what you created with uh low quality data will always [10:49] you know, will always create trouble for you to take a good decision. So, it is very important to maintain the quality data, structured data to get a Because dashboard should not give you some incorrect data. Dashboard [11:04] completely depends upon your data. The graph what you are creating, each graph, you know, they have their own uh you know, principles. As per the principle, you need to select the data. If data itself is not correct, if data [11:17] itself is not good, you cannot expect a good result at the end. data format, [clears throat] resolving inconsistency, and creating a unified view of the data [11:29] is very important. In a nutshell, you are preparing a dashboard to check all are preparing a dashboard to check all the answers at a time in one shot. Unified view, I can say. But if data itself is not not correct, [11:42] you cannot expect a good result. All the graphs will be affected, not only one graph. Everything will be affected there. [11:57] Data storage, again, it is expensive, and companies are spending lot on data storage itself to store the data. We have cloud platforms. We also have uh different data centers available where each company stores their data there by [12:11] paying. And here, organizing the data, especially structured data, is very important. This ensures the data is easily retrievable and protected from loss or corruption. They are I mean, cloud platforms are [12:24] storing all the data. Whenever you want, you can extract it. So, data storage again is very important. And when I talk about data analysis, using statistical techniques and data [12:37] mining and machine learning to uncover patterns and trends in the data. This involves applying analytical methods to extract meaningful insights. For a business analyst uh group, data analysis is also a part of it. [12:54] When you understand the domain, you can easily understand the data. So, we have so many techniques which we can implement on the data to extract meaningful information. For me to understand uh you know, the data better, [13:08] right now, I'm using Power BI to extract meaningful information from the data. If you are using Python, you will go with different libraries to uncover the pattern to understand the data. You use different Python libraries to [13:22] understand. Now, we are going with Power BI. What what uh how Power BI helps us? It helps us by creating the graphical representation of the same data. ways. Not only with Power BI, using different [13:39] coding language also you can analyze the data. Just using Microsoft Excel also you can understand the data, you can analyze the data. Multiple options we have. Right now, we are depending on Power BI to understand and to visualize [13:53] the data. Two options we have with this Power BI. Power BI. For a BA role also we need Python skill map. Yes, it is important for a business analyst also to concentrate on Python [14:07] programming language. So, let us continue with the next topic that is data visualization and reporting. You already know I've mentioned it multiple times. Data data data then data visualization. So, you [14:21] are creating different graphs all together called I call that a dashboard. This helps stakeholders to easily comprehend complex data and make informed decisions. You have a raw Excel sheet data. You will give that to your [14:37] stakeholder. Will you think that your stakeholder will check each and every row in that Excel sheet? Of course no. Stakeholder will not do that. Because they are your stakeholders, you need to [14:49] give them a very good service. They expect you to upgrade yourself with the different options available in market. Where you are depending on Power BI now to create a dashboard. For stake not only stakeholders, for you also it is [15:05] easy to clearly understand the data in the pictorial form. Generating reports that summarize key findings and insights. This involves creating structured documents or presentations that communicate the [15:19] results of data analysis in a concise and effective manner. See, data creation I mean, not only about data visualization, for you to move towards data visualization, before [15:31] you move towards the graphical representation, data will be available in the form of report. We have a report view in data, I mean, in the Power BI, where you can clearly see the complete data in [15:45] a structured format that will help you to create a data mission. So, I will show you all these one by one. Don't get confused. Dashboard is nothing but adding all different graphs together. Even if you uh you know, change if you [15:59] add some filter, if you change one graph, all other graphs will be automatically filtered as per the change what you did. That is called as a dashboard. The report is nothing but the before you create these graphs, before [16:12] you create this dashboard, the data will be available in a tabular format, in a structured format. That is called as data report. [16:26] can see as I already mentioned. Yes, this for decision-making it will be very helpful to increase the efficiency and also productivity, to understand the data crystal clear. This is helping us. [16:39] Customer satisfaction is important. When you give a good service to customer, they will again come back. So, this will help you to understand data, which is also equals to understand the customer, understanding the business. [16:52] Competitive edge again, to move forward with advanced technology. Industry use cases of BI. Now, you can clearly see different Now, you can clearly see different industry, how they are using the data. [17:07] Sales analysis, of course, business operations, sales team, how sales team uses BI. For them to track the sales, for to target or not. To understand what customer uh you know [17:22] expecting, what are the products they are expecting, what are the top selling products, what are the least selling products, reviews on the products. Sales team will concentrate on the entire end-to-end sales data, I can say. [17:37] financial reporting. So, here they concentrate more on So, here they concentrate more on revenue, finance, economy, I can say. So, generating financial statements, analyzing the profitability of the [17:51] business or loss, it will be taken care by the finance team. So, end-to- end about, you know, finance, it will be taken I mean, for using Power BI to showcase the same. Marketing analytics, to understand the [18:07] customer. Customer recommendation is very important. And that's one of the important topic when I mentioned AI, recommendation system. You are searching something on Google, you will find multiple options on [18:20] you will find multiple options on Instagram. How it is possible? advantages. It is used in market It is used you know, Power BI is used with [18:33] marketing team also. HR also depending on Power BI to understand the employee project is running, everything can be analyzed. Supply chain management. So, not only these five teams, apart [18:47] from this everyone can depend on Power BI to showcase their data. So, what is data analysis? Now, so far you know how your company works. You are working for a e-commerce or any platforms, you are working [19:00] for different companies, not at all an issue. First thing is you need to know how your organization is working. Just a second. So, we have multiple steps. One by one I will write here. So, this is the first step where business [19:13] the first step where business understanding is important. knowledge, this is the first step, you will move towards data. [19:29] first phase where you will move towards the data analysis, DA which I can say. the data analysis, DA which I can say. You need to correlate your business questions to this data. For example, you're working in a e-commerce platform, [19:44] they're expecting you to extract some meaningful information from the data by listing some questions. They're giving you around five questions. You need to understand the business first. You need to understand these questions. To these [19:57] questions, you need to use this data to answer. the questions, these questions are related to business only, business [20:09] operation But to you need to answer this question using this data. So business operation, business understanding, and then your data understanding or data analysis. [20:21] Now this is the important part. I mean, all the steps are important. Here, you say 60 to 70% of your time, you will be [20:33] spending in analyzing and working with data. When I say data analysis, uh sorry, I will just uh write here. When I talk about data write here. When I talk about data analysis, [20:58] structure, if required, data transformation. And then the third step is to check the data quality. When you're checking the data quality, it is also checking the data type or data formats. You can call [21:13] it different technical terms, but sometimes the meaning is same. Okay? So, data format check. And then comes [21:28] understanding the data. Okay? So, what are you understanding in data? You are checking the data structure, you are going with the data transformation, you will understand the data quality, where you will concentrate [21:40] on data type, data format, and then you will move towards I mean data structure, data transformation, data quality, and then understanding [21:57] That is nothing but which we call data visualization visualization principles. So, these are the Sorry. Yeah, these are the important principles. [22:11] will consider when we are doing a data analysis. the data transformation. Data transformation is when the data is not transformation is when the data is not structured or if you want to do any uh [22:25] structured or if you want to do any uh one second. [22:39] or modifying the data or it can also be removing the existing data. Not only adding it, also removing existing data. Data transformation is also where you [22:55] concentrate on modifying the existing data, removing the data, adding the new records to it. Positive. In Power BI, once you grab the data, based on calculations, you can add new [23:08] column or new row or appending the column, which we call. You can also removing the existing column. Adding, removing, you have both the Adding, removing, you have both the options. [23:23] understand the data, when you understand what is your project, or when you properly. And then you will move towards the dashboard preparation, whereas you need to know the data visualization [23:35] principles. Just a second. Is that a SQL knowledge required here? Yes, analytical skill analytical knowledge will help you to understand these concepts better. Related to Power BI, whatever [23:48] that we have, I will be explaining you. But additionally, if you already have a good knowledge on stat, it will a really be a added advantage, I can say. [24:01] So, it is all about examining the data. Another important thing I want to mention based on my experience, your mindset should be a research mindset. When you're working with data, you might have observed it, your mindset should be [24:15] a research mindset, where you will research only with the data. You will not go out to do the research like cops. But you will sit, you will alone do the research with your own data to understand it better. So, research [24:29] mindset research analytics itself what separate topic for you to work with the separate topic for you to work with the data. So, research mindset, analytical skills, and decision making, problem solving, these are some soft skills or [24:42] non-technical skills, which I can say, which is important for you to carry in which is important for you to carry in this journey. [24:54] did it. Data collection, data cleaning, data Data collection, data cleaning, data exploration, data transformation, and data modeling, data visualization. Data modeling is something new which I didn't [25:08] mention before. So, here, let me just take. first stage. You know what is what is the purpose of creating the dashboard. [25:20] You know what are the questions you need to answer. So, you have the data. You collect all the data which you need to consider to answer the question. You need to go with the data cleaning. You need to explore the data. This This [25:33] is also called as descriptive data analytics. analytics. I think I can write here. [25:51] Where you will do in-depth research on the data. See, I'm using so many technical terms. Please don't get confused. If you have any questions, please let me know so I can help you. [26:03] Data exploration is in-depth doing the analysis of the data. Also called as descriptive data analytics. And one more important point, always you will work with the past data. I can tell you what happened yesterday. [26:19] morning. But I can't tell you what can happen tomorrow morning. Always the data what you use to prepare dashboards or any project, machine learning, AI, or any projects you work, you will always work only with the past [26:35] Future prediction will be taken care by the model. Especially this Power BI Tableau dashboards or you build, you are building on top of past data, which is helping you to take a future [26:49] decision and for future prediction this data is helping you to think. So always working with the fact data. [27:04] want to do any data transformation, do any data changes, adding, removing that is also possible in data that's the you know stage of data transformation. [27:16] And then data modeling. Data modeling is nothing but you can merge data from different sources. Okay, that means the one Excel sheet will have multiple tabs. I hope you all observed it. One Excel sheet can contain [27:32] multiple tabs. One tab can be related to another tab. Imagine customer details you have. I will just write here. Please understand the concept. [27:45] Customer details you have. What are what's the main important data which you can have in customer details? That is customer ID. so sorry. Just a second. I will write again. [28:06] important you know data what you can expect here is customer ID. Customer with this customer ID it's a unique ID given to each customer where you can check the entire customer details. Imagine you also have the order [28:19] ID. In order ID, sorry, order data. In this order data you will have customer ID who ordered the order I mean placed that order. So customer ID will be there that order. So customer ID will be there and order ID will be there. Sorry. Order [28:34] ID will also be here. Using order ID you can check oh sorry, using customer ID you can check what is the order being placed. So you can merge this customer ID and this customer ID you can create a link between two data sources. [28:50] Using the common you know using the common column. This is what we do in SQL. We will try to merge the data by considering certain common column. Especially the unique column what we will be considering to merge the tables. [29:10] will the new data model will be created or the new data set or the report will be created where you can see customer details and order details together. merge. You already know about I mean if you know SQL, you will be knowing you [29:27] know join like right join, left join, outer join, So we can also do all these modifications in Power BI. We can write a query and we can merge the data together. Data model is nothing but you [29:43] will have multiple data sources where you can combine them together and you can create a dashboard out of it. You no need to create for each data different dashboard. One dashboard can give you the information of multiple [29:57] dashboard, sorry multiple data. Joins using unique ID primary key, exactly. So we have the different joins where we will be merging the data with this unique ID which we can consider maybe the order ID or customer ID based [30:12] the customer details. So SQL is also integrated with Power BI but not exact SQL code. The code or the query will be almost similar to SQL. [30:25] That is where the data model. I will show you all these. Please don't mind I'm you know speaking so much today than showing you. All right. Please don't mind. Please make a note on these steps for sure. We will be working [30:37] on all these topics. Next comes the data visualization after creation, you are showcasing the data in terms of data vision. I mean creating a dashboard. This is a complete step which you need [30:51] to concentrate when you're working with the data especially with Power BI or Tableau. Even if you're working with Tableau, the stages are same. Only thing is you are using a different tool. You can also use Microsoft Excel [31:05] to create a dashboard. I think you already [clears throat] know about it or you might have created. If not, please creating, the dashboard what you are creating in Power BI, you also have an [31:20] option to create in Microsoft Excel, but the efficiency will be low. Efficiency will be low. Manual task will be high. You and Excel will of course sometimes it will hang when you have large amount of data, Excel will not you [31:34] know work I mean Excel will not open properly. It will hang sometimes. Efficiency will be low. It will work similar not exactly the same but similar task, you know it will help us to create a dashboard using Excel, but it [31:49] you cannot expect a good efficiency out of it. So let us look each step in detail. As [clears throat] I already explained, data collection is collecting the data from different sources or from different [32:03] from different sources or from different portals or from your company. If your company is an e-commerce company, you need to understand the e-commerce how the business works. You need to collect the relevant data. [32:20] correcting errors, inconsistency, or the missing values. Data exploration where you will do in-depth data analysis. That is also in-depth data analysis. That is also called as descriptive data analytics. [32:38] formats. Adding, removing, it is also possible. Data modeling. The data model is not only about creating a link between the data model. If you want to move towards an advanced technology, you can go with [32:53] the machine learning model to predict for you. With this dashboard, you can see the picture. But, if you want to know the exact [33:05] prediction, you can also make use of machine learning algorithms which we have with respect to Python code. We have certain libraries which will help algorithm. So, with that also, you can do a [33:20] So, with that also, you can do a prediction. So, creating dashboard, I mean, data visualization is nothing but creating a visual representation of data, like graphs, charts, dashboards. [33:34] Descriptive data analytics, diagnostic analytics, predictive analytics, prescriptive analytics are the four important types of data analytics. It's very simple. Descriptive analytics is all I mean, I [33:50] will write here the the phases. First thing is you will always have a past data with you. With this past data, you can clearly With this past data, you can clearly understand what was the sales in 2024. [34:04] Because 2024 is done. We are also about to complete 2025 now. to complete 2025 now. You have a 2024 sales data. Your manager gave you sales data of 2024. Where the target by the end of December, [34:19] it was closer to 2 CR. This is the revenue generated after removing all you know the investment, this is the profit generated. Okay, profit generated, I can mention. [34:34] Now, your manager gave you this data. You are in a critical situation right now. Because it is November 2025, Because it is November 2025, where after removing all the investment, [34:47] it was possible for us to reach only 1.5 CR. Only 1.5 CR. See, every year we try to improve ourselves. We want to grow. We want to improve. Nobody expects, you know, to have a very [35:02] less sales. Nobody expects to reduce, you know, or any reduction in the sales or revenue. We don't expect it. We always work and we want every time to [35:14] see something which is good. We want to grow the organization by the own. So, he gave you the data instantly and he want at least to achieve the sales as same as 2024. Even though it is not possible to reach more than 2 CR, he [35:31] expects to reach at least the until 2 CR. He's giving you a dead target. Which you within 1 and 1/2 month, within, you know, 40 days, reaching 50 lakh, he's giving you a data [35:45] and he's making you to understand what was earlier sales, how the sales was running, what impacted the increase in the sales last year, why it is in the sales last year, why it is getting decreased in this year. [35:59] Multiple analysis can be done. Why default? 2 CR. Descriptive analytics is all about working with the historical data to understand the pattern, to understand the relationship, to understand the [36:15] entire sales of 2024. You need to concentrate on the past data and that is called as descriptive analytics. [36:29] methods. You will check I know for example the sales was related to pizza. So you need to understand the sales of 2024 pizza sales, which [36:41] pizza sold maximum. And what is the you know what strategy did they use to sell maximum pizza and what was the cost? When the customer purchased maximum pizza, which month contributed maximum? [36:56] Why that month contributed maximum? So all the analysis we are doing here. Descriptive analytics is all about I identifying, understanding the past data. If I move to diagnostic analytics, with the results what you get from [37:11] descriptive analytics you will go with that in-depth research on the same data to understand the insights. I will just stop stop coming here and then I will move. Diagnostic analytics is all about [37:27] you got to know what you know in which month it was maximum, which product or which pizza we sold maximum. You have all the insights. Now you will go with a deep investigation internally to check each [37:42] and every record. Each and every data. Investigating you know the here the investigation level is 50% maybe. Here the investigation level is more than First you will extract some of the [37:56] important information to answer your project. Later for you to see not every time you are you will find the sufficient answer in the first step. You need to understand, you need to check data multiple times where you will get [38:10] the maximum insights. So extracting maximum insights from the data, we call that as a diagnostic analytics. Please remember when you're working with Power BI or maybe a business analyst role, data analyst [38:24] role, not at all an issue. You need to understand all these types. You can sure. What are the stages? How the stages helps us to create a dashboard? You can expect all this. [38:39] Predictive analytics based on the investigation what you do in stage one and stage two, you will move towards the prediction. Prediction, you can either do the prediction with this dashboard [38:52] by predicting what are the possibility. This is your actual data. This is your actual data because in both the stages you are working with the past data which already happened and this is your actual data. [39:07] This is your model predicted data, I can say. This is just a prediction. This prediction maybe true or may not be true. The situation and the results what [39:20] you're getting from this prediction though, it can be true or may not also. So whenever I talk about prediction, it's just the meaning itself, the term itself says it's a prediction. It's not the actual scenario, it's not the actual [39:33] It's just a prediction which is helping you to which is helping you to have a very good knowledge, also for you to prepare for future. This predictive prepare for future. This predictive analytics will help you. [39:50] Prediction or predictive analytics is all about future prediction with respect to past data. Then comes prescriptive analytics. You predicted for future now. You need to understand to reach the [40:03] target. You are getting a prediction that you You are getting a prediction that you can reach the sales of 1.9 CR within next 40 days. You are right now in 1.5 CR. 40 lakh you can increase [40:17] within next 40 days. That's the prediction what you are getting. To reach the target, what necessary actions or decisions you need to take? That is called as prescriptive [40:30] analytics. After you build a dashboard, your task will not end. To be honest, your task will start. Once you build a dashboard, you will discuss that with your stakeholders. These are the possible strategies which [40:46] These are the possible strategies which I observed from the past data, and with the prediction, I can say that these strategies will help us to reach a near target of 2 CR. You can either reach this target of 1.9 CR, or even if you [41:00] this target of 1.9 CR, or even if you stop at 1.7 CR, not an issue. But, this is just a prediction. You cannot 100% depend on this prediction. You may go higher than this, or it may be lower than this. [41:14] Increase, you can also uh it can also increase, or it can also decrease. So, step by step, these are the stages which we consider whenever you work. See, not only with Power BI. These stages will help you any tool you use. [41:28] If you are working with data, please consider on these four analyses. After this, there is one more analysis called as cognitive analytics, where after you build this prediction and prescriptive, you can make use of machine learning [41:43] models and AI models, and make your uh machine to learn this data for further more benefits. That is called as cognitive analytics, where you depend on So, when I talk in terms of business, imagine you're working under a business [41:59] operation team. So, what is your main goal or main target? Business operation means yes, you need to give a very good service to customer, customer satisfaction. And again, you need to run multiple apps [42:12] to gain customer attention. Marketing strategies decisions will you will be able to take with this different analysis what you will use with respect analysis what you will use with respect to data. [42:29] in analyzing the data. Exactly. All the steps whatever I'm just mentioning here, these are the four important analysis or also called as analytics which you will be implementing on data step-by-step. [42:47] care industry, analyzing the patient data. Healthcare industry takes the data >> [clears throat] >> help doctors or physicians to understand the data why customers are coming back, which tablet is working, which tablet is [43:02] not working, which is the advanced one, treatment outcomes, improving, you know, the healthcare industry is uh you know, it's a big impact I can say because even though if there is a minute change in the prediction, it will affect [43:16] patient's health. So, healthcare industry means please be very careful. The data accuracy is uh you know, it's high uh you know, it's like high-end I can say. To talk about finance, [43:31] predicting market trends, accessing risk, and managing the investment. Stock market analysis is one such good example uh which will give you a clear idea on how to understand the stock stock data, [43:45] where to invest, where not to invest. You will get the entire prediction. And let me tell you, all stock market platforms are using the AI techniques or are the dashboard what you can see. If you are working with I mean, [43:58] if you are using the stock applications like Grow or Sharekhan, I personally use that. So, I'm just giving you that name. There are so many platforms where you can see the chart, you can see the dashboard, you can see the numbers in [44:11] percentage or you can also see the actual value plus or minus. All the predictions are happening just with the help of data. Stock market is one good example when it comes to banking industry or when it [44:25] comes to banking industry or when it comes to economy, I can say. science, without engineers, it's not possible to do with all this. Discovering new patterns and insights in scientific research. [44:44] all the industries are behind data. Everywhere data is collected. We need to remember the four important analytics to move forward. Again, the entire stages, whatever I mentioned so far, [45:01] preparing the data, data modeling, data visualization, preparing the data, governing it, and I mean, sorry, maintaining it, clearing it, and data quality, data structure, data governance, which is [45:14] also called that. Data management. Again, the terms are almost same, the task will be same. Analyzing it, preparing a vision as per the requirement, data modeling. All the [45:27] topics are important. As a data analyst or as a business analyst or as a Power BI report developer, you will be concentrating on the entire topic, actually starting with the Power BI tool, a business intelligence tool [45:42] developed by, you know, Microsoft. Most of the organizations are behind this Power BI, which we are again learning. So, let us quickly start. This is how your application looks like, [45:56] This is how your application looks like, the Power BI application looks like. a business intelligence tool, a platform that allows you to connect to various [46:09] data sources, for data transformation, and also for report building, and to share insights with your team, or with your stakeholders, or with anyone you are using this dashboard to [46:22] anyone you are using this dashboard to represent your data. sources also for cleaning purpose. This platform's core function is to enable organizations to make better data-driven decisions by providing clear [46:39] and uh useful information. If you give some uh you know information which is incorrect, there is no point, and it will uh see, whatever dashboard you create, no, especially in a company, multiple teams will use the dashboard. [46:53] One wrong data will affect multiple teams' performance. So, the main key functions of this Power BI is the data connection, data BI is the data connection, data preparation, data [47:15] first the data connection, then data preparation, data transformation, data visualization, and then comes your data analysis, and then preparing the dashboard, and then sharing it with people. [47:31] This is how it looks. I hope you already know about it, how it looks, and other So, step by step we are going. You can see the key features. Data connectivity, data transformation, modeling, the mobile access, report [47:46] creation, dashboard creation, and the newly added the AI features, that is natural language query. You have an automatic Q&A option, or a chatbot [47:58] option available when you create a dashboard. So, it will help you you know to I mean whatever the data you mean your I mean whatever data you use to create the dashboard, you know, that will be taken [48:11] care by the Power BI. You can ask any question using this Q&A. It will answer. I mean the Power BI dashboard what you created will answer itself. That's the AI feature which is added. [48:27] The key features of Power BI or you can also consider as an advantage. Benefits of Power BI as you already know, efficiency, productivity, collaboration with multiple teams by sharing the data in a centralized [48:42] platform. Scalability of handling large data sets, complex analysis can be done using this. Availability, data availability and also you can use it even in your mobile phone also to check or to refresh your graph. [49:00] To check the insights. You may not be able to create a view created in your desktop, but it is also you can also access it in your phone and you can check the view I mean you can view it. You can check the [49:13] result. Improved decision making, enhanced collaboration, productivity, enhanced collaboration, productivity, availability and scalability. [49:25] know because I know these topics are quite similar. I think we have already But still if you have any questions, please let me know. These are the three options what we have with Power BI. One is the desktop [49:39] version, Power BI service, Power BI mobile. This service is paid version. This service is used by organization where people will use the different options or they will create a dashboard using this Power BI service. [49:54] other people. The way you use Power BI Desktop, it is almost the same, but it is with respect to server. Where it is all completely managed by a company and data sources will be there, databases will be there. There are [50:09] different servers which is maintaining this dashboard. The way you create the dashboard is same everywhere. Don't get confused. What if you are not using Power BI service? So, it is the Only difference is like it's a [50:22] Later, maybe in your company you will use a website, like a server. Power BI Desktop is what you all will be downloading to your laptop or a desktop to use it and also to create dashboards, to do the data transformation and all [50:38] the options whatever you have with Power BI, you can do it using a Power BI Desktop download after downloading it. This Power BI mobile, no. See, when it's mobile phone, you can view the data, you cannot do any you know, you cannot [50:51] create a dashboard like how you create it laptop. That's everyone know because it will be clumsy when you use the mobile phone. It is good to view. So, the three options what Power BI is offering us. [51:08] looks. This is the primary tool for creating and developing report and dashboard. It offers a rich set of features for data cleaning, data transformation, modeling, and also for visualization. [51:26] dashboards. So, right now, please understand I will make all of you to get answer experience. Please don't mind because today's agenda is quite different. Tomorrow's agenda is very interesting where we of course get [51:39] experience on you know, this tool. This tool available in paid version also, free version also. You are downloading the free version. Please don't go with the paid version. But if you are working in any company [51:52] and if you have a paid version, you can utilize it. This is how your Power BI service looks. It will be a server-based application, I can say. And this is only available for organization. This is online cloud-based platform of Power BI [52:09] where you can publish, share, view, edit, collaborate with people version. same as the way you create the graph here also. [52:24] applications. This is mobile. Access with Power BI mobile. This mobile app enables you to view and interact with your Power BI content on your smartphone or tablet. [52:38] smartphone or tablet. This is the Power BI architecture. sources. Data can be internally in your laptop, like an Excel sheet data, or you may grab the data from a database, [52:54] from a physical database, or a cloud platform, which is an online database, or you can have the data in text format also. Different data sources will be used to create a dashboard. And this can be [53:09] again created in Power BI service or Power BI mobile phone also. You can do Power BI mobile phone also. You can do that. I can say. Grabbing the data, creating a dashboard. But before you create a [53:24] there. And then visualizing it. This is the architecture of Power BI with the multiple, with the multiple, uh, you know, services what they have. [53:40] building block. See, there are two important points here. One is the static dashboard and one of the another one is dynamic dashboard. I think that is not mentioned here, not an issue. I will write here what is the [53:52] difference between static dashboard and dynamic dashboard. This is applicable for all the you know tools whether you use Excel or you go with Python code or you go with Tableau Power BI, it doesn't matter. There's a dashboard called a [54:05] static dashboard or dynamic dashboard. The main nothing but okay first one one by one we will do. Static dashboard is where you will create a dashboard only once [54:19] which is not been updated. Like you cannot update the same dashboard because cannot update the same dashboard because it is only with a limited data and you are not doing any automation. You can do the automation but when I say static [54:31] dashboard you are not updating or you're not automating it. Dynamic dashboard is where you will do automation. [54:44] As the new data becomes available the graphs are updated automatically. graphs are updated automatically. Automatic graph updation unlimited. So these are the two technical terms when you are creating a dashboard people [54:57] may ask you is this a static or a dynamic? Static means which is stagnant. >> The graph will not be updated. It's only with the data what you imported. See you 1,000 lines of data in this Excel sheet. You will import it, you will create a [55:13] visual, you shared it with people, that's it. This data is not getting updated. But when you want to go with dynamic dashboard you have today 1,000 customer details. Tomorrow again new customers [55:26] will be added or the same customer will be purchasing something new. Data will be adding up right? So that's a dynamic dashboard which you create. You will link the same dynamic data. You will create a dashboard. Automatically, [55:39] it will be adding up with the new records when you go with the refresh visual option. I will show you all this. Please don't mind. I'm giving you enough information now. Please make a note. We'll work on [55:51] all these topics. I will show you how you can do automation with Excel, how you can do automation with database also. Static dashboard is where with the available thousand uh records, you will use the same Excel sheet. You will [56:05] import it. You are creating a dashboard. Your job Your job is done. You share it with stakeholder. Stakeholder will understand these Stakeholder will understand these thousand customer details only. [56:18] customer data you're giving to your stakeholder. until yesterday, you created a dashboard. What about today's data and what about tomorrow's data? The data will be available to you. [56:33] You have 2024 dashboard. But, 2025 we are already in We are already in November. You don't You don't have the data available in the dashboard of 2025. Because it is static dashboard, you made [56:48] your dashboard only to learn the thousand records of 2024. Automatically, it is not getting updated. There, man, you need to go again. You need to create a new dashboard. You need to import the data. [57:03] You need to do all the manual work. Again, the same dashboard you're creating. You're not upgrading the same dashboard. static dashboard, no, you're not upgrading the same dashboard. You're not [57:16] doing any automation here. But, when I talk about dynamic But, when I talk about dynamic dashboard, 2024 you created, that's for the same dashboard, you are giving new records. [57:28] records. You are giving 2025 data also. Same dashboard is getting upgraded. You make your dashboard to learn the new dashboard. So, static in the sense which is [57:41] Which will not be changed, which will not be modified again and again. Dynamic is where it will be keep on changing. You where it will be keep on changing. You can link multiple data sources to one [57:55] dashboard. So, that's the one which I mentioned earlier. Multiple data sources you can link together and you can prepare a dashboard out of it. One dashboard you can create. [58:12] visualization are the main building block of Power BI. You can expect questions like interview questions like what is the architecture of Power BI? What are the stages involved in creating a dashboard in Power BI? What are the [58:27] building blocks in Power BI? Can we do automation? For that you can give an automation? For that you can give an example of static and dynamic. So, now talk about the block. Semantic model and [58:40] visual. See, semantic model is nothing but we have multiple views in Power BI. That is report view, data view, uh visualization pane. So, we have so many options. Semantic models are nothing but the DAX query or data model or the [58:56] report view. Data transformation, data cleaning the back side. Before you create a beautiful dashboard, lot of work we will be doing in the back end. That is called as semantic model. Collecting the data, transforming the [59:11] data, cleaning the data, checking the relationship between the data. It is called as semantic model. When I talk about the visualization, visualization is all about creating a dashboard, creating an interactive [59:27] dashboard, the outer layer. What we or what you will share it with part of it. You will not show the inner part, the complete the work what what you do all the match. No, you will not show to people. You will only show the [59:42] presentation, the proper dashboard. The outer part. Inner part you will not show. So, that is your thematic model is your inner part, where you work where you work with the data transformation, creation, then we can go. [59:55] Visualization is where creating a dashboard out of it. Uh, Power BI empowering the data analysis. Of course, Power BI see this icon is nothing but a [01:00:09] combination of Python and a Power BI. So, Power BI, not only Python will help you, or not only Excel will support you, or not just other tools. Power BI is giving you a very good uh analysis on uh you know, very good [01:00:23] analysis you can create with respect to data. Uh, deeper analysis you can do. Earlier, there was no concept of DAX query for Power Query, but now it is integrated with Power BI. I mean, from few years we have, almost from two, [01:00:38] was only dashboard. Uh, query option was quite less. Now, advanced query options we have. Additional graphs are getting added every now and then. And every 6 months [01:00:52] uh the version will also be adding the new versions will be getting added. So, simplifying the data preparation, data accessible is easy because 99 plus data sources options we have. Enabling visualization exploration, this [01:01:07] is your diagnostic analytics, facilitating the deeper analysis is also possible. Empowering data analysis with all the Empowering data analysis with all the key concepts. [01:01:22] tool. I will give you I will share the link I will I'm checking I'm opening a Microsoft store. If you have Windows, this is one such method in which you can use to download the Microsoft application. [01:01:39] Just type Power BI. How do you download application to your mobile phone? No. How do you use in your mobile phone you know the App Store or Google Store to download the application? In a similar way, open Microsoft Store in your laptop [01:01:53] or a desktop and then download it. I will I mean in Mac also, you can directly go and check the applications or I don't have not used Mac, but please check your App Store. You can see you will be able [01:02:09] to download Power BI. I think right now it is not available. Paid version is available I believe for people who are using Mac. I will share you the link which you can download. I don't think so you have an application, [01:02:22] but if you have it, please download. So, Microsoft Desktop I already installed, so it is showing installed. Don't go with this Power BI Report Builder. This is for report purpose. [01:02:34] This we are not using. This we are using Power BI Desktop. Directly download it. Let me see if I can I will share the link in the chat. All of you, please use [01:02:46] this link to download. This is one link. I will this is the link which belongs to Microsoft Store. explore. It's a very simple you can explore the tool. [01:03:08] points you will see here. I've given enough information on the application and why are we using this Power BI. I believe you all are on the same path. You can explore this. I've already [01:03:22] downloaded, so it is asking me to open. This is one such way to download. go and use the link which I'm sharing right now. [01:03:35] Second link. Official Microsoft Power BI website, which will help you to download Microsoft the Power BI application. [01:03:48] It's a user-friendly, free to download. Please don't pay anything for it. This is pro. They are not going with this pro. Please don't buy it. [01:04:03] download. Another link we have, I believe. to download. I will share another link. You can go with these three options. [01:04:16] Please don't go with third-party links like you can see here. is server. Uh you will not be able to download. This only works for people who [01:04:28] this. And don't go with any of these And don't go with any of these applications apart from Microsoft. see my screen. You can see this Microsoft Power BI, which you can [01:04:43] download for your iPhone. Power BI service is available available for people who are using Mac. But Power BI free option what we have But Power BI free option what we have got, that is not available. [01:04:58] don't sign in. Without signing in, you will be able to work. Please don't sign in. Hope you all can see my screen. This is how it looks after you download. No, it's not. If it is not, then you [01:05:11] have then you might have downloaded uh the other uh third-party one, I think. See, that's what I'm telling. You will not be able to download or use Power BI Desktop in Mac. For Mac, only paid versions are [01:05:26] available. So, Power BI service, which I mentioned, no? That is available. So, for you to work, if you have an iPhone, then it supports you. You can download it to your iPhone. But again, that is only to [01:05:39] view. So, so far it is not possible for people who are using Mac to use this free option. Because this free version is Windows. Not for Mac. [01:05:57] only, or you need the the service one. can see this uh Power BI Desktop. [01:06:11] Can I use service one for practicing? For sure, you can use the service one for practicing. But I think it may ask you to pay something. Please check once. Uh you will be able to uh understand what exactly it requires. [01:06:25] uh it's not available for you to download like how Windows people are downloading. So, I'm just opening the blank report. I didn't save yesterday's one, so I'm opening the blank report. And it was a [01:06:38] opening the blank report. And it was a CSV file, which I sent to you yesterday. [01:06:55] that is the patient data. I'm importing the file. the file. It's a CSV file. This is how to import. It's a CSV file. This is how to import. You already know that. [01:07:09] You can see we have eight rows. There is a value which is missing and you can see a different data type. So, replacing the value, replacing the null value, and doing modifications you already know. So, now we are concentrating on how to [01:07:24] change the data type. We have so many options. I will show you all the options one by one. First with respect to Power BI I will show. You already know the changes how to do in Power Query. Today I'm showing in both. I can say [01:07:37] Today I'm showing in both. I can say this is the data transformation step. Okay. So, this is your date date the sorry data pane. And this is your visualization pane. This is your filter pane. So, here you [01:07:49] can see the table view of the data. So, here also in case of table view you will be able to do the data modification. Like you can create a new column based on the measure. I will show you that. But we are concentrating on data [01:08:04] transformation especially with this column. One by one I will show you. As soon as you select the particular column you can see the patient ID. That's the column name. If you want to change the column name, [01:08:16] here you can do or double click on this here also you can do. And you can see it's a whole number. If you want to convert that to a decimal number, you can convert that to a decimal number. It is asking you. [01:08:29] With this data type your data will be stored differently. This will cause a loss of data or precision. After you make this change you can restore the make this change you can restore the column by refreshing the table. [01:08:45] number data type. If you want to convert that to text, now text. So, this is already in text data type. This is whole number. This is text. This [01:08:58] This is whole number. This is text. This is again a text. This is also a text. This again it's a text, but this as you can see treatment date. It's a date time data type what we usually have. To understand [01:09:12] you know, what was the treatment date of that particular customer customer's treatment date I can say. You need to convert. You just want to do this modification. So see if you want to see the date time data type combination of [01:09:25] date and a time together, then you can use this option to do the changes. This comes see first you need to come to table view, select the column, then you will get this column tools. [01:09:38] Here you can do the modifications now. So now I just want to convert this to a date type. Yes. Now automatically you can see the data transformation is happening. It's a [01:09:54] right alignment of data with both the date and time together. data type. And another one. [01:10:08] Date. If you don't want to go with date and time together, if you only want to see the date, you can convert it, but you can see the format what you're expecting is different here. See here it is. So [01:10:23] what exactly you want to see? Do you want to see the date short date long date or DDMMMM YYYY? So whatever the data how do you want to store, you can go ahead and select it. I just want to go date month [01:10:37] select it. I just want to go date month year. data which is getting converted to DDMMYYYY. [01:10:51] This is how we will do the data transformation in Power BI. Here you can see it's not only that you need to do here. After selecting, you [01:11:03] can come to the format and you can see the multiple options what you have. exactly get the format and do remember an important tip here. This date and [01:11:19] data type is also aligned with your laptop or maybe a desktop what you're be aligned properly. If not, that is the system issue which If not, that is the system issue which usually occurs. [01:11:39] sort the column? So, sorting is nothing but you can sort the column based on particular column names. You want to sort based on gender. See, based on gender. And I selected the treatment outcome [01:11:53] column. If you select this column, sort by column, sort by patient name. We can't sort gender column by patient name. There can't be more than one value in patient name. So, this sort is not possible. Come [01:12:08] here. Sort based on patient name. Usually, numerical data and then a categorical data what we consider. So, if you want to sort age based on if you want to sort age based on diagnosis, now you can see. [01:12:26] You can select. So, if you want to prepare a new group of data, then you can select this option. This is this uh bins is nothing but the This is this uh bins is nothing but the size of the data what you want to store. [01:12:41] the I selected age column and I group the data here. This is one of the option to copy paste it and but this is a bins now. So, age of bins. If you want to add any new measure to it, we will first copy [01:12:56] that. It is called as bins, and then you can measure it, you can create a new column out of it, or you can delete it. This is as same as what we do in Power Query to take a duplicate of it. We will do it with respect to bins in case of [01:13:11] Power BI. Okay, so what I did is in the last call, like yesterday's call, we know we just copy pasted a column. That means we copied and we pasted. It was a duplicate, which was a, you know, which [01:13:25] we learned in yesterday's call. But now, you cannot copy and paste a column like what we do in Power Query. This is your normal Power BI. I'm going with this table view option, and I'm doing modifications for my for the column. [01:13:39] Like the same yesterday, there was an issue with the date and data type, and I mentioned you that we'll be discussing that today. So, we are discussing on that, and I showed you the changes. Now, the data is perfectly aligned. [01:13:52] What other options we have with respect to this table view is to sort the column. So, you're sorting the column based on certain measures. Usually, you need to consider one categorical data, or maybe based on the numerical data, [01:14:05] you will be sorting it. That we discussed here. To talk about data grouping, if you want to create a data group, create a new group of new group to combine multiple values into one. [01:14:22] So, I selected a treatment date that is called as treatment date bins. you want to store it as a bin? It will be asked here. If you go with the list be asked here. If you go with the list for First, I will show you bin only. [01:14:38] let us see how we can create bin. Number of bins. So, minimum value, maximum value, bin count is four. And then, if you go with open you can see here. A bin is nothing but the same data is copied. [01:14:55] The same original data. I selected this treatment date column and I created a bin out of it. Like you can see here. This is a bin. Whatever the calculations you want to do, if you want to do it if you don't want to do it on the original [01:15:09] you don't want to do it on the original data, you will take a copy of that. Directly control C control V is not possible in case of table view. That is your power query. This is with respect to power BI. You have a table view where [01:15:22] you're copying and pasting the bins. I will delete it. Now, see it's nothing but the same data you copied. It's like a copying the raw format of data to do the changes. So now, if you can just [01:15:37] record. I will show you what is list now. For example, the same column I will choose and you will go here, create new data. If you go with list This is how the This is how the list is [01:15:51] stored. Contains all ungrouped data. List. So what exactly you want to group? I will select these three records and I will group. These three records are grouped and okay. [01:16:08] That means I'm just grouping wherever it is Jan Jan 1st I'm grouping. Jan 15th I'm grouping. And again, this two it's these three are the same dates. Uh Jan 1st we have repeat It is repeated two times. I just selected few records [01:16:25] and I grouped them together. They are coming under one category. Yes, because I I didn't change the name. If you want to change the name, I will show you. It's like any name you can any name you can give. I'll delete it and I will show [01:16:39] You're just grouping the data. For example, this is the data what I'm choosing. What we are doing now is the same What we are doing now is the same analysis or data transformation, data [01:16:51] processing, data cleaning which we did in the last call. That was with Power If you remember whatever we did yesterday, it was all with respect to Power Query. Where I copied the column. I replaced [01:17:06] the value. I renamed the column. So, data cleaning part what I did yesterday, it was completely with Power Query. Advanced version of Power BI you can say. We have used that. Now, what I'm telling you is the same [01:17:22] operation. I mean, we can do the same operation in a different way. So, I I'm just starting from the beginning. We imported the data. I will beginning. We imported the data. I will remove this column. [01:17:42] Okay, I'll just import the data again from the beginning. I will remove this. So, I'm importing the CSV file. Yesterday, I you know, by the end we had [01:17:56] a question that is how to do the data transformation, especially with a date time data type. As we can see the changes in the data type format here. Slash I mean, dash back slash. So, we wanted to do the data [01:18:12] transformation. It was not possible with the Power Query that. So, what I'm doing, not only one option we have. We have so many options to concentrate on this data. I will move to table view. Now, you can see the table view of the [01:18:26] For example, you want to copy paste this data. Control C, control V. It's not possible for me to do that with table view of Power BI. But with Power Query, [01:18:38] you can do it. Not an issue. So, let me to copy-paste the original data. What I will do, you can just come here to data group, go with the new data group option. You just do a group. [01:18:58] Statement date. Sorry. This is a text data now. What I will do, first select apply the changes. Whatever the changes you want to do, apply on this. you want to do, apply on this. I just want to convert this to a date. [01:19:13] Date type change. You are changing the date time. See, as soon as you select the data, any column, as soon as you select, you can see the name of the column and the date and the data type of the column towards your [01:19:26] uh right left-hand side top. So, now we want to do the changes here. I will select the select the column where where I want to do the changes. I'm applying the changes now. I'm converting that to a date. [01:19:41] converting that to a date. It is dash. It is backslash. You want to make sure all the data type should be aligned. And this column, you can clearly make out, it should not be a text format. It is a calendar date. You [01:19:56] text format. It is a calendar date. You want either date time or date. So, now I want date. I'm going with the date. You can see the column data type is changing now to date. Again, you don't want like 1 January [01:20:10] Again, you don't want like 1 January 2023. So, come here, apply the changes. 2023. So, come here, apply the changes. You want date, month, year. Or you want whichever format you want, you can choose. I'm just going with this [01:20:23] you can choose. I'm just going with this DD MM YYYY and dash format. This is usually everywhere people use the same format. Now, you can see the data format. Now, you can see the data transformation. [01:20:40] to your columns in Power BI itself. Sometimes, if it is not possible in Power Query directly, you can do that in Power BI. Hope until here everything is clear. So, we uh did change I mean, we changed [01:20:55] here and you can automatically see now it got converted to a calendar icon. it got converted to a calendar icon. Okay? So, this data I mean, here [01:21:10] Now, we have another option. So, as soon as you select the column, so you you can see here don't count distinct. If you go with count, [01:21:24] Here, sorry, wait a second. Okay. So, this will let you know the count or the count or the don't summarize. Usually, we don't summarize. We want to see the actual This is uncategorized. The data category uncategorized comes here. [01:21:37] Here, you can This is by default category options you have, but nothing category options you have, but nothing to worry about it. I'm concentrating on this data group. We are creating a group data by common value or a common [01:21:51] do the grouping in the similar way of grouping the data now. column. So, how do you want to show it? Yeah. Failed, in progress, success. I want to group the data that is failed and in [01:22:04] progress together. I'm choosing. I'm creating a group out outcome group. Group. So, failed and in progress values are together. Successful is earlier. I would [01:22:18] I'm creating a new group here. You can add a group or exclude other Okay. And you can do that. Wherever it is successful, you can wherever it is in progress and wherever [01:22:30] it is failed. The purpose of doing this movie what I'm looking at and I'm looking at the data. So these numbers automatically [01:22:45] by default you get failed and wherever it is in progress and in failed and in progress. If you want to change this to data between so wherever it's failed and good issues [01:22:59] or if you want to name it you can see only two categories or inactive. [01:23:11] categories and you can create a new column out of it to or to read this data groups. The purpose of doing this is all of The purpose of doing this is all of these main categories [01:23:33] or again data clean. Not only this our query [01:23:49] you can give me if it is numerical data you get this Now here you need to select and you need to grab all of the data that we can do [01:24:01] example, move over to patient name, patient name is here and patient age is here. Okay, so this is the data. patient age. This is the table format of data how to view. [01:24:24] So here if you want to see the patient name. Here we can apply a advanced filter and you can filter the data based on how exactly the customer name begins with. It contains particular customer name or and operator or operator logical [01:24:38] operator you can add and you can filter the data as per the requirement and then you can view the data here. So you we will be learning all this in the coming up you know our topic. [01:24:52] So multiple ways we have to do this analysis not just one way. So this is about how this is how we can do the data type changes. That we change the data type which it was not possible yesterday for us to do [01:25:08] was not possible yesterday for us to do because of certain data type issue. So we know what are the different data types we have. We have discussed all these topics yesterday and until here we completed. Like we also know how to [01:25:23] create a column and how to do the data transformation. We will learn more about data transformation in the other examples also. See whenever we say data transformation [01:25:37] it is also about sorting arranging in ascending or descending or grouping the data just what we did, removing the duplicates. [01:27:17] almost the same. Power query is as it is already or transformations what we are doing. Advanced transformation let us discuss today that is merge query append the [01:27:33] today that is merge query append the query pivot column unpivot column create tables. Apart from the basic transformation we Apart from the basic transformation we can also go with the advanced operation. [01:27:46] What is this merge queries append queries? If you want to merge multiple columns together so column wise if you want to merge the data then we go with want to merge the data then we go with that is called as merge query. [01:28:04] will show you that. To talk about append queries if you want to append row wise like you have five rows I will give you another five rows You append You append the data. Now with these 10 rows column-wise three columns you have. [01:28:18] Again, add a new column to it. That will be your six columns right now. In just a second. So, create calculated columns. If you want to do any calculation and if you want to store it in a new column, that [01:28:31] we are doing it here. Then create a calculated tables. In a table-wise also, you can do the calculation. Pivot column unpivot. Uh we all are using Excel. How we do data pivot? Like how we use the pivot option what we have in Excel, we [01:28:47] can also do that in pivot columns. Like based on column-wise preparing the data or based on row-wise preparing the data. That is pivot and unpivot what we have. I will show you all these examples now. So, to demonstrate how to clean and [01:29:03] transform sales data by creating I think this conditional logic is done. This is this conditional logic is done. This is about merge query. Merge is all about See, product ID sales amount you have in one one data. In the other sheet of [01:29:17] data, you have another data. So, you want to join them together. It is a type of query that combines a data from two or more tables. You have Excel sheet data. You have a CSV file [01:29:30] Excel sheet data. You have a CSV file data. You want to merge them together. That is possible with respect to Power BI. We are merging two data sets. That together. With respect to append or merge. [01:29:48] So, we have different types. You can see the purpose, join type, join condition, and the result. Combines data based on a common column. Add rows to the end of the table. Then you merge, column will be merged [01:30:03] together. When you go with append up rows will be added together. This you can expect as an interview question. So, merge is to add multiple columns. [01:30:16] So, merge is to add multiple columns. Append is to add multiple rows. Whenever you go with merge query, you have options like inner, left outer, right outer, full outer, or a cross join. When you go with append, it is [01:30:30] appending the rows, right? So, all the columns by default will be added. So, union or union all it is. When I talk about merge column, you can merge based on a particular column like a primary key or all columns if you want, you can [01:30:45] merge together. So, in case of merge requires a common column for matching the rows. Here, no column is required because you're merging below, one below the other. New table with rows from both the tables [01:31:00] based on the joint type. New table with rows from appended tables. Here, you can see multiple columns and the rows will be added. There also you will see multiple rows will be added just under the same [01:31:14] So, append and merge are the two different operations which we will be doing. The main purpose of doing this is to merge different data sources together or merging different data merging or [01:31:28] merging different data merging or appending different data sets together. Any questions here? We will I mean, I will show you how to do all this, but for now, understand these topics. I will show you how to do it. [01:31:40] Hope you all can see this PPT. So, what I'm trying to do now is one is merge and the other one is append. When I talk about merge, this is your data set one [01:31:52] about merge, this is your data set one plus this is your data set two. You have three columns here. You have three columns here. You are merging. So, three columns here. You are merging. So, the final result is nothing but [01:32:12] is also a table and the other one is also a table, but combine data based on the common column. You You will create a link from one table to another table and you can grab all of them [01:32:24] together. It can be a inner join or it can be left outer join or sometimes even if you don't have a common column, if you just want to merge them together, it is possible to do that. [01:32:37] Like adding new columns to the existing data source. How we added new rows and we saved it. When you refresh it, you get the new rows, right? That is nothing but a pen query. Where in this case, here you have [01:32:56] Append, this is the append query. Here you have four rows. Four rows. The final result will be three three plus four, which will be seven rows. Here it goes a row wise. Like rows will [01:33:11] be getting appended. At the end, the new records will be added. For example, yesterday's example, like it was first five rows yesterday. I added two or three records to it again like a row wise. The new records added. [01:33:25] That is called as the operation is called as append query. When I talk about merge, merging the data set together, that is based on the column. So, the main purpose of doing all this, adding, merging, adding rows, adding [01:33:39] column, it is all to concentrate on the data, mean basically the data set and data, mean basically the data set and the column names and also to have one proper data set or maybe like multiple data sets combined together to build a [01:33:53] dashboard. For all this, we will go and do all this operations. Cardinality, data quality, improve the performance, and the business logic to create a relationship. So, data in data modeling, we come across this cardinality to to [01:34:08] check the connection between one data set to another data set. Maybe one data and then other data may have one-to-one connection or one column with multiple connections or many column, many connections. [01:34:21] today's agenda actually. That is data So, data quality, of course, any data whether you merge or you append, always maintain the data maintain the data and the data should be [01:34:34] clean with a very good quality. Data consistency is very important. Improve performance, optimize relationships by using appropriate data types and index. You already know how to do changes to the data type columns. [01:34:50] Business logic, align the relationship with the underlying business logic and the data requirement. Because when you are merging columns or when you are adding new rows, always we need to check whether at the end are we getting the [01:35:03] right data result or not because it's not about having a big data, but having a quality data and a data which is giving you enough information. Business logic, it's important. So, let us work on this now. So, to demonstrate [01:35:19] how to combine data from two different sources such as a CSV file with the movie ratings and a JSON file with the movie details using merge queries in Power BI. So, you can see whenever you import apart from Excel file or CSV [01:35:35] file, if you import, it will directly take you to Power Query. The main reason is when the data is an Excel file, it clearly shows that we have worked on that data. So, it will ask you to load the data. You can see as soon as you [01:35:49] import, you will get load or transform. When you When you select load option, the data will be automatically loaded and you can see that in data pane. But when you select transform option, it will automatically take you to Power [01:36:02] Query. The reason behind it is you can do the data transformation, you can do the data quality check, you can work on the data manually before going with the dashboard. That is the purpose of That is the difference between load and [01:36:16] transform. This also you can expect like, you know, as an interview question, what is the purpose of going with load? What is that transform option? What is the usage of it? That's the usage. Like, when the data is Excel, [01:36:29] automatically it will ask you to load. Or you can also go with transform option. So, I imported the data and JSON file as it was the reason, so it took you to I mean, it take It will automatically take you towards Power [01:36:42] Query. But now you can close it and you can come back. You can see the movie details here and you can also see the ratings here. If you go with this table view, you can see this Right now it is movie details. [01:36:55] ratings. Now, let us move to data table or the model view, I can say. So, this is the model view. Model is nothing but to check the common details here between [01:37:08] movie details I mean, check the common column. The common column here is movie connection. See, I don't have rating here or I don't have a review in me. Here I don't have a director or I don't have a title. [01:37:23] Only column which is getting connected here the movie ID. schema in the model view. You can see here the model view. I selected the [01:37:36] model view. If you move towards table view, you can see the tabular data structure of this. Now, we are moving towards append query and merge query. Okay? For that, we need to move towards [01:37:48] Power Query. Directly after importing the data, you can move towards transform data. Hope you can see. Select the table, that means selecting the movie details. Okay? We selected the move all the [01:38:02] columns here. You can see the transform column option we are getting. So, you can see here merge the columns. Or you can also see here uh remove the column, remove other column, add column, remove duplicate, remove error. You can [01:38:18] You already know how to do all the changes, but now we are going with the append and merge. So, we have a common column that is movie ID. And you can see here we have an option that is to add add as a add as new query. [01:38:35] Or you also have an option to group the data here. If I go ahead and check this title, you can see add as a new query. And here also you can see add as a new query, bring down, split the column. Splitting [01:38:49] the col- splitting the column I think I can discuss on that data. Come to the can discuss on that data. Come to the CSV file now. Here you can see add as a new query or other options whatever we have. In transform also, you can see the [01:39:02] have. In transform also, you can see the same options whatever we have. [01:39:17] new data, this column is particularly added here. added here. So, you can also just delete it here. [01:39:45] Just a second. I'm just checking here the column. [01:39:57] Okay. So, movie ID, movie ID, title Okay. So, now comes merge the query, append the query. Merge queries as new, always go with [01:40:12] merge queries as new. I hope you all can see on top under home option. Uh any column you can select that is not an issue. You will get this merge query and append queries every time. [01:40:26] I will just come to merge query. What you want to merge? So, this is the movie details column. I want to merge the ratings column, as you can see. I'll show again. I think I just did it without showing. [01:40:40] First, select I'm just selecting movie details. Come and select any of this column or all the columns also you can select, not an issue. This is your This is called as tables or queries also it is called. Come to this merge query. So, [01:40:55] merge query means merging the columns. Okay. Select this merge query. This is your movie details. Base What you want to merge now? Come here. This is the current data. [01:41:08] Movie details is already here. This table is already here. Go to the ratings column. Okay. So, what joint you want to do? You want to go with left outer, right outer, full outer, inner join, left anti, right [01:41:23] one. Use fuzzy matching to perform this merge. This is automatically matching that is nothing but the option. that is nothing but the option. So, I'm going first with left outer. [01:41:48] 1 second. Let's take that. The ratings column. Here it is movie ID. Here also it is movie ID. You need to select the common column that is movie ID movie ID. It's the common [01:42:02] column. See, the selection matches three of three rows from the first the table of three rows from the first the table not an issue. data is added. You can just select it and you can go with expand. Okay. [01:42:19] Now all the data is expanded. Based on the based on movie ID you are merging all the data. The movie ID is one. Movie title is here. Just a second. I will explain again what I did. So, movie ID is here. Movie title is here. This is [01:42:34] derived. Now I just went to this CSV file and I grabbed movie ID, a rating and reviewer ID. So, you can see movie ID, a rating and the reviewer name. You can also see from which column we are getting it. [01:42:49] What I did is if I just do this, see this is nothing but after you merge it, you will get this option. This is nothing but the this option. This is nothing but the table is added from ratings CSV file. A [01:43:03] new table or that from the ratings CSV file the new columns are getting added to it. Initially it will be a table format. You need to select this drop down where you can see add particular column if you want to merge. You don't [01:43:17] already have a movie ID. You can unselect it. Okay. Now you will see only rating and the reviewer name. the reviewer name. Again you don't see the movie ID here. [01:43:33] is the movie details. In movie details, you have three rows. In ratings, you have four rows. I hope all are clear with this. I hope you also have the same data. So, now [01:43:45] what I'm doing, I'm just directly going with this merge query option. With the available data, this is the movie details what I have. I have three movie details what I have. I have three rows. Just select the ratings file. [01:43:58] Now, you need to select the column what you want to merge. That is movie ID. Movie ID are the common columns you're selecting. See, movie ID is one. This is the title. He is the director. Movie rating is here. Movie one ID, movie [01:44:13] rating for this movie and for this director, the rating is here. And who gave the review? The reviewer name is also here. So, I should get for one, two, three, but I don't have anything for four to get linked or to get merged. [01:44:28] Okay? So, I selected it. I'm going with the left outer join. All from all the records from the first table, matching record from the second table. So, one, two, three will be there. See, use fuzzy matching to perform the [01:44:42] merge. This is nothing but fuzzy matching options when you have a uh special characters, then we will go ahead with this. But we we have a very less data and this option is not required, so it's not required to, you [01:44:56] uh go ahead with that option. Okay. What this So, we connect the common columns here. So, full outer left outer mean selecting all the data from the first table and merging it from the second table. [01:45:13] So, you get all the records from first, matching data only from the second table. Now, this is your first table and this will be your second table. Whenever you are connecting, whether it is Python or SQL or Power BI tableau, [01:45:28] you will be able to you can only have a one primary key in a column. Multiple unique data or IDs will not be there unique data or IDs will not be there in the data set. [01:45:42] One-to-many connections are possible, but many many connections is not but many many connections is not possible to prepare entire data or possible to prepare entire data or entire data. [01:45:56] You Here also you can have movie ID title. But, the common connection is movie ID. Movie IDs can be titles can be repeated, but movie ID it's the unique value here like which act like a primary key. [01:46:11] So, we selected select okay. As soon as you select okay, you can see this option. You will not be able to see the data. You will only see the table here. Here you need to select this. Do you want to go with aggregate or do you want [01:46:26] to go with expand? I mean, expand is nothing but for you to view the data. See, movie ID movie ID I don't want to repeat the same column. I will unselect it. I want to see movie ID. I want to see the title. I want to see the [01:46:39] reviewer name and rating. So, I will only keep these records with me. See, use original column name as prefix and later you can change the column name here. See, this column name is file name dot column name. File name dot [01:46:55] column name. Can you see this option a drop-down kind of option here? So, here expand or aggregate. Expand is nothing but for you to view the complete data. Because you are viewing here as a [01:47:09] table. You are not checking the complete data here. The linked data or the merged data is not here. Merging means adding multiple columns, but you are not able to see the columns here. So, what we need to do, come here, select the column [01:47:25] what you want to view. Movie ID do you want to view? You already have a mean if you select with okay, see movie ID movie ID like from this ratings.csv file Just a second. You are grabbing every [01:47:39] all the columns. So, you can see movie ID rating and reviewer ID. column, you need not have to select that column there. [01:47:52] aggregate. Aggregate is nothing but do you want to see the aggregate functions are nothing but some count, minimum, maximum, average. All these are aggregate options. Whenever you work with the numerical data, then it is good [01:48:07] to go. See, if I go with sum of movie ID movie ID one, movie ID two, movie ID three. What is the purpose of some going to What is the purpose of you know, the taking a sum of all that? See, if I go with sum of ID, sum of rating, count of [01:48:24] reviewer name, and you can see this is the movie ID. This is sum of rating. Sum of rating is nothing but from here you're getting 4.5 3.8 2.7. Uh you can see here 4.5 3.85. Count of reviewer name. This is This will be same [01:48:40] because you don't have the same names repeated multiple times. And here you can see reviewer name. How many people give review on this first title movie ID is one is the director. It's only one person. There is no purpose of using [01:48:55] this aggregate function here. In this for this data set, not for all the data, for this data, it is not required to go with the aggregate functions because we don't we don't need to find any sum or count or average [01:49:07] to find any sum or count or average here. expand because you want to see the entire data? Whatever you are merging. [01:49:19] You can ignore the same column because it is repeated. If you want to see, you can keep it for sure. >> Okay. >> This is just See, expand is to expand the date expand the column so I can see [01:49:32] the data. Aggregate, it is to If you have any numerical column and if you want to sum them and check, then you can go with the aggregate. But aggregate will not work for all the columns. The main purpose of [01:49:45] merging query is to see all, you know, multiple columns what you are merging together. And expand is what is suitable for that. [01:49:57] merging, that is adding multiple columns. Let us go ahead and see how we can append the queries. Okay. Append three or more tables or only two tables. I will go with the [01:50:11] same. So current data is movie details. I will go with the ratings of CSV file. Okay. See here. How it is getting appended. You have a common movie ID column. Same column [01:50:25] names will be appended. What is happening here is if you don't have a same column name, then a new column will be added and you here. In rating CSV file, I have movie ID, I [01:50:40] have rating, I have a reviewer name. So movie ID is getting appended in movie. I don't have title or director, so it is null and null. I have a rating column which is getting updated here. But for me, I don't have data in the first [01:50:54] table, so it is null. I don't have data for the reviewer name in the from the first table. Again, it is null. Here, you can see the data is getting filled. This is your append query option. [01:51:06] Which is I mean, the data rows are appending and you can also see the appending and you can also see the column. The new columns are also here. This is append queries. So, if you want to go with the append this is the data [01:51:19] you can see now. I'm just going with append query as new. See two tables. First table is movie details or you can interchange not an issue. Second table is movie details now. So, okay. [01:51:33] Now, you can see the new appending new is nothing but it will not do the changes in the original data. A new table itself will be created. This is good to go because original data will not be affected. Earlier when I showed [01:51:47] you an example, it was appended to the original data itself. But when you go with as new every time, no the new table itself is created and you can see this data in the new table. Uh I will go with [01:52:00] the right outer right now. Right outer in the sense all all the data from the second table and whatever the data is from the first table which is matching, that will be you know, merged together. [01:52:15] And the column you need to select is movie ID. That's the unique data. The selection matches three Uh come to merge query. If you don't select the data, see right now even you don't select the movie ID and movie ID, [01:52:30] this okay option will not work. You will not be able to merge. You need to select the movie ID. This indi is support. If not, it will not support. Now, go [01:52:46] with okay and expand this. I don't I will see all the columns. So, I will just go with okay. Why this is null? Because you are you can you are merging CSV file and then this move I mean first rating file [01:53:02] and then this movie file. You will get all the details from the ratings file because this is the table what you selected and this is the right outer. Here you can see we have null because we [01:53:14] Here you can see we have null because we don't have data in this movie in the movie details file. This is right outer what I did. We will get null when data is not available and do remember you need to know the joints [01:53:28] what you are doing. Right join, left join based on that this is join based on that this is this result is dependent. details and this is the ratings CSV file. How do I decide whether I want to [01:53:44] go with right outer or left outer? So merge query as new. This is my ratings file and this is my movie details file. You need to see what result. Now I want to understand movie ID and director. Here you have movie ID. [01:54:02] Here also you have Here you have a movie ID. Here you have a director. So you ID. Here you have a director. So you want to see movie ID and director. what you [clears throat] will do from this ratings column you will select [01:54:16] movie ID. Here again you will go with director maybe. You will select You're selecting movie ID and you're selecting director. You can see the selection matches zero of four rows because this is not the common column what you're [01:54:30] selecting. Common column is here. You're selecting it. So here you can see what you want to see left outer. All the data from the first and matching the second. If you go with right outer, all the data [01:54:44] the movie table table you get all the data and only matching data from the first table. Full outer, all the rows from both. Inner, only the matching rows. Now I will go only the matching rows between a [01:55:00] ratings column and movie details column. I want to see movie ID. I want to see director. That's it. Okay. created. You need to I mean you need to expand the data to [01:55:14] view. What you want to see? You want to see just movie ID and the director. I will select only movie ID. This is the movie ID. This is the director. If you don't want to retain this, select it and you can just [01:55:29] sorry, remove column. Now, you can see the proper data what you wanted to view by merging movie details and the ratings CSV file. It completely depends upon what analysis and how you want to showcase the data. [01:55:44] showcase the data. You have one Excel sheet where you have all the movie IDs, the title of the movie, and the director name. You have another Excel sheet that is a rating. You have a movie ID. You have a [01:55:59] rating given to that movie ID. You have a reviewer name. In this rating file, you don't know the movie You don't have the movie name. But in the movie details, you have a movie name. How do you merge these two files now? You have [01:56:14] you merge these two files now? You have a common column that is movie ID. This acts like a primary key. Primary key and foreign key are the two different terms which we use when we work with the data with multiple data [01:56:28] sets. Primary key is nothing but which is a unique column, unique data in that That is primary key. This primary key will be a foreign key [01:56:41] in the other data set. That is helping you to build a connection between the you to build a connection between the two data set. So, movie ID is the column which will help you to build a connection. And how How you know movie [01:56:55] connection. And how How you know movie name is helping you to build if I just take you towards Power BI model view? See, this is one-on-one connection. Movie ID from the movie details is getting connected with the ratings CSV [01:57:08] file. This is a bridge which connects these two data sets. With this knowledge, you can move towards the transform data option now. Now, you can do the data transformation. [01:57:22] One such is to merge merging the query. So, we merge the query here where you want to see the review of the who's the director and what is the movie ID of the [01:57:34] director. Only these two columns you wanted to see. So, you merged it because you don't have director details in rating. You don't have I mean, you have a common ID which is a movie ID to connect these two files. And [01:57:47] you can see the you know, the movie details sorry. I think movie details we add here. I'm sorry. I think I wanted to show you the rating of the movie ID. [01:58:04] two. Movie ID is here. Sorry, movie ID something which is column. You can see movie ID column which is the common column between the two data set. Rating is the column which you grab from the ratings CSV file. [01:58:23] do how this join works with respect to SQL, then this will be easy. Okay. So, this is the clean data what I have right now like what you all are Movie details and the ratings file. Now, let us go and check up and query. Good [01:58:39] to go always with up and query as new. Appending is where rows are getting added. Ratings CSV. Two tables I have. If you go to three or more, then multiple tables you should [01:58:53] have. I don't have so much data right now. I'll go with the two tables. So, rating is the first table. Second table is movie details. Or you can change it, not at all an issue. Concatenate rows from two tables into a [01:59:08] single table. That is the purpose of this append query option what we have. I'm concatenating the two tables right now. Okay? now. Okay? Okay. [01:59:26] means multiple rows are getting appended. See, when you normally see two tables, movie ID, title, director, uh rating, and review. Total five columns will be there. 1 2 3 4 5. The [01:59:42] same five columns we have right now. Movie ID is filled because movie ID is available in both the tables. Rating only from the first data set it [01:59:54] is filled because this is the first data what I considered. First data is filled. The remaining 1 2 3 This is from movies data. I don't have these columns in movie details, so it is null. I don't even have reviewer name column in the [02:00:08] movie details. Again, it is null. Coming to title column. Title column will be null in reviews ratings CSV file, and below it is filled. This below is nothing but from the movie details. [02:00:23] Again, director, it is filled, you know, with the help of this movie details, so with the help of this movie details, so it is filled. The above one is null. This is how append query works. Wherever the data is not here, wherever the data [02:00:37] is missing, maybe from first table or from second table, accordingly, you can from second table, accordingly, you can find this null value. [02:00:51] replace the null value, if you want to replace any particular value, you can do that. So, Microsoft Power BI with the AI modeling? So, data model. [02:01:06] Quick recap, that means related to whatever we just covered. Power Query helps to load, clean, and shape a raw data prepare it for accurate analysis using [02:01:18] column tools tools and selecting the correct data to improve the data quality. AI features in Power BI assist to automatic cleanup task like the data distribution, data clean, data error, [02:01:31] data sorry, the column profile, column distribution, whatever you see, you know, that will automatically help you to understand the error rate or the to understand the error rate or the accuracy uh rate of the data. [02:01:45] So, now engage and think. You are building a report in Power BI using multiple Excel files. One file contains order details, other other file contains a product information, a third includes customer [02:02:01] data. See, this topic what we are discussing See, this topic what we are discussing now, it is closely related to append and merge topic. Left join, right join, outer join, [02:02:15] whatever we just discussed. It is closely related. So, if you have any questions, please stop me in between. Okay? So, when you create a chart to show sales by product and by month, the number appear incorrect and [02:02:30] some filters do not function properly. If all the necessary data is available, what do you think might be missing for the report to work correctly? You have the data different data available in different Excel files. [02:02:46] You want to do the analysis, you want to create a chart. You will you are merging all the Excel files together, but still some of the data is missing. Some of the data is incorrect because we [02:02:59] didn't do the data transformation. Or what other options we have to link all what other options we have to link all of them together? Let us see how data files or data sets is connected. What are the different connections? What [02:03:14] different connections are possible? So, let us see that step by step. This is one of the important topic before you build the dashboard to check the data connection, especially with multiple data sources, maybe from a different [02:03:29] database, or you upload manually via Excel or a CSV file. The steps will be same. You can expect maximum interview questions on the maximum interview questions on the theme. So, please understand. Requesting [02:03:43] all of you to go back and rework. And there are some study materials available related to data join, that is left join, right join, to understand it better. Within 1 or 2 hours, it is difficult to understand all the topics, especially [02:03:58] So, please rework. And if you have any By the end of this lesson, you will be able to understand how to design a structure data, how to merge multiple [02:04:10] data together, how to prepare a report out of this new data, and of course analysis, apply a role-playing dimensions, and configure relationships [02:04:22] to ensure reliable data behavior across different data sets. Create a centralized data table. That means whenever you have multiple tables, it is good to create one centralized data, one unified data view. You can [02:04:38] also export the data whatever you created to Excel sheet. A simple thing, you merge multiple Excel files together. One Excel you I mean uh you prepared a proper report out of all this by [02:04:53] merging, by appending, you created all the data properly, all the columns, all the null values you replaced. This report what you built, you can download that report. So, create a centralized data table and [02:05:08] link it with other tables to enable time-based insights. Define roles and permissions to securely control user-level access. We have different level of access, the row-level security, and the lot [02:05:21] different lot if we have. One by one we will see all that. Okay, I believe you have no question based on that uh you know, based on your opinion, I'm proceeding further because all the topics are interconnected. [02:05:36] all the topics are interconnected. Design and implement a data model. question, uh see uh most of the cleaning part, transformation part will be taken [02:05:49] care by database. It depends on how well we are maintaining the database, how well the organization is maintaining the database. See, different databases, they have their own advantage and disadvantage. [02:06:02] So, from database, if you are pulling the data, so you will write a query and you will grab only the relevant columns what you want to create a dashboard. [02:06:14] So, when see what are different steps whatever I'm showing, no, all these steps you will not use in your project. You will not use in your dashboard. Whenever time comes, you should be in a position to do data cleaning, to do data [02:06:28] transformation, to append or to merge or to remove duplicates, all these are the options what we are getting with I mean from this Power BI or Power Query. So, if the data is already cleaned, if [02:06:45] there are no duplicates, if data is maintained very well, you have all the relevant data which you need to create your dashboard, then you need not have to think about Power Query. You need not have to clean the data. [02:07:00] When data is not cleaned, when data transformation is not happening properly in the database, or maybe some calculated columns that you created based on the available data, [02:07:14] during the time only we need to go with this data I mean the Power Query or the dashboard. So, lot the whatever there are so many ways of cleaning the data. You can clean in Excel and then get it here, or you [02:07:27] can automatically do it here with the available procedure or the steps. So, it depends on how we use Power BI. To be honest, if your organization is maintaining a very good data, you spend very less time in cleaning the data in [02:07:43] this using this Power BI. But if data in the base itself if it is not clean, then you need to spend maximum time. So, if you ask me what percentage of data is transformed using Power Query, when data is being pulled from the database, it [02:07:58] completely depends on the data. So, whatever the data is getting pulled, so here. If this raw data is good to go, if no changes required, then you can directly take the raw data to create a dashboard. [02:08:14] If the data transformation I mean if the data is not good, if data quality is not correct, you need to spend more time in this cleaning part, in this transformation part. Others it is all whatever the different [02:08:29] examples we are learning now, it's not that you will use everything in your dashboard or everything in your project. Depending on the scenario, depending on the data set what you have, you will be choosing in right scenario, right [02:08:43] analysis, or right techniques, I can say. So, different data sources, either you add the query or directly import the [02:08:57] data. Relationship, this defines how one data set is connected to another data set. Different data types, each column talks about different data types. Make sure the column is having the right data [02:09:14] Or if the data type is not correct, you already know how to do changes in Power Query, and also you know how to do changes in the view report option, that table view option. So, requesting you to use [02:09:29] use it. And to talk about measures, we have a new measure and a quick measure. I will show now how to use that. We can write certain formulas there, and we can do calculations on top of the column or [02:09:44] on top of row, whatever the data we have. Calculations performed on the data are called as measures. Hierarchy, predefined levels within the what you selected a column, inside that column you are trying to create a sub [02:09:59] hierarchy. So, that's called I will show how to prepare all this. Hierarchy or a data hierarchy it is called as a predefined level that you will do it. [02:10:12] Calculated column that stored directly in the table as a new column. You see measure is where you apply a formula on the entire column or on the complete data to measure it. Calculated column is where you will calculate a certain [02:10:29] record and a new column itself is created. Either you go and use measure. When you are creating a dashboard, we go with this quick measure or new measure option working at. If you want to create a new entire new column out of it, we [02:10:46] can go with calculating the in the new column. So two or three options we have. Uh the main I mean sorry, the main advantage or the key concepts of data modeling is again the performance we expect to be [02:11:00] again the performance we expect to be good, accuracy we expect. good, accuracy we expect. Flexibility that is connection between should be easy. Scalability. The data Again, the data [02:11:14] size The data should not be something which is big. If then if it is huge, then it affects the dashboard or the Power BI. If it is paid version, free version Sorry, if it is free version, in paid version, we usually don't come [02:11:28] across the scalability issue. So let us go and understand the different technical terms now to understand the data and database with respect to this Power BI. If you're working with the data, like a important [02:11:43] topic that is having a good knowledge on database, having a good knowledge on SQL is very important because whether it is Power BI or Tableau or if you are using Python because these are the tools and programming language which we use [02:11:59] maximum to work with the data. So if you know about database, data modeling, data analysis, this will this will help us in all different tools and scenarios. [02:12:11] So dimension table and fact table. Dimension table and fact table are core components of data warehousing. They help data structure in a way that [02:12:23] simplifies data query, data analysis, and data reporting. Identifying fact tables and dimension tables enables efficient data modeling and supports [02:12:35] effective analysis and reporting. Whenever you have a data with you, you should try to distinguish what is dimension table and what is the fact table. That will help you personally to [02:12:48] create a effective dashboard. Let us see how we can do that. So, to talk about a dimension table, dimension table is something which is dimension table is something which is giving you a good information and it is [02:13:01] something like a unique data and the data what you identify as a dimension table may help you to connect that using that column, you can connect another data set from the other file. Dimension table contain attributes or a [02:13:18] descriptive information that define the context of the data. They help to context of the data. They help to categorize the data or to group the data Like a customer table where you will find customer ID, name, product [02:13:32] dimension table, something which is unique data, unique column. Which is giving you a very good information. Location dimension, country, state, city, region. Descriptive [02:13:45] stats and also a and also a column which is giving you a Analysts use dimension table to categorize data such as product category or customer segmentation [02:13:59] to also identify the trend. So, this is about dimension. When I talk about fact, fact usually the measurement or a continuous values I can take. Sales, quantity, price, profit, cost. [02:14:16] Inventory levels, satisfaction. All these are nothing but the fact table. Fact tables contains numerical measurements or quantitative data associated with the dimension tables. They represent the actual data points [02:14:32] that are analyzed and reported for business decision making. When I thought when I take an example of customer table, you have customer ID, you have customer name, you have customer location, you have customer [02:14:47] ordered, what is the quantity, what is the cost. So, here customer ID and important customer details what we have like customer details what we have like customer name, customer ID, this will be [02:15:01] a dimension. Customer quantity, customer sales, customer order history will be the fact table. It is our own responsibility to understand, you know, the fact table and the dimension table here. [02:15:24] when you are working in depth on a particular report, you will distinguish this. Fact table helps us to analyze the sales amount, quantity, like in depth analysis of the data. [02:15:41] expect as an interview question. What is dimension? What is fact? Uh in Tableau, you can expect what is measure like green and blue category and measure. Here it is fact table and [02:15:55] dimension table. If you know what is customer satisfaction rate, you can understand, okay, the customer satisfaction rate is neutral. It is three. Okay, this is the sales amount. It is [02:16:07] 3.5 CR. Where it is giving you the information related to business operation. But customer ID, something which is unique, customer name, with that it's a dimension table. [02:16:20] Where you will you know basically concentrate this dimension table will help you to connect the data because you all have the unique ID and it is giving you a description about the data. But it is not giving you [02:16:35] the entire in-depth information about the data. So that is the difference. fact. In this data set, customer ID we have, [02:16:48] product ID we have, date we have, and sales amount we have. Date When I talk about dimensions, that is basically a descriptive stat or where you are having Sorry, dimensions and facts are interrelated. [02:17:03] Facts are linked to a specific combination of dimension. See, this sales amount can depend on date or can depend on the product or can also depend upon the customer. Sales amount may relate to the other [02:17:18] columns, but this customer ID, product ID, date will not depend upon the sales amount. Customer ID, product ID, date are Customer ID, product ID, date are dimensions while sales amount is a fact. [02:17:32] That's the actual figure which where you can consider which you can consider to take a business decision. But product ID, customer ID will not, you know, create a major impact. I It will not give you a [02:17:46] uh very good business decision-making, I can say. [02:18:04] Star schema and snowflake are the two common data modeling techniques which is used in different data warehouse and data mart. >> [cough and clears throat] >> Sorry, this topic is not completely [02:18:18] related to Power BI because snowflake again it's a different database management application. So you need not to worry much about this snowflake application right now. That's a completely a different tool. Why we [02:18:34] are used then why are we learning this because the technique the data modeling technique what is used in Power BI it is as same as the star schema or the snowflake tool. They are designed to optimize the data [02:18:48] query and analysis particularly for reporting and for analytic application. Like the same same you know schema we have here. In star schema the data is organized around a central fact table which [02:19:05] contain measurements or a metric surrounded by a dimension table. This is giving you customer name, customer ID, product name, product ID and the The fact here is total sales [02:19:21] quantity, profit, loss. So the fact table is in between surrounded by a surrounded the surrounding will be again a field with the different columns which is nothing but what we [02:19:35] have the descriptive analytics or descriptive stats you can call. So dimension table. Fact table and dimension table. Surrounding this fact table are dimension tables which provide context [02:19:49] or attribute and do remember these dimension tables are independent. Fact dimension tables are independent. Fact table depends upon the dimension. [02:20:03] So, fact table usually stores numerical data or any metric about a specific event or occurrence. Dimension table contains descriptive information about the fact table's data. That means [02:20:15] the fact table is depending on the dimension table. And this is the fact schema structure. So, configuring table and column properties. So, formatting the data, you already [02:20:29] know. I showed you in today's call already. This will help you to maintain already. This will help you to maintain the data, format data call data type or a data formatting option what you all have. Whole number indicates integer [02:20:42] number. Currency will be the different currencies or the rupees or you know, dollars what we have. Decimal number is a floating point number. Then percentage result will be in different percentage. And the fact depends on what data you [02:20:57] So, select the column, right click on the column, and then you can check the data format over there. You can select what format you want to apply. All these options, where can we do? Do you all remember where we can uh do [02:21:12] Do you all remember where we can uh do all these changes in our data set? screen I can say. If you just import the data, so you want to change the data type. So, what options you have to change the data type? In data model, you [02:21:28] can see different data sets and you can see the connection between one data set within to the another data set. You can see the data connection there. >> One data and other data, how they are connected. One-to-one relationship or if [02:21:41] it. do we check and where do we change the data type? [02:21:56] template. See, import, load, and transform. That's incorrect. You import the data. You will get either load option or you get a transform option. Any one you need to select. If you select transform option, [02:22:11] then you will go towards Power Query. But, this data type option is not with respect to Power Query. When you select instead of transform, if you select the load option, then you will take your data directly to Power [02:22:26] BI. So, I don't see the right answer here in No, it's not a Power Query. This changes just today in the beginning I showed you the yesterday where we stopped that date and data type change. [02:22:41] No, I showed that today. This is not with respect to Power Query. This is not even a table Sorry, this is not a model view. In model view, you don't see the structure data. [02:22:57] column. So, what do we do now? data transformation, but not only in Power Query because in Power Query, you [02:23:10] Power Query because in Power Query, you don't have all the options. Direct data format change option we don't have in Power Query, to be honest. [02:23:33] can do on our data? This question is to all of you. Hope you all can see my So, you want to change the data type. How to proceed now? You already know the specifications of Power BI. We can change in data view in format. We [02:23:48] don't have a data view. We have a table view. So, this option is with a table view. You see, this is the model view. The third option is model view. If you move the cursor, you can [02:24:00] the cursor, you can see the different views. column. Now, you can see the data format. [02:24:14] Please remember all the steps we just did it today. How to do the data group, how to sort the column, how to concentrate on the data type. We just did it today. We just learned this in today's class. [02:24:28] So, it's not a data view. It's a table view. So, in table view, you will be able to see the data in the structured format. [02:24:42] is how we will do the data formatting. Sorting and filtering, you know how to Sorting and filtering, you know how to sort the data in the same uh you know, in the same tab you have an option to sort the data, which we just did. [02:24:55] Sorting either ascending Sorting in ascending order or sorting in descending order. Not only in table view, but you can also do it in a dashboard. That I will show you when we move towards dashboard preparation. And then, [02:25:10] filtering the data. Select the column, and then you can just come here. This is your sort option. Sort ascending or sort descending. I will go with sort descending. Why it is the same as everywhere it is the the the You can see [02:25:24] t h e t h e t h e. So, it is the same. I will come here. I have different names. I will go with sort ascending. Ascending order in a sense a b c d e f. Ascending order in a sense a b c d e f. If I go with descending, [02:25:40] if you want to clear the sort, clear the sort. Original data will be accurate. So, these are the multiple options what we have. And now comes the data filter. When you have huge data, if you want to filter the data, you can use this text [02:25:55] filter to filter. Equal to particular director name or does not equal to particular director. If a director name starts with a, director name starts with starts with a, director name starts with or ends with or director name contains e [02:26:10] s. Or if you go with this custom filter, you will get this logical operator and or. And indicates you are checking equals [02:26:23] equals Frank Sorry, Dara Boat. Equals Frank Dara Boat or does not equal [02:26:39] Equals Frank Dara Boat or does not equal to of the condition is correct. And in and operator, both the conditions should be [02:26:52] satisfied. In or operator, any one condition should be clear. Now you can see I'm going with the or operator. So, or I want any one of the condition to be here. I have that condition available [02:27:07] here. So, if you can come to text filters again, custom filter or either this or that. Show rows where director is equal to this or does not equal to is equal to this or does not equal to Apple. [02:27:23] you don't see the other rows, you can only see this row. only see this row. This is how and or operate and or or This is how and or operate and or or operator works. [02:27:36] have. I will remove this I will I will remove this custom filter remove this custom filter what I just added. [02:27:54] drop down, you will see all these options. Hope this is clear or you want me to repeat this again. Anyone? [02:28:10] reviews data. Sorry, ratings data. Here also, you can prepare it. [02:28:23] either in ascending or in descending order. Filtering the data is where I will apply a particular condition on the particular on the column, maybe multiple columns or one column at a time. [02:28:36] Additionally, you can also add this filter that is and or sorry, and or filter that is and or sorry, and or filter what we have there. [02:28:48] it's the same thing sorting. Sort by column also you have which I showed you in the beginning. So, you all all these operations sort, filter, if you want to do on the raw data, you need to directly go with this [02:29:04] Not in the model view. Model view is nothing to do with this changes. Model view is only to check the connection, data connection. Whatever the changes you are doing, if you want to observe the data, if you [02:29:17] want to transform the data, process the data, it is possible only in table view. For advanced features, you will move towards Power Query. [02:29:33] showed like particular value also you can filter. You can either arrange in ascending order or in a descending order. Select one or two rows or select one director name and see all them all the data coming under this name. That is [02:29:48] possible. So, let us work on a project now. So, I will share the data set again. To demonstrate how to configure table, whatever I showed with this data now, with the new data I'm showing now. So, [02:30:01] this is the orders CSV file what we have. You have order ID, you have a product ID, you have order date, you have quantity, unit price, and the total. And you can also see the data set description. Order ID is nothing but all [02:30:16] the unique order ID you have in product ID column, it contains all the unique product IDs. Order date column, it has all the order date. Quantity, which is containing the product and the quantities which is [02:30:31] sold. Unit price, unit price is nothing but only for one product. So, unit price but only for one product. So, unit price into quantity will be the total cost. So, for example, 100 into five, then it is 500. Five quantity, unit price is [02:30:47] is 500. Five quantity, unit price is only for one product, one quantity. But what is the cost for five? So, unit price into quantity will be the total cost. So, this is the data what we have. Let [02:30:59] us import this file and let us do like uh the operations on whatever we uh the operations on whatever we discussed. Seven rows were loaded. [02:31:12] This is data pane. You can just close it. Now, come to table view. So, you want to change the data type of the data. [02:31:25] Usually we go with date, the month, and year. Done. Here it is. So, this is a date date now. This is old number. This is [02:31:38] old number. This is again old number, old number, and total. We are done. So, now new column I just want to create. How to create a new column? I'm showing you now. This is your calculated column. [02:31:52] All these are calculations. I will give a new name to it. know, we have quantity, we have unit price, and we also have 500, that is the total. Now, I just want to give a discount. [02:32:10] What discount I will give? You know, I will give 10% discount maybe, or I just want to reduce 20 rupees from the total cost. I will be removing 20 rupees from the total cost. [02:32:25] So, what is the final cost? To customer. So, what is the what should be the final cost? You need to first You need to do the calculation here. Just a second. Uh just a second. One minute. [02:32:59] Mhm. Total column minus I want to reduce 20 from the original. [02:33:18] So, 100 500 minus 2480, 240 minus 2220, 200 minus 2180, 480 minus 20, which is 460. This is how you will create a new column with the help of existing column. So, this will be your calculated column. [02:33:34] Hope all are getting. Not only with this. This is number. Not only with this. This is number. Minus 10%. [02:33:47] during pasting invalid token line given offset 17%. Uh so, the way we work with this, uh you know, format, no, it's uh very difficult know, format, no, it's uh very difficult with Power BI directly to mention. Uh [02:34:01] because the percentage will be usually taken and not directly with respect to uh view. So, here you need to exactly mention the number. [02:34:16] to multiply that uh 10% maybe like 0.05. [02:34:33] percentage, this is just a number. So, you don't see any changes here. It's a you don't see any changes here. It's a minute one, I can say. [02:34:46] Now, you can see the change. So, [02:34:58] percentage right, but we are not doing that. that. So, Now, you will see the actual data. So, this is how we will be able to do [02:35:12] So, this is how we will be able to do the conversions. [02:35:28] you want to represent the number in any currency. For example, I want to go with euros. Now you can see the column is filled with this euros. Calculated column is filled with euros. [02:35:42] You can change this column name again. This is the final cost. directly we will not be able to convert what we did. So here you can see [02:35:57] you want to do sum or you want to do subtraction, you can do the way we just did and you can also do the calculations maybe average. So you want to know the average of uh you know, the quantity maybe average quantity. [02:36:19] average is calculated based on the quantity, the entire column average. So quantity, the entire column average. So the entire column average is 4.28, not a row wise column average because row wise we will not calculate the average. It's [02:36:32] a column wise I'm calculating so this is incorrect. This is incorrect way of representing the data in a column. So this should be considered when you create a dashboard. Quantity average, so [02:36:47] when you want to do addition, subtraction or any calculation based on the row wise, then new column is good to consider. But when you are calculating the entire column, you cannot go with the column creation like this. [02:37:05] So here also when we are selecting formulas, we need to be careful. So Microsoft Power BI with the AI assistance. I again welcome you all to today's lecture. Let us quickly start. Any questions? Quickly, you know, unmute [02:37:20] Any questions? Quickly, you know, unmute and speak or raise your hand so we can come clear it then and then. So, let us quickly start now connecting the data sources. As soon as you download your application, the next step what we do [02:37:34] with the Power BI is to connect the data. We have data sources available in different formats. We need to connect different formats. We need to connect that data to our Power BI desktop. [02:37:50] say that today we are going to learn connecting data sources as a topic? Yes, this is the today's topic. Connecting different data sources to your Power BI. How Power BI works with this data? Because different data formats will be [02:38:07] it and we are we are working accordingly. intelligence helps organizations make decisions using the data. You all know [02:38:22] about it. Business operations are completely depending on the data. Not only business operations, it can be any industry. All the industries are having a business. They are running a business. For them to take a good decision, it is [02:38:39] important to depend on the data, to understand the data, and then it is good for them to take a decision. That is possible with respect to a BI. Data analysis uncovers insights by [02:38:55] examining the raw information. You already know the different procedures. First, you will import the data. You will clean the data. You will do the data analysis to extract meaningful information from it. We always work with [02:39:10] structured data. So, data sources, always it is good to ask if it is And I can see some questions in the chat. chat. Okay. [02:39:29] share later in the chat. I can see the same question asked again. So, data analyst prepare data analyst will prepare a data model, prepare the will prepare a data model, prepare the data structure, and they also prepare uh [02:39:42] data visuals. And access data at every stage of the life cycle. Power BI connects to multiple data sources, transforms the data, and builds the visual reports out of it. So, we [02:39:56] have so many advantages working with Power BI, which you already know, but still we are understanding how it is helping different data sources today. [02:40:08] So, you are a business analyst developing insights for a strategic customer loyalty initiative. The required data resides in different systems. Excel files from marketing, CSVs from e-commerce, and SQL databases [02:40:24] managed by the IT department. The stakeholders expect a unified interactive report to guide the decision. What is the first step you would take after receiving the data to create the report? [02:40:39] So, now you have a while data available in different sources. So, what to do now? The first thing, is it possible for all of us to connect Excel file, CSV file, and SQL file together? I want all of you to please answer this [02:40:54] question. Is it possible for us you know, to uh merge all these records you know, to uh merge all these records together? access Okay, now the access is with all of you. You all can unmute and speak up. [02:41:11] Then and there, you no need to raise your hand. Directly you can unmute yourself and you can let me know. The question here is very simple. First question is you need to create a dashboard for your customer. [02:41:25] Data is available from different data sources. First thing, is it possible for us to link all different types of data together? [02:41:37] >> And I think yes, ma'am. can >> Okay. >> We can connect the three datas. >> Okay. So, if if it's uh possible, if [02:41:51] your answer is yes, then what made you to say that yes, we have options to to say that yes, we have options to connect these different data sources? [02:42:06] >> Okay. Okay. So, basically in Power BI, we have Okay. So, basically in Power BI, we have 99 options of connecting different data sources. Like there are so many applications available where people [02:42:19] maintain the data that uh we directly we can link that application to this Power BI and then we can extract the data. So, Power BI is with added advantage where [02:42:31] it helps us to link data from multiple data sources together and you can create one report which helps to create, you know, the entire interactive dashboard. [02:42:44] So, this is one such uh you know, simple question I can say. I can see people are mentioning no. Uh Rajshekhar, Mamie, Anudeep. Uh to answer your question, no. It's not No, the answer is not No, it is [02:42:58] Yes. You can connect Excel file to your Power BI. In the same time, you can also you know link CSV file to your Power BI, SQL to your Power BI. It is possible for [02:43:10] us to link all different data sources together in one shot to this Power BI I don't know what made you to think because the same topic we had discussed in the last class. The required data resides in different [02:43:25] systems. Excel files from marketing, CSV from e-commerce, SQL databases managed by the IT department. The stakeholders expect a unified interactive report to guide the decision. What is happening here is different data sources what you [02:43:41] are getting, it will be available in different files. Excel sheet of data, CSV file data, SQL data. Two, three different files you will have. That is true. You are not merging merging the data together, but you are creating a [02:43:55] dashboard out of these three records what you have. It's not data merging in the sense data model you are creating and using that model of three different reports, you are creating a dashboard. So, to clear all your confusion, it is [02:44:10] not merging the files, it is like merging the data together, not merging the data in the sense not like uh merging the data in different files. We are grabbing all the data from different data sources which is available in [02:44:24] different parts. We are connecting all of them together and we are building a unified dashboard out of it. If the question is like uh are we merging multiple data sources together? Yes, we are merging Yes, we are merging [02:44:39] are merging Yes, we are merging different data sources together. Different data sources we can merge together to one Power BI dashboard. the data. You imported Excel sheet, you imported [02:44:54] CSV file, you also imported a sequel now. Three five three different reports you will have, so you need to go inside that report, you need to check the report step by step. What is the you know how [02:45:06] many columns we have, how many rows we have, if there is any irrelevant data, if there are any null values. So, we will do all that investigation. Before you import the data, only structured data you can import. [02:45:20] So, Excel sheet, CSV file, it's a raw file what we will be having. Now, we can also do the data cleaning part over there, and then we can import it. Or after importing here also, using the power query or in a report view, I will [02:45:34] show you that, you can check all the you know I mean what I can say the similarity or the data structure or the irrelevant data you can check it. But in power query, you can do all the modifications. [02:45:48] So, after you import the data, these things you can check. Or if you have an Excel file or a CSV file, before you import them also, you can do all the necessary checks in that Excel sheet or in that CSV file also. Two ways we have. [02:46:08] structure, column names, or if you want to add new columns, or if you want to delete the columns, or adding the rows, deleting the rows into it, we can do it before importing or after importing also. [02:46:22] But it is good if you have Excel or a CSV file, before importing you can do it. But when you're linking the data from different data sources, you can't do it before. If you're linking a SQL query, you're giving a database path, [02:46:36] you're linking directly data from a database to this dashboard, means you can't do it before. After importing, you need to do in the Power BI dashboard only. That is with the Query. That we will discuss. [02:46:49] will be we will be able to understand different data sources what we have with Power BI, categories, how to connect with them, to choose the right data sources to link your data, utilize [02:47:05] gateways, authentication, and credentials to enable secure data connection, utilize properties, privacy, and refresh settings to ensure data quality and compliance, build connections to Excel, CSV, and SQL files [02:47:20] to centralize data from analysis in Power BI, select appropriate data models and storage models to improve performance in So, you will be able to understand end [02:47:33] to end about different data sources, what alternative options or advantages we have inside Power BI dashboard the settings, we will also see that. Okay. So, let us quickly go and understand [02:47:48] what are the different resources we have, identifying and connecting to data sources. So, different data sources in Power BI. See, what is the meaning of data source or it is also called as data set. Don't [02:48:04] get confused, the meaning is same whether it's a data source or data set or just the data in layman's word, the meaning is same. A data source that meaning is same. A data source that refers to the location or any external [02:48:17] system or service or a store storage format from which Power BI retrieves data for analysis and visualization. You have a Excel sheet of data, 50,000 records you have. [02:48:32] I will show you that one example I Power BI can only connect data from Excel, CSV, or SQL or it can connect data from other dashboard. Dashboard meaning is different, Data sources meaning is different. [02:48:45] sources meaning is different. Not only Excel, SQL, or or Excel file, or CSV file, or SQL. We have 99 plus other data sources, or other databases, which we can link. You cannot create a path from one [02:48:59] dashboard to another dashboard. Dashboard is different. Dashboard is where you will create the complete picture, complete graphical representation. That's a different tool. This data connection for us to create a [02:49:13] dashboard. Data connection from different dashboard is not possible, but the same data you can use for multiple dashboards. Not an issue, but you will not link one dashboard with another dashboard. [02:49:28] Okay, so I will show you in Power BI. This is where you will work. This is where you will create a dashboard. This is your report view. Towards your left-hand side, you can see we have report view, we have table view, we have [02:49:42] a model view. This is related to DAX query. I will talk about it later. We have data which we can connect from different data sources. You are creating a dashboard here. If you want to link this dashboard to a new [02:49:58] dashboard, it is not possible. You can use the same data set to create multiple dashboards. See, I will show you. [02:50:13] This is one of my dashboard. It's just opening. Just a second. create the complete data visual. This dashboard you cannot merge with [02:50:25] another dashboard. It's opening. Just a second. some different work, I think it was created. [02:50:40] I don't know if it's possible. Okay. can see this is sheet named as sheet one. [02:50:56] This is the data source for you to create this dashboard. If you are expecting me that this data set you need to add it to a different dashboard, yes, you can connect it. You can import the data the way you imported here. Look, [02:51:10] you can import it even in the other dashboard, uh sorry, in the other Power BI, and you can work. If you are expecting from this dashboard, if you want to link to another dashboard, it is not possible. [02:51:24] You need to import for all different dashboards the way you can see here. For this dashboard, this is the data. Same data set you can create multiple dashboards. Not an issue. This This dashboard you cannot link with another [02:51:39] This is your dashboard. The meaning of dashboard is where you created all this. If I just go ahead and check October month data, getting changed. If I go with this August month, if I go [02:51:54] with July month, you can see the numbers are changing towards my right hand side. This is dashboard, an interactive dashboard, a simple dashboard. This is dashboard, a simple dashboard. This is not a complex one. This is helping us to [02:52:07] This is one simple dashboard helping us to understand month-on-month transaction and the profit. So, this dashboard you cannot link with another dashboard. You can create a new dashboard by importing the same sheet of [02:52:22] data if you want, or if you want a new data sources, that you can do it. to import the data when you are creating the dashboard. [02:52:42] 48,000 621 records I have as you can see below. If you want to import this data, you can [02:52:54] directly import this Excel file to Power BI. Uh after importing it to Power BI, you can do the changes. If you want to do any modifications or if you want to add new columns, if you want to delete some [02:53:07] columns, you can do it here or after importing also you can do not an issue. Okay, this is an Excel file. You can do the quality check in Excel file also. Just put a filter and you can see [02:53:26] have any null values, blanks will appear here. You need to check that. See, when you're working with Power BI, it's good if you also have a knowledge working with Excel. Especially as a business analyst or as a data analyst, you need [02:53:40] to have a very good knowledge on Excel tool, also on Power BI because Microsoft same uh you know, a company I can say and similar the way we work is similar. This also you can check or if you don't want [02:53:56] to check like this, just a second. You can remove the filter and you can see. You can just move up, scroll right, left to check if there is any null values. A sanity check, quick sanity check you can do. All these are manual task only when [02:54:11] it comes to Excel to check if there is any null values in any. We can also apply the formula and we can also do some analysis here. So, if you ask me what formula or how we [02:54:25] go and check. So, I will show you a simple quick formula here that is to check medium-sized pizza, how many medium-sized pizza, how many medium-sized pizzas we have. [02:54:46] This is the range. And this is the criteria. You can see if you ask me how many medium-sized pizzas we have. Total out of 40 to 48,000 records we had, you know? So, out of 47,000 pizza [02:55:02] records, 15,385 pizzas belongs to the pizza size which is medium. It's a quick calculation. It's not only analysis or these formulas you can also apply in Power BI, but not only in Power [02:55:18] apply in Power BI, but not only in Power BI, you can also check in Excel. or the aggregate functions which is used to maximum. [02:55:31] Not only this, you can also check now. Let me talk about this supreme. You want to understand how many This is a pizza category. How many pizzas are a pizza category. How many pizzas are coming under this supreme category? [02:55:55] And this is the criteria. 11,777 comes under supreme. If you want to cross verify, it is 11,777. If you want to put a See, you can also put a filter here and check. [02:56:15] In formula also we are getting the same result. In when you normally check it same result. This is just to cross verify for your understanding. [02:56:31] revenue, if you see this is all count. Sum is also possible. You want to see Sum is also possible. You want to see the total revenue generated. [02:56:43] apply. Uh revenue is here. Revenue column is Uh revenue is here. Revenue column is here. you just check here, see here also this is the sum. Sum, count, average you will [02:56:59] get here only. So to do all this, you know, analysis you can do it in Excel also or after importing to Power BI also, it is possible to do. I think you all know Excel very well. Uh I'm just telling [02:57:13] you, I'm just giving you information that this is how you will be doing the modifications or checking the quality in this Excel sheet. Hope this is clear. So you can import this Excel sheet to Power BI. [02:57:29] fication or before doing modification, you will be able to import it. you will be able to import it. So Excel file or CSV file or any cloud platforms like SharePoint or OneDrive and structured systems like SQL and [02:57:44] and structured systems like SQL and another important point is Power BI will always help us to only I mean Power BI will always work with structured data, but if data is not structured means if you have any irrelevant data or [02:57:59] inconsistency in the data, we need to work upon that. And Power BI has also many options to connect with different data sources. All the sources stores the data in the structured format only. See, the main [02:58:13] agenda or the main motto of using different data uh different databases or different online cloud platforms is to maintain the structure data. [02:58:25] The use the main usage of that is to maintain the structure data only. But if that structure data if it's not possible to maintain or if there is any issues then that is we need to address it and we need to you know clear that. [02:58:40] it and we need to you know clear that. You all can see here the data option. come this is to insert this is the data select this get data. select this get data. Select this more. [02:58:57] multiple options of connecting data. See maximum times people will go with Excel file. If they are working you can connect the Excel file data here. I will show you one by one. Coming to Azure. Azure is [02:59:13] one such cloud-based platform available. You can see all Azure related databases options. Imagine your company is using Azure SQL [02:59:25] database import data from an Azure SQL database selected. Come here and use the tap on connect option. [02:59:41] See different databases are stored a different data will be stored in different servers. So if your company is using a SQL Azure server you need to mention the server here. Whether you want to directly import the [02:59:55] Whether you want to directly import the data or direct query or advanced options you have. You can write a SQL statement to do modifications when you are to do modifications when you are extracting the data. [03:00:09] and this will work only if you have a paid version. By connecting the server and this will be this will only work for company be this will only work for company related data and companies can only [03:00:21] spend so much to make to spend so much on databases to maintain it. So they can whether you are importing directly or writing a query and then writing a SQL writing a query and then writing a SQL query here to import the data. [03:00:45] Imagine your company is using a Teradata database. additional components to be installed before it can be used because this is my personal laptop and I don't have the tools which supports Teradata database. [03:01:01] This is not my company laptop but if you are having a company laptop then you will not be you know you the pop-up will not appear. You can directly link the not appear. You can directly link the data. [03:01:16] Why it's not appearing? Just a second. [03:01:34] because of the unavailability of other option. I will go with Oracle. Oracle.Data.Access.Client is not installed because all these are [03:01:47] taken care by companies. So this is my personal laptop. You can give a Oracle server link here. If you want to go with the direct query, yes you can write a query here and grab it or you can directly input. This is [03:02:03] like additional or options what you have. This is not something which is compulsory. This is an advanced option. If you already have a clean data and if you want all the columns, whatever is available in that server, [03:02:16] if you want to directly import all the data, just give a server path here and then select okay. The data will be directly linked to your database. I mean, this Power BI data. [03:02:31] asking. So, I will show you now what are the different options we have. This is how you will link. So, I will show you how we can import the data later. See, there are so many options. If your company is [03:02:45] using IBM or Oracle or Google Big Query, Snowflake, or if they're using PQL databases, SQL databases, Azure, SharePoint, Dynamics, Google Analytics, Adobe Analytics, [03:03:00] Adobe Analytics, OData Feed, Hadoop, Python script, Assemble, I thought I I'm sorry. Bitsight Security, then Dataverse, then FactSet, Ciner, Google Sheets. There are so many options which you can [03:03:15] see. If your company is using any of these different data cloud platforms to store the data, you can use any one among them. These are the files. It is aggregated. You can see these are the files. These [03:03:30] are the format data format which is in file. These are the databases where you can collect the data from a different database. Microsoft Fabric, Microsoft related internal application. The fabric, [03:03:44] yesterday I think someone was asking me. Power Platform. Azure. Azure is also with respect to Microsoft only. Other online services, [03:03:56] online platforms, cloud platforms, or at least or you can also call that least or you can also call that Zoho. [03:04:11] domain of Zoho, then you need to mention your workspace name, application link, then you can also you need to also go with the report link. [03:04:25] So, you need to give all this information to connect to connect your information to connect to connect your data. [03:04:38] you're working with data basically, you will have all these details available. So, nothing to worry when you work for a company, you will get all the details. [03:04:54] you can find here. This I think I was showing here. Other online third-party options also we have. Depending on what your organization is using, almost more than, you know, 100 only I can say. We have so many options [03:05:07] only I can say. We have so many options available to connect the data. [03:05:19] mentioning yesterday. Salesforce, a CRM tool which also maintains the data in the form of reports. Where the Salesforce report you can directly link to it. I will show you this. Salesforce objects. [03:05:41] In Salesforce, I personally use Salesforce for creating a There also you can create a dashboard, but it is not something like complex one. For a simple data, for a simple understanding, it works very [03:05:53] well. So, if you want to a the data of a Salesforce to this. salesforce.com. So, if you want to link URL will be there. For different reports, we will have different URLs. [03:06:07] You can copy that URL, you can paste it. Select okay. The entire data, all the columns will be directly imported to here. This is in this data, you will find it. So, you need to just mention the URL path. [03:06:27] the different data sources. So, these are the different categories of data sources in Power BI, which I already showed you in Power BI, which I already showed you in Power BI tool. Database data sources, [03:06:42] file-based data sources, cloud platform-based, and other data sources you can see. So, data will be always in a structured format when you are using this Power BI tool. If the data is not structured, then it is our [03:06:56] structured, then it is our responsibility to clean the data before creating the dashboard. So, if someone ask you, "What are the different categories of data sources you have?" You should mention that database, [03:07:10] have?" You should mention that database, file-based. Database, file-based, and cloud-based sources we have. [03:07:36] the different options. Not only these options, you will also find it in Power BI. You can just open that and see what are the different options we have. The one which I showed you, no? Options. If someone ask you, "What are [03:07:50] the file? Can you give me options for file? These are the options. File means it can be Excel file or a text file or a JSON file. To talk To talk about databases, these are the different options we have with respect to [03:08:03] databases, Microsoft Fabric, Power Platform, online services. These are some other third-party applications. [03:08:21] Before connecting to data in Power BI, please consider the factors to ensure smooth and secure prompting or reporting. Data format, data volume, a refresh frequency, and also data security. I [03:08:35] think that is not added here, but not an issue. Data security is also very important. To talk about data format, make sure before you create a report, we already discussed but I'm repeating again. Before you [03:08:50] create any dashboard using the report, please clean the data. If column names are not matching, if you want to change the column names, please do that. Make sure your data is crystal clear for creating a dashboard because you are [03:09:06] downloading that dashboard for future use also, which should not affect now or also in future. Data formats we need to take care To talk about data volume, choose a source that can handle current and [03:09:21] future data size efficiently. See, for paid version, as you go with If you pay maximum, they will give you more space for you to work, and the organization will be having paid version, so nothing to worry. Data volume, data structure we [03:09:38] need to maintain properly because later it should not affect the dashboard because if you have large amount of data, it should not affect the dashboard. Then comes the refresh frequency. Align [03:09:51] the sources update a schedule with your report refresh as I said we have an option to refresh the data. Whenever you're creating a dynamic dashboard or an automated dashboard, you need to link the data from different sources. Data [03:10:09] the new as the new data becomes available, the data will be stored in a database for you again to grab the new data. You need to refresh your dashboard here. You need that means you're also refreshing the data. As new data becomes [03:10:24] refreshing the data. As new data becomes available, graphs will be updated. sources update schedule. Like people will usually refresh it on weekly basis [03:10:36] or maybe on daily basis. Some it depends on the data source also. What will happen is as the new data becomes available, not everywhere the data will be updated automatically. It will take two or three [03:10:51] days of time to get new data sometimes. For example, today you're refreshing the For example, today you're refreshing the dashboard. Today it is 23rd Sunday. You only have the dashboard updated until Friday's data. Saturday and Sunday data [03:11:05] is not updated. Even though you're updating, you're expecting the update on Sunday, you're refreshing it on Sunday, you will not be able to get Sunday's data or Saturday's data. You can get on an average until Thursday's data [03:11:20] an average until Thursday's data properly. Two to three days of gap will properly. Two to three days of gap will be there to extract the right data. [03:11:32] we get the actual data or if you're expecting today yesterday's data today, sometimes or on some data bases it is not possible because of huge volume of data which is getting generated or you can also [03:11:47] call that the software usage it you know it's the low efficiency of the database it's the low efficiency of the database also we can call. [03:12:06] based on my experience because I've seen whenever I create a dashboard almost the five days of data I can see today. Again, if you are expecting today's data you need to wait for next one week to get the right data until [03:12:19] that until today that is 23rd November. So that means you need some time for the database also to breathe and to extract the right volume. [03:12:31] So data format, data volume, refresh frequency are the three options what we have also to connect a data source. These points you need to remember. Hope this is clear. This is theory only [03:12:45] I'm not I mean this topic is theory but later we will of course have you and some examples we will see today. So let us proceed further. See all the databases whatever you connect it's a dynamic dashboard because new data [03:13:00] becomes available you can refresh it and you can proceed. When I talk about Excel sheet data most of the time Excel sheet data is used for static dashboard. So you can add new Excel you know to the same Excel file in the same location in [03:13:16] the same path you can add new data you can save it and then you can come to dashboard and you can refresh it. That is the I mean that possibility we have. So we are not refreshing in Excel file [03:13:28] we are doing that in a dashboard. So let us proceed further. As I I data security permission, compatibility with Power BI, and the connectivity mode. Select different import, direct query, or live based on [03:13:44] performance and freshness. That is the new data. And then data security and permission, verify secure access and proper user permissions for sensitive data. Compatibility with Power BI, that means you have [03:13:59] uh the options, you know, the database option. You need to use that database option or Excel file or CSV file options only to connect the data for easy integration. That means integrating data to this [03:14:12] Power BI only with this option, whatever you are finding right now in Power BI data import option. Data connectors are the bridges that Power BI uses to connect to various data sources. See, these are the different [03:14:27] various data sources which we have. I already showed you in Power BI. To talk about gateways, gateways are nothing but which is helping you to grab the data. Gateway acts as an intermediate between Power BI service [03:14:42] and on-premises data sources. Excel sheet data. Excel sheet Excel Microsoft Excel is a gateway for us to connect the data what we have inside connect the data what we have inside Excel sheet to this Power BI. [03:15:01] Similarly for other tools also. It is helping us to do the data connection. on-premises data using a gateway, follow the key steps for access and connection. [03:15:17] I [clears throat] will show you I already showed you, you know, how to mentioned here. I will show you again. In Power BI Desktop, go to get data and select the appropriate connector. Enter [03:15:30] the necessary details. Choose the specific data you want to import or connect. So, you will select Excel sheet, you will connect it. So, open the Power BI, select the get data option. If you want [03:15:43] to work with Excel, select this Excel sheet. Connect it, load the Excel sheet sheet. Connect it, load the Excel sheet data. [03:15:55] select this text. Connect it. Then you can see the same data available in Power BI. If you want to go with SQL Server data, it will ask you to give some password sorry, it will ask you to add a SQL Server name of it, or if you want to [03:16:11] add a query to it, you can add it. I already showed you. So, the same thing already showed you. So, the same thing is in picture now. [03:16:23] as data pane. Okay, before we before I proceed, let me give you the complete instructions to this Power BI tool, what we call these options. So, this is your [03:16:35] report view. There are three important views in Power BI. Report view is to grab the data and to create a dashboard, we use this report view. This is your table view. As soon as you import the data, you can see the tabular structured [03:16:52] data available in table view. And then comes data model. This is your model view, which is helping us to link, or if you are importing different data sources. That was the first question which we [03:17:06] discussed today. Imagine you are grabbing Excel sheet data and CSV file data, and also database linking together, you can see all three sources available at different models. If there is any connection between one file to [03:17:21] another file, maybe common column names, that will be linked together. I will show you all that. I will import the data and I will show will import the data and I will show you. Data model schema you can see here [03:17:34] in model view. And this is your DAX query. You can write a query to get the result. This query is similar to SQL, but it is not a SQL. This is your advanced one. TMDL view. [03:17:51] This is your advanced one. TMDL view. So, to write uh multiple uh queries or to write the entire structure, we go with this. Like a SQL query only, but with this. Like a SQL query only, but this is a recently added. [03:18:14] TMDL in Power BI. Basically, Tabular Model Definition Language. It's a new view what is created, which is allowing us to view script and modify the semantic model using a code-based approach. [03:18:34] And this is your data pane. Okay. See, this is your data pane. As soon as you import the data, all the columns you will find here. This is called as data pane. This is your visualization pane, where you can see [03:18:49] all the visuals. This is your filter pane, where you can apply the filters on a on to this particular page or to the entire data. I will tell I will show you all this data. As soon as you import the data, [03:19:02] filter pane, visualization pane, data pane. This is your report pane. You can pane. This is your report pane. You can call it as dashboard pane also. This is This is your home page. The entire thing is your home page. Where you can see [03:19:16] uh you know, data uh importing option that you will find. This is your transform data, the one which I mentioned data transformation. I mean when I import the data you can see here this is refresh. [03:19:31] This is one refresh which will help you to refresh the data and we also have the refresh option available here. Pause the visual, refresh the visual. This will go and refresh the data and the query as the new data becomes available. [03:19:47] This is to add the visual or you can also come here, select the visual to add it to your dashboard. This is to do the calculation. And this publish is for paid version. This sensitivity is again that is I mean [03:20:01] after importing the data I will show you. Coming to insert option, you can either select this to add new visual or you can select here only to add the graph. And this is the AI visuals what we have [03:20:14] the AI integration with this Power BI. The narration after you complete the dashboard and auto generated a written summary of your data to this report will be taken care by this. And then comes the power platform. This [03:20:29] There are so many power applications or Microsoft applications what we have with this Power BI. So that will be integrated that you can see integrated here. And you can use this you know this [03:20:43] option of power platform. But again that's for the paid one I can say. And there comes the data modeling. This is you know data model will be created here. You know just [03:20:59] Data model will be created here and if you want to manage the relationship between the data models you can either do it here or you can do it here. If you want to measure anything you can either measure here or you also have an [03:21:13] option to measure here also do the calculation. this is the page refresh option also we have here. This is your view. How you want to view the data with different color codes. [03:21:29] Or you also have an option to format your visual view. I will show all this. This is the filter whatever you want to apply. And this is the performance analyzer. This is to sync all the filters to your [03:21:43] report. You can also do that here. Optimization is where here also you can see apply slices to all. Performance analysis and then you can you can also refresh the visual view. As the new data becomes available you can come and [03:21:58] becomes available you can come and refresh it here. So, this is your entire Power BI specification and if you want to export it you can see here save as. [03:22:12] Save it and export it. Or directly you will not be able to share because we don't have a organization account. You need to download it and then you need to share. The files will be saved here. You can open the file again to rework. [03:22:28] The import import option you have. This is export. This is what we need to choose. Please remember to download in Power BI template only because you can rework if you download in Power BI template format. [03:22:41] If you download in PDF you can't do anything again. So, don't make sure you download the file as Power BI template with me. Don't sign in. It will ask you to sign in. Please don't do that. [03:22:53] This is the entire information about this Power BI tool. Multiple pages you can add. And you this is your mobile layout view. And you this is your mobile layout view. This is your desktop view. [03:23:10] We already know now how we can connect different databases to our Power BI. this, I already showed you know where you need to give the URL or server or [03:23:23] you need to write a particular query to do the to grab the data from a different databases and that is with a paid version and also you should have a database to do all that. Authentication in Power BI is the [03:23:37] process of verifying the identity of a user or a service to access the data. The following are the common methods on our data sources for the security. Because see, I'm working for one company. I cannot go and take the data [03:23:52] of other company. I don't have access. I should not have access because that is only related to that company. So, only employees working under that company, they have an authority to use the data. Again, not all the data. If [03:24:07] you're working for a customer team, only that team's data or relevant data access will be given to you. So, data security the way company maintain is very important again. So, [03:24:20] So, that will be taken care by them. Handling credentials. So, don't store Okay, we need to concentrate on few data here. So, never store password or other [03:24:33] sensitive credentials directly in your Power BI model. So, for non for on-premise of data sources, use data gateways to securely manage data credentials. See, this is only for paid version. Right now, we are [03:24:49] using the basic exchange sheet data or a text file data or a CSV file data to work. So, we don't have any passwords to add So, we don't have any passwords to add or passwords to maintain. [03:25:04] account if you're working, then by default passwords and other information default passwords and other information will be taken care there. [03:25:17] information like passwords or details in that. And whenever you're working with the organization account, it's a Power BI organization account, it's a Power BI service what you choose. [03:25:31] Then it is all you know, it's all maintained well. So, let us have a demo of how we can connect the Excel workbook in Power BI. [03:25:44] connect the Excel workbook in Power BI. So, let us see this. [03:25:59] Hope you all can see my screen. I'm just importing the data now. See, it is you can first let us import the sales data. [03:26:12] data files at a time. Only one file you can import at a time. I'm importing the sales data. >> [clears throat] [03:26:28] one. In this sheet in this file in this Microsoft Excel file you only have one sheet. Select the sheet. [03:26:43] Hardly five columns, six rows, seven rows including the column. So, this is how you need to import the data. You can load [03:27:15] have some patience. It will take some time. Some It will properly. You need to wait for few minutes here You need to wait for few minutes here sometimes. [03:27:30] come with this pop-up. And later, once you completely import it, it will you completely import it, it will vanish. is fully imported. So, this is the sheet one, and you can [03:27:44] see all the columns. This is your data pane, and automatically, you can see this summation column. This indicates that you have a numerical data. Product name and category is a string. It's a alphabets only. So, it it is not [03:28:00] identified with any symbol. If it is numbers, it will be identified with this. If it is a you know, if it is a date time data type, it will be identified as a calendar icon. Okay? So, this is the sheet one what we [03:28:14] imported. You can close it, or you can open this to I mean, you can use the open this to I mean, you can use the drop down to open and to close. [03:28:26] uh delete the data from this model, you can delete it. Or if you want to hide the file, you can hide it. If you as the new data becomes available, if you want to refresh refresh the schema and the data, both [03:28:40] you need to refresh. Okay? If you want to edit it, you can go with this edit query option. If you want to rename the file name, you can do it here. If you file name, you can do it here. If you want to go with the quick calculation, [03:28:56] The new measure is created. What analysis, what calculation you want to do, you can do it. But I will show you later. Now, let me not confuse you with everything at a time. So, this is the sheet one data. [03:29:11] This sheet one data, if you want to view, this is your table view. Come to table view a table view and you can see the tabular view now. Tabular data structure you can see. Sales data description. Product ID talks [03:29:26] about each product ID. Product name, again the product name. Category, to which category it belongs to. Sales and profit. Please understand one thing, before you do data cleaning, data analysis, data transformation, or before [03:29:41] you do any step on top of data, please understand the data. Please understand the columns. It is very important for us to concentrate, understand the business, understand the data, before you do any changes on your [03:29:56] data. So, this is your column names and the column name description. You got to know what each column is helping you to understand related to it. Not only this Excel file, you all This is your Excel [03:30:11] the data what you imported. Product IDs you have 1 2 3 4 5 6 7. You know which What is the product ID for laptop? What is the product ID for smartphone? You also have product category. What is the sales? What is the profit out of it? [03:30:28] Understand the data before you proceed. This is how it looks in Excel. In a Power BI also, almost it looks in a similar manner, I can say. Now, not only this file, you imported Excel file, but multiple data sources we [03:30:43] can import right together. So, let me just go and grab the CSV file also, the one which I sent you. The file name is mentioned as customer The file name is mentioned as customer details. [03:30:59] Customer data. One second. Customer data. Please select the right option. If you [03:31:12] select Excel and if you are expecting the CSV file to appear, it will not appear. Because it is dedicated to only that uh format, right? So, only that format should be here. So, customer data is the [03:31:25] CSV file. Select the CSV option and then select the customer data, then open select the customer data, then open this. [03:31:46] I will rename the sheet one. This is sales data. To avoid confusion, don't Make sure you give a proper name to your data set because it should Whenever you go with multiple data sources, it should not affect. I created customer data, [03:32:02] sorry, I imported customer data CSV file. I also imported sales data Excel file now. We have two data sources available with this free version. [03:32:17] You can see different models. That means different data view. So, come to this table view. See, uh you have again two data sets. Now, you can see sales data is here. So, because you can see this highlighted. If you select customer [03:32:31] data, customer data will appear. If you select sales data, sales data will And come to this model view. This is your model view. [03:32:46] any common terms between sales data and in common tables >> [clears throat] [laughter] [03:33:01] So, if you have any common column names or it is also called as common tables between the data, it will There will be a connection created. See, category you have in sales data, you don't have category here. Usually, [03:33:17] the unique ID or which acts like a primary key under each data set will be connected together. Here, if it is a primary key, then here it will be a foreign The connection if they have customer ID. [03:33:32] See, here we have a product ID. Here, we don't have a product detail. Here, we don't have a customer detail. So, both the data set what I have, there is no link between them because there is no connection, there is no proper [03:33:45] Sorry, there is no unique column name uh between these two. We Sorry, we have a unique column name. We don't have a common unique column name between two data sources. So, we will not be able to create a link [03:34:00] between them. Model view. We have report view. This is your report view, the home page. Come to table view to see the tabular data structure. You can select any data set what you want to see. The third view is [03:34:16] your model view in which you can see the data model. I'm removing the file and again I'm uh importing the file. ask you, "Are you sure you want to delete the same data?" Yes. [03:34:33] Wait for a few seconds. Now, I have no data. It's a blank Power BI desktop what I have. I need to to the data. See, here also you can select this get data option, or here also, you can select tap on it and import the file, or you can [03:34:49] also select the file from here. First, I'm importing the Excel file. Your sales file is an Excel file. Your customer data is a CSV file. So, I'm importing one by one. First, I will import Excel workbook. Select this workbook, or you [03:35:04] also have an option here. Select this Excel workbook. Connect with it. the files are Excel only. CSV file will not appear here. For me, sales data is an Excel file. I'm importing it. Select the [03:35:20] Excel file. I'm importing it. Select the file. Tap on open. Wait for a few seconds. Your data is getting loaded. It will ask you which sheet you want to grab within this Excel file. I just have only one sheet which I [03:35:34] need to select. Select that sheet, and then load it. You need to change the file name to sales data. [03:35:47] To have a better understanding on the data, to avoid confusion, make sure you give a right name to your data. For future understanding also, it will be easy. I will just rename it. [03:36:00] So, instead of sheet one, I will just name it as Sorry. Select this three dots or tap on the three dots. And then, you can three dots. And then, you can come here, change it to sales data. [03:36:21] use the drop down to see all the columns which you have in this. This is how you will import the Excel sheet data. Whether it is page one, page two, page three, doesn't matter. For all the pages, same data you can use. [03:36:41] uh uh I mean you can just close the drop down. Now, I want to grab another data. down. Now, I want to grab another data. So, this indicates you are uh importing multiple data sources together to this workbook. I will select this get data [03:36:55] workbook. I will select this get data option, come to this more, and come to this file again, then go with the CSV because the second file what I'm giving you what I shared with all of you is a CSV file. That's a [03:37:07] customer data. If you select Excel workbook and if you're checking for a CSV file, it will not appear. Because the format is different. If you're selecting PDF, then you should import PDF only. And other files, it [03:37:21] will not show. See, I'm selecting PDF. I will go to connect. I can only see all the PDFs here. I will not be able to see anything apart from PDF because that is anything apart from PDF because that is the data filter which I added there. [03:37:38] Okay? So, I'm importing the CSV file now. Come here, select the CSV, select the data. This customer data is your CSV file data. Connect that. [03:38:10] Excel file only. See, Microsoft Excel only has the option of CSV file. CSV is nothing but comma separated value. Delimiter which we call. So, you have two files now. One file belongs to Excel [03:38:24] and the other file belongs to CSV. You can come to table view. Table view this you can see. Sales data This is your sales data. Select customer data. Now, this is your customer data which you can view. This is your tabular view of the [03:38:40] Tabular structure. You can see here structure or not. You can select which one you want to see and you can see. See, when you select it, you can see. How to identify, if you [03:38:53] ask me, this shows this is the sales data. If you go to customer data, the name will automatically change and this is your customer data. [03:39:05] your name to the name of this uh data set. data. And again, if you go to model view, if there are any common column [03:39:17] names between these two data sets, it will be linked together. If you have a customer ID here, you also have a customer ID here, this customer ID from sales data will be linked to this customer data customer ID. That's [03:39:34] how the link is created when they have a The primary key is here and it may act as a foreign key here. So, I will show you that. We will discuss on that later related to data [03:39:47] model, but this is your data model view in which you can see the schema of it. are the things which we need to keep in mind whenever we are working with the data, especially the data settings. [03:40:03] When connecting to data sources in Power BI, you can configure various properties [clears throat] to control how the data is accessed, refreshed, and used within our reports and dashboards. [03:40:22] The reason is, as I already said, when we are working with a database, uh data privacy is very important, maintaining a structured data, and maintaining having a very good data security is very important. [03:40:37] So, the refresh visual option I already showed you. There's an option called as refresh visual, where you can refresh as the new data becomes available. So, this privacy level and refresh schedules will usually work when you import the data [03:40:52] from different databases. Public organization or private types of data is what we have. Power BI uses privacy level to control how data from different data sources can be combined, ensuring [03:41:09] secure compliant data handling. Power BI has three privacy levels. If your data is a public data, or if your data is organizational data, or if your data is a private data, that you can easily check. And these are the three privacy [03:41:25] check. And these are the three privacy levels what we have in Power BI. So, where to check this public, organization, and private, I will show you within the next few minutes. So, public data sources means files, [03:41:39] internet data, shared workbooks, which can easily shared with people. The data data. It's free to use. When I talk about the organization data, it is restricted. [03:41:54] See, if it is my organization data, I should only work uh, with my team members, and I cannot I should not share with other people. Maybe uh, even in within the team also sometimes there will be restrictions. [03:42:10] So, outside the company is of course not at all possible. It's not possible, and it should not be shared. There's a strict rule. strict rule. >> [clears throat] [03:42:25] sensitive or personal information and cannot be combined with other data unless explicitly allowed. So, public organization and private, the name itself talks about the data level. The data security level. Data source [03:42:40] location and path. In Power BI, the data source location is where the data is stored and the path is to is the exact address used to access it, such as folder path or a server URL. You can see, you know, like as soon as [03:42:55] you go with the data, it will ask you which path you want to select. Excel file, CSV file, text, or different databases. That is your data source location. Understanding how to [03:43:08] how to specify data source location, whether it is a local data or a network-based data or a cloud-hosted data or a database-driven. You need to understand If it is a local data, data will be in Excel file or a CSV file or a [03:43:24] text file. If it is a network-based data, it's like a SharePoint or different uh CRM tools. If it is a cloud platform-related data, then you need to add that server. If it is a [03:43:38] database-related data, there also you need to add the option that is uh the server, the one which I showed you a few minutes back, no? So, that is called as data source location and path. It you need to exactly give the path for [03:43:53] It you need to exactly give the path for the extraction purpose. uh you know, the local one. This is your database one. This is your Microsoft Fabric. This is the Power Platform. This is Azure-related and these are online [03:44:08] is Azure-related and these are online platforms for a network-based. And all other third-party options we have here. [03:44:20] This information will be used by the Power BI to locate and to access the data. Local files absolute path, the complete path to the file including the drive letter, folder structure, and file name. [03:44:36] See, this is your Excel sheet data. This is your local file example. Excel sheet, is your local file example. Excel sheet, CSV file, text file are the local files. [03:44:48] Specific server name and also you can write a query over and also you can write a query over there. [03:45:01] linking the different servers, data will be in CSV file or sometimes it can also be in Excel file. Few platforms maintain Excel sheet data. Few platform maintain CSV file data. Usually, [03:45:15] 99.9% data will be in Excel or CSV only data will be in Excel or CSV only compared to text. [03:45:27] As you can see the different cloud platforms. Azure is also an application of Microsoft. [03:45:42] Database, where you need to mention the database name, field name, server name. And these the apart from local, if you are linking network data, cloud data, database, you need to have a paid version because all these are taken care [03:45:56] by organization. Personally, we don't maintain any database. paid version. But you already know how to connect. [03:46:13] Only we don't have a paid version, but we know how to connect. Performance, security, availability, and cost. The key consideration when it comes to data. The location of your data source can affect how quickly Power BI [03:46:28] can access and process. The security measures in place at your considered. So, the security if you have an Excel sheet data, if you're linking in the Excel sheet data from your laptop to [03:46:42] your desktop, the security measure on your laptop at that particular place we need to take care. The location of your data source can be influenced Sorry, can influence how easily you can access. See, if you [03:46:56] create multiple sub folders and if you're grabbing the data, it will take some time. If you have a very simple path, a very clear path to grab one file from that one folder without any multiple sub folders, it will be easy. [03:47:12] Cost. The cost of spotting and accessing data can vary depending on the location. That's what if you have a local and other options, it's a paid version. So, next let us move towards this data model selection. [03:47:25] model selection. Shared semantic model, local data model. So, what is the meaning of data model? Data model is where you have a structured data. The data is represented in a tabular [03:47:38] format. The structured representation of data that defines how tables and their relationships are organized within the Power BI environment. Power BI is a pro Sorry, supports two [03:47:51] primary types of data models. One is shared semantic model, and the other one is local model. You already know, uh you know, we need a tabular data, we need a structured data. It is splitted into shared semantic [03:48:06] model. and the local local data model. So, what is this shared semantic model? So, let us see that. us see that. So, shared semantic model in Power BI, [03:48:19] it helps us to work with a Power BI dashboard, a Power BI dataset published to the Power BI service, which can be used to create reports in different connection. [03:48:33] Shared semantic models are stored in a shared workspace like a Azure analytic shared workspace like a Azure analytic service or a CRM tool or any common drive which is related to that organization where multiple people, [03:48:47] multiple users can access the same data for analysis. It's a live data for analysis. It's a live data connection. [03:48:59] the graphs, the data, everything will be updated. This will help us to handle the complex data, multiple columns, multiple hierarchies we can add. Can optimize data access and query performance for [03:49:13] data access and query performance for multiple users. Facilitate data sharing >> [snorts] >> In this for this for you to work with this semantic model, you need a paid version. [03:49:31] our organization data. It can be a database or it can be a different tool, a cloud-based platform, anything it can be. You are creating a path. >> [snorts] [cough] [03:49:45] >> So, so you are linking the data with respect to different servers. And then you are utilizing that data to create create That's called as shared semantic model. [03:49:58] Sharing data and insights across multiple users, the same data see that shared with all of you and you downloaded you imported. Similarly, if [03:50:10] it is related to any organization, multiple people can use the same data to create a dashboard however they want. Or one dashboard what you created can be accessed by all your team members or maybe the all organization can use your [03:50:27] maybe the all organization can use your data and the dashboard. connect data from different servers? See, different servers as we don't have [03:50:39] a particular server related to organization, I just showed you how to give a path and how to import the data. But, the way you work with the data, the way you create the dashboard is same. Right now, uh we are not uh importing a [03:50:54] database data, we are working with Excel sheet of data and a CSV file data only. But, getting it I mean you already have an uh you know experience on how to import the data. I showed you the semantic more [03:51:08] data model. That is the way that's how you will import the data. But, the after importing the data, the way data looks in data pane, the way data looks in data model, the way data looks in the table view, it is the same. [03:51:24] Only thing is you are importing the data from a different source. If you are working with a semantic model, then you are importing data from different sources from from paid version like [03:51:36] SQL Server database. This is how you will import the data. You need to give a server name, you need to mention the database, select okay. Similarly, data will appear here from the server and from this database. [03:51:49] Table view will be same, model view will also be same the way you are doing here. Data will data will be more maybe because it's the database data. Data simple data, but you will work with large data also. I will show you how you [03:52:05] can work with large amount of data. If you know SQL, then it will be easy uh because the query looks like the SQL query only. The way the query what you write here, you know, that is also a SQL query. [03:52:24] server. I need to give the database name here. And here you need to you can write a SQL query here to extract particular columns or SQL is used basically to [03:52:36] extract the data from a database. Here also we are extracting the data from a data database by giving some instructions, by adding some conditions to it. So, that's a SQL statement what we will [03:52:49] write here. Similar to SQL, we have this DAX here. This is to work upon the data after importing. This query what you are writing in the beginning during the data import stage, that is to import the data you're writing a query. But after [03:53:04] importing to understand or to optimize the data, we go with this DAX query. If you know SQL, it will be easy, but don't worry if you are new to it. [03:53:20] Uh you know, if you practice it for some time, then it will become very easy. So, whenever we work with the shared semantic model, it's also about working with enterprise level data, governance, and management. [03:53:36] the local data, that is nothing but the Excel file data. Even uh you know, the relation data if you are extracting uh by writing a query with SQL, you can extract the data to Excel file or a CSV file, and you can import it. [03:53:51] The the data what I sent to you now, it's an Excel sheet data and the CSV file data, which you can easily use and you can import it. >> So, easier to create and manage for small data set or for a simple analysis. [03:54:07] Allows for customization and tailoring of data models to specific report. Provides a separate environment for each report. You see, customer data is a environment. Sales data is a separate separate thing. It is not merged It is [03:54:21] not merged together. But, you can create one dashboard using two data sets, also. Even though you're not merging them together, but same dashboard you can together, but same dashboard you can create for multiple data sets together. [03:54:45] have in your local drive. Smaller data set, simple data. You can also have a large data, not an issue. But, if you are importing like how we are importing the Excel sheet now, manually, [03:54:57] manually, then it's a local data model. shared semantic model and a local data model. So, location is where when I talk [03:55:10] about semantic model, you are importing data from a different databases or data sources. >> [snorts] >> Third-party tools. Different services, shared workspace, [03:55:23] about the local data model, it's a file. Collaboration. This data, as it is available for the complete organization, multiple users can use it. But, here, [03:55:37] when I have an Excel sheet, I can share it with you. You can use it. It's a limited users. So, this usually works shared semantic model for a large amount of data for complex analysis and also live [03:55:51] connection. Here, smaller data, simple analysis. Even you can have a large data set, you can also work with complex complex with the Excel sheet of data when you have a complex data. That is not an [03:56:04] issue. But data is not available for everyone. Data is value imported manually. And can be managed at an enterprise level and here only you you person only can manage because we are working [03:56:19] only can manage because we are working for ourselves. >> [cough] >> Sorry. [03:56:37] creating data models in a different way. Direct query, live query sent to the database, grab the data. You will write a query you will select the server or you will select the database, you will write a query, you [03:56:50] will grab the data. In a direct query mode, queries are executed on the source database in the real time, ensuring that any report available. When a report is viewed, Power BI [03:57:05] queries the data source directly, ensuring access to the most up-to-date uh sorry, up-to-date uh the updated data information. [03:57:18] query. You first you will select the server, you will write a query and you will get grab the exact data as per the query. Only if the query is correct, you can grab the data. If the code is incorrect, it will not able to it is not [03:57:33] possible for us to grab the data. So, we need to check the code properly. So, in this case the performance direct query can be slow. The reason is you are connecting to a database, you're writing a query. [03:57:47] It will take some time to extract the data from a database to here. You can expect, you know, it will take some time for you to grab you're checking something for today, you will get last 5 days of data. [03:58:02] You will not be able to get exact today's live data. So, there will be a today's live data. So, there will be a gap of 4 to 5 days. and capabilities of direct query, depending on the database or on the [03:58:17] Some model features like calculated columns may be limited or unavailable in direct query. You're writing a query. On top of it, if you want to add new top of it, if you want to add new columns or new rows, it's not possible. [03:58:35] working with a real-time data, then you can go and make use of this direct query. Make sure the raw query or the code what you write is a straight and a simple without any complex calculations. [03:58:49] Because if you try to do that, it will take maximum time. You cannot prepare a proper dashboard out of it. That is the disadvantage. [03:59:04] means you we have multiple options, you know, like Excel sheet, CSV file, data import from different sources, website. Directly, you can link a website to grab the data or data from a social media platform. Here, you need APIs or [03:59:19] sometimes you need uh some uh you know, proper information to grab the data. Once imported, the data becomes a part of your Power BI model, allowing you to analysis. [03:59:34] This is how you will import the data. After you import the data, a model will be created. A complete picture or the dashboard will be created out of it. So, you need enough storage. Data I mean regular you need to uh regularly refresh [03:59:51] it as the new data becomes available. If the source data changes frequently, importing can lead to inconsistency between Power BI model and the source system. The source is a CRM tool. Imagine you're linking the CRM tool data [04:00:06] to it without writing a query. In this case, you're writing a query in data query you will write. Here, you will directly import without writing a query. Sometimes uh you know, you can import actual data within 2 to 3 days. [04:00:19] Sometimes it will take maximum time. So, whenever there is a See, in the salesforce, you can see that there are 100 customers, but in your dashboard, you can see only 20 customer details because the remaining 80 customer [04:00:33] details are new. And it will take some time for you to appear the same data in your dashboard. So, there will be certain time limit. uh you know, the data consistency which [04:00:46] usually happens between the actual data and this Power BI data. Actual data is your source. Power BI data is the model after you create a path between the data source and the Power BI. [04:01:04] created. Online and offline options both we have. [04:01:16] Composite mode enables Power BI to combine the data from both import and direct query. If you see the option there, you can import it. At the advanced options, you can see where you can also write a query on top of it. [04:01:30] You're importing the data and also you're writing the query, which will again take a longer time. With composite models, you can you can connect multiple data sources, some using import mode, others using the [04:01:43] using import mode, others using the direct query, and model them together using a relationship. That means the data model option. If there are common columns between, you can create a relationship between them, and you can [04:01:56] relationship between them, and you can also calculate certain columns. That is your composite mode. You will be learning all this in the coming up >> [clears throat] >> So, storage mode configuration, model [04:02:09] performance, data behavior differences, because you're grabbing data from different sources. Not every time you get the actual count. Again, it will take some time for you to get the actual count. Be aware of the [04:02:21] limitation. The disadvantages are maximum when it comes to composite mode. [04:02:38] lesser amount of time, faster queries, then you can go with the import option. If you want a real-time data, then you need to write a query to connect it. [04:02:55] different sources, like a hybrid data mode, then you can go with this composite large model in real time and processed data, pre-processed. See, real-time data you're grabbing, and also you're writing [04:03:08] you're grabbing, and also you're writing a query on top of it for processing. the necessary settings for maintaining a [04:03:21] data privacy, and also where to refresh the data, and how to check the data access. Let us check that in our Power BI. As we are working with the paid version, we don't have the organization related [04:03:36] privacy option. Come to this select this options and Data sources settings you need to select. This is your privacy. We are going with the data privacy check now. We need to maintain the data properly. [04:03:55] So you can see the actual data sets available now. You can change the source or export PBIA sorry PBIDS edit [04:04:08] permission clear permission. So there will be multiple files sometimes. This is your global permission. You don't have any reason is this is the this is my personal laptop and this is [04:04:22] not something related to organization. I can see a question. Okay, you want me to repeat the step. Okay, this is the data this is your Power BI. Select this file option on top. You can [04:04:35] see home insert modeling view optimize and help. You can also see the file and help. You can also see the file option. [04:04:49] cannot sign in. You can go with this options and settings. First let me show you what are these options what are the options we have. So coming to option. So you can see here. [04:05:03] Always detect column times and headers for unstructured sources. These are by default options what you have. Power Query Editor Direct Query. See for [04:05:15] direct query you should also have organization account. But you can see Python scripting if you want privacy. So, always combine data according to privacy level settings for each code or combine data [04:05:30] according to each file's privacy level settings. Always ignore privacy level So, combine data according to the privacy level settings. You can it by default will be there, but you can just pick. [04:05:44] Save to computer by default. If you want to store all the documents for saving, you can select it. Usage data. Usage data. It is selected. [04:06:02] It is English. Use application language or always English. Use the application language. Application language is also English. Coming to current file. So, my current file is customer data and the sales [04:06:15] data. Regional settings, privacy settings. Combine data according to privacy level settings. This is what by default I will keep. For semantic model, for now I don't have option to semantic model. You [04:06:28] can see this works for paid version. [04:06:40] Don't allow end user to save filters. You can select it when you're sharing with people. Then you don't allow the other users to save the filters on the file what you have. [04:06:52] This occurs with Power BI service with paid version. [04:07:07] >> So, this is your privacy setting options. about options. Now if you go to data source settings, [04:07:26] can check in options what I showed you few seconds back. If you go to options to the data source settings what we have. [04:07:38] it, uh, you will have no way of getting it uh, you will have no way of getting it back. Privacy level. Now you can see I mentioned, you know, there are three [04:07:53] levels of privacy, public data, organization data, and the private level data. So if this data set is public data, you can select public data, select okay. Or if this is organization-related data, select this and tap on okay. If [04:08:08] this is private data, then select it, tap on okay. This is how you will edit the changes. By default, it will be none because we are working with the normal because we are working with the normal free version, so it will be none. [04:08:27] this data and I'm doing all the changes here. If you select sales data, then you are doing the changes for the sales data. Individually, you need to do. Now I selected the customer data. If you want to export this, you can export this [04:08:41] Power BI data. This is nothing but Power BI data source. You are I See, the CSV file is already here. If you again want to export this CSV file for you, then you can select the file and you can export it. [04:09:00] together, select tap control on your keyboard and select them together and then see edit permission. Both the files see two entry can apply, you can do the changes. [04:09:14] download. Individual files you can download. If you want to change the file, you can see change the source Browse where you have this file. If you want to rechange, see I want to change [04:09:27] the file to a new file. Select this, tap on okay, the new file will appear here on okay, the new file will appear here and the other one will be replaced. This is to change the file replace the file in this data source setting. [04:09:41] So options and data source settings will help you to work with the higher level of your data for this permission, for setting, privacy level, it will help [04:09:53] you. Global permission is for paid version. So I repeat again, select this file, come to this options and settings, you will find options and data source sorry data sources settings. [04:10:09] Select this, explore it. It's a very simple options what you all have. As per changes. By default changes will be there. [04:10:21] But again, if you want to apply on top of it anything, you can select it. connects to various data sources, Excel file, CSV file, SQL databases for your [04:10:37] analysis and for reporting. Data connectors are the bridge that Power BI uses to connect to various data sources. Privacy levels as I said, it can be a public or an organization or a private. [04:10:52] This is one of the important interview question, especially for business analyst or for data analyst. And if you are I mean if the job description expects you to Power BI is mandatory. If your manager is asking some questions [04:11:06] related to Power BI and a data. Data security is what usually organization people will think. So, this is very important. Public organization and private are the ways to control the data and the dashboard of Power BI. [04:11:20] Data source location refers to where data is stored and the path to understand the exact address of the data. Data model structure and stores data for analysis using either a shared semantic [04:11:35] model for centralized use or a local model for individual report. So, these are the important points whatever we discussed with so far related to our Power BI and different [04:11:49] data sources option. So, in today's call in a nutshell, we have discussed on how we can import the data from different data sources to our Power BI platform. [04:12:02] It can either be a Excel file data, CSV file data, or from a different server. We know how to create a path. How to import below. After importing, [04:12:14] how data looks in a table view, in a data model view, we also observed it. [04:12:27] I will help you how to create all this. Now, just understand the different tabs. Different tabs you can see is consisting of different analysis. We imported the data from different data sources. [04:12:41] See, target. This entire target is calculated. This entire thing is calculated. This is the date time data type. data. [04:12:55] This is the region wise data. An hierarchy is created. I will show you how you can create this. This is the reseller's data. hierarchy which is connected. [04:13:08] This is the sales data what we have. This is the sales person data. performance data. This is a schema of it. This is the sales person region wise it. This is the sales person region wise data. [04:13:25] This is your date data with the target. This is the product product related data. This is the region wise data, reseller data, sales data, [04:13:37] reseller data, sales data, sales person. person person performance data, sales person region wise data. Wherever you [04:13:50] can see this option, you can see this is a query related. They have mentioned a query and then these columns are created with this calculation. I will show you with this calculation. I will show you how to do all this calculation. [04:14:08] uh formats I mean different data types again available in different files also. We imported all the data available here. This is the data model. So, this is how if there is a connection between one file with another file, you can see the [04:14:23] direct connection. You can see the complete link of it. This is the first main data. This is to product. You have a product key. Product key product key a product key. Product key product key link together. Here we have date date. [04:14:35] Here we have a date. Here employee ID employee ID employee key we have which is linked together. Here target. Target is getting linked with employee key is getting linked with employee key employee ID. [04:14:53] data. This data is not directly linked to this. This data is linked to sales performance because employee ID and employee ID are common. Many-to-one connection this is. And this is there is a connection. You [04:15:06] can see the entire data model. Last Power BI which I showed you, there was models. The reason is there was no primary key or the common unique key between them. The common key is what it was not there. [04:15:21] But now you can see this is your entire data model. connection you can see. As soon as I move the cursor, you can see which key [04:15:34] is helping to connect one data with another data. Here it is product key. Sales data and the product. Here it is date, order date. Here it is employee date, order date. Here it is employee key. Here it is target or month. [04:15:48] Data between these two it is employee ID. To understand, this is sales person based on employee key. You can expand And here this link is to is between sales and a region. This is your entire data model. We'll talk about it later. [04:16:05] For now I'm giving you information just how it looks when we merge multiple data sets together with a common ID or a common column. [04:16:24] see if I select Australia, you can see there's no data to Australia. Canada you can see. Central you can see. France you can see. Germany you can see. You can see it is an interactive dashboard. As soon as I say do any [04:16:39] changes to this filter, you can see the dashboard graphs are also getting dashboard graphs are also getting changed. France you can see the data. And this data is taken care with respect to this [04:16:55] region. You can see this is taken. This filter you know, data set. Select all. Everything is here. Select this dashboard. You can see this this graph is created with the help of date [04:17:09] data and the sales data. If I select this graph, you can see from which you can see all the options. I've selected different data from I've selected different columns from different data sets to create this. I [04:17:23] will show you in the coming up class how to create all this. For now, please understand the data source. So, if I select this graph, you can clearly make up I've selected the data that is product and sales from these two [04:17:35] that is product and sales from these two files. Another filter, 2018. 2020, 2021, 2022. 22, we don't have a data. 21, we have no data. 2020, we have a data. [04:17:56] region are the two filters what we have. You can then select this filter page. is for this filter. Which we have. [04:18:09] created by using multiple data columns from different sources. So, as soon as you select the data, you can see which data source is linked to can see which data source is linked to it. [04:18:24] page. I have the same data set. [04:18:37] Australia, you can see in 2020, we have received 108 orders, sales, cost, received 108 orders, sales, cost, profit, profit margin is negative. Central region. See, it's not about creating a dashboard, it's also a story [04:18:52] what it is telling us we need to understand. It's not that I created this combo of bar graph with line. So, what it is telling me? It is telling me that in 2019, you can see the profit is very less. But [04:19:07] in 2019 November, the profit gradually increase, especially the profit got increased from September to October, November, December until May, entire month the profit you can see increase. Only before 2019 September, the profit [04:19:22] is very less. This is called as data storytelling, not should also concentrate on how to create these graphs. What are the principle behind it? All this comes in the next class. [04:19:36] Today, it is only to understand the data source, how to link and other things. So, I will explain you in the coming up class, nothing to worry. So, this is the performance. So, this is the finance fiscal year [04:19:49] So, this is the finance fiscal year 2019. This is sales, target, variance, see sales, what is the sales, and what was the target given. This is sales and target based on month. This is [04:20:03] horizontal and vertical difference. No no more changes. Okay. No more changes, horizontal and vertical difference. If I go and select 2019, this is [04:20:22] data, we don't have the actual sales data. So, you can see here. So, to create all this, I will show you that in the coming up class. Now, I'm just helping you to understand the data storage link here. [04:20:37] So, you already know that multiple column how to import multiple data sources, how to view multiple Tableau I mean you already also know about the table view, how to understand the data of different tables, and then you also [04:20:50] know about model view, how it looks. How to create, how to understand, I will tell you after we import the data once we start creating the dashboard. You also have an option here to reset the layout. [04:21:09] automatically linked. You need not have to do it. [04:21:23] If you want to delete, you can delete it. Assistant. So, today's concept today's [clears throat] content is all about preparing the data set. So, Power BI connects to various data [04:21:37] sources. A recap like whatever we discussed in the last call last two days discussed in the last call last two days I mean last week two days. So, Power BI connects with different data sources which you already know. [04:21:49] Data can be dashboard can [clears throat] be static or a dynamic. You can create it with the help of data. And you also know about privacy levels and source location helps to control the data. The data model organizes and [04:22:05] stores data using shared or a local models to support analysis and the reporting. Power BI supports three important types of data. One is directly importing the data, and the other one is the query I [04:22:18] mean the direct query where you will import the data with the help of uh different databases, or maybe you will write certain query on top of it and you can import the data. So, different ways of importing the [04:22:31] data, but as soon as you import the data, the data you will be able to see in model view, and you also can see that in the report view. In report view, model view, table view, or you know, the different views what we [04:22:46] have, it will help us in understanding the data. for its quarterly executive review. The business intelligence team is tasked [04:23:03] with presenting a regional sales performance in Power BI. However, upon reviewing the data set, you notice several issues: inconsistent inconsistent data format, mispaired the region names, missing the sales figure. [04:23:18] Would you go ahead and build the dashboard with this messy data? What would be your first step in this process? See, whenever you receive the data, you already know first you need to have a very good domain knowledge, and [04:23:30] business, and then you need to understand the data. So, when data quality is incorrect, we cannot build a dashboard, and you cannot cannot build a dashboard, and you cannot even expect a good result out of it. [04:23:44] So, when you prepare a dashboard, it is our responsibility before creating to do all sanity checks. That is data quality check, or I mean, if there is any spelling mistakes, or if there is any null values, if there are [04:23:59] any duplicates, data inconsistent check, or the irrelevant data if you have you need to convert that irrelevant data to a relevant data, or converting unstructured data to a [04:24:12] structured data is a very important. So, first step, as soon as you import the data, is to clean the data. [04:24:29] session, you will be able to understand uh why data quality how and why data quality is important. What are the different options we have with respect to Power BI? Power BI normal tool and also with the Power Query. You will also [04:24:43] be learning Power Query or Power Editor which we say. Identifying the errors during the data import to ensure the data is complete and accurate. Select appropriate column data types and create and transform the columns to [04:24:59] the data. Transform queries to improve their efficiency and performance. Identify and create appropriate keys to establish the relationship between the different data [04:25:13] types or data entities. So, you can you can clearly make out how to clean the data, how to check the data quality also after cleaning to cross-verify, how to replace how how to remove [04:25:27] uh the existing data or how to add the data to it, how to add new columns or how to add new rows. Append, merge, today's college. All, you know, you will get answer and experience maximum [04:25:41] compared to last week, I can say. So, please uh So, first thing is to So, please uh So, first thing is to clean the data. data? The important the main uh agenda of creating this dashboard is to take a [04:25:56] business decision. And if a person is taking the decision based on this data, the data should be accurate. So, if the data is not accurate, the decision what is taken by the business management team uh will be completely, [04:26:10] you know, incorrect or you will not be able to achieve the target. It's a crucial step in Power BI data modeling process. It involves modeling process. It involves transforming your raw data into a format [04:26:23] visualization. So, data shaping or data structure you can call or data cleaning you know for creation or sorry for creating the dashboard. You can give a name to it, not an issue. So, data shaping is all [04:26:39] about data pre-processing or data transformation we can call. data. Again and again I will not repeat the same step. So, you you will import [04:26:54] the data using uh Excel sheet if you have then you need to select it and then you need to import it. Then you can create a dashboard out of it. Later you will publish the report. But for you to publish the report [04:27:06] online, you need to have a paid version. Right now we are concentrating on the free version. Whether it is paid version or you know the free version, cleaning, data quality, data transformation, all these options are available for us. [04:27:22] So, nothing to worry. 100 I mean when it comes to only publishing the report, you may not have the option. But apart from that for all other things for Power Query also, everything as a paid version user, you will be able to access and [04:27:35] So, it's a powerful tool that enables user to connect to various sources, then clean and transform the data before analyzing it. [04:27:48] you already know. You can connect the data directly from a database or you can also import the Excel sheet data or a CSV data directly from your laptop to [04:28:00] this Power BI. So, Power Query Editor connecting to data sources, it is the process of connecting to external data source. So, usually when you go with Power Query or when you are creating a dynamic [04:28:14] dashboard, usually we in you know we link the database data to this It is a process of connecting to external data sources and preparing that [04:28:26] data for analysis and visualization within the Power BI platform. So, what is this Power Query? You know, you have seen Power BI. After you import the data, you can go with the transform option. It will take you towards Power [04:28:40] Query. That means within the Power BI platform, you have a linked option. You have a linked application that is Power Query Editor. This is used for you to [04:28:52] Within the Power BI only, it will take you to a next part that is Power Query Editor. And this is an advanced topic of Power BI. So, the I mean, you are learning See, earlier Power Query was not integrated [04:29:07] example, if I go back was a different one. It was not integrated with Power BI. So, now they merge together so that people can go with the advanced analysis [04:29:22] and advanced calculations. So, I will show you that uh you know, is. So, when we go with Power Query Editor, data cleaning and data transformation comes [04:29:36] They are essential steps in the data analysis process and Power BI provides robust tools to handle these tasks efficiently. So, when it comes to data cleaning, it involves identifying and correcting [04:29:51] errors, inconsistency, and inaccuracy in the data. So, data cleaning, the main agenda we know, to remove the outliers or to remove or to correct the errors or to remove the irrelevant data, any inconsistency if we have or if the data [04:30:08] is incorrect, it is our uh priority to clean it first. Data cleaning. Coming to data transformation. See, data transformation is where you're modifying the entire structure of the data. Data cleaning is where you [04:30:22] concentrate more on the data values. I repeat again, you can expect a very good interview question and an important question here. What is the difference between cleaning and transforming? Data cleaning and data transformation, [04:30:36] you can expect. You will learn more about it, but still now, data cleaning is where where you concentrate on the data numbers or data values. Data transformation is where you will concentrate on the data structure. You [04:30:50] will try to convert rows to columns, columns to rows, pivot and pivot. That is there. So, data cleaning is where we will more concentrate on the where we will more concentrate on the data inconsistency values. Here, in data [04:31:04] transformation, entire data structure. It's a very I mean, the difference is very easy, but sometimes, you know, it is a bit tricky to understand. So, when I talk about data cleaning and data transformation, [04:31:18] you can see here, like removing the duplicates, handling if there is any missing values, concentrating on different data types what we have, data formatting. See, to removing the duplicates is to maintain [04:31:32] proper data and the maintaining the unique values to get the right result at the end. Handling the missing values, you have a still some of the values are missing. [04:31:46] Some of the maybe in some cell, try to imagine Excel. Some of the values are having some missing data. If you apply any operation on this, you will not get the right result because of the missing value. [04:31:59] Usually, what we do is when any of the numerical value is missing, we will replace that either with zero or will or by taking an average of all other values what we have in that column, we will replace that [04:32:14] null value either with zero or with a or by taking an average. Coming to changing the data type. Sometimes when the data structure is incorrect, we will we need to concentrate on the data type. If not, [04:32:27] you will we will not be able to create graphs. Sorry. So then concentrating on different data types. Again, sorry to add on one more point. It completely depends on what data we have. We also need to [04:32:39] concentrate on the data from where they are getting how the data is and we need to take certain decisions based on checking the previous value also. So to move on to the next one, it is changing the data types to concentrate [04:32:53] on different data types. We can easily identify the data type in Power BI. And then formatting the data again. If you want to convert month, year, it was like it it is like a date, month, and then year. And if you want to do the [04:33:08] formatting, maybe short date or a long date options what we have, that can also be done. So changing the data type is also possible and that is nothing but formatting the data. You can also create I mean, you can add [04:33:21] formula and you can create a new column. Create cal- creating a calculated columns. You will be learning all this in today's call. So how to create new column, how I mean, by creating a new column in the sense, adding a formula, [04:33:35] you are creating a new column. Merging and appending the data, pivot and unpivot, grouping and aggregating the data. will be you will be getting hands-on experience on this and all these are [04:33:50] possible with respect to Power Query. So not only Power BI, we are going with the advanced application of this, that is Power Query. Hope it's good to go. I believe you have no questions, but if [04:34:03] know. So the key components of working with this Power Query Editor. See, Power Query Editor, the the name itself says it is a query. Sometimes you will write formulas, sometimes you will [04:34:16] write the entire query to get the result. The editor provides a user-friendly interface with a ribbon-based layout, and it is easy to understand the icons. Formula bar will help you to know the [04:34:31] formula, you can do that. In Excel sheet, how we add on top formula bar, in a similar way, we also have it in Power BI. The query steps pane displays a list of all the transformation applied to our [04:34:47] data, making it easy to understand, and you can also modify the transformation in the query. I will show you all that uh you know, we'll learn all this now. uh you know, we'll learn all this now. So, intuitive interface, formula bar, [04:35:01] query steps, which you will be concentrating and learning. Query folding and custom functions. The Power Query Editor can push a certain transformation back to the data source, improving query performance and reducing [04:35:15] the data transfer. Query will help us to enhance the data quality, and also data optimization is possible. Optimization is nothing but again to increase the data quality, we go with this optimization technique that [04:35:30] we also have it in Python. So, custom functions, user can create their custom functions to automate the repetitive task and streamline their data uh sorry, their data preparation process. [04:35:44] For us to prepare the data as how you want the formula, if you want to add new column, or if you want to delete the existing column, so those custom functions are easily accessible, and you can also add it easily. [04:35:59] To simplify the data structure, we make sure the name consistency, data retention. We go with this Power Query Editor. So, databases serve as containers for holding related collection of data. See, this is your [04:36:13] entire database. All the data will be stored in the database. It is holding the entire data, whatever we are expecting. So, for you to create a dashboard, you will create a path to create a Sorry, to grab the data [04:36:27] from database to this Power BI. There is a path which is created. Data retention is here. Name consistency is here. And you're using a Power Query Name consistency is because all the databases will be Again, the data will [04:36:43] be encrypted here. That will be stayed stored in different uh you know, databases and it will be in different tables. From that, you're extracting the data. So, data retention, [04:36:58] retaining the data, name consistency is to maintain the proper name for different uh data databases and for different data set. Use Power Query Editor to do all this transformation. [04:37:11] You need to know the advantage of all this. What is the purpose of going and everything. So, to simplify the data structure, if you want to rename the queries, whatever the transformation you're doing, if you [04:37:23] want to rename it, you can do that. You can The main purpose of using Power Query is to concentrate on the queries, to replace the values. Uh I mean, to replace the particular values you want to change uh [04:37:36] for you to uh for you to under- I mean, for us to understand the data easily, we can replace the values. Especially, categorical data will be converted to numerical data for machine understanding. That's an example what we [04:37:49] models, to convert categorical data to numerical data. Here also, it is possible. Anything you can convert category to Anything you can convert category to numbers or numbers to categories. [04:38:02] And to replace the null values if you have any null values or usually the blank cells will be mentioned as n u l l. That you can replace it with zero. Then to remove the duplicates again to [04:38:15] maintain the unique data and to avoid, you know, the repetition of the same numbers we will go and we will remove the duplicates. Column wise we can remove the duplicates. See, whatever the operations you're [04:38:29] Excel sheet. So, if you know Excel very well, the same operations we also have a power query which is added with Excel sheet. That there also you can do all these operations. [04:38:46] usually towards first one, just a second. [04:39:00] So, cleaning data for health care in Power BI, this is the first, you know, today. I'm giving you so much of explanation, I just don't want to keep the session boring. First, let me show you. Let us import the data and then [04:39:15] again I will start from the scratch. So, this is the blank report, new report which I opened just now. You can also open the file and you can see this get data option. Tap on this get data option. Go with [04:39:30] Tap on this get data option. Go with this more. text or a CSV file. Just tap on it again and then connect it. [04:39:48] remember. I'm uh help I mean I'm helping you to get this patient data and then tap on open. Make sure you select the CSV. If open. Make sure you select the CSV. If not, you will not be able to import. [04:40:08] So, you can see here it's a very simple data. I have only five rows. I'm importing it. You can load this data first. [04:40:49] imported. You can see here all the columns. And if you want to go and check the data, you can see here you have so many views. Go with the table view to check the entire data in the [04:41:04] structured format. So, this is the table view of our data. I can see a message imported. Okay. [04:41:17] Okay. Hope you all imported the same file. >> Yes. >> Can Can you Can you once again go back and start once again the second one after you loaded? [04:41:32] anything there. I I mean I just went to this PPT. >> Okay. >> I will show you that. As of now, I didn't do any operation on the data. I will show you one by one. [04:41:44] >> Okay. >> So, requesting all of you to please load the data and keep it I mean make sure you open and keep it I mean make sure you open the blank report and you load the data. [04:42:06] the particular value. Replacing the null values, wherever there is null, it will be replaced with zero. And then we have a Directly we can remove the duplicates by selecting the particular column. [04:42:23] removing the duplicate by considering all the data, it is good to go. Because sometimes categories will be repeated. Categories of different month sales will be different. So, select all [04:42:35] the data and then go with the duplicate removal because all other rows also you need to concentrate. Accessing data types and properties. So, the main purpose of understanding properties and the data type is very [04:42:50] important. So, first thing is detecting data types automatically. When importing data, Power BI scans the first thousand rows to determine column type. If data types are mismatched, it can [04:43:04] lead to slower query and execution will also be late. also be late. Manual data entry in CSV or Excel file type. Let me show you how this works in our [04:43:18] As soon as you import the data in Power BI, you can see the summation option. So, this is nothing but we have a numerical data in this column. In this column also, we have a numerical data. So, this Power BI, it's automatically [04:43:35] identifying the data type. How Python programming language works? There also it automatically recognizes the data type. Here also this Power BI automatically recognizes [04:43:48] the data type. This is data model. This is that. So, this is the data what we have. You can also go to this transform. If you want to open your Power Query [04:44:00] If you want to open your Power Query Editor, as soon as you import the data, you can see load option, transform option. There also you can tap on transform option to directly go towards Power Query. [04:44:12] Or you can come here and select this transform data option. Here also this will take you to towards the Power Query. You can see it's a Power Query Editor. See, this is your Power Query. So, today [04:44:27] we we are more towards getting hands-on with respect to this. So, please concentrate. See, this is numerical data. So, 1 2 3. It indicates it's a numerical data. [04:44:39] AB So, you imported the data now. You need to do some calculations. You need to go and check the data type. Select this transform data just in the middle. You can see this transform data. [04:44:53] So, select this transform data option. It will take you towards a Power Query It will take you towards a Power Query Editor. [04:45:09] Hope you all can see this Power Query Editor. you can check. This is your rename option for whatever [04:45:23] query you do, no? Whatever the transformation you do, it will appear here. I will show you that. You can close it. So, this is the data You can close it. So, this is the data what we have. [04:45:40] rows we have. So this is your transform data option and it is automatically identifying the data type. 1 2 3 indicates numerical data, ABC indicates character or a string data which we can find. [04:45:55] here. See? the eight rows. Here what you are applying, you know, this is by default what you have. Again, whatever the operations, whatever the [04:46:08] modifications you are doing, it will appear here. That is called as uh that. Right click on the column. See, I just selected the first column. Right click on that. You can see remove other [04:46:22] columns, duplicate the column. Now imagine, I will duplicate the column. As soon as I duplicate, the new column, the the duplicated column will always appear at the end. This is how we will duplicate or copy [04:46:38] the same column. And this you can see as soon as I did the changes, you can see the new query got added. This is where we will rename the query. Right click on this. You can rename it. This is how whatever the operations you [04:46:53] do, that is called as query in case of power query. You did a change. You added a new column to your existing data. So that the changes what you did, it is [04:47:06] considered as a query. This is the steps. You can see all the applied steps are here. What you did now? It's a column duplication. So you can see duplicated column. You can just right click on this, rename it, or if [04:47:21] you want to delete it, or if you want to insert a step after insert a step after before that if you want to add anything. [04:47:34] So, if you want to change it, if you want to rename it, here is where you can rename. What is this? You created another patient ID column. You copied So, copied patient ID. [04:47:49] Just for your understanding, you will give a proper column name to it. This is a query change. This is not a column name change. This is a query name change. So, that when you see the query, [04:48:03] about you copied a column. That transformation it is talking about. This is how you will rename the query towards your right-hand side. Right-hand side the entire applied steps is for you [04:48:19] to concentrate on the queries whatever we did onto this I mean the transformation or the changes whatever we did on this data. Okay, so you already know how to rename the queries towards your right-hand side [04:48:33] the steps you know whatever we do the changes. So, there if you want to give a new name because it will show the common or a general term for us to understand again you know in future if you just want to go back and check what queries [04:48:47] you did the changes. So, as per your understanding, you can give a name to it. So, that is renaming the query. Rename unclear queries using a rename option to more familiar and the descriptive names. [04:49:01] So, replacing the values. Let us see how to replace the values now. I will uh share uh okay. So, this is the data what we have. This is the table view of the data. [04:49:15] You can uh just a second. You can see eight rows now. So, this is table view. See here you here also you can do the modification. It's not that always you need to go and make use of power Query. Here also you [04:49:27] can do some basic modifications. Here also you can see the text data type. So it shows, but we are going towards Power Query now to concentrate on the additional features. [04:50:01] So this is the data. So now let us see how we can replace the data and how we options. So one thing is you want to replace the data now. For example, right click on that. You can go you can see here uh [04:50:17] replace the values. It is the same what we do in Excel, no? Control R. If you just go with Control R or Control F, we will find and we will replace it. So what you want to replace? Wherever it is [04:50:30] eight, I want to replace it with eight dot. Find this, replace it with this new data. [04:50:49] change it with 800. See? Eight is getting changed to 800. If I remove this now, what action it was taken, it will be [04:51:01] replaced with the original data. Like it is just is just uh undo. sheet. The same thing is happening here. That is to replace. It's not only with [04:51:14] the numbers. Uh you need to select the particular column. Select it, go with the replace values. You will find the data here. For example, and you you want to replace it with a different name. [04:51:28] You can see advanced options also. Match entire cell content. Replace using special characters. So, wherever it was the name, now it is added with a new term. Again, if you unselect it, I mean, [04:51:44] if you delete it, you will get the original data. If you save it, original data will be saved. This is how you will replace the existing values, and it can be anything. But, another example, [04:51:58] But, another example, you want to replace 700 with the seven. Okay. See, you did a you did some changes, uh but as we already know, we don't have seven and it in this column. [04:52:12] And uh 700, if it is there, it was it was supposed to be replaced with seven. We don't have the data. Actual data itself is not there. You can see the query here. So, replace value, wherever it is 700 to [04:52:25] replace it with seven, but we don't have anything called a 700 in this column. You can see the column name here called as patient name. So, no changes, even if you delete it, no effect, no data changes. [04:52:42] is. Earlier, it was Mala. Now, it is getting changed to a different name. If you remove this also, then original data will be there. This is how we will replace the values. [04:52:56] Now, you can see how to replace the null values. We have a null value here. Select the column, and then you can see here this is to uh replace see, remove the duplicates. This if you select in this column, [04:53:10] remove duplicates. Duplicates will be removed, and the existing data is here. But, see, please be very careful when you're removing the duplicates, especially only by selecting one column, because it will affect the [04:53:23] other data. See, here it is heart disease, diabetes, broken leg is the unique data. But, see, heart disease for this cust for this person is for this person John Doe 35, John Doe 35 actually copied it. [04:53:37] Select all the data. Uh select multiple columns where you want to check uh you know, the duplicates, select everything, and then come here. Now, go with the remove duplicates. [04:53:51] Now, you have removed the duplicates. This is how by selecting everything you're removing the duplicate. You can see still this John 35 male heart disease is still here. The reason is the ID is different. Here, all the data what [04:54:05] we have is unique data. So, when you select everything, as this is unique data, so no changes is happening. Here also, if John Doe was uh one, for example, I will change this data. Replace value. [04:54:21] Five will be replaced with one. Only one cell also you can select and you can do cell also you can select and you can do the changes. [04:54:38] Right click. Remove duplicate. Now, you can see John is removed. The reason is that was the only column with a different number. You remove that also, you change that, and now you can see that data is unique. If you save it, [04:54:56] the Power BI file will be updated with the seven records. I will delete this. We'll delete this also. This is the data what we have. So, you already know how to work on this [04:55:10] rename query, replace the values, replacing the null value. So, here we have a null value. So, if you want to replace that, it's a blank value, right? So, you can directly see this replace the values. [04:55:25] What value it is? Nothing to find. You want to replace it with 4 5 6. See it now. Whenever you want to replace the null is blank. And the second one is [04:55:39] what to what to fill? That's 4 5 6 what I fill, and you can see 4 5 6 here. But 4 5 6 data type is different, and this data type is different. Maximum of the column is belonging to string data or a character data. So, it is [04:55:54] or a character data. So, it is identified as ABC only. Okay, so first, I will just replace this value now. I will just replace the value. It is 4 5 6, and that will be replaced with BP. I [04:56:08] will just add this. Now, you can see the data. It is BP. So, here also what you can do is, for example, if you want to apply for a particular thing, you can come here. You [04:56:21] can put a filter to it. For example, here it was null, right? So, you want to fill the null only for females. So, you need to select female only, and then you can use it, and then you can apply a filter. So, like we need [04:56:36] to do again, it's a manual task only. I don't want to say that it is easy. It's a manual task, whatever you're doing on this Power Query. [04:56:55] just replace the null value compared to my original data, and I will save this. Wait for a few seconds until it is saved. See, it will show you this patient data [04:57:09] updated. Now, come and see this. See, now this has been updated with BP. You need to wait for a few seconds, or else Power BI will hang for sure. Okay, so now you can see the replace uh the null value is replaced with this BP. [04:57:23] So, replacing the values, replacing the null values, removing the duplicates, all the topics we are clear now. Just right click on the column or select multiple columns if you want to replace altogether. And if you are checking the [04:57:39] duplicates by considering the entire data, make sure you select all the data, make sure you select all the columns and then remove the duplicates. So, remove duplicates, replace the values, you already know how to work on [04:57:51] this. So, anything you want me to repeat? The main purpose of using this is to uh concentrate on the data quality, addressing if there is any mismatch in the data. [04:58:04] the drop-down option, and you can see the data filter. profiling tools. There are There are essential for understanding the [04:58:18] characteristics and data distribution, column quality, column distribution, column profile. Why we go with this data profiling tool is to understand the data column quality. I will show you how it looks in Power [04:58:33] BI. I will uh show from the beginning. I will just close this. See, this is the Power BI dashboard. I hope you all can see the blank Power BI. Again, let us move towards transform data. So, hope [04:58:46] you all can see the Power Query now. Now, you can move towards add column Sorry, view data. View option. You can see transform, add column, view. The main purpose of using Power Query is to add new rows, add new columns [04:59:02] uh with the help of queries or doing the data transformation. So, you have a tab separately for transformation and again for adding the column. Let us move towards view now. Let us concentrate on view. [04:59:16] You want to view the data quality, data column, column distribution, and other perspective. Coming to data quality. So, here you can see in this each column what is the error rate, if it is any null value. I have already replaced the [04:59:31] null value. I have already replaced the null value. So, empty is 0% error is 0% null value. So, empty is 0% error is 0% and valid data again it is 100%. If earlier if you have not filled the data, if you have any blank space here, [04:59:45] then you will see the different percentage. see. And in this column everything is unique, eight distinct, eight unique. So, unique will talk about uh without [04:59:59] any repetition. Here Here you can see. Totally in this column we have three distinct values, out of which one value is not repeated, that is failed. Successful is repeated, in progress is repeated, whereas failed is not [05:00:14] repeated, it is one unique. That's the difference here. distribution, you can see. You can also expect this as interview question. What is the difference between distinct and unique? It's quite confusing, but [05:00:29] distinct talk about the entire column unique data, whereas unique count talks about only the one non-repeated value I can see. So, this is column uh distribution and this is column profile. So, if I [05:00:44] select column profile, you can see the complete statistics of the column. So, what is the total count of elements? It is eight. If you have any error, zero error. Any empty cell, zero empty cell. I think if you have anything which is [05:00:59] empty, it will appear as empty zero. I mean, if you have one cell which is empty, empty one. If you have five cells empty, empty one. If you have five cells empty, empty five. The count of null values [05:01:11] will appear under empty. Distinct data. So, here also you can see there are total five records. Out of which or non-repeated records are three. And then you can see minimum, maximum. [05:01:27] This works if you have a numerical data. The entire data description, basically this is called as descriptive stats. If you know Python, we have a describe method with respect to Python method in [05:01:41] Pandas. Using Pandas library, we can go with describe method, which will describe the entire column. Similarly, the same options we have here. category, no, you don't get that minimum. You get see, minimum and [05:01:57] maximum you will get, but you will not get like average, standard deviation, even, odd. The entire stats will not work for categorical data. You can see only the common. Because whenever I say stats, it is for numerical data. It is [05:02:13] not for categorical data. Okay, so this is the These are the different options what you have to understand the column better. [05:02:25] you can also unselect if you don't want it. This is your formula bar. You If you unselect it, you don't you will not see the formula bar. This comes under view option. So, you all know about column [05:02:37] option. So, you all know about column quality, column distribution, and column profile. In column quality, on top you can see uh the error, the percentage. In column distribution, you will be able to see [05:02:49] the data distribution. In column profile, you will see the data profile, you will see the data descriptive stats. Requesting all of you to please go and check on your own. Okay? [05:03:02] So, yes, column quality will let us know the value count, unique values, duplicate, the null, or the error. And also the data type on column label only, automatically it can see. So, this is your [05:03:16] Uh you can see the column quality, the valid, the error, and the empty. see the column quality. You need to select You need to select this option, [05:03:30] select You need to select this option, if not, it will not appear. enhanced the data analysis, you can understand the data better, main thing. And then to take a decision, if you have a good data, it is easy to take [05:03:45] decision. Before you directly go and apply any changes on the column, before you transform the data, before you replace any value, before you do [05:03:57] anything on the data, please consider this as the first step, come, select these options, and check the entire view of data. It is time saving, of course. If no error if error is 0 0%, you don't need [05:04:12] to do any changes. So, please check this first, and then take necessary action. So, this is your column distribution, the unique data, and the distinct value. It is a powerful tool that allows users [05:04:26] to visualize and analyze the distribution of values within a specific column of the data set. You already know the data distribution in terms of bar graph, distinct data, unique data. See here, you can observe A [05:04:41] A is repeated twice, B is repeated thrice two twice, C is repeated once. Three distinct uh uh A, product B, product C. Out of this, product C is not repeated twice. It is the unique data in that [05:04:57] It's like first counting all unique data, entire column unique data, and then only checking for the uh non-repeated value. That's the meaning of the value, I think so. This is your column distribution. [05:05:17] column you know, then you can see the bar graph, no? So, to understand or to check only the non-repeated value, to customize it, you can understand how many categories are present in that column easily. [05:05:36] It's not a histogram. This is a column. This is not a histogram. If you select the column profile option here, as you can see this column [05:05:48] profile, you can see the complete statistics of the column, or you can also call that as a descriptive statistics. But the in general, it is statistics only. In depth, it is called as [05:06:02] descriptive statistics as per Python. So, whether it's Python or Power BI, the analysis what we are doing is the same. So, let us proceed further and understand the different issues what up what comes [05:06:16] in. See, whatever the changes we did now, it is all transforming the column. As soon as you select the data, you can see transform column here coming up. It will only concentrate on the data column-wise. [05:06:31] Any calculations, any analysis you do based on the column, this transform based on the column, this transform column types comes into picture. I will show you here. I will remove all this. Make sure you keep white spaces [05:06:44] because if you add any tab space in between, it will appear. between, it will appear. Select the column. [05:06:57] different options with respect to it. So, now move to transform. select the replace value, you can see replace value. If you select duplicates, [05:07:11] you know, it is getting changed to the duplicates removal part. So, detect the data type. If you select, you will be able to mean [05:07:26] It It I mean, you can see the new addition one got added here. My cursor was here. My My cursor was on before that, so the change is is here. that, so the change is is here. I will remove this. [05:07:51] want to do any changes like lower case changes, upper I mean, alphabets upper case, lower case, trim. Trimming the data is to clean the white spaces. To check the length of that particular data, you can go and select this length. [05:08:06] data, you can go and select this length. So, this is nothing but 10 elements, 11 elements, 10 elements, six elements. You can see Earlier, it was alphabets. It got changed to total count of elements now. You can see the transform columns. [05:08:21] Any changes you do on the column, it is transforming the changes. So, I don't want this. I want to see the original data. So, you need to create a new column, and then you need to apply that function. [05:08:35] So, transform data usually transforms the existing column automatically as and the existing column automatically as and when you apply any method on top of it. So, earlier it was seven to eight. See, successful s u s u c c e s s s u l. [05:08:54] Totally this 10 elements. Instead of you seeing this original data, if you go with the transform column, especially with this to check. See, these are the options what we have with respect to transform. [05:09:09] Length, clean, a lower case, upper case, capitalize each word. See, if I go with capitalize each word, it's already cap it's already in caps, so you don't see much changes. But, I will select it, come to transform, [05:09:24] lower case. Now, everything is changed to lower case if you observe. We transformed this table, the existing table only I transformed. I didn't copy that table, I didn't do the changes to the other [05:09:38] didn't do the changes to the other table. I did changes to the same table. That is called as transforming the or transforming the column. [05:09:51] see here, there also you can right click and you can see this transform option. This is to change the data type. This is to transform the data. This you already know, replacing. Pivot and unpivot, I'll talk to you. [05:10:07] capitalize each word. If I unselect it again, you will get the origin the last past data back. So, this is my home tab. You can either move to transform to do the changes, or in home tab only, you [05:10:22] the changes, or in home tab only, you can select the any of these column and right click. You can see this transform data option the transform option here, where you can The These are the basic transformation. [05:10:35] Converting to lower case, converting to upper case, capitalizing the word. Trim Trim function is used to remove the white spaces in that cell of each I [05:10:47] mean, in that column of each cell. Length is to check the length. Uh for example, male is four letters. Wherever it is male, it is four. Wherever it is female, it is six. If I go with the length function, it will give me the [05:11:01] total count of elements. You can see in the cell it was four, in this cell it is the cell it was four, in this cell it is six. But, this data is incorrect. four and six? So, this is not a good transformation. [05:11:15] Select again, come to transform. JSON file, XML file. It's uh If I convert to file, XML file. It's uh If I convert to JSON, it's a writing the data in terms of a key-value pair. So, it will throw you an error. There is no use of going [05:11:29] with XML and JSON. Usually, they go with this uh Select Select the entire column. Come to transform. Either we go with lower maximum. And then, you already know removing the [05:11:44] replacing the errors, replacing the duplicates. So, let us concentrate on what is this transform data. I will come choose this diagnosis column. I need to transform the data. [05:11:57] So, these are the basic transformations what I have. If you want to do that Sorry, uh this is the basic. I will go to this data. You can see Here it is a dash format. Here it is I mean, here it is normal dash, here it is backslash, [05:12:11] again dash, again backslash. If you have these kind of data, it will create a mess when you create a data visualization. You need to do the changes. So, especially, this transformation [05:12:26] So, especially, this transformation option is for date and time data type. Here you only have date. Sometimes you have a combination of date and time together. That is called as date time data type. [05:12:38] For now it is a date column and you can see the date option here. You need to right click. Sorry, select everything, right click, come to this change type. See, right now it is text. [05:12:50] You can also see it is ABC. It is not a date time data type. If it calendar symbol. Because of this multiple options like backslash, dash, what we have, it is not getting changed. [05:13:06] getting changed. See? So, what else we can do? Select this. Directly try to change it first to date. For some it is changed, but for some it you can see it is showing as error. [05:13:23] It is not directly getting converted. It is not getting changed properly. I will is not getting changed properly. I will remove this. uh date, month, and year. Here also it is date, month, and year. Here it is uh [05:13:38] month, date, and year. You need to select wherever there is backslash. These are the data. Either you do it you will not be able to do any changes manually to it. [05:13:53] You need to select it. You need to come here. You need to replace it. [05:14:05] it with a dash. But this is not doing the changes. everything. And you can also we filter the rows here. Let us select everything now. [05:14:24] dash format. You don't see any backslash, but this is incorrect data. The reason is here it was and the month format are different. I will remove this. Now you can see the actual data. First [05:14:39] you need to do the data transformation by creating a new column. You need to take that data type into a date time data type column. You need to do it with respect to a query. Sometimes with query also it is not [05:14:55] possible. We need to go back and rework on the database data. But it is possible for us to do the changes here. So with this add column option based on the condition you can add the column or you already know how [05:15:09] to duplicate the column or index column also we have it. So you can see custom I mean if you want to add the data and on your own by creating a column, this is the option. The first thing is you can give a column [05:15:24] name to it. I will give a column name A B C D. And if I mean this is to concentrate on the other columns because you already have the data. Based on this data if you are [05:15:37] creating this column, then you can write a query here. You can write a condition because I selected conditional column. If patient ID If patient ID equals to one, then the output will be A [05:15:50] equals to one, then the output will be A B C D E F. here. You need to select the data type here. zero. Okay. [05:16:05] See, only in the you can see the new column is created. It's a condition based column. I my condition was if patient ID is one, then fill this with A B C D E F, else fill it with zeros. This condition is [05:16:19] is one. So, this everything is zero. This is So, this everything is zero. This is another example of data transformation by adding a new column based on certain conditions. [05:16:34] not appearing here. So, this is better to go and I will show you the other method by writing a query. So, I'm just removing it. So, let us concentrate on the other [05:16:50] basics, but before that, you all I hope you all understood what is data column transformation. So, if you want to do any changes on the data type for each column, maybe a quantity data, numerical data, [05:17:05] categorical data, we go ahead with that. So, why collecting the data is important especially when we are concentrating on the data quality and also when you create a dashboard to build a relationship between the data, to to see [05:17:20] a complete picture on the dashboard, data cleaning, data formatting is very important. So, two key checks for ensuring the data quality, you already know that distinct and unique, it may be quite confusing, [05:17:34] but if you work on multiple times, it will be very easy. Data type validation, ensure that the values are of the correct data type. Especially the numerical data and the text data. [05:17:47] Range validation, ensure that value falls within the expected range like to concentrate on the outliers. If you have any duplicates, make sure you select all the columns and then you remove the duplicates. [05:18:00] If not, you know the changes like John, it's a different name, but the person is also suffering from the same disease. If you concentrate only on the disease, the other person data will be deleted like rows will be deleted. So, we need to [05:18:14] the columns and then we need to address the issue. That's the scenario. A retail data set contains a customer information with inconsistent email address including typos and different formats. [05:18:29] So, you can see here it usually all the mail IDs will be lowercase. Uh but you can see here you know every year it is A B C D is [05:18:41] A B C D is A B C D is A B C D is A B Identify the inconsistency in the data. doing. Convert to all email address to common formats. Lowercase and no spaces. [05:18:58] For duplicate email address with a different customer information, manually different customer information, manually investigate and we need to resolve. See, first thing is directly sometimes it is not good to apply the changes. [05:19:10] Take a copy of that column. That original column will be there. Create a new column, not an issue. To that new column, I mean, the copied column, apply So, whatever the changes you are applying, you know, for you to again go [05:19:25] back and to check whether the changes what is taken care by the Power BI is right or not, make sure you retain the original data. practice. Copy the column always which you want to [05:19:40] do the changes and then apply the changes on that column. Later, if you don't want that, you know, the previous the old column, you can delete it later. But before you cross verify, don't delete it. You will not get again the [05:19:53] original data. It will be in Excel. Again, you need to import the Excel not get. You need to do from step one, I can say. So, sometimes manually we need to investigate and we need to resolve the [05:20:07] issue. That's the case which happened with that date and data. outliers, impute the missing values, correct the error correct the error. [05:20:21] Sometimes we can automatically correct the error with the option what we have. Sometimes we need to do it manually. That is the error which usually comes Handling the null values, you know, you can also delete the null values or you [05:20:37] also know how to select the row, how to select the column and you can I mean you can remove it. But do remember when you are removing, it will affect the other are removing, it will affect the other data or the entire data set sometimes. [05:20:54] common techniques which we choose. One is identifying the missing values, checking the duplicates, checking the data type date I mean checking the data type for all the different data, data privacy or data governance, [05:21:08] distribution, data quality, the multiple tick options what we have in that Power BI. All these are the options what you have All these are the options what you have in Power Query to ensure you have a very [05:21:21] good data. Hope it's clear. interview question. What errors we will come across as soon [05:21:37] as we import the data? What errors this Power BI usually faces some difficulties to tackle? One is again, if you change the file location the you have your laptop what you are using. If your file locations if [05:21:53] you are changing the file locations, data source will be affected. >> If the data type column is incorrect in the data source, that is what we are facing now. The date time data type column [05:22:08] itself in the data source is incorrect. So, the same incorrect is happening here. We need to go with the advanced technique now to do the changes. technique now to do the changes. Date type mismatch. Data type mismatch. [05:22:21] Data source connection issue. Again, when the data source is incorrect, data path is incorrect. Query syntax error. When the query what we are writing is incorrect. It will highlight us. It will show. If you [05:22:35] remember error error error, it was showing during the power query when I did the changes. That's a query syntax error because it was a different data type. Data volume or complexity. When the data [05:22:48] is huge, to be honest, sometimes Power BI hangs. It hang at I mean, you will you know, it will take more time to recover. Data source specific issues. Again, the data source path, permission issues, or [05:23:03] uh if you want to connect the database directly to this Power BI, if there is any back end the data issues, it will affect your dashboard. These are the errors with respect to data import. You can expect this as [05:23:16] interview questions or maybe in MCQs. What are the possible data import What are the possible data import errors? null values. That is why I know that it will take some time because today's [05:23:31] topic is huge. So, I already before only I made you to import the data, the health care data. I showed you how to replace the null values, how to handle the null values, how to remove the duplicates for the good data analysis. [05:23:44] So, transforming and loading the data, you already know different data types we have in Power BI. I'm giving you all this information because this is bit theory only. Again, we will uh use the same uh you know, data set, not the [05:23:58] other one. I mean, I'll use the the same one also. And we have another data set which we wanted to use today. Due to time uh issues, it was not possible to complete it. These are some basics. I'll give you the information now which you [05:24:13] already know also. Uh and then today's another project we will be doing tomorrow also. And tomorrow's also we need to do it. Text data type, numerical data type, date time data type, Boolean data type. [05:24:27] All these are the important data types and this will be automatically uh by this Power BI uh you know, the dashboard what you are creating. Power BI will recognize it. How Power BI is recognizing? Based on the data. If you [05:24:43] have 10,000 records, it will check the 100 records out of 10,000 and it will consider what is the data type of that column. In this case, it was only five, consider only five, six rows. If you have 1,000 rows, not all 1,000 rows it [05:25:00] will go and check. If data is properly uh if the data quality is good, then yes, automatically it can check. If data is data quality is not good, then it will check step-by-step like it will check the top [05:25:13] 100 rows or 1,000 rows, sorry, top 1,000 rows and it will identify what is the That one thing you need to know. It is 1,000 rows. Using the correct data type to improve the performance, to prevent the error, [05:25:27] and also to enhance the quality of the report, we concentrate on the data type of each column. The column data type to identify is very [05:25:39] important. Whenever you see text like ABC, it will Whenever you see text like ABC, it will be uh again uh alphanumeric, I can say. Alphabets, numerical together, or mail ID, special characters, everything [05:25:52] together. Please remember these are the different examples. What are the applications of ABC or text? Names, addresses, descriptions, textual content, codes, and identifiers, free form of text. [05:26:05] It can be a word, a sentence, or I can say an entire paragraph. Combination of alphanumeric. Whenever it is a numerical data, either it can be a positive number or it can be a negative number. It is considered as a numerical [05:26:21] data. It can be again integer data type or a floating value data type. It is or a floating value data type. It is taken care the by this Power BI desktop. This a calendar icon indicates a date time data type. You can see the use [05:26:35] cases for doing time series analysis, for tracking, for scheduling, planning, analysis purpose, we go with this date time data type. [05:26:50] no, true or false, on off. Like when you have two different categories, which usually indicates zero and one. Then it is called as a Boolean data type. If you know Python, then this a [05:27:02] data type is almost the similar. Best practices is to match the data type of the column, to check the data profile, to consider the data again, to performance, we check each and every column. Avoid unnecessary [05:27:19] uh on the conversion of See, sometimes it is not needed to do the data transformation. Then don't do that. Only in necessary stages do the changes. Do the conversions. [05:27:32] And again, sometimes don't do exactly on the same column. Copy that column. Do the changes in the new column. Let the original data column will be there as it is. In future, if you want, you can remove it. Not an issue. [05:27:47] Benefits of correcting the data for accurate calculations, for the very data visualization. We concentrate on correcting all the errors before creating the dashboard. [05:28:02] So, this is where you can see one-to-one connection or one-to-many connection. This we completed in the last call, also called as cardinality. [05:28:16] So, one-to-one connection is where one column is connect is connecting to the other data set, the same the same column. So, there is only one column which is common in both the tables. A single row in one table can relate to [05:28:30] only one single row in the other table. One-to-many, one connection with respect to multiple Sorry, one column is getting connected to multiple columns in the other data set or in the other table. See, multiple data sets, as per Power BI [05:28:46] See, multiple data sets, as per Power BI term, we call that as different tables. Many-to-one connection, many-to-many connection. So, you have so many columns which is common from one table, but in second table, you have only one [05:29:00] Many-to-many connection, multiple rows in one data set can connect to multiple rows in other data set. So, so, five columns, five columns, like the these five columns are getting connected to these five columns. One two data set or [05:29:15] two tables getting connected with multiple column names. That is called as many-to-many connection. So, Power BI supports one-to-one, many-to-one, many-to-one Sorry, one-to-many, many-to-one, and then [05:29:28] many-to-many connection. These are the four types of cardinality what we have. This also you can expect as interview question, maybe in MCQs or different cardinality or what is cardinality, and what are the types. [05:29:50] SQL, again, this will be easy. The reason is SQL is where we will be learning about joins. Uh that we are discussed in the last call like left join, outer join, how to do the data joins we are discussed in the last call. [05:30:04] After learning the joins, we will see after joining two day two tables together or three multiple tables together. We can check the connection between one data set with an other data set. I mean, one table [05:30:18] with another table. That connection building is here called as cardinality. Row level security rules, this is by default interview question. What is RLS, [05:30:30] and what is OLS? Object level security, row level security. Row level security is accessible to all of us because we have a free version. But when you go with the paid version, [05:30:44] picture. OLS can be added in multiple ways. But when you add it with respect to a query, you don't need to go with the paid version, but automatically you can go [05:30:58] with the paid version by linking external tools. I will show you that also, but I sorry, I will explain you that. I will show you how it works, but I can't show you. The reason is we have a free version. We don't have a paid [05:31:12] version. I will show you, but the row level security is important, and this is available to all of us, whether it is paid version or a free version we have this. We will concentrate on this. When I talk about the ONS, object level [05:31:26] I talk about the ONS, object level security, it supports in two different ways. One is by writing a query, the advanced level query, PMDL view, or by connecting to an external source. If you want to do this option, if you [05:31:41] want to opt this OLS option to your dashboard, you need to have a paid version. Because when you are connecting your data set or if you are connecting your dashboard with an external tool, you need to merge external tool to this [05:31:56] Power BI. You need to have a paid version to do that. I will show you that. Uh but row-level security is supporting us. I will explain you what is OLS, but the entire 100% I can't show you because this is a [05:32:10] free version. And this is I don't have you know external tool link. But in my I mean this is my personal laptop, but uh as I work in a company where I will be using a paid version, that option I have [05:32:25] there. I have used it. I will be using and I will be working on that. I will show you there's a slight changes which is missing in Power BI paid version, sorry, free version. Nothing to worry about it because when you know row-level [05:32:37] security, object-level security is also similar to that. It will be easy. [05:32:51] first week, second week, it was completely related to data sources, how completely related to data sources, how to work with it, data models, and it was all data filtering, data transformation. We were completely into the back end of [05:33:04] I mean, back end of Power BI dashboard. But now we are slowly moving towards the front end part, the dashboard part. So, it will be exciting I can say, and we will concentrate on all this one by one. If you have any questions, let me know [05:33:19] If you have any questions, let me know or else we'll proceed. so I want your responses to understand. >> Yes. Yes, Megna. So, maybe once you you [05:33:33] know, take us through uh in practical, we will be able to security. >> Yes, I will show you that. What is this role level security, how to do it, how to apply on dashboard, I will show you. [05:33:46] to apply on dashboard, I will show you. >> Uh Megna, you mean to say that the that the OLS is for use for that paid level, right? And >> Paid level and also we can use for free version, but we need to write a advanced [05:34:00] level of query to >> Both level are used for both things, no? >> Free See, for paid level, I will explain you. Role level security, we can use it automatically even in paid version [05:34:15] also in free version. Done with that. When I talk about OLS, you can apply When I talk about OLS, you can apply this object level security in two ways. One is by writing a query. You will write a advanced level query and you [05:34:29] will apply that. Another way is we need we can we know we can connect with an external application. I will show you that. So, connecting an external that. So, connecting an external application, you can maintain that OLS. [05:34:43] That is not possible for us because we have a free version. That is only for a paid version. External tool option we will not get it right now. But by writing a query, if you want me to show, that we will be [05:34:55] doing at the end. Because that's an advanced level of query what we write, we need to connect it with an API. That is the reason. it with an API. That is the reason. >> Got it. Thank you. [05:35:10] >> So, let us concentrate. Now, what is this OLS and what is this RLS? Role level security and object level security. Security roles and permissions allow you to control what data individual users or groups can access in [05:35:25] your Power BI report and a dashboard. A simple example here. You You all know about Google Sheets. So, we you you know where we I will create a Google Sheet and I will share it with you. Google Sheet will be [05:35:40] directly linked with my mail ID like a Gmail what I have. I need to give access to people so that they can view my Google Sheet what I shared. If I download it and if I'm sharing with all of you, it will support you because [05:35:56] I downloaded the raw data and I'm sharing it with you, you all can see sharing it with you, you all can see that. But, you can apply the security two ways. One is when you download the file, you can again add a link I mean [05:36:09] file, you can again add a link I mean you can again apply a security link by selecting it is only accessible to few people. It is not accessible to everyone. If you are sharing a Google link Google [05:36:23] Sheet link without downloading, it is by default a security level is added that you need to give permission to people who need to view that. We all come across this scenario. Even Microsoft Excel if you are using, you [05:36:37] are storing something, then you are saving it, you will store it in your laptop, but you can also apply a password on top of it. It's like a security for the data maintenance because it's not all the [05:36:50] data is available for or some data will be of course confidential which should not be shared with everyone. Only for few people you are giving access by giving their mail ID. Here also we have the same scenario where we will apply [05:37:04] the same scenario where we will apply some row level function or maybe a object level function which we are applying on top of our dashboard. If you are preparing a dashboard, you if you are adding on top of dashboard, that [05:37:17] data. So, if I'm restricting for people to see the dashboard or to see the data, so it is again like not everyone can access it. It's like I'm maintaining a proper security on top of on my dashboard. [05:37:34] you need to give permission only to those people whom you want to see that dashboard. Adding a advanced security level here that is OLS. So, RLS and OLS, row-level security, both of [05:37:50] a security role is a named collection of permission that define what data a user or a group can access. We have row-level security. I will show you how to apply all this, but now understand it's all like giving only [05:38:06] some data to people who can view your complete data to see. You can give them complete data to see if it is good to see, but if you want to share only few amount of data, you can [05:38:20] apply a security on top of it. That is called as row-level security. Where I'm adding a I mean, I'm creating a restriction here. I'm creating a path. I'm adding a particular I'm giving only some access [05:38:35] not everyone can see. It's a crucial component for implementing a data security and ensuring that users can only see the data relevant to their roles and [05:38:48] Now, I will show you how you can go with this RLS. You need to go to go and select this modeling. You will see this manage roles. [05:39:01] Select it. This is your security option. Now comes your manage security roles. You are creating a role now. Create new security roles and use filters to define row-level data restrictions. [05:39:17] restrictions. Okay, you give a name to it. This is the name what I'm giving. Uh the data set what I'm using as you [05:39:29] can see this is pizza sales data. This is what you can see here and you can tap on selecting this new and now you need to add your add the condition. You will be able to see all the columns of your data [05:39:42] So on what exactly you want to apply the restriction? Based on certain amount of data I want uh okay before that if you want me to explain this data set See this data is a pizza sales data. [05:39:57] Okay. First I will explain and then I will come so that you can understand it. So this is a pizza sales data. I can see the pizza revenue. I can see the amount of pizzas we sold like the total quantity of pizza what we sold and what [05:40:12] quantity of pizza what we sold and what are the different pizza ingredients. ingredients we have and uh you can see here month-on-month how many [05:40:25] pizzas we sold, what is the pizza category, what is the pizza size. So this is the data. It's completely related to pizza sales. We will do a project. Uh so this is the overall revenue. These [05:40:38] are the based on different pizza sizes. This is the see if I select large you will you will be able to see the complete records for large. So this is the you know pizza sales based on the size. [05:40:52] This is if you want to check maximum quantity count of pizza. This is based quantity count of pizza. This is based on the pizza category. categories and this is you can see here this is I have sorted it. You can see [05:41:08] the top five pizzas which we sold. This is month on month what's the sales month on month sales to check. [05:41:26] but in a different way I have created. This is the total orders what we received in the year 2025, sorry 2015 and this is the number of pizza types. I will show you how to create this dashboard later. [05:41:39] This is the pizza quantity, pizza revenue, month on month sales, different category analysis, pizza ingredients and the uh you know based on the quantity I have sorted the [05:41:51] pizza names. So now you want to restrict people to view all the records. You don't want to share all the complete analysis or the share all the complete analysis or the data to others. [05:42:07] you can clearly come and you can manage this. Now I can remove this. Uh RLS one. Sorry, let it be RLS [05:42:21] Sorry, let it be RLS one. you are select if you don't select this no this is your data set also called as data table or table. You need to select it and then come here, go with the new [05:42:35] option. So I need to add a filter now. Now I will give you option so you can only select what exactly you want me to apply So Yes. Can I get any response? So how you [05:42:50] want me to add a restriction? A security we are adding on top of our A security we are adding on top of our data. right? So how you want me to restrict [05:43:03] right? So how you want me to restrict this data? >> Total price. Okay, so if I select this total price, see total price is nothing but this is a column where you can see different pizza and again a different [05:43:20] think. See, this is the total revenue See, this is the total revenue generated. Total price column, if I go in terms of data set, each and every row we have a different order details. Total [05:43:33] price is a column which is nothing but giving me the details of all the orders. Wait, I will show you that. See, this is your total price column. See, order order ID 2, a person is ordering one pizza. It's a Thai chicken [05:43:48] pizza. It belongs to chicken category and this is the total price. If you ask me to add a filter on top of it, like if I want to restrict people seeing from 20.75, then all the pizzas which we sold under [05:44:02] 20.75 will be restricted. So, to add a security also we need to understand the data structure. You It is good to add always the security on top of categorical data. You can restrict the data easily. [05:44:17] Numerical data, if you are adding it, you can add it. I'm not telling no, but it will affect the data set. It will not give a good result at the end. So, always we need to concentrate on the [05:44:31] data set and accordingly we need to add a filter on top of it. So, let me go let me just open this manage roles. manage roles. Just a second. [05:45:06] also as you mentioned, pizza category. Pizza category, I have chicken, I have classic. I will select classic now. Okay, so this is I'm saving it now. Show [05:45:18] data if all the rules are true or any of the rules are true. I'm just adding one level of condition now. I will save this. So, what I'm doing now, I'm restricting this data for others I'm just restricting now. I'm saving it. [05:45:40] You can close this. Again, I will show you. You can come here. See this RLS. If you want to duplicate or if you want to delete, you can do that. And this you can see again another rule you can also create. [05:45:54] Now, let us open this. This is the dashboard what I have. dashboard what I have. I will just refresh this. [05:46:06] I have not signed in. When I don't sign in, I can't give this dashboard access to people's mail ID. Okay. Now, if I select classic, you can see the classic level data is coming up here. But I'm restricting this classic data only. [05:46:22] In this manage rule option, I'm restricting this classic data only. So, coming here all the data then pizza category, then uh [05:46:37] coming to this pizza size. So, the end. All the data should be selected. All the data should be selected. I'm saving it. [05:47:15] delete this. It's not getting applied. Let me see. [05:48:23] uh the data only average pizza size which is equal to N because I only selected after applying a condition only related to N. And now this view option what you have, no. Now you can select it and you can give only this data access [05:48:40] to people. Other user. People need to mention their ID and then then then then they can use. As this dashboard belongs to me, so I can go ahead with all this because [05:48:53] untitled is what I created. I will show you again. I can see some messages. You can add pizza category should we be going to see that in new app? Yes, of course. I will show you one by one again. I will rename this. I will first [05:49:08] again. I will rename this. I will first delete it again. Again, see this is the overall data set. Come to this manage role. Uh give a [05:49:21] title to it. I will rename this. Okay, so now what I'm doing is I'm giving only access to some data. Like for example, you work for a sales team. I'll give you only sales related dashboard access. Or in this dashboard wherever there is a [05:49:38] sales related graphs, I will only give you access for the same. Now, you work for the team called as pizza category your lunch. You're only concentrating on the large pizza category. So, I will restrict you to see other data. I want [05:49:53] you only to concentrate on this large data. Okay, so based on this I'm applying the condition now. So, this is I'm renaming. So, you Here you need to come to model and then you need to select new role and [05:50:07] then I'm giving a name that is RLS one and this is large pizza. I will write L. Select this. Okay. So, here what I want to select for you I want you to see only the pizza cat [05:50:22] size. Should be equal to L. That's it. You can also switch to DAX editor and you can see the condition which is in you can see the condition which is in terms of query. [05:50:41] you know, this as I'm the owner, I can see the complete dashboard because I created it. So, now this is the version what I'm using is a free version. Uh whenever I have a paid version, see, I need I can give their mail ID, and I [05:50:56] can only give them access to view the dashboard automatically. As I have a free version, I can't give this dashboard access to any of you. need to download, and then I need to share it. [05:51:12] not work for you. It will only work for me because I'm the owner. So, that's the restriction what we have with paid version and free version. Not an issue version and free version. Not an issue when you work in free paid version, you [05:51:26] need to give their mail ID here. That means you are giving access to others. Now, imagine that I'm other person. I created a different role for that person, and I'm giving access to them. I'm just selecting RLS, select okay. [05:51:43] Now, you can see the entire dashboard where the dashboard is restricted to large pizza. This is pizza size, which is large. 18,956 pizzas got sold. In this, this is the revenue generated from large pizza. [05:51:57] Again, same 18,956. It's the same data count. You can see, you can cross-verify. And this is large pizza sold out in different categories. It can be veggie, or it can be supreme, or it can be classic. And this is again [05:52:12] month-on-month sales of large pizza. And come to page two. This is also now the entire page, the complete dashboard is restricted now to large pizza. [05:52:28] uh the you know, the role what they have given to me. this is month-on-month sales. This is the top pizza which we sold, which belongs to large category. Total pizzas we sold, this is 19. Actual figure is [05:52:42] 18,956. This is usually rounded up. So, what are the different type of pizzas we have when it comes to large? 30 different types we have. So, totally we have uh received 12,000 plus orders, [05:52:56] like almost 13,000 orders for this pizza. This is month-on-month revenue generated. This is the total revenue. This is the entire dashboard analysis. So, when you give a particular role, so by selecting it here, you will write [05:53:12] their mail ID and then you will give access. As I'm the owner, I don't have user, I need to mention their mail ID here. But, for me it's a paid version, sorry, free version, so I can't do that. But, [05:53:25] if you have a paid version, you will have that access. Yes, please. [05:53:40] speaking. >> Can you Can you hear me now? >> Uh yes, yes. >> So, the example that you have shown here >> So, the example that you have shown here is the condition what we want others to [05:53:52] to see. >> Yes. You can You are restricting the data by giving access only to few records, only [05:54:04] to few data. >> Okay, so row row level security is nothing but what we want others to see. >> See, it's not See, if you give access to people what you want others to see, by default the other data is restricted. [05:54:17] Whether you restrict the data from people or when you give access only to few records, the meaning is same. Don't get confused. You are restricting the data by giving access only to few records. [05:54:30] That is called as data security. We are doing it in multiple ways. This is one such way that is row level security, which we call. For object level security, if you have a paid version, you will see external tool [05:54:44] option, and other option here called as external tool. Just a second. I will remove and I and I will put it again. So, I will remove it and I will put again. [05:54:56] I will get the original data now. So, whenever you are adding row, make sure you add it at a time. So, now, as you said, I'm just giving said, I'm just giving L and M option. Okay. [05:55:11] condition should be satisfied. All means all the rules should be satisfied. For me, as you said, pizza size should be equal to M, and the other one is pizza size should be equal to L. You want people only to see M and L together. You [05:55:28] can see the condition here, M and also L. The both the condition we need to check. Save it. Done. Come here. Now, you gave access to people only to check L and M. [05:55:51] second. All the rules are true. Equal pizza size pizza size equals pizza size is L and M only. size is L and M only. Pizza size is L and M. [05:56:20] All the conditions should be true. Okay, L and M are not in the same cell. L and M are two different categories. So, I need to select the any only. So, now you can see L and M. With this L and M, this is the revenue generated and these are [05:56:34] the top pizzas you can see. And this is the pizza category and this is month-on-month sales. Here you can see the numbers. Because I have multiple conditions, so I need to select here any. [05:56:47] need to select here any. All Why I should not go with all is M is a different category, L is a different category. They are not together. If I want to go with all, it's like it's like you need As I'm selecting the same [05:56:59] sizes here, it's the same column. L and M are not together. If it was a different column, then you can change this. As it's the same column, L is a different, M is different. It's in a different cell. Try to imagine Excel [05:57:12] now. When you apply any condition, I mean, if I consider any, then if I just come to this row level, See, now it is any. Any means either the category can be M or that column can be [05:57:27] L because I have choosing the same column for both the condition. It should be or operator every time. Either M or L. If you know and or conditions, and condition you need to select when you want both the conditions [05:57:42] to be satisfied. Or you you need to select whether any one of the condition is to satisfy. Here any and all are the two options. Any of these rules are true or all of these rules are true. In this case, as [05:57:57] I'm going with the same columns, I want to see both the categories, that is L see. This is or operator in Python also and in Power BI also. In I mean, when I go with all, [05:58:12] any is nothing but or operator. And in the sense, I'm going with a different column. I mean, sorry, I'm going with the same column and it's two different the same column and it's two different conditions, M also and L also. [05:58:26] But when you go with or operator, it will give you both the data because you want to see both the data. Or operator is considered to check multiple records multiple conditions. I mean, I have two different conditions [05:58:41] which I'm applying. See, two different conditions I have. I just don't have only one condition. I have multiple rows here. I mean, multiple levels here and I want I want to see only the data which where I'm applying these conditions. [05:58:55] where I'm applying these conditions. I can add another one. And I can also select here the pizza one second. Quantity should be two. That means I want pizza size to be M. I want to see [05:59:10] pizza size where it is L. And I also want the quantity of the pizza where I want the quantity of the pizza where I sold this M and L should be two two you can see [05:59:23] I'm not able to see anything. Because I want to see the category M and I also want to see the category L and I also want to see where a customer is also want to see where a customer is ordering minimum quantity of two. [05:59:37] ordering minimum quantity of two. I want now I applied all conditions. It I want now I applied all conditions. It can be medium or it can be with the quantity of two. When I consider medium [05:59:49] a medium pizza you can sell quantity one or quantity two or quantity three or quantity four. Because it's an or operator. To talk about quantity now, it's like it is medium only but medium because two [06:00:03] conditions. One is medium and the other one is two. Medium only but this medium you can sell one, two, three pizzas. Or this quantity two what you have, it can be a medium pizza or it can be a small pizza or it can be a [06:00:17] extra large. So both the conditions are filtered here and you're getting 16,000 rows out of overall 48,000. This data is having overall 48,000. This data is having 48,000 rows. 48,000 records we have. So, [06:00:30] we are restricting it here. Either it can be two or it can be M. So, we have so many options. will be applied to all the pages and it will also restrict the data source. You can't see all you know, all 48,000 rows. [06:00:45] You Now, you can only see 234 rows. So, it when you are giving access to people based on this roles, you know, not only to dashboard, but data source is also getting restricted. That is the main purpose of [06:00:58] restricted. That is the main purpose of using this. additional you will be able to see external tools, but that I don't have external tools, but that I don't have here because this is the free version. [06:01:15] show you the data. This is the column. Now, you are applying a condition on this column. You need to first see this data, how this data is because this is numerical data. You can see 35, 40. These are nothing but the pizza size. [06:01:27] Order ID two, a person is ordering this pizza. This is the total cost. Hope you all are clear with this data now. This is unit price. Pizza for one. Total price is unit price into quantity will be total price. This is the data [06:01:41] will be total price. This is the data what we have. Now, I will come here. what we have. Now, I will come here. Okay. [06:01:53] I will give the name as price only. I will select it. Come here. So, okay, total pizza. Total cost I mean, I'm applying to that particular column, I'm applying a condition now. So, equals to [06:02:07] applying a condition now. So, equals to 20. Sorry, 20. Now, you will only see you know, uh the cost of the pizza is 20. [06:02:22] Close it. Now, uh come here. Price. [06:02:37] exact value, which is 20. So, it is restricting me. equals, because it was a floating value what I was having, instead of equals, I will go with greater than. Greater than 20. [06:02:53] Now, you can see the data. You can come here. You can see all the cells are having greater than 20 only. Less than 20, everything is restricted. [06:03:06] by filtering, you're giving access to people to see the data properly. I will show you. See, this is the condition. Total price greater than 20, and it is in. Sorry, where it is? [06:03:26] In. Close. one column it is getting selected. So, even though if I go with any, it is not getting changed. It is only on. And I don't have another option, but [06:03:41] wait, I will show you. I'm just going with another condition. You know, pizza size should be equal to M. Now, you can go with in. So, greater than 20, or it can be [06:03:55] operator. So, pizza I'm sorry. So, pizza size should be M. Oh, sorry. Pizza size is M, or it can be S, or it can be large, or it can be [06:04:10] medium, whatever we have. Great pizza column, the price total price column can be should be greater than 20. I'm just saving this. Now you see this data come here. [06:04:23] See, pizza size not only M I have, I have L, M, S, XL, double XL. When I talk about total price here, I also have uh the data like greater than 20. I also [06:04:35] have some data which is lesser, but I don't have overall 48,000 data because there is a condition applied. I can only see 30,000 records now. Both the conditions are satisfied. See, when I consider this row, it is greater [06:04:49] than 20. When I consider if for example, I will consider this 11 and 12. You can see this 11 and 12. And here the pizza size is yes because you considered all here. [06:05:02] And if I just go with 17, 18, 20. See, you considered Oh, sorry. What if it's 16? [06:05:14] See, this 16 is coming even though it is less than uh 20. Sorry, greater than 20. Sorry, it is less than 20, but the condition is greater than 20. As the pi- pizza size is medium, I'm getting this data also. [06:05:29] 17,909 rows I can see. is total price. Total price is greater than 20 or pizza ca- pizza size should [06:05:42] Here, total price even though it is less than 20, but the pizza size is M. So, it is displaying you this data because I'm going with or operator. Or operator means any one of the conditions should be satisfied. Either M or greater than [06:05:57] 20. So, here I can see M is satisfied or the total Sorry, I'm applying to unit price. Just a second. [06:06:09] I have not selected the data properly. I had applied a filter. See, please be applying these conditions, you know, when any of the filters are active, it will affect the data. Even though your condition, even though your role is [06:06:23] correct, it will affect the data. So, it was affecting me there. So, now I will go with this. And as you said, you want me to go with the total price you want me to go with the total price which is uh greater than 20. [06:06:37] And another condition, that is I want the pizza size should be equal to a the pizza size should be equal to a large. Come here. Go with this untitled. And now you can [06:06:52] see this data. You can come here and you can see uh pizza size is only large and this total price column is greater than 20. All is like and operator. Any is or operator. [06:07:15] Uh I'm not getting here to add uh you know, the mail ID because mine is free version. But when you go with paid version, you will get a definitely an option to add a particular mail ID so people can only see this data. [06:07:35] So, that's how it works. If you want to go with two restrictions, then two different roles should be created as per the technical term. So, I think you are mentioning these two, right? These are called as rules. [06:07:48] Rules. Two rules, sorry, two rules under one condition that is untitled. I mean, you can just rename it, not an issue. This is your role. This is called as This is your role. This is called as roles. One role can contain multiple [06:08:02] roles. One role can contain multiple rules or one rule. conditions should be satisfied. It's like either it can be this or it can be [06:08:14] this, give me the data. That's how it is. This is or See, uh and or not are the logical operators what we have. And operator means whenever you go [06:08:26] with multiple Usually, and or operators will be added more than two conditions only. So, if you want all the conditions should be satisfied, then you need to go with or operator. You want total price column should be [06:08:40] greater than 20, and also you want, you know, the size of the pizza should be L. Or operator means any of the conditions are satisfied here. I will show you this are satisfied here. I will show you this again. [06:08:55] want to go with and operator if only these two conditions should be satisfied. If any of the conditions if you want, then you can go with or Okay. Got it. >> I will write in the chat. It It This may [06:09:08] be confusing. Uh uh especially if you're new to this. And uh you know, the condition If you have two conditions, and operator concentrates on both the conditions should be satisfied. [06:09:27] need to add additionally two new columns. And in this data set because I don't have a user uh I need to have employees' name. I need to add their main uh What is their role? So, for example, there are so many team [06:09:42] members. There are so sales data and other things. A person is responsible for two different teams. That mean That means in that column, the person name will also repeat multiple times. And then what I will do, I will come here to [06:09:55] this uh manage role, and then will give a person I will select the particular call Sorry, I will select a [06:10:08] particular column here, where I have the employee name, and where I have uh the So, two conditions are applied, and then I will be giving him access. I need to create a calculated column. So, that is only possible like you need to create a [06:10:23] column when you have a flight data. Now, on top of this pizza sales data, sorry. On top of this pizza sales data, I will not be able to create any new column. I can create a column based on this data [06:10:38] only. So, this will create a new measure here. And then, for example, I want to check uh the count of uh pizza [06:10:50] pizza count of [06:11:02] So, you can see this measure. I need to rename this. R1 I will write this is count of pizza So, pizza count. I think I already have some data [06:11:16] count. I think I already have some data here. Not an issue. This is saved now. So, this is a measure. This is not a separate column, because I don't have a supportive data for me to create a new column with this pizza sales data. [06:11:28] Because it's a very simple data, I cannot create any new column out of this. I think with this I With this I can create a new column. I can split 8 October and 2015. [06:11:40] See, if you want to create a new column, you need to select this new column option. And what exactly you want to add in this new column, you need to write here. Okay, for example, I want to add I want [06:11:52] split here. I think date. meaningful one. This column I want to represent [06:12:11] is supported for us to create uh these different columns. So, that one thing also we need to remember. And then now what I will do I will just go and uh for me the total price column, I need to [06:12:27] for me the total price column, I need to select Look, I need to select here the subtract, right? So, the difference. Okay, I will select the this two. [06:12:45] Example total price minus [06:13:01] Okay. So, now this is the profit. Okay, I will change the formula again. It's not 0.1, I will go with 0.30. [06:13:15] 10 50%. Let's go with 50% now. So, this column is talking about uh this is the profit. This is the total price what you sold to customer. But this is [06:13:27] you're adding a formula to it and you're checking what is the profit from these uh these different fields. And this is only for untitled, the condition is restricted here. Now, uh let us move towards this modeling. Come to this [06:13:42] towards this modeling. Come to this manage roles. So, like this you need to create a new column and then you can also add it. [06:13:55] So, you want to see whatever the profit is greater than 10. See, all these three conditions are applied now and this is any. All if you want to go, you can see everything is and operator. [06:14:11] So. Now, everywhere you can see blank. Uh I would I think I did it. One second, wait. column, no, make sure you don't create on top of this row. I think I created on [06:14:24] top of this row. I will delete this and I will create again. Okay. So, now you can see this is the actual column. Now, I will come here, [06:14:39] manage row. Now, you whenever you create a new column, make sure you don't apply on top of it because I did that I did that mistake here. So, now I'm just clearing it. I will again add now like P1. I will give a name P1. [06:14:53] I This is the calculated column that is profit. Profit should be uh you want to see the profit which is whatever it is greater than seven. greater than seven. And uh I will go and select [06:15:07] uh the total price should be again greater than 10. That means total price is greater than 10, profit is greater than seven. And then you can also go with uh pizza [06:15:20] And then you can also go with uh pizza category, which is equal to So, now small pizza, the total price greater than 10, profit greater than Come here. See, this is your and operator. And operator means all the [06:15:36] conditions uh we need to I mean, all the conditions should be satisfied right conditions should be satisfied right now. And then I will save this. now. And then I will save this. Close it. [06:15:56] small and the profit greater than seven and we don't have uh uh you know, the condition here. So, now I will just change it. all to any. Any of the conditions should be [06:16:09] satisfied. Save it. Save it. Close. Now, you can see the data. T1, that means please be very careful. Whenever you are checking as in a view, [06:16:23] don't apply any conditions or don't apply or don't modify the data. It will not affect automatic It will not affect the original data. So, come out of it So, now you can see the restrictions. You can see pizza size here and the [06:16:40] total price here and the profit here. [06:16:55] you a few more ways of how to do how to create new columns. You already know about how to manage it and there is some restrictions also [06:17:09] because it is completely related to mail ID to whom you're giving. For now, I will delete it because it's as I have a free free version, it will affect my graph. So, I'm deleting all that and now you can see the complete overall data [06:17:22] set. Okay. I'm opening the fresh page here as you can see. I will remove this. Page number four is my working page now. Okay. This is the data. I also have the [06:17:35] calculated field here that is profit. That's a that the column which I created. Okay? So, I'm resharing the file again. I think someone asked me again to reshare. I'm sharing the file. This file [06:17:49] is what I'm using. This is a CSV file. Okay? Maybe CSV, yeah, it's a CSV file. Even though it is named as Excel, it's a CSV file. So, please pick that. Done. File will be there with the next few [06:18:02] Okay, yeah, it will be there with the next few minutes or seconds. This is the data. These are the different visuals. This is your report view on which you will be creating the dashboard. This is your home page. This is two [06:18:16] calculations you already know. This is to import the data. This is to transform the data. To talk about insert options, see, this these insert options will ask you if you want to add new inputs or if you want to [06:18:30] add you know, buttons, images, shapes. This is all completely based on your creativity. Coming to model view here, this is again to do the new calculations and this is to change the color code, color themes. [06:18:45] This is to optimize. This you already know. As the new data becomes available, if you come here and if you refresh it, the new data will be added. This we have already discussed I think day one or sorry, day two or day three maybe we [06:18:58] Now, let us these are the different visuals. Uh all the first of completely is filled with a bar graph. Clustered bar graph, stacked bar graph. This is stacked bar graph. This is your clustered graph. [06:19:12] This is your bar graph. Either you can go row wise or you can go column wise, not an issue. This is your line chart, area chart and this is your dual chart, graph. [06:19:26] This is waterfall chart related to project management. Funnel chart. Scatter plot. Uh statistical graph. This is pie chart, donut chart, tree map, map, [06:19:41] card, card, this is old card, this is your but this is slicer and this is your list slicer, this is one second, there is a new card here, this is your this is new one, okay. [06:19:54] Sorry, this is new card and this is old card and this is multi-row card. These are the new slicers what we have, this is your old old slicer. Wherever you can see this highlighted in orange color, [06:20:08] I mean this highlighted one is nothing but it shows it's a newly added a graph, but it shows it's a newly added a graph, earlier these visions it was not there. Okay, now this is the complete dashboard what [06:20:21] We will create you know using these visions we will create a dashboard now. So this is your bar graph, this is your line chart. This is your donut chart, similar to donut chart we have pie chart. This is your scatter plot, this [06:20:36] is how your area chart looks, this is your bubble chart, this bubble chart you will find in Tableau automatically but we don't have a direct bubble chart in Power BI. Heat map, again we don't have this in [06:20:49] Power BI but we have it with respect to Python code, we also have it in Tableau. This is histogram, this again we don't have in Tableau or we don't even have this in Power BI. But we can do some modification to convert the existing bar [06:21:03] but histogram is not available in pie chart, sorry, in Power BI or in Tableau. and this is your radar chart, this again we don't have it here, this is not technically used maximum. This is your whisker box plot, one of [06:21:18] the important graph, this automatic graph we don't have in Power BI. This is stacked bar chart, this is your waterfall chart, this is net chart. This is your funnel chart. So, let us see the charts whatever we [06:21:32] have. Yes, I have sent the same pizza sales data. Someone asked me to share the data set again. So, I sent the same data. If whoever not downloaded it, please download it. [06:21:52] which is bar chart. So, by looking into this bar chart, you should be in a position to understand why we are using this bar chart. The main purpose of using this bar chart is to showcase the comparison between [06:22:07] different bars. So, to create this bar graph, you should have a categorical data and a quantitative data. X-axis categories, Y-axis numerical value. Categorical and a quantitative data combination is this [06:22:21] bar graph. This is used to show the comparison between different categories. You can see there are four I mean we are we can see four bars here. Orange bar is [06:22:33] highest with highest value. And the green is lowest. The orange one highest value what we have, it clearly indicates that category having maximum sales. Imagine this is sales versus region. Orange region is with maximum sales. [06:22:49] Green region is with very less sales. It's very simple. But, we need to be very careful depending on the data what you have, you need to choose this bar graph. Categorical and a quantitative data you [06:23:04] need to have. Use a bar chart to show the comparison between different the comparison between different categories. Bar chart display the data using a rectangular bars with the bar length representing the value. [06:23:17] Avoid using the bar graph because when you have so many categories. See in this example, in this graph, you can clearly see four graph four different bars. see four graph four different bars. Minimum of two bars, maximum of 20 bars [06:23:31] Minimum of two bars, maximum of 20 bars you can add depending on your dashboard. you can add depending on your dashboard. If you go more than 20, not everything will fit within this range. That is one drawback. Minimum of two [06:23:43] actually it is maximum of 15, but somehow depending on the data size or the dashboard size, you can fit 20, not an issue. But it is good to go between two to 15. That's the best one. That is bar graph. [06:24:00] And the next one is the line chart. Important graph. Important graph. Line chart is used specifically for time series forecasting. When data is getting dependent on time period, the example [06:24:15] which I gave you, it was the sales data what I was having for August, October, and November. But there was some listing of dates for some particular week, no? Because of that, it was possible see when the data [06:24:29] points are getting connected, that means from for week one to week two, week two to week three, week three to week four. But one of the week is missing. I don't have the data for this. I cannot link this data with the next month in one [06:24:42] line chart. That's the error. That's an error. First thing is when you are creating it with respect to month wise or year wise, [06:24:55] with respect to month wise or year wise, you should have 2022, 2023, 2024, and 2025. Then you can create one line chart out Data when it is getting dependent on [06:25:07] time period, it should be continuous values. Year is continuous. Age is continuous. Month on month is continuous. Dates are continuous. And continuous. Dates are continuous. And weeks are again continuous. [06:25:20] So, continuous values we need to add to create a line chart. It's a time series. Hope this is clear or any questions if you have, let me know. Line chart plot the data points connected by lines that [06:25:34] shows there is a continuity or there is a connection between week one to week two or last month to this month or last year to this year. If any of months or any week are missing, you cannot link them together. [06:25:49] Only use a line chart when there is a logical order between the data. If not, it will give you a a wrong result or you know, there are See, all these principles are same. Any tool you use, universally these principles are same. [06:26:06] People will see the dashboard what you have created. And they can clearly make out if you are creating a line chart, what is the principle as per them? You are showcasing a time series graph. But if you are if your data is something [06:26:19] different, then the dashboard, the results what they are getting out of it is incorrect. That is the reason understanding these donut chart. When you want to show the data in terms [06:26:33] of proportion. The contribution of this category is The contribution of this category is 20%. For example, 20%, 20%, 20%, 10%, 30%. You are seeing all different uh you [06:26:47] know, categories are having a contribution of overall contribution towards the sales. 10 10 10 20 20 All together will be some amount. All together will represent 100% only. So, [06:27:00] when you want to showcase the data in terms of percentage. Minimum of two categories, maximum of six categories, not more than that. Again, everything is not suitable to fit here. So, that is the reason we concentrate on [06:27:15] this donut chart when we want to showcase the data in terms of proportion or percentage values. Category can enter proportional data into app. Use a donut chart to show the proportion of each category. [06:27:29] A donut chart represent data into different slices, each piece representing the different category and proportion of that category. You can show it like numbers also or you can also convert that to a percentage. [06:27:44] Usually, donut chart and pie chart, it's the same. Uh here you can see a small slice, right? Here you can see small slices. But there a complete slice will That's the only difference. Here you have a hole. There you don't have a [06:27:59] hole. Everything is covered. I will show you how to create all this, but now I'm giving you the information. Then comes an important graph, that is scatter plot. If you know about machine learning, scatter plot, whisker box plot [06:28:14] easy. If you are If you don't know or if you are new to it, not at all an issue. This scatter plot is created to check the relationship between two variables. If I talk in terms of machine learning, [06:28:30] this scatter plot is used to create a Sorry, it is used to create uh to check the relationship between output and input. Independent and dependent variable. In this case, any two continuous values. [06:28:44] Profit versus sales. Quantity versus sales. Age versus height. Age versus health care. When you have two continuous values together, you can create a scatter plot out of it. [06:29:02] indicate it is two variables. And this line here is nothing but it's the result of the result result result or the difference between actual data and the model predicted data. We going to calculate with this line. [06:29:17] chart. So nothing to worry much about it. Scatter plot is used to check the relationship between two values. I will show you how to create all this in Power BI now, but before that I'm giving you [06:29:30] all this information so that you all can remember. Scatter plots plot the data points it's on a two-dimensional plane. One variable on X-axis and the other variable on Y-axis. Don't use a scatter plot when [06:29:45] the relationship between the two values or the two variables that is on X-axis and Y-axis, the data if they don't have a relationship then don't create it. It will give you incorrect. And when you have a categorical data, you cannot [06:29:59] create a scatter plot. It will throw you an error. If you use a code to create a scatter plot using matplotlib, it will throw you an error. If you go with Tableau, I don't In Tableau, I don't think so you have a [06:30:11] No. We No, we have Sorry, we have a scatter plot in Tableau. Indirectly we have. In plot in Tableau. Indirectly we have. In Power BI we have a scatter plot. [06:30:31] in line chart you only can see the line. Here area is also occupied. But avoid using the area chart when you have multiple lines because one line is getting overlapped on the other line. It will create an issue. You will see for [06:30:45] uh you have Imagine there's another line coming below going up coming below going up. It's getting overlapped and you cannot see the exact value of this because the area is filled. Actual value [06:30:58] you cannot identify. So that is the reason we don't go with the area chart, especially when we have multiple lines. So, it's a continuous again time series data. You should have a [06:31:12] continuous data as usual, the same principle of line chart, but here area is also occupied. I will show you how it work how to shade it. This bubble chart we don't have it, but if you ask me what is this bubble chart? [06:31:25] If you want to show the data representation and also the connection between different variables with the size of the bubble, because higher the value, the bubble size will also increase. If bubbles are getting [06:31:38] connected to each other, there is a relationship between one category with another category. So, this is bubble chart, but there is no much technicality behind it. Histogram, one of the important graph I [06:31:51] can say. Histogram, we will be selecting only for one variable, only one column, and that column should be continuous value. column should be continuous value. Height, weight, total price, quantity. [06:32:04] So, the this x-axis will be the variable what you're choosing, and y-axis will represent how many times the repetitions are possible for these different. There will be number here, 10 20 30 40 50 depending on the data set. This will the [06:32:19] y-axis talks about frequency. So, histogram in our school days also we were creating this histogram, no? The same histogram is here. Continuous data you should have, and it is univariate, that means only one variable. [06:32:38] distribution. And this is the heat map. Heat map we don't have directly in Tableau or we don't have it in Power BI. don't have it in Power BI. Just a second. [06:32:53] tree map, we don't have this heat map. But we can create a heat map based on the condition. Based on the value, based on the condition, I can I will show you how this heat map is created depending on [06:33:07] the condition. So you should have a continuous and a multi-variant. Continuous in the sense The first thing is the scatter plot what we create it is only between two. Let me write and show [06:33:19] you. All these are some technical graphs. See this is how the scatter plot graphs. See this is how the scatter plot will be. I will do create again. Sorry. [06:33:44] This is your scatter plot. This can only be created between two variables. But if you want to see you have so many columns in your data set. You want to see the correlation of all these variables together. [06:33:57] Correlation between this column to this column, correlation between these two columns, again these two columns and these two columns. Again this column permutation and combination will be there. [06:34:09] there. So 1 2 1 second A B C D there are four columns. 4 into 4 16 different combinations you will get. Here also it is A B C D this is like a matrix calculation. [06:34:21] The correlation between A and A when you check the correlation between same variable the result will be 100%. It is one. Correlation between A and B it is two. [06:34:34] See whether correlation between A into B or B into A should be two. or B into A should be two. So now B into A is also two. When A into So now B into A is also two. When A into B is two means B into A is also two. [06:34:48] So A into C correlation result will be here. A into B correlation result will be here. This scatter plot will only talk about two I mean one value here. This one value A into A you can only see here. Only two variables at a time. [06:35:04] Whether you select same variable or a different variable, you will get only one result and this correlation value will be between minus one to plus one. This is as per the rule, but when the data quality is not good, it can also be [06:35:20] data quality is not good, it can also be between minus infinity to plus infinity. machine learning models. But usually when the data quality is incorrect, it can be between minus infinity to plus [06:35:33] infinity. But usually it will not go to minus infinity. It can be minus three, minus four, minus five will be there. But this is the defined rule. Don't get confused why I'm giving all this explanation if these are the [06:35:46] graphs. This may be related to machine learning, but these are the important graphs which you will be created. So some techniques than mentioning machine learning. This is these are some mathematical functions [06:35:59] which we need to remember. This is stats actually, but it is used maximum in machine learning. I know machine learning and Power BI may not be as per you may not be related, but they are related because data is here. Data can [06:36:14] the same data will also be used for machine learning models. machine learning models. So we are checking the correlation here. >> So after checking the correlation, the results what this graph is giving now, [06:36:26] that you can apply a correlation method. There's a formula correlation method which you will apply on the data. The result will be in terms of metric. That numbers you will convert it into a graphical representation with this heat [06:36:41] map. Heat map not only for this I mean not only for this example, it can be any example which I will show you this heat map. When you go with the conditional formatting, you can apply [06:36:54] this. That is called a heat map. The result will be represented in terms of color intensity. That is heat map. [06:37:08] Any questions anyone here? This is tree map. Tree map like top five There are 30 different categories. You are sorting it. When you sort it, you can either sort in ascending order or you sort in descending order. Tree map [06:37:22] store the data in descending order. You can see the size of the boxes is can see the size of the boxes is representing us the different values. If the size of the box is maximum, the value or it is top one. This is top two, [06:37:35] top three, top four. Sorry, top four, top five. So, similarly, it is arranged top five. So, similarly, it is arranged as per sort. It is sorted. [06:37:48] And I think whisker box plot we don't have in Power BI, so I don't want to confuse you. It is statistical graph. So, I just don't want to confuse you. And we have a stacked bar chart. See, stacked bar graph we will be using when [06:38:01] we have two two different uh axis like sorry, when we have two data and the other one is numerical data. When I move towards the stacked bar chart, no, you have X axis and Y axis. You have bars here. [06:38:19] Each bar is not only representing one value, it can also represent two different numerical values. One categorical data, but multiple numerical columns. Like one categorical column and two or three numerical columns you can [06:38:33] two or three numerical columns you can have. This is same uh sales uh region A and region B. In region A and region B, you want to also see the sales, you want loss. All three numerical values you want to [06:38:48] see under one bar. Then you can go with the stack bar chart. This option we have in Power BI. Directly you don't have in Tableau, but you can also create this in Tableau. But direct option you don't have there. [06:39:00] So this is about stack bar chart. And with that we have come to the end of our question, ask them in the comment section below. Thank you for learning section below. Thank you for learning and keep learning with Simplilearn.