Power BI Tutorial For Beginners 2023 | Learn Power BI In 90 Mins | How To Use Power BI | Simplilearn

0:01 foreign what are its features what are the basic advantages of using a visualization tool like power bi and we will talk about how to install powerboat bi and other features it provides and the basic setup so let's discuss what's in it for us today now we will first talk about why power bi you know why it's a popular tool and what problem it solves what is power bi

0:48 uh and what are the primary features of power VI which you can use in your day-to-day data analytics visualizations creating fancy reports creating meaningful intelligent reports uh for your organization for your personal use for crunching numbers for generating reports real time Etc now the most popular tool for power bi is the power bi desktop uh I'll show you certain uh aspects of power bi desktop and then I'll show you the steps how to install power bi desktop on your machine and then definitely power bi desktop is a free tool provided by Microsoft but then you can also subscribe for an Enterprise version which is primarily

1:34 used by Enterprises for publishing their data so we'll see the difference and then overview of our dashboards which can be created uh you know what kind of dashboards can be created in power pi so this is the agenda for us today now why power bi so generally uh you know visualization tools reporting tools are required in order to create and prepare and analyze meaningful data it could be a data for an organization it could be a social media platform data it could be a data from iot devices but something which needs to be analyzed and some

2:21 intelligent inferences and data mining has to be done on top of it now imagine there is today we are in a world where terabytes of data and information is getting generated on an instantaneous basis on minute-by-minute basis so it becomes very essential to churn out something meaningful something intelligent out of it in the market there are a lot of other tools which are available like clicks all tricks Tableau and power bi so power bi is a Microsoft product which is one of the most popular products and it comes as a free to download product Microsoft power bi desktop which is available and I'll show you a couple of ways how you can install it on your machine but why power bi is

3:08 popular is because it provides a lot of out of the box features drag and drop features which we will talk about in our subsequent sessions and you know classes but today's session is primarily focused on giving you guys an introduction on what what is the purpose of power bi and what all problems it solve uh in the in the real world so power bi allows you to view analyze and visualize huge quantities of data and the data could be in any format Excel CSV text or it could be a direct connection to a database like SQL MySQL Azure Oracle anyone IBM db2 so it supports n number of uh you

3:56 know data types or data sets and it's very powerful in terms of data connectivity so it uses powerful compression algorithms to import and cache the data within the dot pbix file so it's as convenient as a simple software if suppose you import a data and then you prepare a report and then you can easily share the reports uh with your peers or someone who's co-developing with you either through Power bi cloud services or even you can share the pbix file in an email or through any other means and you can share the data set with the concern and they can then work on the report independently so there are different ways there is no uh kind of a limitation

4:43 for you know working on power bi there are multiple ways and it is very fast it is the most fast uh tool to work with Excel because definitely Excel is also a Microsoft Technology so it works very fast on Excel based data and gives you numbers and Reporting at a very high speed so now once you have imported the data power bi allows you to model the data allows you to work intelligently on the data it allows you to model data in a way that if you are importing data from multiple Excel sheets importing data from multiple tables you can easily create a relationship between those tables or data sets in power bi and then

5:31 create visually appealing reports meaningful reports as I've been emphasizing and make sense out of the data no data in silos is of any use data in Silo means a single worksheet or a single data set will not churn out any meaningful information until unless you basically join it clubbit merge it Union it append it with some other data sets because a single data set will never be able to hold that much amount of information which is generally required for a you know important report so it has easy drag and drop functionality with features that allow you to copy all formatting across

6:17 similar visualizations so just like in Microsoft Excel we use format painter to copy the format of one cell to another similar features very very similar to excel products or Microsoft product they have provided that if you have applied uh a particular thing on a report you can easily replicate that on any other report the font uh the header size the background color you don't need to do it again and again so there's a lot of reusable features which are also available okay now as I said Excel is a Microsoft product power bi is a Microsoft product so they have intercompatibility you can publish data from Excel to power bi now

7:05 with the latest developments and enhancements as of today Excel has also plugged in a new feature called Power pivot which I'll uh show you later down the line but that also allows you to do a quick analysis no you can't create of course complex reports or fancy reports like power bi but Power pivot allows you to create you know quick measures quick functions quick calculations on your data quickly only in Excel so it's an Excel plugin but whereas you know you can power bi is also compatible with Excel so when you create a report in power bi it gives you inbuilt feature to export your power bi

7:52 report into Excel format directly you don't need to do any programming for it also uh you can easily publish when you publish your power bi reports it allows you to give some inbuilt intelligence of analyzing your reports in Excel and it gives you all those of all those features of exporting your power bi reports into Excel which is not available in any other tool or otherwise those tools have to create plugins create add-ins and probably they might charge for it but Power bi comes with lot of out of the box features which are very very helpful for analyzing data in Excel and vice versa Azure Cloud now Azure itself again is a

8:37 Microsoft cloud Tech stack so using power bi with Azure allows you to analyze and share large volumes of data so Azure basically Azure database or Azure Cloud servers are meant to hold huge amount of data and power bi allows you to have seamless connection you can easily connect to Azure data Lake you can reduce the time it takes to get insights and increase collaboration between business analysts data engineers and data scientists so azure data Lake becomes the central focal point where all your analysts Engineers can keep working on on the centralized piece of data and churn out their reports data scientists primarily job is

9:24 to keep the data in a structured way optimized way optimize the input output operations disk operations and memory utilization so that the reports also get churned out in a faster manner so every uh you know every person has their own role in order to give a quick refreshable report a quick rendered report any report which is taking huge amount of time to get rendered will not eventually be used by the business users because then it does not solve the purpose the report should be fast reports should have uh you know appropriate filters slices and dices you should be able to you know create the reports or dynamically or you should be able to analyze visualize the data

10:09 dynamically so all those visualization features are available in power bi and it works seamlessly either it is small data or huge data uh it allows that you to work on those kind of data in a seamless fashion right so this is just a very uh quick uh example of how a typical dashboard looks like dashboard is nothing but you know you have clubbed couple of multiple reports on a single page and you know if you change a filter a single filter on the page all the reports will honor that filter and the numbers will change accordingly so if you see the in this example there is a filter or a drop down or product ID product name employee name

10:56 or supervisor or a date range so whatever date range or filter you will apply all the reports on this dashboard will get changed based on the filter you have selected so power bi allows you to get insights from data and turn insights into action to take data driven business decision and that is the ultimate goal of any visualization tool that is the purpose for which visualization tools are bought uh and purchased by the organizations and data is fed into them Now power bi fetches data from Factory sensors social media sources to get access to real-time analytics so that you are always ready to make timely

11:42 business decisions so so basically there is a feature of live connection or cut off data connection so either you can work on data which is deciding on a machine and you can just work on the cutoff data like for example it's there's a data which is available for sales 2017 2018 and you're just working on a historical data it's a cut off data or it could be possible that you want to be connected live uh to a real-time iot based sensor based data or social media data like Twitter Facebook feeds or you know you're connected to live Google worksheets that is also possible you just need to publish your Google sheet for a public domain embed that you are

12:28 in into Power bi and then whoever updates that Google sheet automatically the power bi report will also start honoring and consuming the new data which is added in the Google sheet so all those kind of real-time streaming analytics is also possible and that is one big feature and very important feature of power bi which is widely used and has a very huge uh you know Market acceptance and Market utilization now what is power bi power bi is a business analytics service provided by Microsoft that lets you visualize your data and share insights right so earlier you know uh uh Microsoft used to have a

13:17 technology called ssas now they have replaced actually ssas and SSRS with power bi so basically you can use power bi on the data which is there in your Excel or any other data source and the power bi service or power bi desktop basically creates a connection to those data sets and uh imported cache it and give you a handle to it in order to work with it so you can create these fancy meaningful visualizations like for example there's a geographical map if you are importing data for a country or a continent or a region power bi will automatically detect that it's a geographical information and give you a map with latitude longitude information

14:02 and you just need to plot uh your uh your numbers on the map either you can use bubbles or either you can use triangles or whatever data structure you want to use but all the mapping will be available geographically then you can create pie charts which is shown in this visualization you can create dream apps you can create cards where you know you can highlight the most important numbers like sales total sales of your company across all the regions or the growth chart or the month on month uh you know sales of your organization or number of total number of products or units sold so whatever is important and to be highlighted for the management to take any meaningful decision or any insights

14:47 you want to share power bi visualization tool the power bi visualization uh chart allows you to drag and drop and create wonderful reports okay so what are the features of power bi so power bi desktop is something a standalone tool which you need to install on your machine it allows you to build reports by accessing data easily you do not need Advanced report designing or query skills to build a report though yes it is beneficial that if you know some SQL programming analytical programming or you are aware of advanced features of any analytical

15:34 tool that might help you but that's not a showstopper you can easily build reports in quick turnaround time without needing any technical background you just need to have some analytical mindset and you can create uh Savvy visualization and you know analytical reports stream analytics as I mentioned you can create a live connection uh with any kind of data it could be iot it could be media social media it could be Google Docs it could be uh you know any other kind of uh you know live connection it could be a live database connection itself so any insertions or updations or deletions happening will automatically

16:21 reflect in your report yes multiple data sources and that has to be the primary criteria for any tool to be popular if any visualization tool is limited to certain data sets then you know it will not be highly acceptable in the market and custom visualizations right so as I showed you certain uh examples in the past in the previous presentation uh that feature is very important because someone might want to look the kpis look at the kpis from a different perspective some management might want to look at the kpis from a different perspective so you need to you need to have that capability to create different

17:06 visualization from the same data set now let's take a look at how to install power bi desktop on your machine so basically what you need to do is you need to go to this URL power bi Dot microsoft.com en US desktop okay and you need to just enter this now you can download it for free so just click over here and

17:52 it will open Microsoft store so basically now what Microsoft have done in the latest operating systems is that when you are trying to download you can actually directly go to the Microsoft store and search for Power bi so let's wait for a couple of seconds right here so power bi desktop in Microsoft store for me it's already installed so it's asking me to open it I'll open it in a while but for you for anyone who is not installed he will see the button of install over here and it

18:38 will automatically install in your machine and then you can easily go and open power bi desktop now if I click open over here now this is the uh UI of the power bi desktop I'm not going to go right now in creating reports right away we will talk about that in a subsequent session with sample data sets and we will cover the features of power bi desktop one by one but this is what it is this is the whole tool of power bi which is having the visualization pane all the different visualizations are you know can be created from this pane

19:24 then this is the pane which allows you to select the data data fields then there is a report view data View and relationship view the data model view where multiple relationships you can create you can view the data in the grid of the tables which you will create and the reports so you can create multiple reports on multiple Pages you can keep adding pages either you can create multiple reports on a single page and it will become a dashboard or you can create separate independent reports on the single page and these are the menu options which we will talk about how you can change color scheming you can do data modeling you can create new reports and you can also

20:12 transform data which is the biggest feature extract transform and load the data apply different logic changing data types massaging the information creating new joins appending the data you know adding new columns etc etc uh we that is what you can do in transform data so this itself is a whole different world it's a dedicated topic so we will talk about that in our subsequent sessions so what's in it for us today we will be learning how to connect to data different data types data files like Excel PDF then what are the different data importing modes and then I will also show you practically different sets

20:58 how to import them in power bi and use it for your visualization purpose now what are the steps to connect to data so now we will go directly into Power bi and try to import one by one few most commonly and popularly used data sets which are most commonly used in a day-to-day activity rest of course there are power bi supports n number of data sources but we will do something practical on the most popular ones so let's let's open our power bi now this is my power bi and first I want to show you that how can I import data

21:44 directly from a web page and import the data now it is asking for a URL in order to import data so what I have done is I have created a Google Excel sheet with simple data with rows and columns and what I have done is I have shared this sheet as published to web okay so you just need to say publish to the web the link as web page and say done it's it's automatically published and say link so copy the link which you have published on the web copy this link and then go back to your tableau paste that link over here and click ok

22:31 Now power bi will try to establish a connection with this Google doc sheet because it's published on the web thank you need to wait for a while while it is reading okay now it has read one of the HTML tables I'll select this one now you can see it has it is showing me a preview of the table which is there on my Google sheet right it has 11 rows so it has all showed all the 11 rows so now I can go and transform this data because I can see my headers are there starting from the second row so there's an

23:17 opportunity for me to transform the data so I'll go and transform it so that it looks clean okay so first is I need to remove the first row which is the null row remove the top rows okay and then I need to use the first row now as a header so you just click this option use first row as headers that's it so now if you see my row ID order ID order date shifted all my data is now ready so I can say close and apply click apply changes

24:06 now this is an example of web data import you can go and preview your data right now the biggest advantage of this data connection is that it's a live data so for example I insert another row let me change the order ID some some I've seen some basic stuff and I it's Auto saved Ctrl s now I'll go to my tableau and I'll refresh

24:56 now you can see as I refreshed my power query editor I click refresh all and I got my new row which is there in the live data I got that fetched from my foreign number 12 so I have to say close and apply now you can see the new row the row number 12 is now available in my new data set in the data set because it's a live connection it's a live connection

25:42 with the web-based Google sheet okay so this is one important way in which you can import data now let's try to import data from a text file now I have already prepared our text file called sub categories Dot txt now let me just open it in a notepad now it's a very plain simple file tab separated file in which you have product subcategory ID subcategory name and product category key so basically to which product category this particular sub product belongs to right so what I am going to do is I'm going to go back

26:28 to my get data option and I'm gonna select text slash CSV option and I'm gonna select option mod product subcategories.txt okay so now power bi has identified that it's a tab delimited file it has recognized the headers Etc right and I can now directly load this file okay so now once the data is imported in power bi it is like irrelevant to me it's a composite data in in Port I'm doing right so in my presentation when I'm talking about importing data there are different importing modes right import our data

27:15 import can happen through different ways okay one is direct query mode in which I create a live connection to the database which I'll also show you uh using MySQL and Ms SQL server and also you can do a composite mode in which you can have data imported from Excel plus you can have direct query modes so you can have multiple uh modes to connect and create a composite data model and that's what we are doing right now in our practical so what we are doing over here is one we have imported data from the web second we have imported data from a text table now after doing text now our next task

28:01 is to import from CSV let's try another one so now I have imported product subcategory now I'll import a CSV file so again I'll choose the option Text slash CSV and now in this CSV file let me open this CSV file and show you what it is it so this is a list of all my products product key product sub category Key Products uh stock keeping unit Etc a simple CSV file and I'm gonna import that okay so now it is identified the delimiter is comma rather than a tab and it has already recognized the headers correctly so I load it

28:47 okay so now my products are there product subcategories are there for product categories now what I have done is I have created a Excel mode now so now Excel I am using to import my product category so now I have to click on the option of import data from Excel and I'll say product categories select the sheet load and now so my products product categories product subcategories do with different uh data storage types but still now the data is imported into Power bi it is a composite data model now another very important data type

29:32 which you can import is the PDF also right so what I have done is I have created a PDF called customers my customers data is lying in a PDF so what I've done is I've created a PDF which has data for some columns are there like you know customer key prefix first name last name birthdate marital status gender email address annual income total children etc etc so this is the data set which I have created in PDF so what I'm gonna do is I'm going to select PDF now and import customers.pdf and see it has recognized my table on page one which I'm gonna load

30:22 okay you can rename this as PDF table okay so this basically these are the different type of data types we have imported PDF Excel text CSV and web page now let's take a look at another interesting data set which you want to import is the my SQL Server data set

31:11 so what I have done is I've already installed MySQL server on my local instance and there's already a schema of SQL live tutorial over there and I have certain tables already prepared over there like Department employee Etc so my goal is now to import this data or create a live connection with this data set now in order to import my SQL database Connection in power bi you need to First download a connector MySQL power bi connector so you need to go to this link

31:57 and then click on download and install the MySQL connector based on the operating system you have you click on download and install it after you have done this go back to Power bi and then give the IP address of the database in my case it's there in this local machine and the schema which I want to import is SQL live tutorial so I'll give the name click connect okay now it's connected so now it is asking me which particular tables you want to create a connection with I am choosing department and employee

32:43 and I'm just loading them okay so now this is the exact data which is there in the employee and Department in MySQL okay so this is one example of how to create connectivity between power bi and MySQL now I want to do the same thing using SQL Server Microsoft SQL Server so I have also installed Microsoft SQL server on my machine and I have used the SQL Express so this is the name of my server so which I'll copy the server name and

33:28 go to get data select SQL Server and for now database is optional I can say direct query click ok okay now it is showing me what all tables I can import so in my SQL Server tutorial in my SQL Server I have I have these three tables customers employee tuition Olympic events so I can use probably the customers one which is

34:30 now you can see this is the data the customer's data which is lying in my SQL Server okay so I can preview it and load it so now you can you can preview the data in uh power bi that is this is the data so I can rename his customers from mssql and this is from MySQL and

35:19 okay so now this is not the only data sets you can import now if you take a look at the options which power bi gave what different type and variations of data it can it has compatibility to import from okay so we can just take a look at the categorization on the left hand side first there are file based like Excel text XML Json is also possible you can evenly directly import an entire folder and within the folder whatever uh data types of files are there it will detect it PDF power key or even SharePoint folder which is itself Microsoft uh

36:06 technology then different kind of databases SQL server and MySQL we just saw but it's not only limited to this you can connect to Microsoft Access ssas Oracle database IBM db2 postgres SCI base teradata and then sap databases Amazon redshift Impala vertica Snowflake and N number of databases which are there in the market today Amazon Etc then it also allows you to connect with its own power platforms power bi platforms data mods power bi data flows data boards Etc Azure there are different kind of storage

36:51 mechanisms in Azure and Azure itself is a Microsoft Technology so it has a compatibility of a lot of azure uh based data storages like Azure SQL database blob storage uh Azure data breaks right Azure HD insights path so if you have those kind of services running on your observed cloud services you can even import them over here now online services like you know you have erps running uh or some data which is shared on the internet if you want to import it uh that is also possible through certain products uh Dynamics 365 Microsoft Exchange online Salesforce

37:38 Google analytics Adobe analytics GitHub LinkedIn sales if you want to do some analysis of some social networking uh you know feeds that also you can import and then other miscellaneous are also their web-based hive R script python script if there's something to import get data from uh Google Sheets like we saw one example in our video right now so there are multiple options available now once you have imported the data which is relevant to you um in our subsequent sessions we will

38:24 see how to create relationships but just giving you a glimpse that whatever data you are importing power bi Auto detect certain relationships and it will create for you but then you can go and manually also change so this is the composite data model which is getting created in the back end while you are importing the data you can easily go and manage these relationships either keep them as is you can delete and create new ones manually so there is no limitation in that so this is what we have witnessed we have imported data from different files types data types and then you know we

39:10 have tried once it is imported into uh Power bi then there is no limitation of how you use it you can create visualization across different data sets and then create your standard reports so this is the example of importing data from web importing data from a database from a PDF and then once you have data you can shape and combine data you can basically do what whatever transformation you want to do you want to uh make joins merge the data so for example if we go back to our power bi and if I go back to my transform data section

39:55 now as I have now different data sets available with me I have I can do any kind of you know operation transformation on the data right uh so like I showed you I uh upgraded the header row because one of the imported data was not showing the header correctly uh or this columns like this exact one column is extra I can remove the column right all those Transformations whatever I do in the back end gets captured in the applied steps section right this is the customer data you can create uh you can merge it you can append it uh you know with other data sets right

40:43 let's for example I want to create a merge data set of my categories and subcategories so I can say mer selectors two data sets and say merge queries as new and I can select product categories and product subcategories select product category key on both the side and then they do a left autogen so whatever product categories are there I'll get the subcategories associated with it and I'll create a new table which will have now I have the table which has the category and the subcategory and subcategory in one table itself so I can

41:28 rename it now to as category sub category table it's a it's a merge basically it's a join between category and subcategory now I have a common table right and I can close and apply so imagine I have created a new table which is imported created from one data set is which is Excel page another data set which is text based see this category subcategory table so now I can use it the way I want in my visualization reports so that's what the presentation says right that once you have uh the

42:13 imported data you can shape you can combine you can adjust you can do whatever transformation you want to do and create your visualization okay so what topics we are going to cover today we are going to talk about different types of data modeling and the most important part and aspect of data modeling is the cardinality the cardinality which you basically decide after reviewing the nature of data and after we've imported it what kind of cardinality you have to basically highlight right and there are different type of cardinalities which you might have heard earlier also if you are from PL SQL background like one is to one one is too many Etc we will we

42:58 will talk about that now what are the different types of data modeling now dimensional data modeling is one of the most popular and most uh you know widely used uh modeling in dimensional data modeling you have Master data uh like for example customer data date store data product data so these are like you know uh less frequently changing data sets so there is an organization right and you have set of customers their email ID phone numbers Etc that will change less frequently as compared to the sales transactions because transactions are happening every day every minute so sales is a more fast

43:44 changing data set in dimensional modeling which is in the terminology of data uh is also called as a fact and customer store product which are like more of static data and less changeable data is sometimes called a diamond so this is a typical dimensional data model which is typically used uh sometimes right and then there is another model which is relational model this is a typical model which we have been using in database design like you know primary key foreign key relationships so for example you have a customer who has purchased a product so probably he might have the customer might have the details of the product

44:29 which is processed and you will make a join between customer and product table and even you can make a join between product or product type or customer or product types customer table will also have a key to the product type so this is less conducive for reporting but it is more of a transactional relational model but of course this is also feasible but from the power bi perspective when we talk about reporting and visualization this is the most extensively used dimensional data model and this is what we are going to see in our example now so what I'm going to do is I'm going to show you certain data sets first we will prepare and create certain offer data sets and then we will

45:14 Import in our sample power bi file and then slowly slowly we will create the relationships now one important thing which you need to understand that in power bi if you go to Power bi there is an option that that power bi Auto detect new relationships after data is loaded and import relations from the data source on first load so for example if you are importing the data from a database where you have already defined the primary keys and the foreign key relationships so uh that is the first option which power bi will Auto detect and secondly if suppose you are importing two different kind of data

46:00 sets one is Excel one is csb and if power bi detects a common column key columns it will auto detect a relationship which you can go and later change modify manage in your relationship menu manage relationship menu in power bi which I'm going to show you okay so if I open a power bi and this is where the option lies go to file go to options and setting options data load and these are the two options which are by default check you can uncheck it and

46:45 auto and manually prepare relationships there's no limitation to that but if you uh keep it checked then power bi will do its job to detect the relationships okay now coming to the next important factor cardinality now before I start playing around with my data and start showing you certain relationships it's very important to understand these four types of cardinalities one is many to one right so basically many to one means that many orders contain data of One customer so per order one customer is there so from customer to order or product or delivery address it's a one-to-many relationship and from the other side from order to

47:32 customer perspective it's a many-to-one relationship okay second other cardinality is one is to one one is to one relationship is only applicable when you are saying it's an extension of the current table so for example in one table you have employee details and you are extending the details of the employee in another table like employee address employee ID so that is like one is to one there is no multiple records of a single employee in the address table only one employee ID exists right now one is too many as I said is the reverse side of Venice to one okay so in customer table only one customer record exists per customer and one customer can

48:18 place many orders for multiple products and can also have multiple delivery addresses so that way this is a typical one is too many relationship we will be seeing this example also in our sample data set and last is the many-to-many relationship now many to many is a very typical example so which I'm going to show you practice practically and in our case we will see that like for example you have placed an order for a particular product uh you know but there are multiple fulfillments which has happened so suppose you made order for 10 products but at the back end when the company is fulfilling it is first fulfilling the first two products then the rest three so basically you the

49:04 Fulfillment is happening in batches so one order ID might have a multiple fulfillments for the same order ID so there will be a multi many to many relationship which I'll show you practically so now with this background let's start importing our data now the first important thing which we need to import is the Master data so first I'll import all my master Dimensions which are which I'm going to you know use in my example so first is the customers table customers data so this is the customer details the

49:50 customer key prefix first name last name birth date marital status and gender some redundant columns are also present but we'll remove it so my customer data is loaded now today's session is all about this section of modeling so we will keep our Focus over here okay now some columns probably some blank columns are there I can select them and say delete from model yes okay so now this is my customers data

50:35 with the relevant columns and the key per customer customer key now there's no relationship in this model right now right because only single table is there and the associate data is only imported now let me also import my another important master table is the products select the products data product key product subcategory product SKU product name model name product description color size so just see all the relevant information only specific to the product is available so I'll import it okay now see there's no relationship

51:21 between product and customers directly because until unless a customer makes an order places an order for a particular product there is no join right so now between these two tables the most important now another table which will now make sense is the sales order table sales table now I am assuming that power bi have Auto detected the relationship now you can see that because I've already uh ticked that check box now let's see what power bi what relations power bi has Auto detected let's first say check the

52:08 relation between customer and sales I'll double click this uh joint now what it has done is it has created a join of many to one between sales and customer so what does that mean is that one customer has can place many orders right and that is that it is detected by the quality of the data and the data sampling which power bi has done you can also reverse this relationship here I can select customers and I can select sales now it has become one too many so that you can also do manually so that is what I said whatever power bi is detected it is up to the description of power bi internal

52:55 configuration and algorithm but you can go and change it so this is now you can this is by default active so we want to keep it active One customer many sales orders cross filter Direction means that only from customers to sales is the filter applicable not reverse I'll come to this with my another example but first let's change the relationship so one is to many means from One customer and many sales orders similarly let's see what has happened at the product side of the relationship similarly power bi many sales orders for one product you can for simplicity's sake you can say products sales

53:41 product key is the join now just focus one more thing please also see the the column on which the join is is the grade column grade out column product key is also here and product key is also there and it is what we wanted so one is too many relationship from product to sales table and act now looks fine this is something which is looking logical and probably now we can proceed further to create a report now let me explain the cross filtering with an example now for example I want to check in a report that what is the count of products which

54:27 uh which a particular customer has ordered okay so what I'll do is I'll select the product count of product name now if you see and for each customer in front of each customer name the count is coming as 293 293 it is getting repetitive because because there is a one-way filter Direction filter between customers and sales and sales and products right so this join is single sided it means that from customer to product you can't find a relationship because it's a single side

55:13 cross filter right what does this if I change it to both it means that it is equal to a join between product and sales and every product detail now is appended to the sales table so if I want to make you visualize this you need to go here I'll first open my sales table we can also open it here let's make click it is okay now if I click OK you can see the single arrow is changed to Double Arrow it means it's a it's a both site filter So when you say a both site filter it means that implicitly within power bi

55:58 you can imagine that all the product columns now will get appended because of both ways filter you have applied and if you go to your report now see the change of the numbers now for T20 so the total count of products across all my customers come out to be 293 now the report looks uh correct if I change the relationship from back to single between product and sales then you can't make a join between customers and products basically you can't derive the product count from the product table see this if you have to live with it then you would have to go to the sales table get

56:44 the product key and get the value of count of product key but that is not correct okay so if you want a report in which you want the count of product name and even if you want a count of distinct product name so this will not come correctly you would have to go and change the direction of the filter which is from single to both so this is a typical example there where you want to use a two-directional filter now let's proceed further and import other data set in order to give show you another example now I want to show you an example of

57:30 1 is to 1. so I have another table which is called customer details so the key in this table is again customer key but only email address annual income total children education level Etc other details of the customer is there so I'm loading the customer details now you see it has Auto detected a one is to one relationship what is the meaning of one is to one means One customer key only has one entry in customer details there is no multiple entry so if you click this button it's a one is to one and the cross

58:16 filter can be both or single doesn't matter because one customer will have only one value you can make this as active okay and if you go to the customer report table you can now easily associate a email address with the first name you will get one is to one record so now you can see that with one is to one relationship with the first name I have Associated the email ID and for each email ID there is a Associated first name method so this is an example of

59:02 one is to one relationship so in this example what we have explained is that for each customer there is an Associated customer detail right uh so you have the first name email address education level homeowner occupation and total children count so in this report what we have done is uh if you click over here so the first name and the email address okay so there's a one is to one relationship and then and if you drag the customer key report takes time to render and even if

59:49 you can so this is the reporting output you have the customer key first name associated email address and the count of product names uh which the customer has ordered now this is an example of 1 is to 1. now I want to show you an example of many too many now for that I'll import my fulfillment data set

1:00:41 okay now in my fulfillment data set there is a column for order number so basically what I'll do is I'll drag order number from here to here okay so now what has um uh Power bi detected I'll do one thing I'll select sales over here fulfillment over here and order number to odd number okay so it's a many-to-many relationship so it means that per order I have created multiple batches to fulfill that particular order now many to many relationship is a definitely a candidate for both ways

1:01:28 cross filter detection uh a Direction but you can you can check that but definitely uh Power bi shows a warning that this relationship has cardinality to many to many and this should only be used if it is expected that neither column contains unique values okay so we know that fact that's why we are accepting this relationship as many to many because we know there are multiple order numbers over here in the sales table which are mapped to the multiple order numbers in the Fulfillment table we'll click ok now you want to keep the uh uh direction as both ways or One

1:02:14 Direction that is up to you the way you want to map the report so I can double click over here and you can even click so now you can select from which way single filter you want from fulfillment to sales or sales to fulfillment I'll prefer sales to fulfillment and click ok okay now we have our all our different kind of relationships over here uh which we have tried to shortlist one to many many to one one to one which is uh this example and many too many now if I show you further relationships which you can keep on adding like for

1:03:00 example I have a uh the example of territories now in which particular territory the sales was done map it over here okay so now it's a typical one is too many relationship because territory is my master table uh where I have a static list of continent country region and it is mapped to the uh territories which are for in which my orders have been placed so it's a typical one is too many so that way you know you can keep on

1:03:46 adding data then you have uh details of returns now this is another transactional table which is about the orders which have been returned rather than being you know returned by the customers so you have a product key and so automatically power bi has detected a relationship between the product key and the product uh table right and even if you can join the territory key in which territory the return has happened right so mostly the most common relationship which you will observe is the one is too many because

1:04:32 as I told earlier the most common relational model is the dimensional model uh the static data the slow changing Dimensions the scds are the master tables and the most frequent changing are the fact tables so if I talk about a typical dimensional model the Fulfillment table sales table and the territory stable sorry the Fulfillment table sales table and my returns table are the fact tables of my data model now so far what we have done as per our last session is that we did data modeling on the different data sets which we had imported in power bi like products sales data returns fulfillment

1:05:19 customer details uh and customer Master data calendar details Etc so in the last session we prepared a data model and established the relationships between these different data sets like one is to one one is to many many to one one is to one Etc and we saw the examples now once our relational model is prepared our data model is prepared now our next activity is to create certain additional columns which we want to derive basis the data which we have imported so for example I'll start with my product data set now in my product data set I want to introduce a column which

1:06:06 basically categorizes that if any product which has a color uh you know red black or gray I am going to tag it as a colored product rest I'm gonna say not a colored product right so all these are like example of byte type product skus so for that now in order to introduce a new column you just need to do what you need to select the table in the data grid go to the table tools and say new column okay so column will get appended to the rightmost part and you will start seeing a uh formula section typical to like you

1:06:53 get in your Excel now I'm gonna say that the name of my column is going to be byte type color okay and I'm just creating a if condition if product color is equal to black okay or or if it is equal to red or if it is equal to Gray then say yes it's a colored product I'll say

1:07:43 no okay so now you can see this is the product color red and black they are saying bike type color Yes blue is no multi is no etc etc so this is a classic example of an if and else condition based conditional column okay so you can create such columns now second column custom column which I want to create is I'm going to call as discount now this is the pricing of my products I want to associate certain discounts which I am ready to give to my customers this is the product category like what

1:08:28 is the pricing of the category again I'm going to use make use of if else but in a nested way so if I am saying if my product price is less than 100 then I will give zero percent of uh zero percentage of discount so 0 into product price just to keep it consistent now I'm saying else if less than 100 and 0 else I'll check again that if the price is

1:09:16 less than 500 then I'm ready ready to give one percent discount on the product price else I'll move further so like this I have created a formula so what I'm saying is if product price is less than 100 give zero percent if it is less than 500 then give one percent less than 2 000 then 1.5 percent less than three thousand then two percent and otherwise else less than three thousand if a two percent else three percent right now after this column is created

1:10:06 now you can check right so this C the product price for this particular product it is less than 100 so that's why there is no discount it is uh between 100 to 200 then this has been given a one percent discount so like this all the discount call up is now calculated now this column is available just like a regular column in my product table now after this I'll go to my sales table now in sales table I want to identify a create a column called as cost

1:10:53 there is no product cost column over here so that will be derived so let's create a column called as cost and it is derived by order quantity into now the cost of the product is in the product table and I know I have already created a relationship between product and sales table so I just need to select the product cost column now only keyword which I have to use in power bi is the related keyword so this will pick up the

1:11:39 relation and now for this particular sale order the cost has already been derived so this order number this is the cost for which uh the product has is the costing of the product for this particular order thank you okay now I'm gonna create another conditional column over here called as order status

1:12:30 I am saying if any order whose order quantity is greater than 2 then for my organization it's an urgent order else it is a normal order so this is my order status column and I have my order quantity

1:13:19 urgent or normal so any order which has order quantity one is normal any order which is having order quantity as greater than 2 is urgent you can see this so there's a this whole power bi uh tabs and sheets allow you to also review the data what you're doing so it's very convenient now I have my sales data now what I want to bring within the sales is my discount column so here also I want to bring the

1:14:06 discount which I have created so I'll say discount [Music] discount will be order quantity into related product discount right so the discount calculated column which I had created under products I'll bring over here now I am creating the order level discount so if you see for this particular order these are 25 uh uh you know for 25 rupee discount at the cost is 100 000 rupees okay

1:15:03 and what is the order price now so I have taken the order cost the discount now I have to create a column call as price order price so that will be again order quantity into related price which is per product price enter okay so now I have the cost the discount and the price right available with me now I want to calculate the total uh total revenue total profit and loss

1:15:49 right per order how much so first I'll calculate per order how much revenue I am generating so now I have to generate a column called as Revenue revenue is price minus discount

1:16:48 so 1700 minus 25 1700 minus 25 2071 minus 42 and if I want to calculate the profit per order then it is Revenue Minus cost okay so now you can see you know typical custom columns calculated columns which we have created are all playing around with the number numeric values numeric

1:17:34 data primarily and trying to give inferences into per order cost discount per order price revenue and profit so typical calculation columns which I have prepared in front of you foreign variations of custom columns I'll create certain columns for text based custom columns calculated columns using Text data so I'm now moving towards my customer table in which I have customer key prefix first name last name birth date marital status and gender

1:18:20 now I want to create a new column in which I want to derive the age of each customer as of today right so I'll use another function a date function called as date div now date div so I want the difference between the birth date of the customer and as of today in years

1:19:07 okay so this customer as of today 68 year old one is 74 68 57 Etc so this is one derivation of uh calculated column of age let's take another example now this is a text based column where I want to derive the full name of the customer now here I'll say first lowercase in lower case I'll concatenate

1:19:53 the prefix then ampersand peace ampersand first name ampersand space ampersand last name and closing brackets Etc full name okay so this is an example of full name in low cases now another calculated column conditional column at the customer level I want to identify a flag which says who

1:20:39 is my Target customer base is the demographics shared over here Target customer so I'll say if the marital status is equal to m and total children hurry and total children annual income

1:21:28 so okay so let me change the logic a bit so marital status is M and age is less than 50. these customers are my Target customers okay so I would say yes else no see this he's a marital status is married Logan Diaz and age is less than 50 else

1:22:15 everyone so if I try to filter so these are the my Target customers 69 out of 1178 so this is just a conditional column but a logical condition an example which I'm trying to highlight over here okay now let's look at certain calendar date oriented columns calculated columns very typical like now I have a simple date column now I'll keep adding certain columns which are you know which help you which will help you understand how

1:23:01 we can uh you know do some calculations on the dates so like for example I want a date which is 12 days after the current date the date in the column so just simple 12 Days After select the date and add 12. now if you see the date format you can go and change the format at the top and if whatever you feel like like this now see 12 days after first gen 2015 is 13 Jan 2005. you can go and change the format and other details let me also show you if I go to my cost and other columns I can go and change

1:23:49 the format this is like a currency cost is currency so I can go and select the change the currency type and you can even show the dollar value or whatever currency type it is so for numbers you can do currency or text or dates you can select the format so this is available at the column tools level now in customers like we had our column of full name so now what all things are available format as text okay

1:24:35 data type text so very minimal options are there with date you have options of the date format now next I want a column which defines the expiry date okay so eight months prior to the expiry date with this which is coming up in eight months so I'll create a column called as eight months expiry and then there is a e date function I'll use that I'll use my date in the data set

1:25:21 comma I'll say eight so now this date column will append eight months to my actual date and again I can go and change the format correct now another important column like I want to know the date name so I'll use a function called as format and I'll select my calendar date column and I'll say give me the DDD

1:26:06 format of it so it will give me the day name the day the day name of on that particular date next years in between so I want the ears in between the today's date and the date of my calendar so equal to date diff the calendar date comma today comma year end seven years 2015 to 2022

1:26:53 then last date of the month so if I want what is the last date of this particular calendar month I need to use a function EO month which is there and label in the power bi so I'll say last date of the month equal to EO month then just select the calendar CSV date comma 0 in months and enter change the format

1:27:51 then similarly start of the month so I'll use a function called start of month so for for all January dates end of the month is 31st Jan and start of the month will remain 1st of Jan change the format

1:28:38 next I want to know what is the week number of that particular date so now there is an inbuilt function called week number week num and just pass the date and you will get the weak number first week of the year second week of the year Etc now another very good example is whether the weak

1:29:23 day is a weekday or a weekend right so what is it's a weak type okay so I'll check I'll put a if condition and there is a function called weekday and I'll pass the calendar if it is less than 6 it means it's a weekday else it's a weekend so all Saturday and Sunday Day news will come as weekends or else everything else will come as weekly

1:30:13 so these are very different variations of different column types calculated columns which is a very important utility and uh and any Bravo bi project you will definitely be ending up creating n number of calculated columns to derive your numbers to prepare your reports but it's important to understand what all things we can do yes there are n number of functions available in power bi but what I have tried to Showcase over here is some important functions but basis your utility basis your problem statement you can look up for a relevant function in the power bi dictionary now with this introduction to calculated column this is the base for us to now get into our

1:31:00 next session where we'll be talk we will be talking about creating Dax measures and Dax functions we will be using power bi Dax functions which is much more powerful than simple uh calculated columns where you can do more uh complex calculations uh you can calculate totals and then use them in the reports so for that we will look up into our next session so I hope you guys had a great learning session and I'll meet you in my next session thank you hi there if you like this video subscribe to the simply learned YouTube channel and click here to watch similar videos to nerd up and get certified click here