Power BI Full Course 2024 | Power BI Tutorial For Beginners | Power BI Course | Simplilearn

hey everyone welcome to Simply Lo's YouTube channel today we will learn the powerbi full gos that's said if these are the type of videos you'd like to watch then hit that like And subscribe buttons and the bell icon to get notified just for a quick info if you want to upscale yourself and master data analytics skills and land your dream job or grow in your career then you must explore simply Lance goad of various data analytics programs simply learn offers postgraduate program from pero University in collaboration with IBM through this program you will gain knowledge and work ready expertise in skills like prescriptive and Predictive Analytics regression classification and over a dozen others that's not all you also get the opportunity to work on multiple projects and learn from industry experts from Top TI product companies and academic from top universities after completing these courses thousands of Learners have transitioned into a data analytics role as a fresher or moved onto a higher paying job profile if you are passionate about making your career in this field then make sure to check out the link in the pin comment and description box below to find the data analytics program that fits your experience and areas of Interest now without further delay let's get started now let's understand why powerbi is needed first powerbi has the ability to access vast volumes of data from mult multiple sources it allows you to view analyze and visualize huge quantities of data that cannot be opened in Excel some of the important data sources available in powerbi are Excel CSV XML Json PDF Etc second powerbi provides an easy to use drag and drop tool with features and functionalities that allow you to copy all formatting across similar visualizations powerbi has exceptional integration with Excel it helps you gather analyze publish and share Excel business data powerbi helps to accelerate Big Data preparation with Azor using powerp with Azor allows you to analyze and share vast volumes of data as your data leak can reduce the time it takes to get insights and increase collaboration between business analysts data engineers and data scientists powerbi allows you to get insights from data and turn insights into actions to take data driven business decisions finally powerbi allows you to perform realtime stream analytics it fetches data from multiple sensors and social media procces to get access to real-time analytics so you are always ready to make business decisions now let's see what powerbi is powerbi is a business analytics service provided by Microsoft that lets you visualize your data and share insights it converts data from different sources to build interactive dashboards and bi reports as you can see we have an Excel data about sales using this data powerbi helps you build different charts and graphs to visualize the data now that we have understood what powerbi is let us look at the important features of of powerbi first is powerbi desktop powerbi desktop is a free software that you can download and it allows you to build reports by accessing data easily for using powerbi desktop you do not need Advanced report designing or query skills to build a report second as already discussed powerbi supports stream analytics from Factory sensors to social media sources powerbi assists in real-time analytics to make timely decisions third support for multiple data sources is one of the major features of powerbi you can access video sources of data such as Excel CSV SQL Server web files Etc to create interactive visualizations and finally custom visualization custom visualization is another vital feature of powerbi while dealing with complex data powerbi default standard might not be enough in some cases in that case you can access the custom library of visualization that meets your needs let us jump into discussing the various components of powerbi as you can see there are six major components of powerbi now let's discuss them one by one first is power query power query is the data transformation and mass up engine it enables you to discover connect combine and refine data sources to meet your analysis need it can be downloaded as an addin for Excel or can be used as part of powerb desktop second we have power pivote power pivote is a data modeling technology that lets you create data models it also allows you to establish relationships and create calculations it uses data analysis expression language or Dax to model simple and complex data third we have power view Power view is a technology that is available in Excel SharePoint SQL server and powerbi it lets you create interactive charts graphs maps and other visuals that brings your data to Life Next we have power map Microsoft's power map for Excel and powerbi is a 3D data visualization tool that lets you map your data and plot more than a million rows of data visually on Bing maps in 3D format from an Excel table or data model in Excel then we have powerbi desktop powerbi desktop is a development tool for power query power pivote and Power view with powerbi desktop you have everything under the same solution and it is easier to develop bin data analysis experience finally we have power Q&A the Q&A feature in powerbi lets you explore your data in your own words it is the fastest way to get an answer from your data using natural language an example could be what was the total sales last year once you have built your data model and deployed that into powerbi website then you can ask questions and get answers easily now let's see what powerbi service is powerbi service is the software as a service part of powerbi it is also referred as powerbi online to access powerbi service you need to log into app. powerbi.com now let me show you that I'll go to Google open a new tab and search for app. powerbi it's loading this is how the homepage of powerbi service looks like I've have created some dashboards on it first you need to log to app. powerbi service you can see I'm logged in now under my workspace if I go to dashboard here I have created a finance dashboard you can see the different charts and graphs I have prepared and pinned it to the dashboard so powerbi service allows you to connect to your data create reports and dashboards and you can also ask questions to your data now as you can see in this dashboard we have created some charts and graphs so this is a tree map there's a pie chart there's a bar graph below you can see there are line charts and donut charts it tells you the total sales that were made the total number of unit solds the sales by product sales by country sales by segment and lots more one of the key features of powerbi is creating dashboards from multiple reports and data sets powerbi dashboard is a single page visualization to a story The visualizations on a dashboard are generated from multiple reports and each report is based on one data set a single page dashboard is known as a canvas the visualizations you see on the dashboard are called tiles these tiles are pinned to the dashboard by report designers now let me go back to my dashboard so this is called a canvas and each of these are called tiles so on the top you can see we have three tiles now let's understand how to create and publish reports in powerbi dashboards powerbi allows you to create different reports on powerbi desktop these reports can be published on the powerbi dashboard using powerbi service here you can see there is a powerbi report created on powerbi desktop if you click on publish it will take you to the powerbi service where you can build a dashboard here is the button for powerbi publish once you click on powerbi publish it will take you to the dashboard so this is a single page powerbi dashboard on powerbi service now let's understand the powerbi architect Ure powerbi architecture is a service built on top of a z there are multiple data sources that powerbi can connect to powerbi desktop allows you to create reports and data visualizations on the data set Power VI Gateway is connected to on premise data sources to get continuous data for reporting and analytics powerbi services are basically the cloud services that are used to publish powerbi reports and data visualizations using powerbi mobile apps you can stay connected to their data from anywhere powerbi apps are available for Windows iOS and Android platforms now let's look at a case study on how Meer which is one of United States largest Supermarket chains used powerbi to solve its business problems initially Meer had become dependent on its it organization to extract insights from its data it was timec consuming and inefficient as you had to wait for it to build every report Meyer was unable to perform ad hoc and realtime analysis easily so what Meer did was it connected powerbi to an on premises SQL Server analysis services Cube this allowed them to refresh 20 billion rows of data in near Real Time with powerbi teams can now pull in the data faster and perform real-time analysis to derive insights from data a bakery department inside Meer used powerbi to compare its sales with regional performance they analyzed where Meer was behind the regional Trends focused on the problem and created a solution with powerbi they can now drill down into early sales and send out a sales flash to 800 Mayo Business Leaders so powerbi enabled them to standardize data sources and Empower store directors and team leaders to develop and track their data to ensure what they can improve now let's do some practical Hands-On demo with powerbi so this is how the powerbi desktop interface looks like on the left you have the report view the data View and the model view the report view is where you visualize your data with different charts and graphs to build reports the data view allows you to view the whole data while the model view is where you check if there are any relationship between the tables on the right you can see the different visualizations that you can build we'll quickly run through all of these in our demo so here you can see there's a finance sample data that will help you draw insights about the sale of products in different countries we will create a report to visualize different charts and graphs and analyze those sales so let me go to my powerb desktop first we'll import our data so let me go to our get data Tab and choose Excel as my data source I click on Excel so here is our finance sample data we'll select sheet one you can see the data here click on it and then select load this might take some time to load the data now if I go to my data tab you can see the entire data set it has Fields such as segment country in which the sales was made the name of the product the unit sold and the sales price and many more let's start building our report now I'll go to my report view so first let me create a text box let me resize it let me name it as Finance dashboard we'll increase the size of the text we'll use Font consolas Center it we'll also add a background to this we use blue color change it to White and increase the size now let me first show you how you can create a matrix I'll go to visualizations and click on Matrix let me resize it from the data sheet tab I'll select sales and drag on to values so you can see the total number of sales that were made now let me do some formatting so I'll go to the format tab click on column headers let's add a background color and let me increase the text size to 20 similarly under values we'll increase the size of the text to 20 as well we can also click on border and choose the color of the the Border we take as Let It Be black so this is a simple Matrix that we created which shows the total number of sees that were made similarly let me choose Matrix once again now we'll drag on the unit sold onto values we'll continue with the same drill under column headers We'll add a background this time let's choose some other color and under values let's increase the size of the text to 20 even for the column headers let's the size of the text to 20 again we'll switch on Border me resize a bit so here we have two Matrix created for our report the first Matrix shows us the total sales that were made the second Matrix shows you the total units that were sold now let's move ahead and create a simple bar chart so under visualization I click on clustered column chart under this we'll drag the date column onto access and the sales onto value let me expand it so it shows you the sales per year this is the sales that were made in 2013 and this shows you the sales that were made in 2014 now there's a drill down option which gives you more granularity this depicts the sales back water if I drill down further you can see this shows you the sales by month also you have some options like like s by and S by sales so you can see October month made the highest number of sales moving ahead let me now create a pie chart where we will see the sales by different segments under visualization I'll click on pie chart let me first resize it here I'll drag the segment column onto the legend and the sales column onto the values as you can see we have the sales made by different segments government segment made the highest number number of sales with 44.2% now let me add a border to both the visualization I'll click on the pie chart and go to the format tab I'll switch on the border similarly for the clustered column chart I'll go to the format Tab and click on Border let me resize a bit all right next we'll create a very simple table that will depict the total sales made by each product so under visualizations I click on table let me bring this below so from the data sheet I'll first drag product onto values you can see the different products and then sales just below it so this depicts the total sales that were made by each product and finally it displays the total value of the sales that were made this is same as the one shown here now let's do some formatting under format tab I'll go to values and increase the text size to 15 make expand it also under column headers I'll increase the text size to 15 then let me go and add a border now let me create a map that will show you the sales that were made by each country so first let me create a new page and under visualization I'll click on map now I'll drag the country column onto location so you can see we have our map ready and we'll drag sales onto size you can see the different countries and the sales that they made if I move the map you can see the sales made in the europ region let me resize it I'll add a border to this now let me go ahead and create a donut chart that will show you the profit by each segment under visualizations I'll click on donut chart I'll move this to the top now from the data sheet I'll add profit onto the values and segment onto the legend if I expand this you can see government segment made the highest amount of profit with 65.4% let me resize this and we'll add a border okay in the final visualization I'll show you how to create a tree map this tree map will tell you the total amount of sales made by each product so under visualizations I'll click on the tree map and let me expand it I'll drag Sals onto values and product onto group so here you can see our tree map and the sales made by each product you can see now we have our report ready we have created two separate canvas to visualize our data now if you want to change the color of These Bars you can simply go to the format Tab and under data colors you can choose whichever color you want in powerbi desktop you have an option to switch your theme this will make your dashboard or the report look more attractive so now we are under the default mode Let's try out different themes that's Frontier temperature solar which is a little yellowish the one which I like is tidle I hope this was helpful in making you understand the basics of powerbi and how it works you learned the various features and the components of powerbi and looked at the architecture of RBI finally you saw a demo to create a report using Finance data set now now let's get started by how to install powerbi in Windows operating system to install powerbi go to Google and search for powerbi desktop download now click on the first link and then you will be redirected to the official web page of Microsoft powerbi here you have to scroll a little towards bottom and select your language which is English and then click on download this will give you two options so based on your processor select one my system is 64bit so I'll be selecting this one now download the file will be 558 MB so it might take a little time now you can see your Microsoft powerp is getting downloaded so if we go into the download section you can clearly see it so here it is getting downloaded it might take a little time now the file has been downloaded just run the file and you can directly go to next since the default language is in English and in case if you want to select a particular language you can go to the select language option and select your particular language you're searching for it might take a while there you go so the installation process has been started now you can click on next and now you can read all the terms and agreements and click on accept you can change the location if you want but I'll be keeping it as default again if you want to create a shortcut you can on desktop I'll keep it as it is now install it might take a while so power has got successfully installed you can see the desktop icon there double click on it and you can start powerbi so firstly it is open source if you want to have a licensed version of it just get the license or buy now option and for now let's use the open source version so this is the Baria window and those are the charts and these are the data connections if you want to get connected to your data sources you can also have shortcuts to that now what are the steps to connect to data so now we will go directly into powerbi and try to import one by one few most commonly and popularly used data sets which are most commonly used uh in a day-to-day activity rest of course there are uh powerbi supports n number of data sources uh but we will do something practical on the most pop ones so let's let's open our powerbi now this is my powerbi and first I want to show you that how can I import data 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've done is I've have shared this sheet as publish 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 tblo paste it link over here and click okay now powerbi will try to establish a connection with this Google doc sheet because it's published on the web you need to wait for a while while it is reading okay now it has read one of the HTML tables so 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 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 ship date all my data is now ready so I can say close and apply click apply changes now this is an example of web data import you can go and preview your data right now uh 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 some changed some basic stuff and I it's Auto saved contrl s now I'll go to my table and I'll refresh now you can see as I refreshed my power query editor I clicked refresh all and I got my new row which is there in the live data I got that fetched from my okay I got that row the row row ID number 12 so I I have to say close and apply now you can see the new 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 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 a text file called sub categories. 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 sub category ID subcategory name and product category key so basically to which product category this particular sub product belongs to right so what I'm going to do is I'm going to go back to my get data option and I'm going to select text SL CSV option and I'm going to select option mod product sub categories. txt okay so now powerbi 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 powerbi it is like IR relevant to me it's a composite data in import I'm doing right so in my presentation when I'm talking about importing data there are different importing modes right import data import can happen through different ways okay one is direct query mode in which I create a live uh connection to the database which I'll also show you uh using MySQL and mssql 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 our next task 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 / CSV and now in this CSV file let me open this CSV file and show you what in is it so this is a list of all my products product key product subcategory key product uh stock key keeping unit Etc a simple CSV file and I'm going to import that okay so now it is identified the D limiter is comma rather than a tab and it has already recognized the headers correctly so I'll load it okay so now my products are there product subcategories are there for product categories now what I have done is I have created us Excel mode now so now Excel I'm 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 though with different uh uh data storage types but still now the data is imported into powerbi it is a composite data model now another very important data typee which you can import is the PDF also right so what I've 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 birth date 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 going to 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 going to load okay you can rename this as PDF table 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 we want to import is the my SQL Server data set so what I have done is I've already installed my SQL 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 powerbi you need to First download a connector my SQL powerbi connector so you need to go to this link 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 powerbi 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'm choosing department and employee and I'm just loading them okay so now this is the exact data which is there in the employee and Department in my cull okay so this is one example of how to create connectivity between powerbi 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 go to get data select SQL server and for now database is optional I can say direct query click okay 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 Tre Olympic events so I can use probably the customers one which is now you can see this is the data the customers 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 powerbi that this is the data so I can rename is customers from mssql uh and this is from my SQL and okay so now this is not the only uh data sets you can import now if you take a look at the option which powerbi gave of 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 entire folder and uh within the folder whatever uh data types of files are there it will detect it PDF Pary or even SharePoint folder which is itself a Microsoft uh technology then different kind of databases SQL server and my SQL we just saw but it's not only limited to this you can connect to Microsoft Access ssas Oracle database IBM db2 postgress uh sbase Terra data and then sap uh uh databases Amazon red shift Impala vertica Snowflake and N number of databases which are there in the market today uh Amazon Etc then it also allows you to connect with its own power platforms powerbi platforms data Mars powerbi data flows data vers Etc azour there are different kind of storage uh mechanisms in azour and azour itself is a Microsoft Technology uh so it has a compatibility with lot of azour uh based data stor like aour SQ database blob storage uh azour data braks right aour HD Insight spark so if you have those kind of services running on your AER 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 Google analytics Adobe analytics GitHub uh LinkedIn sales if you want to do some analysis of some social networking uh you know feeds that also you can import then other miscellaneous are also there 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 see how to create relationships but just giving you a glimpse that whatever data you're importing powerbi autod 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 have tried to once it is imported into uh powerbi then there is no limitation of how you use it you can create visualizations 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 powerbi and if I go back to my transform data section now as I have now different data sets available with me I have I can do any kind of U 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 column 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 set right look for example I want to create a merge data set of my categories and subcategories so I can say mer select the two data sets and say merge queries as new and I can select product categories and product sub categories select product category key on both the side and then they do a left Auto 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 rename it now to as category subcategory table it's a it's a merge basically it's a join between category and subcategory and 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 and another data set which is text base 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 imported data you can shape you can combine you can adjust you can do whatever transformation you want to do and create your visualization what is powerbi it is a business intelligence tool to visualize your data and share insights across your organization so when we talk about bi it came into existence as a self-service bi tool and it does have different components which can be used now before we get into details of powerbi let's understand what are the different components or what are the different ways in which you can work on your powerbi now one of the main challenges when it comes to organizations or users is that data is scattered in different places it might be in different formats and anyone everyone would want to use that data to basically perform some calculations create visualizations or dashboards which could be interactive and that's where they would want to bring all the data in one place might be transform it so that you can filter out and not load huge amount of data in your system and you can can work on selective data so when it comes to powerbi it helps us in ETL it helps us in data modeling it helps in data storage and Reporting so what are the benefits of powerbi here are some of the benefits so extract intelligence rapidly and accurately so that's basically transforming your Enterprise data into Rich visuals and accurate reports for enhanced decision making now one thing we already know know that when we talk about data data in raw format might have lot of hidden information if we look at different data sets which I'll show you in the process it might have lot of meaning but then the real meaning comes out of the data if we can create visualizations if we can create relationships between different data sets and thus that can help us in enhanced decision making now powerbi supports Advanced Data Services it integrates seamlessly with Advanced cloud services like Cortana to provide results for the verbal data queries as well when you talk about seamlessly integrating with existing applications that's one more benefit of powerbi so it adopts analytics and Reporting capabilities easily to embed interactive visuals quickly in your applications you can build rich personalized dashboards so it basically provides a unified user experience with customized dashboard and reports that meet your exact needs it also has a way where you can have secure way of publishing your reports so you can set up automatic data refresh and rapidly publish reports allowing multiple users to Avail the latest information across your organization or across your working community so powerbi can connect to different sources we'll see that in a while so basically you have an option which says get data and that basically opens up a window where you can find different type of data sources such as Excel your CSV or text Json PDF getting data from databases or directly accessing data from databases now before we get further into understanding how powerbi looks like it would be good idea to share information and how you can set that up on your machine so when it comes to your powerbi and let me open up a notepad here so for example I bring up a notepad let's say when you talk about your powerbi components so you basically have powerbi desktop and that's mainly your playground or that's mainly used for any kind of development activities you have your powerbi server or you can say service now this one is where you would make Reports online and share or make them [Music] available to different buus now that's one more component of powerbi and then you also have your power bi mobile which is mainly for viewing the information or I would say viewing reports so these are the three main components we can also look at the licensing information of these so these are the mains so powerbi desktop is something which you can set up on your laptop or on your machine powerbi server is where you can login with your user idid and password and powerbi mobile is mainly to view your reports now how do you set this up before you can explore or start working on powerbi so here is a link which you can basically use so if you look into this so this one basically says Service s Service sign up for powerbi it says sign up of powerbi service as an individual normally when you would want to use powerbi you can use a website called HTTP and then you have basically app and let's say I think it's called app powerbi.com now this is the place where you can basically login now if you see here I have created an account and if you look at my account it says auat l. onmicrosoft.com now how do you get this kind of email because when you talk about powerbi it will expect you to have a official ID and it does not take IDs which are from common domains such as Google or Yahoo and so on so this particular link gives you an idea how you can do that so basically you have what is powerbi basic explanation on that it says signing up for powerbi service so powerbi desktop it's a totally free download and then you have mobile apps also a totally free download and here it says that what kind of email addresses it supports and if we look into this you have to either sign up because that does not accept your private email IDs or you can go for this one which says enroll US government organization and this is where you can basically sign up for powerbi so it basically says try free if you go to the website say powerbi Microsoft . or you could go into this one which I was saying HTTP SL SL app do your powerbi.com and this is what you can use or as mentioned you can go to powerbi.com for example if I open this in a different tab it takes me to powera Microsoft I can say start free I can click on this it says Drive free but then when it asks you to sign in this is where some of us face problem because it does not take your private email ID now how do you tackle that what you can do here is on this page which says learn about alternate ways to sign up you can basically open up this link and in this link it says sign up for powerbi with a new Microsoft 365 trial account and what you can do is you can basically click on this link which takes you to The Office 365 and what you can do here is you can search for something which says say 365 E3 and here you have tried for free so in my case it is translating okay so here you have an option which says tried for free click on this one and then basically go ahead with your sign up process now once you do that you can basically create an account or give a email ID so it asks you to give an email ID to check if you already have an account and once you do that it will guide you through the process where you can create an account like I have done now once you have done that so for example we can go into my this page which I said HTTP SL slapp powerbi.com now once you have created an account you would be asked to login now I can click and login here and then basically given my password and once I do that it takes me to the powerbi server or service now on the top right it might say that go for a trial version I have already selected that and this is a Pro trial which is giving me validity for 60 days so this is the service which I can use now now what it means is I can be using my powerbi desktop which would be also installed so once you log into this page you can basically click on apps you can basically search for something like powerbi and that will show up an app and you can install and download on your machine now once that is there you can basically bring it up so for example in my case I can just say power bi desktop and that's the app which I have installed on my machine and basically that comes up so that's your powerbi desktop which is coming up and it will still ask you to sign in so that you can share your information through powerbi so you see here on the top I'm already signed in and here it also shows you some tutorials and videos which basically helps you in getting to know something more or what's new so you can always browse that so you would have your powerbi desktop which would be set up you would also have your powerbi service which would be running and then basically whatever you have developed on your powerbi desktop you can share that through the powerbi service now usually when you talk about licensing I can give you brief insights here so you basically have Your powerbi Service as I said licensing so you have the pro version which is basically uh your $9.9 per user per month and basically it has some kind of limitations so it has say Max 10 gab you can work on uh some features like incremental refresh is not allowed you can always look onto the my Microsoft website for more details and in those kind of cases you usually go for the premium account if you are a extensive user and premium account basically is conditional based so it depends on your requirements and then basically you pay for the service what you use so that's mainly about your servicing now when you talk about your server and Service as I said it is basically making your reports online and sharing and making them available to different buus so that is the Highlight so when you talk about sharing reports that's one of the things you have anomaly detection that's also possible here you can talk about automation of reports you have security that is you can go for role based or roow level based kind of security implementation and all those are some of the features of your powerbi server and service so it is good to know and basically have your desktop and powerbi Service setup now once you have that then you basically have your powerbi now when you talk about your powerbi it basically helps you with various things so this is how easily you can have it set up and then basically you can explore this so for example as I was saying powerbi can connect to different data sources sources now I do have an option here which says get data I can click on this and that shows me all the different data sources I can even click on more if I'm interested in looking what more powerbi desktop tool helps me to do so it shows me all the different ways in which you can get the data you see here the servers so the database Services your folders your different formats you can click on file formats or databases you can look at Power Platform so if basically you are connecting to a platform and getting some Services you can connect to the cloud that is aure you have online services and then you have other options so these are all the ways in which you can get your data when you talk about visualizations you see a lot of visualization options here which can be used once your data is loaded and I will explore and explain more about this so you have something called as insert wherein you can go in for different visuals or different types you can also get into say transform the data now that basically is going to pop up and bring a power query editor now that's where lot of your ETL Works happen so when you when you do a transform data it opens a power query editor which we can use to transform the data or change the data or modify the data as per our requirement before for loading all of it into our powerbi so you also have option here which says modeling where in we can create new tables or we can work on our data where we can manage relationships so as of now we don't have any data so it does not show this one as activated but that can be activated this is where you can view your reports so this is in short exploring your powerbi we'll see what are the different options which we can use here so it basically supports different kind of data so when we look at the visualization pane that basically allows us to create different kind of visualizations here and we will understand that so you can basically visualize on your different data and create different kind of charts graphs maps and basically derive insights from your data now when you talk about data models that's where you can basically establish relationship so when you talk about data models it is basically used to connect multiple data sources to build a relationship now we might have different data sets or we might have data coming in from different tables where we may want to basically get insights or get data from multiple data sources for our purpose now in that case data models do help us so for example if you have two tables let's look at the standard tables so you have products lookup table and you also have the sales table now usually what you have in any kind of scenario is if we basically say you have your data tables so that's basically where your data resides and then you have have series of your lookup tables so usually you might have say your data tables here and this is what I'm talking about which might have some data for example let's say sales is one of them you might have some other data table which might be for example let's say budget table or might be something else and these are your data tables now at the other end you might have your lookup tables so basically you have various lookup tables and these lookup tables for example let's say this one is customer this one is territory let's say this is product and let's say this one is a calendar so these are basically my lookup tables so we can basically as I said your data might be coming in from different sources let's say database or let's say some kind of files or let's say some kind of systems what you have so your data might be coming in from different places now you might want to transform the data so this is where I could say there is your query editor which I was explaining so you have your query editor which basically allows you to edit the data table or basically allows you to edit the data before it is loaded right you can hide a column you can add a new column you can modify your column so your query editors would be basically used to work on the data which goes into your data tables now you might have lot of lookup tables as I said so let's say these are my lookup tables and these are my data tables so what we need is sometimes we need information based on our lookup tables and data tables now that's where data modeling comes into picture so basically if I would want to extract information from here so I can notice that there is a product key here and there is a product key here now this is where we are already talking about say foreign keys or we are talking about your relationships right now when you talk about relational databases you have something called as foreign key constraints which is in in powerbi terms I would say it's not exactly a constraint but it is more of a filter propagation instead which is used to basically connect your different data sources and you could have basically crossfilter directions you can go for single or one to one or one to many or many to many kind of relationships which basically allows you to work on the data so we will learn more about data models when we are doing a quick demo there where we can talk a little bit about normalization and denormalization the way the data exists right and then you basically would want to gather insights from your data so when you talk about your two data sources as I said so you have a products lookup table you have a sales table now if you would want to calculate the total order quantity of each product name which is we are talking about the order quantity as a information here and you have product name here now how do you get that information what we see here is we would want something like this or we would want more information so how do we do that so what we can do is the order quantity for each product is BAS basically showing us the same value now this is because the product and sales tables are not connected and there is no relationship between them or even if you would want to take two sources and just get information out of them powerbi I would complain that there is no relationship established between them so what we do is we create a data model so what we do is we build a relationship between both the tables using a common key column which exists in both cases as I said there is a product key here there is a product key here so that basically allows us to have a relationship between these two now that could be one to one this could also be related to other tables so it could be one to many you could have many to one so all those relationships are possible so product key is basically used to create a relationship you see the arrow Mark and then there is also this star which can basically mean one to many now when the product key is used to join these two tables or form a relationship then we can look at the product names and the order quantities which basically gives me a total now that's the basic use of your data models now what about this Dax so basically data analysis Expressions now that's say library of functions and operators that can be combined to build formulas and expressions and power VI desktop usually when we work on our data sources when we would want to connect them it automatically shows us these data analysis Expressions however this gives us extension it basically gives us more power to work on our data now sometimes instead of working on Dax or Dax Expressions it would be good to go for better data modeling and have the relationships established better and sometimes when the relationships are established you could use your tax which basically gives you more power on working on your data so your values are calculated based on information from each row of a table it appends values to each row in a table and stores them in the model it increases the file size so that's what happens now you can right click on any column to add a new column and for example in this one it shows that there was a quantity type calculated column which was based on the calculation what we see here on the top in the in the expression bar which says what kind of calculation was performed and that basically gives us the value for quantity type and that is basically added here to our existing data so that's the power of Dax and powerbi so you also have something called as uh measures so Dax allows you to create new calculated columns and measures so basically here if you see we are working on AW score sales and then we have selected quantity sold and what we are looking in the report is we can basically right click on any table name to add a new measure here so we have added what we call as quantity sold as the measure so values are calculated based on information from any filters in the report we will see this how this can be done and that basically here the measure does not increase the file size it does not create new data in the table themselves in comparison to what we were seeing earlier that is your calculated columns so these are your type of Dax functions so Dax allows you to create new calculated columns and measures so you have dat and time which basically allows you to work on date and time data or Fields you have logical functions which allow us to create new filters or add more filters to our data you have text functions which basically allows us to say transform the data into a lower case or an upper case or basically get the length of a string or concatenate two fields or basically uh do a filtering based on some criterias or replacing some content you also have statistical functions which can be used you have information functions which can be used so these are different types of Dax functions which we can use in powerbi so we will learn more on powerbi through a quick demo where we can U use some data sets and those data sets could be found on internet although I can also upload that on a GitHub link and you will have access to those data sets so let's learn about powerbi through a quick demo by uploading some data sets and playing with those data sets let's look at a sample data set and let's upload it and as I explained while uploading let's also transform the data so that we can have selective fields or selected data loaded here instead of loading the complete data set and then let's see how we can visualize this or how we can use the information in this data set now what we can do here is we can click on get data and then we can choose one of the data formats which we would be looking for so for example I can go for Excel and basically click on this now here are some of my data sets so let's look into the folder here and these data sets are also available on my GitHub link which I'll share with you later so here we have something called a super store let's click on this and here I already have a data set which is global super store or I also have selective data so let's select this one click on open now that's basically connecting to my data source and that will show me what does that Excel sheet have so it has different tabs which is orders people and returns so let's s orders and that basically gives me a preview of the data which I have and if you scroll all the way to write it shows me city state and then country and if you see here we do see information of all the countries now this can be huge amount of data which may which we would want look into but say for example my use case is that I'm interested in looking for the data for United States and uh as a country and all its states so we will do that when we do a transform now we can also select the returns Tab and that shows me these three Fields however the first row should have being the heading of this uh particular data set and we will transform that now I can go ahead and click on load but that will load all the data so instead of that let's go for transforming so let's click on trans form data now once you do that it brings you your toolkit that's brings you your power query editor which allows you to transform your data so for example we have uh data from returns tab or returns data source as you see here so we see column 1 column two and column three and that also shows the type of the data here it also gives me a quick small option here let's select this and then I can say use first row as header now there are various other options which you can do you can add a custom column you can add column with examples you can keep the top rows you can remove the top rows you can keep errors keep duplicates so there are different ways and you can also do a merge query or a pend query so as of now let's just say use first row as headers and that basically shows that now my first row has become the header you ALS o see in the applied steps it basically tells me if I have changed the type of the data if I have made any other changes those steps will get added here so it basically shows me the name as it returns it shows me applied steps where I've changed the type and now I basically have this information now this is something where you can change the type or you can basically set it to a particular format however we are not doing anything of that sort right now so we can do that for any of these columns so this looks good when it comes to orders let's click on this and as I said I would be interested in selecting for country as United States only and let me just work on that data however we can work on all the data so let me scroll all the way to right and here I have the country now there are these filters which we can use so basically I can click on this and that shows me all the countries are selected now there is also something called as text filters which we will see how we can use to select particular data I basically have other ways of filtering the data so for example now I will just uncheck this select all and what I would be interested is in United States so let's type it here that shows me has an option here select this and and then basically say okay so that should basically now filter out and it shows me the data is United States only and then you have different states and rest of the data remains so if you look at the applied steps it tells me that there are filtered rows now we have done the basic transformation for this data set on these two data sources that is orders and returns so here you have an option with says close and apply so close the query editor window and apply any pending changes you can click on this and it says apply so for example I can just say apply for now and that should basically apply the changes which I have performed using my query editor that is I have transformed the data so that I can have selective data uploaded in my powerbi now it's doing that it shows me it is working on both of these data sources that is orders and returns so that's done and now basically if there are any other pending changes we can just do a close and apply so that basically has closed and now you would see the data appearing here so I have uploaded the data as per my preferences now if you click on the data tab here so it basically shows you your data fields it might take some time to populate but if you see in the country field now if even if I click on the filter it just shows me United States now that's what we wanted so we have already uploaded this data in orders you can always expand the option here which shows me all the fields which are there in this might be this is an aggregation might be that's an order date so this is again some kind of aggregation we can change the data types so we are looking at all the fields in my orders table or basically coming from the orders data source I also have returns which shows me three fields and that shows me the data which is returned order ID and region so the column names are applied correctly as we want and that basically looks fine now we can also look at your model so when you click on model it shows me these two however there is no as of now relationship established between them so it says under properties select one or more model objects to set their properties so right now these are not related there is no relationship established between them so if I would want some data which relates to orders and returns then that would fail because it would say there is no relationship now I can go to the first option which says report and we have not created any report here although we can create a simple report we can look at the data so we have our orders field now we can basically pull out some information from here we can choose what kind of report we may want to create so for example let's go for uh the table option from visualizations you have various options here which we can use this is where you can do a formatting this is where you can select the fields this is where where you can search and filter out the data you can also add data fields here so first let's click on table and that basically gives me a table now this table as of now does not have anything so you can use the filter and slices option here which will affect the visualization or basically what you can do is now since we have orders here so this is my orders and I would like to work on this so let's say for example country is I can select country as a field and if you say it shows me country is all as of now and it says the value is United States what I can also do is I'm interested in the states so I have state now I can basically drag and drop it here and then the state gets added here so I can basically say select all and that should basically take care of my State field being added here so state is all country is all now we can basically look at something else so might be let's choose sales and I can just drag and drop sales here so that basically says if you would want to have any kind of advanced filtering which says filter type so go for advanced filtering is less than or equal or you can also go for for advanced filters so that's fine as of now so we have added some Fields here and that's basically my data here so let's go for filters here which basically should select all my Fields now if you see the visualization shows my country sales and state which we had either by selecting the fields and dropping them here or you can in this section where it says field so you can for example let me show it again so I can just delete this I can click on the table option I can just drag it here I can basically make it bigger and I need to add data to this one so it says add data fields here so now let's say country is what we are interested in we are also interested Ed in States so let's drop that here and let's say sales so this is also what I'm interested so I'm looking at one specific country I'm looking at sales per state and when we look at sales it basically tells me that this is summation so you will basically get the total sales which have happened now we are looking at this data here we can always go to formatting we can click on grid we can basically increase the font here we can change the grid color so for example let's make it for example blue so I can just select this and it should basically allow me to have the grid color as blue now I can go in for the grid thickness I can go for rope padding outline color so we can basically make it a little bit more readable and then we can basically increase the font size here to look at the information and you have other options here so what would you want to do with column headers so I can basically have the font color background color is fine do you want to have an outline do you want to have a change in font what is the text size might be we can make the heading a little bigger and then basically you also have the field formatting so all those could be done you could go for background and all these things can be done in formatting so now we already have our data here and this basically looks good and this is basically one of my visuals which I have here and this has given me some information for sales and basically I can scroll this I can also make it bigger so I could select a particular field if I'm more interested in looking at the information for a particular State now I can add more fields to this so this is my one of the reports which I have created now what I can do with this report is I can basically have more data fields I can add filters to this I can basically look into all the data here by just clicking somewhere in the grid but somewhere outside if you select a particular row then that data shows up here you have an option of focus mode which you can go for you can look at the other options which says export data now if this is the data which you are interested in you can export it you can always do show as a table if you are interested in you can do a sort by country sales or state so for example let's do a sorting by state and that basically gives me the data which has been sorted by state information now we could obviously have the information here so I can then change the order so it shows me alphabetically this is the information which I have so I've created a simple visualization using the data which I have and what I can do is I can click on save so that basically asks me to save this as a powerbi file which has an extension of PB iix and I can basically call it my report so let's say first report and here I can say country or I can say state wise sales in USA let's say USA and and that basically is my first report now once you have saved this report you can always look on your machine for example if I go in here and if I go into this folder might be I should look on desktop and this is where I should have saved it so it shows me first report statewise that will open up in powerbi and we have created a simple report which We have basically used by taking our data now I can also do is I can publish this if I would want to share this information so publish this report online in powerbi service you can basically select your report what we have here and I clicked on publish so it says what's the destination so you can have different workspaces I will choose the default that's my workspace I can click on select now it says publishing first report statewise sales in USA to powerbi you can create a portrait view of your report and you can do all that stuff so let it publish and then we can basically look into our powerbi server that's our service where the information is already shared or published I would say which can then be shared with different sources so we can come here and basically I can look into the powerbi and this is where I will be able to look into my workspaces and let's look in my workspace so it says this is the place where I had initially downloaded powerbi data set it says your data set is ready let powerbi help you explore your data right so you can always do this you can click on view data set which basically allows you to bring out your work space and first report state wise sales now that's the report which we have published so let's first check in our desktop if that's done so it says success open first report in powerbi now I can click on this one straight away and that takes me to my service now once it takes me to the service it shows me the report which we created which we published and it basically has the option where I can save it as a a different copy or give a different name I can embed this in diff in a website or a portal like can publish to web embed this report for Public Access by anyone on the internet we can do that we can export it to PowerPoint so we can do all these options you also have an option of view where you can change the view you can basically also edit report here so you can do that if you are in interested in something specific you can do a sharing to teams so if you have your teams or group setup you can share it with them you have an option of common pains you can basically view usage metrics report now that can be sometimes helpful you can basically go ahead and go and subscribe a particular report so if there are new changes made you will be the one who will be informed you can click on share now if I click on share here from my service it says only users with powerbi Pro will have access to this report recipients will have the same access as you unless Ro level security on the data set further restricts them so I can grant access and this is where I will have to give the email IDs of the people with a message that I would want to have them look at this report right you can also allow recipients to build new content using the underlying data sets and you can send an email notification to the users as of now I don't have any other groups so I'll not be sharing it but I have created a simple report now let's also look at edit report let's just to see what it helps us and when you click on edit report it basically brings up this one which says your file view it gives you the filters it basically allows you to add data fields to this so it is basically giving access to these data sets which were in my desktop it is basically allowing you to give or create different visualizations now here we have the data which we are looking at and if say for example somebody is interested in filtering the data so you could do that so you could click on filter here and that basically applies this is the filter we have now country is fine sales might be I can click on sales and I would say okay let's look at sales which is more than a particular amount so we can say is greater than and might be I can give a number here so I can say 30,000 and basically I can say apply filter so right now I'm applying filter and I would look at the values which also shows me the total value is changed so you have not only created a report you have published it and now from the service you can edit it so I'm looking at particular data here and then basically I can click on file and I can save it or I can say save a copy of the report and let's say I will call it the same name so I'll say first report score state wise sales and I will say modified so let's do a save and the report has been saved so now you're looking at the data here so that is basically giving you information so when I click on my workspace here I can click on reports and that does show me my previous report it shows me the modif report it gives me an option of looking at the usage metrics report so say for example you want to click on this one and it will basically give you the usage metrics so let's click on this one and that basically shows me the report usage metrics which is generated so views per day unique viewers per day you would want to look at the different platforms who was using it views by user and this can be sometimes useful if we we would want to look into this one now I can go back to my workspace I can click on reports and that basically took me to usage metrix I could be sharing it I can analyze in Excel I can look for quick insights based on this data what we have you can basically look at the related information you can also look at the settings of this one and basically this is how you have your data report here now that also shows me the data sets option so which basically gives me the data sets which can be used to create further reports and we have our data here so for example if I click on create report it basically gives me these data sets and we can continue working on this so this is how I have a simple report created without basically working on two different data sources but I have selected some data here and then I can basically add details to this so for example now if we look at orders and say for example this is the data I have and say you would want to add some Fields so let's go to returns and say for example I would be interested in looking at the the products so might be what I should do is is I should replace the report here instead of country it would be interesting to look at the product which we have or basically customer ID so we can look at customer ID we can look at the order ID which would be interesting to see if there is a particular order what was the sales which was generated and if there were any returns which were happening on that so for example here when I have these let me cut out country as a field and I will basically take order ID and place it here so now if you see my data has been easily modified so I have my order ID I have sales and I have statewise information so you have basically all the information but this is now order ID State and so on now what if I would want to also see based on the order ID if I say I would want the returned field so for example I would want to take this one and let's drag and drop it here now that says cannot display the visual now why is that so if you click on C details that says cannot determine relationships between the fields so it cannot display the data because powerbi cannot determine the relationship between two or more fields and how do we fix that so for example if I click on F fix this now it says there is a missing relationship between these fields use autod detect to search for relationships or create them manually now I can click on autod detect which will try to search for fields which exist in both the data sets or basically I can create relationships so let's click on create relationships and that basically takes me to this page which says there are no relationships defined from table to table and so on so I can click on new now here it says select the tables and the columns that are related now I can say orders now those are my Fields where you have order IDs and it automatically shows that returns also has an order ID field although all the values might not be same but this is how you can create a relationship and it says the cardinality says many to one so you can have BAS basically many to one relationship you're saying cross filter Direction is single so make this relationship active and it has already helped us basically identifying the field so I can say okay so it says now these two tables should be related or should be connected based on order ID so here we have this and let's basically say close now once that is done if you see I have order ID I have returned I have sales column and I have state and if you see in returned I do have a value of yes which shows this particular order ID had generated some sales and it was for state Alabama and it was returned and the value is yes so if you scroll down you basically see all the values now we can add different filters where we can say I would want only yes and no now this is again an interesting report so let's go in also into the formatting and what we can do is we can look at say the grid option if we would want to basically say vertical grid and let's say on and it says vertical grid color so let's select this might be I can try doing a black here it puts it in a right nice table format and that basically looks good so you have sales returned and so on and this is basically the order which I'm seeing here so for example if I would want to change the order and if I'm saying okay I would like to look at sales and returned and so on so we can be doing that we can come here and say for example I have sales and returned let's try moving the sales column over here might be state is uh an information which we would want in the beginning so let me also move the states all the way here so it gives me state it gives me the order ID sales and if there was a return which was happening on that particular product so easily I've modified my report now what I can do is I can just save it and I can do the same thing so I can publish it I can basically continue using it or I can work on a new report so let's continue learning and uh now here we will also see how you can load some data and perform some Transformations and basically get multiple results or multiple tables or multiple data sets which can be then further used for reporting so powerbi does give you a lot of options now here you have an option as we saw earlier that is get data or what you can do is you have an option where you can create new data also which says enter data now this is something which can be easily used if you have relatively less number of fields so you can basically add more columns here and you can basically add values so for example if I would just call it something like um scientist ID okay and then I can say scientist name and then basically I can say uh [Music] domain and then I can say for example let's say ear or of joining and and I can keep adding the number of columns here I can delete the columns right and I can give this and I can say country so that's it and now we have created these five countries uh sorry five columns which we have given some names and we can start entering some values so I can basically say let this scientist ID be 2234 I can give some name so let's give Peter let's say domain and I can say biotechnology I can give year of joining 2011 and I can say Germany as the country and I really don't want this particular column so I can go ahead and delete this now I can come here and then I can give something else so I can say this is scientist ID I can give John let's say the scientist is mainly working in physics and then I can say 2018 and let's say France and go back here and let's say 4567 and let's say Marie and let's say uh she does her research in uh molecules and let's call it 2000 one and let's say Italy okay and you can continue adding datas in this way now you can say if the data is already here so I can say for example no I do not want this particular row I do not want this particular row so you can basically keep adding values here now you can say edit so here we have to give some Nam so let's say scientist okay and then if you choose edit it basically brings up your powerbi editor which allows you to work on these fields if you would want to make some changes now we can make some changes here we can see what are the number of rows we can basically perform any kind of transactions here so this one basically tells me what is the data type so here we see scientist ID and this clearly tells me this is an integer however we will not want to do any kind of computations here so we can as well change this so I can just do a right click and I can work on this particular column what I can also do is I can just click on this and this tells me that you would want to change it to date time or you want to change it to some number so I can just say string because we are not going to perform any computation here so on the ID column so let's say text and I'm changing it so it says replace current add a new Step so selected column has an existing type conversion would you like to replace the existing conversion or preserve the existing so I will say replace current and now the data type has been changed it is of a string and if you see this step has got added did here so it says that the change type is the kind of transformation we did here so that's fine now what we can also do is we have the scientist name but we don't like the column name here so it would be good to change this so there is something called as remove or remove columns and so on so when you do a right click it gives you a lot of options in applying filters or doing some transformation if I just just click on this one so what I can do is I can look at the date time and let's go here so we have an option which says do you want to change the type now we could have done that here or like I said you could choose the time and do it you can just say transform and how do you want to change it so do you want to change it to uppercase well you can do that and changes all the values of this one what I can do is I can again do a right click and I can choose basically if you would want to clean up the data or if you would want to convert to lower case you want to capitalize each word so let's choose that and if you see here the steps are getting added now to undo any particular step if I just cancel this then I'm back to this if I cancel this I'm back to my original form so you can anytime undo your changes and you can basically work on this so we are working on this particular column now there are various other options that you can look at so for example these are this is some of my data but it does not have much information it would be good to load some bigger data set and then use these Transformations or basically working on changing the data types as I mentioned or if you would want to do a filter and remove certain fields and only select particular Fields if that's what you're interested doing a right click where I want to create a copy of this and then basically I can use that particular copy now what I can also do is I can add column from examples I can duplicate column and then I can make some changes to that so this is my duplicate column and say for example you would want um this one to be changed so we can say remove duplicates we can basically if you're doing some kind of change you want to change the format here you can do that you can use for other things like filling up and all that now I can just call it rename and let's say um Alias scientist name right and I can continue adding columns or I can do some Transformations and once you have done with these transactions now this one if you see it shows as integer but is it an integer no this is an date format so I can go for modeling and change the formats what I can also do is I can select this and it says it's not a decimal it's not a fixed decimal it is date time it is date it is date time and time zone right and you can select any one of these so for example let's make it date which makes it more meaningful but then what happens is when you do this it is going for the default dates or the older dates so I don't like that so what we will have to do is we will have to basically transform this and here we have transform you have change type so you have date and time you have date you have time so let's choose date and time and if you see here it gives me some default timing based on on these values which we do not like so again filter it out but what we can do is we can just make sure that this is changed or you want to make it a string because we are not going to do any computation here so I can keep it as date but then if I have more Fields like month and days and so on then you can do that so here it also has the options so when you have selected this you have an option called transform and transpose uh sorry transform also has various options which allow you to work on these do you want to do some scientific calculations do you want to work on the date field so as of now it is just integer so we can use one of these we can do a group by but obviously we don't have much data here so as of now let us retain this I can just change this to text and that's okay because we are going to look at this later we can add more data and work on it so as of now once we have done all these changes you can go back to say home and here you have close and apply and I can basically say close and apply so the changes will be applied and then my new table which we just created by entering some random data performing some basic Transformations will be available so if you look into this one so that's where my data is it shows me the columns but there is no aggregated column as such here you don't see any summation Mark you just see the field names or the column names you see the values now obviously if you go into modeling there is no or there is no existence of a different table which you can join these tables or you can perform some transactions come back to the data set so this looks good and we have it here now obviously we have not created any visualization based on this which we can and we can continue working on it so here this is my data set which is a small table where we have created some data and we can use it anytime now let's work on a bigger data set and see what kind of Transformations we can do we can then also see on modeling or basically using some smarter ways of working with the data so what we would want to do is we would want to load some data here so let's go [Music] into sorry let's go into home let's go to get data let's use our old store data and I'm going to take the global super store which is huge data set with all the countries and the products and the sales which have happened and we can take this data but before before loading it as I suggested earlier we should basically transform the data we should basically transform the data so that you don't end up loading everything and you don't work on all the data I mean unless you really want to so here I will this Excel sheet which I'm talking about has two different tabs we have used it before so let's use orders let's use returns and that basically shows me the data what we have so I can do a load but that's not what we would want to do so you can do a load and you can get all the data but let's go to transform make some basic Transformations before we load the data so our power bi editor allows us to work on this now here we have it says this preview may be up to 9 days old so I can do a refresh I can select this and I say first thing is use first row as header because that's what I want so it is basically setting the first row as header which looks good and we have some order IDs we have the returned If the product was returned so let's look at the filter here so it just has yes values which we are looking at so it says list may be incomplete let's say load more and let's see what are the filters here so either there might be a product which is returned or it might be blank field so that is chosen so that's fine we have order ID we have region which is basically showing me different regions here let's look at orders and orders is again having your row ID order ID order date ship date ship mode so you have quite large amount of data here for your different countries now in earlier example I chose United States and then I was only focusing on the states and city in United States so we can do that now I can basically work on refresh so whatever preview was stored in the memory we are just refreshing it now here we have this row ID and if you see the row ID is an integer obviously we will not be performing any kind of computation here so here you don't have any row ID but here we have row ID so let's make it uh from integer let's make it string and I'll say replace current so that's that looks fine it's say row ID which we will use to search but we are not going to perform any computations unless you want to find on an average on row ID or anything else now you have order IDs so what we can do is we can filter some values we can rename the field as we desired right so for example let's go in here and what we can do is let's go into uh customer ID ship okay and here you have say State you have region okay now at any point of time if I would want to filter out the values the easier option is that you can select on this one and here you can do some text filtering okay or you can basically select the values from here so for example if I say let's get rid of select all what we will be interested in uh say United States and UK that's the data we want and then I can basically say okay so it is basically going to filter out the data which is for United States and UK as of now now what we can also do is how about doing some more filtering before we basically work on this so let's go in here and we are looking at United States and UK related data and let's look at it is sometimes good that you can basically uh work on the data here so for example I go into orders now what I would want to do is I would want to look at the fields okay and we want to we have not yet loaded the data so if you look in the background I just have my scientists because we have not applied these changes we have not loaded we are still in the transformation stage right now what we can do is let's go for so we can do segment and all these kind of fields can be used for grouping the data so we will see that whenever you have a data said you would already know there are certain fields which have repeated values which can be used to group the data and we can do that we can change any kind of values which are not going to be computed on so for example I have postal code I can use this but again we are not going to find a postal code which is greater than something right so integer is not the valid type let's change it to text okay and uh if for example I would want to look at this so I have done some changes here okay and this shows me null so for example let me just rever this back and let's make it a whole number okay so there are certain columns or rows which do not have some values and we can basically get rid of those values so as of now we can do a filtering here so let's keep the postal code AS integer or let's change it to text do a replace content and now what we will do is we'll scroll all the way right might be I'm interested in technology category so that's what I'm interested in so what I can do is is here I can do some filtering so I can basically choose technology now the easier way would be since these are categories and there are only three categories we really don't want to go and apply text filters here but if you had something like an office supply and um Office Inventory then you could have done some text filter so let's not do some text filtering here what we can can do is I would be interested in technology so that's the field I'm interested in so now you are only having data which is related to technology okay and you have some product names so this is where we want to do some kind of filtering so we can rename the field we can select some Fields so let's for example let's go for uh any of the field which I think might have more entries here so for example let's go for Canon Wireless or Canon Image right so let's go for product names and I will say let's go here now I could have done a transformation but that's not what we want we want to do some filtering so let's go to text filter and here I can say begins with I can say ends with I can say contains so let's go for contains and it says enter a value here so you would want to keep the rows where the product name contains something and it gives you some suggestion right so where you are seeing in some values here so for example if I would have selected this then it applies the complete thing but that's not what I want so I will get rid of all this and I will say it contains Cannon now I can go for advanced filtering also okay wherein you can select Advanced and then you can give different columns and what do they contain what kind of values you're looking for so you can do that but we will not go for advanced in one step let's go for basic and let's say okay and now I should get all the products which are Canon and you have different products so this is one kind of filtering I've done it tells me what is the category of this it is machines it is copers it is obviously belonging to the technology category we are looking at uh the market which is for this particular data when you look at the country we are still focusing on United States and United Kingdom that kind of data right so we have filtered the data we have done some uh selection based on on the data here and what I can do is I can then basically rename a particular field so I can do that I can say I'm interested in um sales which is the data here we can basically look into the quantity so sales is something which we are interested in but might be we are interested in sales which are more than a particular value I'm not interested in lower amount of sales I would want to look into United States and UK data but I'm interested in sales or I'm looking at if the discount percentage was something or if the profit was more right so we can apply different kind of values here but what we can do is with these changes because you don't want to transform and make changes all here you want to make the changes once the data set is uploaded so we have selected some data we have create done some Transformations we can basically break a particular column into multiple columns if that's what we want if we see that we will do an aggregation based on ear or we will do a aggregation based on the country or year and Order ID right so we can break this data into multiple columns so we can do that but for now let's do a close and apply and let's apply this so that's going to apply all these changes it's going to load my data but remember now we are having selective or selected data which gets loaded so that should get be available here so it takes time sometimes so you have to wait and then you can go ahead and check here so for example now I'm looking in tables let's basically minimize this let's go for orders and this is the data I have which obviously row ID if you see so you have all the row IDs and again again you can do filtering here but this is where you have already loaded the data the data is available and then you can start working on the columns here so we have this if you closely see we see this summation Mark and this basically means that these are my aggregated columns or these values are measures which can be used for calculations so we can see that we can create our own aggregations so we can do that we can rename the field as we have seen we can filter out the data so we have all the fields showing up from orders and let's also look at the returns which basically has the columns the order ID and your region so it basically shows the region here where was the product returned from and we have this information so what we are doing is we when we were doing a text filter in the previous example remember it was case sensitive okay and you have to take care of when you're doing a text filter you have to give a field which exactly matches as it exists in the content now okay this is the data we have and let's look at the order column right now what we can do is we can do some quick Transformations here and we can basically look for more data here so let's say we have uh some filtering to be done now I can do a filtering based on my uh product so we had all these products but now let's do a filtering on product and then products you have copers you have machines so you have many two categories right and when you look at machines it basically talks about your PC uh something so let's look for machines and here we have basically The copier fields which are more so I can basically go for filtering here and that's the data we have so we don't want to really unselect any of these here but what I'm doing is I'm saying text filter and let's go into this one now and let's say contains okay so I'm saying contains and then I can basically say uh let's say copier now that's what I'm interested in and uh show rows where product name contains copier okay now you can give a and condition here so if you would want a specific copier if you are interested in okay let's also say uh contains and let's go for laser also laser and let's say okay so you see the data gets filtered out here and uh we have the sales which we are seeing here so let's go to sales and we will be interested in anything above 500 so let's go to sales let's go for number filters let's say greater than and I will say for example 500 oh sorry 500 okay and uh let's say okay so that basically filters out the data and then I am also interested in quantity Where I would want the quantity to be more than one or more than two so for example so let's go in in here and you can basically choose what is the filter you want so you can apply any number of filters now you have the filtering here you can always click on the filter and if you want you can just do a clear filter and the filtering will be gone so you have all the data right and this is the particular data we have and here I have say for example product name now I can keep the filter because I'm interested only in these values or I can filter out so this tells me that when we did a add it is basically going for laser and copier right so let's say for example clear all filters the thing is gone what we can do is let's go here go for text filter say contains and here I will say it can be a copier okay or so last time we did a and and I'll do a contains laser so let's choose this that gives me more entries so either the product name has copier or it has laser it has the quantity it has the product name and we have all this detail here now what we can also do is we can do some transformation on the product ID so for example you have product ID or order ID so order ID shows up as uh looks like the country name uh the year and then the order ID so we can basically split it up so I can select this and here I have option of let's go to home so when you have you have selected this column so you have an option of transform data here so use the power query Editor to connect prepare and transform the data so if you really want to transform the data here if I basically select this if I just do a right click here now here it just tells me do you want a new column do you want to create a copy of this column okay you want to create copy table so this is your transformed data what you have and for example let's create a copy table and let's come in here so you should be able to see your copy table now so let's go in and select this one and what you need to do here is creating a copy of this or copy table would not be the right option what we can do is we can work on transforming this rather than doing it from here so you have this option of ADD a new column going for a new measure renaming it right so that's okay but what we can do is Select this particular column let's do a home and first thing is let's go to transform so I want to transform the data now let's select this one and it brings up your power editor again here we were interested in orders so we are looking at our data here now if you you see my country is United States and UK so you can confirm that if you look at category it is technology product name has copier and Laser as we selected so those things are retained so you have not lost any changes as of now so this order ID column what we have now as I said you can be doing a filtering okay you can select this particular column and then you have other things which can be used to transform here like you want to change the data typee you want to use first row as header you want to replace some values okay you want to run some merge queries you want to do some analytics so all these options are here now what we can do is while this column is selected I can do a right click now there is an option called transform which is basically going to help me in changing the data here okay now I can basically duplicate the column so I really don't want to work on this column it's self but it would be good to have a duplicate column on which we can work on so I can do a split column here if I would want to but let's create a duplicate column so let's say duplicate column now that gives me a duplicate column so we can rename it later so now I will not work on my original column but I'll work on a duplicate column and what I'm going to do is I'm going to basically transform or split this so again do a right click now you have a split by so we can say split by by D limiter or by number or by characters or by positions so by lower case and upper case so you can do all of this so let's go for by D limiter now if you see it basically identifies the D limiter which is hyphen or Dash now you can go for split at leftmost identifier right sorry left most D limiter rightmost each occurrence of D limiter and that's what we want to do you can look into Advan options where it says split into columns so do you want to split that into columns do you want to split that into rows because that's more or less like doing a group by and you can say number of columns to split into so we have here 1 2 3 4 values so that looks good to me and uh let's do say for example if I choose three so I can choose three and then split using special characters so you could do that so let's for example let's say okay and let's look at the data how it looks like we can anytime delete the data we can keep it the way we want right so what we did was we created a copy of the column then we did a split and what we have seen is we just have three columns now if you see the fourth bit is gone fourth bit doesn't show up right because I just did a three as the resulting column so I have the order ID okay and we can check if there is already an order ID column so we have order ID but that has the complete order ID here and the relatively product ID so you can see the customer ID you can look at this one so it basically has as your order ID and then let's look at the fields here so we have the product ID which says Tec M A I'm looking at the first one 370 and that has in no relation to the order ID right so we can make sure that there is nothing which is conflicting with our entries now once that is done so we have order ID which which can be used to categorize the data you have order ID which is basically the year okay and you have order ID which is basically having some more value now I can keep this data as I want so I can basically click on this I can go for renaming and I can say let's say let's call it order ID and let's say IDs so let us in case there is a particular column and you would want to look at so just give the name correctly now order IDs is f so here we will also rename this one so let's call it uh order year and that's going to be order here so we can again change this to string okay and here you have the order ID so let's rename this one and I have my let's call order number right so we have we are seeing all the steps which we have added here we just split the data based on the D limiter and we have now three new columns which have got added to our existing data which was already filtered and we have done some splitting up of data by creating a duplicate and then renaming it right so if you go and look at your Transformations now if I would have done a split here straight away then my original column would be gone but probably we want the original column because sometimes you may want to search order ID with a Consolidated information sometimes you may want to segregate it based on year right now we have the year field or order date field here you have the ship date right but then might be you want to just aggregate based on year you don't want to really spend time in aggregating or extracting the month and day and so on so my these three columns can be useful now what I can also do is I can merge The Columns if I want so we have split the columns but what I can do is I can say select select select so using your control and now do a right click so you should have an option called merge columns right now this is something which is uh we would want to merge so let's say merge columns and let's say do you want to keep a separator so yes yes I want to keep a separator but might be this time I will give my separator is a uh colon and then what is the merge column name you want to create so let's call it something like um order okay uh and then let's call it num right so some sometimes renaming the fields to a name which makes more sense or based on your naming convention is good so let's do a merge and now what I have done is I have done merging of those columns so I split the data I merg The Columns and now if you see my The Columns which I had created those columns are gone because you did a merge you did a merge and now you have the fields which are either uh earlier you had something which is separated by a hyphen and here you have something which is separated by colon right now anytime if you want you can unmerge this by removing this step right and you can get rid of this merge column so if for example I would do that so I have my data back right I have my data back so what we could have done is we can select this okay and then what we can do is like what we did earlier so you can basically go for removing the columns okay you can do a merge column okay you can select one by one and create duplicate of those and then you can merge them right so all the possible options are there so you can the best option would be to create a duplicate of these columns and then basically merge them as per your convenience so might be you can say order IDs and order number is the pairing what you want ear is something which you don't want right because we already have the date field so I can basically say remove and this one is gone now I will select this and this and let's go for merge columns and I want to give a separator which is might be without the ear and you have space or you can go for custom like earlier we had give a symbol and then what do you want to call it so let's say order specs okay right so this makes more meanings because we already have the date field so why do I want the year into my order ID so I can always be doing a segregation now based on order specs right now this is some simple Transformations what we are doing here we are seeing the data which we have okay now what I can do is I can basically first apply these changes so that all my changes what I have done are applied right now once these changes are done we can basically go ahead and save this file so I can just say save I can go for save as I have different other options so if you would want to perform keep performing your transformation then you can just do this you can add a column you can view the data so for now our Transformations are good enough and what we can do is we can basically go back to home we can do a close and apply and we will be back to our data set which has been modified so we can see if the data what we have has been transformed so we have order specs here that's good we did not do any filtering or um we have the filter left here which B basically tells me that there are these different fields we have not removed them but what we are doing is we are just applying a filter to choose copiers and Laser Printers so that's what we have here and this is good enough now what I can do is once this is done I can basically save it so I would want to call it some kind of report if you would want to create Okay so so let's call it as uh uh let's say second report and here I will say country and Technology specific info let's save it and and now basically I have saved my data so what you can do is you can go [Music] for creating other report or basically having this information published if that's what you want to do if you want to go for visualization because right now what we are seeing is we have lot of data here we have lot of data here it shows me there are these uh tables or data sets which we have worked on that shows me here in the models but there is no relationship in them if you go into visualization then you don't have any option or you have not created any visualizations based on this data but if you go here now based on the data what we transformed if you see we have order specs right now that's what we chose we basically have other fields so you have Shi date you have aggregated column s which can be used for visualizing and we can work on this so I can come back here and what I would be interested in is this data set is fine but I want to do some grouping I want to basically have some selective data in this and for that what I can do is let's go for ship mode now this is something what we have so we are in this data field now we have this transform so let's go back to transform again and let's choose our orders so that's the data we have and now if you see here this is you know huge amount of data what we want is we want to group them based on the shipping mode so here you have a option called Group by so I can select this I can go in here I can basically work on okay get rid of duplicate values but that's not what we want to do you want to do a group buy so as I said you can do a group buy from here or you can choose from the transform option above and you can do a group buy so let's do a group buy now how do you want to group the data so I'm saying I want to group the data here based on shipping mode or which is the other column you want to use to do a group byy so let's go for shipping mode and what is the new column name right so we want to basically find out that you want to go for ship mode but that's not enough I mean I can do a ship mode and I can do a grouping by but you want to just count the rows no that's not what we want to do so let's go to Advanced so ship mode is fine so that's your grouping right but then what we also want to do is we want to do a grouping based on say sales so here for example let's go for sales okay and what should we call this so might be we can say uh shipment wise sales whatever you would want to call so you can basically get the operation do you want to really count the rows no we want to basically do a summing or we want to might be find out an average price right so let's do a sum okay and here I would want to do a summing based on say for example sales so this is what I'm going to use for getting a count of the sales now grouping by might be we will change this instead of uh instead of sales we are using using a ship mode what we can also do is let's go for a segment and that would be valid grouping so it will take a combination of ship mode segmenting group the data based on that and then get me the sales which is which is basically let's call it shipment wise sales so it gets a sum so let's do a okay and that's my more relevant data which I'm looking at so I'm looking at the ship mode I'm looking at what is the segment and then I'm looking at what is the total sales there I'm looking at again the ship mode standard and home office so always remember when you are choosing multiple fields or multiple columns for grouping by you are basically having a combination of two Fields so that has to be unique and your grouping is done based on that so now you're looking at the sales wise and this is something as an important information what we have so we have done some transformation and what you can do is you can use other ways like you can use pivote to get individual values from it you can run on merge queries which is basically running some merge queries and merge the query with another query in this workbook if you have so you can go for for this now I can basically go for apply and close so just to add to the group by step what we did was if you see here I have removed the group by filter which we just did couple of minutes back and what I have done is instead of transforming your complete data set you can basically create a copy of it so for example I can just do a copy and then I can come here and do a based and I have done that and I'm calling it order summarized but this is my complete data now what we will do here is we will basically go ahead and do a grouping byy again like what we did earlier based on your shipment based on your segment and based on the sales so that's what we are looking for so let's go for the ship mode and we go for group bu and and here you would want to go for advanced to ship mode and this one I will go for Segment so that's fine now we want the new column name so let's say shipment wise sales so I want to do a summing not the counting of rows and I want to do a summation based on sales like what we did earlier and then you say okay and that basically gives you the data here now what we have is we have the resultant data based on the data which is coming in from here we did the same thing just 1 minute back but we worked on the original data set so what I did was I created a copy and now I'm working on this one so I'll say close and apply and now we are back to our our desktop so it is applying these changes where we have done some Transformations we have done some grouping by and what we can do is once we have the data here we can anytime look at our data sets or tables so this is my original one if you see now I have order summarized I can just pull out this information I basically have my returns which we have not really touched and we have the scientist so we have all the four data sets here now what we can also do is let's look into order summarized and we just have this data here so this is fine and you can continue working on this you can basically merge columns from two different data sets and then you can get the merged column and you can rename it so you can obviously do that you can basically do a uncheck whenever you are working on these data sets so what you can do is if you would want to work on transforming for example let's go back here and say I want to do some transformation on it so how did we do it we just did a transform and you can go back to transform so so you have this data here and the data what you have you might be interested in transforming this into something else and then you don't want to maybe load this data so you have this option where you are selecting orders and then you have something called as enable load which can be unchecked so when you do a uncheck what will happen is whatever changes you perform only those changes related data set will be load Ed and they will still be available but this will not affect your it will not affect your original one so for example let's say copy and let's go in here I will do a paste let's say orders oh I did a paste I need to rename this so let's renaming let's say orders and here I will say summarize and I will say us so I'm renaming it now let that get loaded we can perform some Transformations on it so I have here where is my country so let's look for Country yeah and let's look for Country so here I will go for only United States okay that's what I'm interested in and then I can choose well I interested in just Central us so I can basically go for just the central us and I can get rid of all of these so now I should have only Central us data and this is fine and this is the data might be we are focusing on right now for doing some visual ization might be looking at sales might be looking at the product name so you have the product name now remember you don't see any filters here right because the filters are coming from the resultant set and your transformation so if you have any filters you would be seeing in the top row now this is the data we have Let's uh let's not restrict it to region region would then can reduce my data but that's good enough for us I can just say no I'm clearing off this filter I'm still looking at United States but I want to look at all the regions yeah what we would be interested in category and that's anyways chosen as technology which we had chosen when we were loading the data we have copers and machines right and we can basically keep this now this is fine I can apply the changes I can apply the changes based on this one and what I can do is I can just say for now apply so that's going to apply all the changes which you have done in orders or summary or the new one right and what I can do is now I can choose order and I can say don't enable this in load so it says disabling load will remove the table from the report and any visuals that use it columns will be broken we are not creating any visuals as of now the table will be removed so that's fine and now I will say close and apply so what happens is you are loading the data based on the changes okay now you have your order summarized you have returns you have scientist but you basically do not have orders anymore so that was not loaded so so I only have this one I only have this one and you have returns you have scientist you don't have the orders column right now that particular data set was not loaded at all because we did not choose that to be loaded right now while I'm in this orders which is let's see here and actually you can drag in yeah so you have order summary and this has basically kind of data which I'm looking for so I really don't need the ORD stable so you can do it in steps and you can aggregate this you can have aggregated data you can have all the data which is filtered transformed grouped by and then basically just load it and the original data set which you used that's no more being loaded here now at any point of time if if I really want I can go back to transform data and remember it is still here it is still here it's not gone right so you can basically select this and you can say enable load and you will have the data back which you can continue working on right so these are some quick Transformations which really help us in working on the data now obviously if you have data you want to perform some left joints right joints joins you have inner joints outer joints so you can always do that you can take two different data sets might be I'm interested in taking the orders summarized which talks about standard class consumer shipment wise sales and here if I look at order summary I have other details but the thing is we need to make sure that these have the values these have the values say for example the segment column here here and the segment column here can really be used to join these two tables so if I create a relationship or if I create a join I can basically merge the data so this is how you work on data we will also see some more examples on might be modeling the data using some Expressions to work on the data so we already looked at creating a report selecting particular fields and then publishing the report onto your powerbi service now this is the report which we had created which says State order ID sales and then I also added this returned field and that was basically by creating a relationship between order and returns which we can also have a look in model so this is the relationship which is created if you just place your cursor here it tells me that we have a relationship between order ID of returns and Order ID of orders and that basically allows me to join the data bring it in my one report now this is basically your data sources you can look at and if you click on your visualization so that shows you your report now what we can also do is we can make it interesting now we would not want to scroll through the fields to see wherever or what was the order or what was the order ID which was returned now I can do a sorting I can filter out what I can also do is I can use this option which shows slicer here and that basically allows me to work with this data so we have this report here and basically as I said you can click in here it shows the data now what I can do is I would want to filter out information or slice the information using your returned either being yes or having a field which has no value how do we do that so basically I can drag and drop the returned here as a field which comes from returns now that basically shows me only the value as yes but I do know that there is there are some fields which are blank now how do I add filter to this so I can click on this one and then I can click on slicer now once that does so it basically pulls out all the different values so you have either yes or you have the blank field for returned so what we can do is we can select yes you will see only the orders and their IDs and sales where the products were returned so that gives me 10 18,18 and I can select blank so that will basically get me all the orders or products which were not returned now this is a simple way wherein I can add a filter to or a SL ER to my report to basically give viewers a choice of uh selecting different fields and you can add any number of slicers you can basically say I would want particular kind of information so for example if I go into orders and we know we have the state now I do have the state information here and basically I can if I would be interested I could filter this out in my report itself I can sort it I can look it in a different order what I can also do is how about bringing in state here and basically dragging it here so that gives me the state option it is giving me a visual which is basically giving me a geographic location of all these points so yes that can be good what I can also do is I can keep this which basically shows me the state map mightbe it can be useful you can zoom in you can zoom out you can look at specific information here you can drag and drop here so that's fine what I can also do is I can again take the state and bring it here and that basically can be instead of my map I can go for slicer so that gives me all the values here and which basically allows users to choose the fields or the states which you would be interested in looking at so for example if somebody is interested in looking at the data for Georgia just select this one and you see the map automatically shows you where in the map that's the place and it shows me all the sales for Georgia State now I can also basically select yes and that shows me which were the orders which were basically returned so that gives me a quick overview of statewise what is the geographical location if the orders were returned or mightbe I can just click on blank and it shows me The non- Returned orders I can again go here and uncheck the joio option and that shows me all the states now once you have done this this looks like a comprehensive report which can be useful for the viewers for your management team and so on so we can just do a save and that basically is saving my report so I have this report now what if I publish this report so I can just do a publish and when I publish it says okay workspace so let's say select and then it says replacing this data set may impact two reports you already have a data set named by this one view the impact I would say replace or I will say view impact so that basically takes me to the powerbi service because sometimes we may have some reports which we have already uploaded and updating an existing report might basically affect my existing report so it basically shows me the impact analysis it shows me one workspace there are two reports they they have not been added to dashboard but these are the ones which will get modified so let's for example as of now go ahead here and let's look into my powerbi so go back to your desktop and I'll say no I don't want to replace so click on cancel so I do not want to publish it what might be what I can do is I can try saving it as a different report so let's say save as and now I will basically say additional filters okay let's save it so now you see the name on the top changes to additional filters now it's save to publish this and now I can go ahead and publish it select your workspace and basically it says this is the report being published with our additional filters with a map which gives a geographical uh area showing us the information and then basically what I can do is once it is done I can look into my powerbi service like we did earlier you can do a filtering we can basically query this data we can share it with other users who might be interested in looking at this particular data now this says it is done so it says get quick insights and might be it's a good on option to look into what kind of insights it's it gives you so click on that see the beauty of powerbi where it tries to search for any kind of insights which it can gather from the kind of report which you have built now once the insights are ready it will let you know what we can also do is we can come in here click on view data set so you see now it shows your additional filters and here you have option where it should be showing your report so let's say View and view is fine so and if it doesn't show up sometimes it might be taking time for refreshing so you can always go here and then you can click on your report and that should get populated so this is the report these are the filters which I have gives me an option of choosing all the states and basically allows me to edit the report and look at all the information here meanwhile we can see here it is still trying to gather some insights from the data and now you see there are some insights here so it says sales which is coming up and a subset of your data was analyzed and the following insights were found so you're looking at sales by ship mode so it's a standard class second class first class and same day so there were different shipping modes in our data and that's what it shows me the sales which one had the majority it shows me the profit so which city or state had more profits so New York City has noticeable more profits here average by shipping cost by subcategories so there are these different subcategories which we can look at such as copers and machines have noticeably more shipping cost profit by product name and you can basically look at at the row ID and quantity so this is where you're looking at a regression analysis you're looking at row ID and quantity so there is a correlation between row ID and quantity so these are two different variables or fields which are related you're looking row ID by category row ID so it says California has noticeably more row IDs sales your profit count of region and count of returns so there is a core relation again between two different variables average of shipping cost now we would have taken lot of time in building all these visualizations but powerbi has already helped you in gathering all these insights and then you can basically select which one of these is what you're interested in you can focus and look for more information based on all the fields it has given you good amount of insights which will help anyone who is looking at this particular report so that's your quick insights here and we have this information now once you have this information this is basically where you have your focus mode so it says subset of your data was analyzed following insights were found you can basically say download and uh here you have other options which allow you to work with your powerbi so so let's look at this one so this is where we have our report and we can continue exploring it more one more interesting feature which powerbi has other than having your insights ready to use which is basically in your workspace and you can basically use these insights what we are looking at various options here and basically you have this option where it says spin the visual if you are interested in a particular Insight you can always pin it which you can always go back and look into what you can also do is you can also click on edit report here now that's a report which has already been published to your powerbi service not yet shared but that can be shared or that can be subscribed now once you click on edit report it has option of reading View mobile layout you have basically basically an option which says basically options for navigating through the data set you can go for how visuals on the canvas interact with each other you have all these options and one of the good options is ask a question so you can always click on ask a question and that basically B says some suggestions now you can open this and it says okay ask a question about your data try one of these to get started what is the average sale sort orders by order date sort orders by product ID how many ship modes are there compare quantity and discount so do not worry that your report has only four Fields so it has State order ID sales and returned with some filters but what about you looking at how many ship modes are there so it is already looking at your data so if you click on this one here and you can see what are the different fields what we have and you have ship date and ship mode so this is one of the fields which it is showing you to ask a question now what you can do is you can say how many ship modes are there let's click on that let's ask this question it says four ship modes and do you want to add this to report yes you can you can let it be as it is so you can basically keep this question here and here you have an option the visual is showing number of ship modes when you place your cursor here if you see here turn this Q&A result into a standard Visual and let's do that and basically it is saying number of ship modes right so now that's the power of your uh bi power bi which has basically allowed you to ask a question and quickly add a visual to your report now what we can do is we can basically save this or you can say save as and give a different name so might be I'll just say save because I would want to have this information and the report is saved so in this way you can basically not only create smart visuals you can not only relate different data sources using data models or relationships you can add maps you can add filters you can add quick questions you can basically just go here and say for example you would want to ask a different question now we see the fields here how about looking at the shipping cost right so here I can say um what is the uh highest shipping cost and that shows me some suggestions let's select that and that shows me the value which is great and I can keep it as it is I can basically convert this to a visual I may want to keep the question because might be this question was asked and uh you would want to see the result here might be you can move this somewhere here and that gives me some kind of question which was asked and we can do a save and that's my report which has been saved in my bi service the data set is still there you can basically go ahead and share this how to connect different data sources with powerbi so in this session today we will see how can you connect different type of data types data sets like Excel PDF CSV Etc to import in powerbi for your data visualization needs 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 how to import them in power VI and use it for your visualization purpose now what are the steps to connect to data so now we will go directly into powerbi and try to import one by one few most commonly and popularly used data sets which are most most commonly used uh in a day-to-day activity rest of course there are uh powerbi supports n number of data sources uh but we will uh do something practical on the most popular ones so let's let's open our powerbi now this is my powerbi and first I want to show you that how can I import data 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've done is I've have Shar this sheet as publish 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 it link over here and click okay now powerbi will try to establish a connection with this Google doc sheet because it's published on the web you need to wait for a while while it is reading okay now it has read one of the HTML tables so 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 opportunity for me need 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 ship date all my data is now ready so I can say close and apply click apply changes now this is an example of web data import you can go and preview your data right now uh 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 some changed some basic stuff and I it's Auto saved contrl s now I'll go to my tblo and I'll refresh now you can see as I refreshed my power query editor I clicked refresh all and I got my new row which is there in the live data I got that fetched from my okay I got that row the row row ID number 12 so I 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 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 a text file called subcategories do 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 name and product category key so basically to which product category this particular sub product belongs to right so what I'm going to do is I'm going to go back to my get data option and I'm going to select text SL CSV option and I'm going to select option mod product subcategories do txt okay so now powerbi 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 powerbi it is like irrelevant to me it's a composite data in import I'm doing right so in my presentation when I'm talking about importing data there are different importing modes right import data import can happen through different ways okay one is direct query mode in which I create a live uh connection to the database which I'll also show you uh using MySQL and mssql 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 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 / CSV and now in this C CV file let me open this CSV file and show you what is it so this is a list of all my products product key product subcategory key product uh stock keeping unit Etc a simple CSV file and I'm going to import that okay so now it is identified the D limiter is comma rather than a tab and it has already recognized the headers correctly so I'll load it 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'm 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 product categories product subcategories though with different uh data storage types but still now the data is imported into powerbi it is a composite data model now another very important data type 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 birth date 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 going to 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 going to load okay you can rename this as PDF table 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 we want to import is the my SQL Server data set so what I have done is I've already installed my SQL 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 data base Connection in powerbi you need to First download a connector my SQL powerbi connector so you need to go to this link and then click on download and install the my SQL connector based on the operating system you have you click on download and install it after you have done this go back to powerbi 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'm choosing department and employ and I'm just loading them okay so now this is the exact data which is there in the employee and Department in my C okay so this is one example of how to create connectivity between powerbi and my SQL 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 and I have used the SQL Express so this is the name of my server so which I'll copy the server name and go to get data select SQL server and for now database is optional I can say direct query click okay 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 employe Tre Olympic events so I can use probably the customers one which is now you can see this is the data the customers 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 powerbi that this is the data so I can rename is customers from mssql and this is from my SQL and okay so now this is not the only uh data sets you can import now if you take a look at the options which powerbi gave of 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 entire folder and within the folder whatever uh data types of files are there it will detect it PDF Pary or even SharePoint folder which is itself for Microsoft uh technology then different kind of databases SQL server and my SQL we just saw but it's not only limited to this you can connect to Microsoft Access ssas Oracle database IBM db2 postgress uh sbase Terra dat and then sap uh uh databases Amazon red shift Impala vertica Snowflake and N number of databases which are there in the market today uh Amazon Etc then it also allows you to connect with its own power platforms powerbi platforms data Mars powerbi data flows data ver Etc azour there are different kind of storage uh mechanisms in azour and azour itself is a Microsoft Technology so it has a compatibility with lot of azour uh based data storages like azour SQ database blob storage uh aure data brakes right aure HD inside spark so if you have those kind of services running on your AER 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 Google analytics Adobe analytics GitHub uh LinkedIn sales if you want to do some analysis of some social networking uh you know feeds that also you can import then other miscellaneous are also there web based Hive R script python script if there 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 see how to create relationships but just giving you a glimpse that whatever data you importing powerbi autod 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 have tried to once it is imported into uh powerbi then there is no limitation of how you use it you can create visualizations 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 powerbi and if I go back to my transform data section now as I have now different data sets available with me I have I can do any kind of U 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 set right look for example I want to create a merge data set of my categories and sub categories so I can say mer select the 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 Auto join 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 rename it now to as category subcategory 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 and another data set which is text base 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 imported data you can shape you can combine you can adjust you can do whatever transformation you want to do and create your visualization data modeling in powerbi now today we will discuss how to create relationships and different kind of data models within powerbi based on the structure of data you're importing okay so what topics we are going to cover today we 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 you imported it what kind of cardinality you have to basically highlight right and there are different type of cities which you might have heard earlier also if you are from plsql background like 1 is to one one is to many Etc we will we 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 number 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 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 dimension so 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 which he has purchased and you will make a join between customer customer and product table and even uh you can make a join between product or product type or customer or product type customer table will also have a key to the product type so this is less uh conducive for reporting but it is more of a transactional uh relational model but of course this is also feasible but from the powerbi perspective when we talk about reporting and visualization this is the most extensively used dimensional data model and this this is what we 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 uh create certain of our uh data sets and then we will Import in our sample powerbi file and then slowly slowly we will create the relationships now one important thing which you need to understand that in powerbi if you go to powerbi there is an option that that powerbi autod 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 powerbi will autod detect and secondly if suppose you are importing two different kind of data sets one is Excel one is csb and if powerbi detects a common column key columns it will autod detect a relationship which you can go and later change modify manage in your relationship uh menu manage relationship menu in powerbi which I'm going to show you okay so if I open a powerbi 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 checked you can uncheck it and auto and manually prepare relationships there's no limitation to that but if you uh keep it checked then powerbi 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 customer perspective it's a many to one relationship okay second other cardinality is 1 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's 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 many is to one so in customer table only one customer record exists per customer and one customer can place many orders for multiple products and can also have multiple delivery addresses so that way this is a typical 1 is to 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 pra practically and in our case we will see that like for example you have placed an order for a particular product 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 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 you need to import is the master data so first I 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 like 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 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 product 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 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 I'm assuming that powerbi have autod detected the relationship now you can see that because I've already uh ticked that check box now let's see what powerbi what relations powerbi has autod detected let's first check the relation between customer and sales I'll double click this uh join 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 uh Place many orders right and that is that it has detected by the quality of the data and the data sampling which powerbi 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 powerbi is detected it is up to the discretion of powerbi internal um configuration and algorithm but you can go and change it so this is now you can make 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 powerbi many sales orders for one product you can for Simplicity sake you can say products sales product key is the join now just focus one more thing please uh also see the co 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 active 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 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 the 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 oneway 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 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 okay you can see the single arrow is changed to Double Arrow it means it's a it's a both side filter So when you say a both side filter it means that implicitly within powerbi you can imagine that all the product columns now with 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 40 20 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 joint between customers and product basically you can't dve 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 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 you want to 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 twood 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 1 is to one 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 autod detected a 1 is to one relation reltionship what is the meaning of 1 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 1 is to one and the cross 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 1 is to one record so now you can see that with 1 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 with that so this is an example of 1 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 1 is to one relationship and then and if you drag customer key uh report takes time to render and even if 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 one now I want to show you an example of many to many now for that I'll import my fulfillment data set 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 powerbi detected I'll do one thing I'll select sales over here fulfillment over here and order number to OD 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 cross filter detection uh a direct but you can you can check that but definitely uh powerbi 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 okay now you want to keep uh the uh direction as both ways or One Direction that is up to you the way you want to uh 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 Sal sales to fulfillment and click okay okay now we have our all our different kind of relationships over here uh which we have tried to short list one too many many to one one to one which is uh this example and many to many now if I show you further relationships which you can keep on adding like for example I have have uh uh the example of territories now in which particular territory the sales was done so I can map it over here okay so now it's a typical 1 is to many relationship because territory is my master team 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 adding data then you have uh details of returns now this is another transactional table which is about about the orders which have been returned rather than being you know returned by the customers so you have a product key H so automatically powerbi 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 to many because 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 table so if I talk about a typical dimensional model the Fulfillment table sales table and the tror table sorry uh the Fulfillment table sales table and my returns table are the fact tables of my data model so what exactly you mean by data transformation in PBI so data transformation can be a little similar to data cleaning so before any kind of data analytics you might be receiving raw data from a website Source a c database or an Excel file or multiple sources right once after you receive the data whether it is batch mode data or stream data you are supposed to clean it right you might have to clean up the discrepancies like the blank rows or the blank cells or any irregularities in data such as wrong data type right such discrepancies from the data should be eliminated in the first stage of data analytics so that's exactly where data cleaning and data transformation comes into the picture so you have various tools for data cleaning and data transformation like Excel SQL but if you are a powerbi user then good news for you power query and powerbi can assist you in terms of data Transformations so in this tutorial we will be discussing the fundamental the most important day-to-day data Transformations which a data analyst takes care of in the process of data analysis is what we going to discuss today so let's quickly switch to power Pi but before that let's have a overview of what kind of data we are exactly dealing with today so we are dealing with Superstore data and that's in Excel format so this is our Superstore data set and we have four tabs here the first one is orders tab where we have customer ID order ID date ship date ship mode and customer name and in the second tab we have store sta set which has the details about the customer from where he or she is the state City postal code and which category or subcategory that they purchase and Order sales discount profit everything right and here we have some information about any of the orders which were returned and some people over here so these are the four tabs that we are dealing with today now that we have an overview on the data that we're dealing with let's quickly now switch to PBI so now we are on the PBI window you can just discard changes there you go let's quickly import the data from our downloads this this is the data that you want to deal with today now it might take a little while to connect to that particular data set and load the data on power PN just a couple of minutes since the data set is a little too heavy it's about 10,000 so let's wait shouldn't take long there you go the data got successfully loaded so you have the option of loading what kind of data you want you have four tabs as we just discussed you can load the orders Tab and you can load the stores Tab and in case if you want the returns tab you can also load that and if you want all of those just load all of those right now I just want the two tabs order and stores now here I can just directly load to get started working on this but in case I don't want any kind of discrepancies in case if I have a doubt that this data might not be cleaned I shall go with data transformation so ideally you should go with data transformation check your data first before any kind of analytical processes so let's go with that transform data and shortly we should be having the power query window open on our desktop screens so there you go you can see the completed data set has been successfully uploaded both these stores and ERS data sets now uh let's quickly check the data from our data set so here you can see we have orders date right but I can see 4 2682 and 4253 this is not in the form of dat correct so this is the simple uh uh step that we discuss changing the wrong data tyag so you can just click on the lower arrow button over here and uh you can right click and here you have an option to change the type correct so it is considering it as a whole number which is wrong so you can change it to date replace current so now we trying to change the order date from a data type whole number to date data type so there is some error if you click on error or if you just navigate on to it powerbi should be able to show you what error was it in just in case if it's not working okay we are unable to pass the value provided so you can just remove that and try it in a different way change type to date and time zone date I think this should be helpful or if it's not working here let's quickly check what could be the eror and now let's try to refresh the data it is taking a little while uh we also have another information that we deal with we have the first row as row headers so we should be declaring PBI that we also have a row header over here just taking a little while to refresh meanwhile let's quickly check into the stores data and here we have customer name so let's try to apply second di proof data transformation which will be like let's say split column we have uh the complete name of U the customer let's try to split it into first name and second name so by the limiter and we can give multiple options over here left mostly limit let's say a person has three uh parts of his name first name middle name and second name or the last name so but we just wanted to split into first and second name so we would go with the first left mostly limiter and split the data set press okay and it should help us to split the data first name and second name and we can also name the column separately as first name and second name instead of custom wiing it's taking a little while than the ideal time but it's completely all right since considering the 10,000 rows of stores data and 10,000 rows of ERS data it is all right not a problem now let's try to take a look at the order status it it has successfully changed it no it is still showing us an eror I don't know what's wrong here it's maybe incomplete let's quickly refresh all instead preview and check if can it can help us so there you go after refreshing the first order date is changed to D type so what we missed is when you change the type you're supposed to add a step so what exactly I mean by that now we are trying to change the second one as well so just wait for a while and it will give us a choice if we want to add a new step or not that's when we select the option yes please add a new Step so what do I mean by steps so applied steps you can see something over here right so every alteration every change change or every modification that you're doing every transformation you're including on your data will be recorded as a macro so that can be implemented if you are loading a similar dat set for the next patch let's say this is 2022 data and if you trying to analyze the 2023 data and every column is similar and you can just follow these appli steps and the same implementation will be automated you don't have to spend time and doing the same process once again there you go so this time it has recorded this step and it automatically take has taken a new step over here change type step one change step step two if you don't want the step to be added you can just select the red x mark over here it will remove it and similarly when you when you go back to the stor data set here you have the first name and second name split successfully and uh now uh let's say you want to have unique uh customer data right in that scenario you can just even remove duplicates from this particular column just remove duplicates and you'll just have unique data it's possible that one customer might have come here to buy the same uh you know buy the same product from different dates or One customer might have uh done a repeated purchase so it's possible but again if in case if you just wanted to know if there is a way to eliminate duplicate entries then you can do that so for now let's not delete the customer IDs here because one customer might have visited the same store for multiple times and might have purchased a different uh product or might have made the same order with multiple products right so there is a possibility for that let's not disturb the data set so I just wanted you to know if there is a way to eliminate the duplication from the DAT is set yes it is now let's also check another possibility of data transformation so let's say you wanted to add a new column and identify or include some mathematical operations for now let's say I have sales quantity discount and profit but I don't know what's the rate right so here the sale is for2 $61 and quantity is two but I don't know what's the rate of one for so I can include that you can just select the last column or where you want a new column just go to the add new column here and here you can just uh choose the custom column should be somewhere over here yeah this is the custom column and now if you click on the custom column you can rename the custom CL column to rate of product and here you can choose the mathmatical operations to be applied on the columns so sales column insert or double click divided by quantity okay now it should give you the rate of each product individual product it is taking some sizable time which don't take so long so there you go you have the rate of product and now let's say you want to combine multiple data sets for for example here I have stores data set but my stores data set doesn't have any data related to orders and orders does not have any data related to customers now I want to combine these two is there a way yes you can do that now let's get back to stores and here go to home option and here you have something called merge queries so now the stor data set has been selected in the first data set here just select the dropdown and select the second data type just orders so here you can see stores with the current one which is in the first place now select based on which primary key you want to combine both so I want to go with customer ID because both of my data sets do have customer IDs so I'll go with customer ID and both data set will be combined at the end at the last column powerbi will show me a new table not a new column it will give me a table combined with all the columns in one column you just have to expand the column and select which columns from the second data set you want to include in your overall data set so let's do that practically so here you can see I just have tables if I select the expand column option over here you can see I have a lot of columns here so just deselect everything I do have row ID I do have a customer ID what I need is order ID order date shift date shipment mode and customer name I do have it so that's all I need so just press on okay and I should have them included in my new data set Al together it might take a little while so there you go we have the new orders ID order date shift date and shift mode added to our data set now just click on close and apply and your data set is already ready for data analysis Supply changes it is taking a little time so at the end you just have to click on the apply changes and your data will be ready for analysis so that's exactly how you can perform data transformation in powerbi desktop version so there you go the res set got successfully loaded all over here and you can just drag and drop them onto the visualization spot and you can work on your data yes yes so now the power Pi visuals are on the screen you can see we have file home and add data to your report visualizations filters data Etc right for now I think we don't have any data we can quickly import the data onto this particular platform and starter analysis but before we get started with that everybody knows there are simple tricks to create all the type of visualizations right if you expand if I if I could expand my screen here you can have all these visuals and let's say if I just double click here and if I want any type of visual it'll just show me the visual right let's say I want a bar graph for the sales happening in all the regions so I'll just double click I'll tap it down give me a bar graph for sales right it will give you and just in case if you wanted the py chart for the country wise you can just give Countrywide sales by chart right but now let me tell you a few things correct so if you are using a pie chart and if you are dealing with let's say a lot of companies or I mean countries a lot of countries like 20 countries or 25 countries right in that scenario pie chart look a little clutter all right and let's say you wanted to find out the sales that are happening in the region wise and you asked power to create a line graph for that that would not give you the proper visual that will not give you the proper visual ization to be honest right so as a beginner if you are learning powerbi the most fundamental thing is not just understanding how the tools in the powerbi work what kind of visualizations you have the most critical thing that you will need to know after understanding the basics is what type of chart is to be used for what kind of visualization right so that's exactly we are going to discuss today what kind of data requires what kind of visual ization let's say I'm working with date data type let's say I'm working with sales data type let's say there is a requirement where I need to present the data in form of table or a card then what are those scenarios and if I want to use the pie chart or bar graph then what kind of data types that I need to deal with what are the situation where I need to use the bar graph and where I shouldn't right a few basic fundamentals that we will be dealing with today so we have uh a wide variety of bar graphs in the first row and a wide variety of line graphs in the second row which deal with the line graph area area graph ribbons Etc and in the third row we have waterfall funnel scatter plot pie chart donut chart tree map and next we have map and right filled map and we have uh the gauge cards and everything right and here we also have a kpi right so we'll also go through a kpi uh type of chart and if you want want to learn about the kpis exclusively then we have the kpi tutorial and powerbi lined up in this particular Series so you can also go through that and after that we have some slicers and we have tables and we have Matrix right so a wide variety of charts available correct so we will try to understand the major ones the first true bar graph which are the scenarios where you need to use the bar graphs next R we have the line right line area so all these Comm the one uh kind of set they are similar to one another so which is a scenario where you can use a line graph which is a scenario where you can use a waterfall or a funnel chart which is a scenario where you can use a scatter plot which is a scenario where you can use a pie chart right a a wide variety of uh probabilities a wide variety of permutations and combinations so what are the exact needs what are the exact visualizations that carry a value that carry a meaning to the stakeholder which are those particular charts which explain the numbers in the right way possible so that's the point of today's discussion so to begin with let's import the data first from the Excel document so we will be using the Excel data over here just click okay now you have a wide variety of tables which are present in that particular workbook we are dealing with the data tab so just click on it select and load if there are any mistakes or if there are any discrepancies power bi will let you know and it will also it has the caliber to also fix those particular data rows right so there is one or seven errors right now you can choose to close but the overall tutorial is about learning the type of visualization right so seven rows out of 10,000 will not make a huge difference for us right now because we are trying to just understand the basic understanding of what type of charts to use so I'll just quickly close this but in case if you want to fix you can just click on this View errors and the power query will open up in the background you can just switch to the power query Tab and fix the data rules where they really need to be fixed right so let me close it for now and now in a short notice you can see we have the data loaded here now we have two types but if you let me let us imagine that you're on tblo and on table you have two things dimensions and measures right so measures is self-explanatory anything that deals with numbers is called as measure and anything that deals with characters or names or anything such which is not a number is considered as a dimension right so here we have category City Country customer ID so these are the ones which can be considered as dimensions and where you have a summation logo or a mathematical symbol right in front of it can be considered as a measure right now let's say we wanted to create the month-on-month sales report correct which deals with the data type so just uh data data type right just remember whenever date is included in your parameters either you're adding it to the rows or columns just make sure that you are using the line chart which is over here so let's quickly rename our page to line charts or graphs correct now we have named that now you can either choose to double click and add that and then here you have x-axis and ya Axis or you know you can just drag the order date expand this order date and the hierarch is over here so if you want to go with the month on month you can simply add this to x-axis and you can have your sales data somewhere over here drag this to Y AIS this is one way of creating it but in case if you don't want to waste time in this particular huge uh process you can just choose to Simply double click on the canvas and write it on here line chart sales month click and there you go right brilliant now if you choose to Spotlight or you can just expand this right and if you choose the spotlight you can see a vertical line showing you the numbers correct we start from January and all the way to December right and here you have the monthon Monon sales so remember whenever the date data type is included in one of your parameters then the best way to represent the best way to visualize the reports will be the Lang dra right with that let's switch to the next one now since we were supposed to discuss the bar first but let's it's it's okay not a problem so let's discuss the bar charts now let us know the data set first so we have sales profit discount numbers Etc right and we have region wise city wise category wise country wise so let us go with category now let us go with category and try to build a bar graph just double click anywhere and category okay bar graph okay if not category you can also choose to have subcategory Okay g r a p just take care of your spellings I think I missed an R over here bar graph subcategory enter sales there you go so you have your chart right here if you just expand it you can see we have a wide variety of subcategories and powerbi who was kind enough for us to you know sort the data in form of highest uh um ticket price or highest selling product in the first place and the lowest selling product in the last place so this is how the bar graphs work but just in case if you you can tell me why couldn't we go with a let's say pie chart right let a soldier check there if I just click on this what happens you can see a lot of cluttered data right you have a lot of subc categories here more than maybe 10 right so in this scenario the visuals are a little displeasing and you don't have the accurate value over here just undo it and you have the bar graph which is a a little clearer to look at a little more you know decent to understand what's happening you can just focus on the top five over here and you can understand what's going on right now you can just rename them as and you can choose to go with any kind of bar graph vertical or horizontal or in case if you have a few more let's say we go with the category first and under the category you have subcategory then you can also go with that and you have an overlapping type of graphs over here stacked bar graphs which can show you in that particular category which was the product which was highest selling right now you can also add that whereas the category right you can just choose that okay so I think we can open a new tab and okay so we have the paragraph next we have waterfall so let's open a and let's name it as waterfall so waterfall is uh something which get performance of that particular uh property or that particular entity let's say I wanted to find out the ear on year or wise performance of that particular subcategory in those scenarios I can go with waterfall so just simply double click and ask PBI to create a waterfall model waterall chart Enter space sale there you go so it's giving you the period over period sales report of subcategories and the green line indicates that it is slowly growing in terms of sales and sub and the red one shows the decline in sales which is not happening anywhere and this is the total the overall you know the overall subcategories so it'll give you a waterfall model and the overall total of that particular uh segment is this much and now we have dealt with subcategory uh waterall model and let's try to create a ribbon chart so ribbon chart chart and the area chart those are similar to one other stacked area area and ribbon chart so ribbon chart will give you the performance of that particular product in the uh timely if you want to see the performance of that particular product in measured in terms of time let's say uh month on month quarter on quarter year on year day by day that can be done using the ribbon chart similar to the line chart but anyways let's let's take a look I'll name it as ribbon and uh just double click the powerbi canvas ribbon chart I just ribbon subcategory enter sale there you go if you write order month you can also track the monthly performance of those products right you can see a study deine you can see a study upline and uh so here you can see the months and here you can see the different colors of months the different colors represent the months so here are the products and based on the products how is the monthly performance right is it declining or is it uh increasing can find it out you can basically find out that and uh moving ahead we will deal with u scatter right so first we dealt with the the barass next we dealt with the line graphs and now we also dealt with waterfall funnel which is something similar next we have stack chart scatter plot chart right now scatter plot is a little different let's say you're dealing with numbers only numbers nothing different usually what we do is we carry out a dimension and a measure right but in this scenario you want to deal with only numbers let's say you are providing some discount on all your products and you wanted to know the profit against the discount offer here you're dealing with two numbers profit and discount right so in those scenarios what do you do so that's when you find out by using the scatter plot just double click on the canvas write it down as build me a scatter plot just check the spelling s c a t e r scatter discount enter and profit now it will create a scatter plot based on discount and against the profit now you can give some uh Dimension you can go with subcategory there you go now you have the profit obtained against discount offered based on different subcategories just expand it and you can see in which subcategory what kind of discount was offered and what kind of profits did you make so 25% uh uh discount was being offered on accessories and you made a profit still you made a profit of $333,000 and here you can see almost 200 discount was offered and still you I think that is OnePlus One offer or maybe end of the season sale but still they made a profit based on some parameters and here you can see the least discount and highest profit okay the least profit and highest discount is in terms of tables which is 33% the maximum out of everything and uh they still did not manage to earn some good profits out there so this is how you create a scatter plot just quickly name it as scatter and next we can deal with the pie chart now just double click on the canvas pie chart region sale so you have four to five regions here and using a pie chart would make a good uh impact on this particular visual as everything seems clear and you can easily make out which particular region made the highest sale by just looking at the size of these pieces right now let's quickly name this as p and now let's move to the you can just uh you know you can use the same data for creating a donut craft you can use the same data use to create a treay map and yeah those are similar to one another and let's let's quickly jump and now let's create some maps right now when do you need to use map type charts let's say your data set has some geographical data something like City something like country something like continent right or longitudes latitudes anything which gets in touch or gets in line with the geographical data then you can proceed and build the map data types I mean the maps correct so something which is related to the map data type you can build a map now let me double click on the canvas and ask powerbi to create a map country sales there you go you have as simple as that you have the country wise sales sales on power PI right here you can also choose to have Fil A map which will show you how much sale is happening the darkest color will indicate the highest sale and the lightest color will indicate the lowest sale now moving ahead we have the cards and gauge so cards and gauge completely similar to each other just click on the powerbi double click we can write card country okay let's go with region because we don't want to go or you can we don't want to have multiple cards we just want to have three to four cards on our dashboard so we'll go with category save so you just have a card here right you can just keep it anywhere on the corner in terms of real time dashboard you you can just keep it anywhere and uh let's name it as card now let's proceed and create a kpi so multi row card you can also create a multirow card let's say you have uh let's go back to card and check it once so if you create a multirow card we have three categories and you can also add add the subcategories of each category so that's similar to a multi row card correct now let's go back to the last type or we have kpis and uh table so KPS and table are completely similar to each other let's try to go with the kpi now we have discount profit sale and uh quantity correct so kpi is something which gives you the direct realtime number of what's happening with your real time dashboard correct now how many number of you know how many number of products are sold what's the overall sale that we performed what's the overall profit that we received what's the overall discount that we offer in terms of one single uh dashboard you can create a kpi for that now now let's let me create a kpi discount first that's easy as that now kpi for the profit enter there you go now double click kpi for sale enter there you go and lastly kpi for quantity there you go now that's how you create the different types of charts so basically if you integrate all these charts into one page or One dashboard or one canvas that's what you call as a realtime interactive dashboard in Pap pi and again coming back to the slicers if you were using Excel you might have to interconnect each and every single chart using the report connection option but in Pia you don't have to create that particular report connection process you can just click on the uh s or slices any of the chart and you can click on the any of the options let's say you wanted to find out country wise sales you just click on the country provided right so here somewhere you had yeah you have Jan Feb and everything right so if you just click on Jan every chart available on the powerbi dashboard will connect to it uh connect to the different you dependent charts and give you a real time information without having to create or report connections to each other right so basically if you just add all these charts onto one particular canvas that's when you get the realtime interactive dashboard in powerbi and so I hope you have understood what kind of chart you need to use based on what kind of uh data you have so we are on the powerbi window right now so here we can see we have already loaded the data we have the filters we should I izations Etc and we have the canvas over here right so in the visuals section if you closely observe you have the kpi over here right so you can either select and drag it onto the canvas or you can just simply double click and create a kpi but before that what exactly is a kpi right it's the key performance indicator right it's a full form of kpi so what exactly is key performance indicator let's say we have the sales dashboard over here sales data over here right now we have region wise sales we have country-wise sales we have region wise profits we have region wise uh discounts we have quantity right so it's like a visual it's like a number clear literal readable number right on top of your dashboard right so if you just want to have a quick glance of how many number of sales you made how's the profit happening how's the sale happening how's the discount that you're getting right and how many number of quantity of certain product is been sold in a segment right so those are the key performance numbers which will be available right on top of your dashboard so that's exactly what you call as a kpi now how do you create a kpi so one way is you can just hold the kpi and drag it onto the canvas or so this is one way of creating the kpi let's remove this now another simplistic way of creating a kpi is just double click anywhere on the dashboard or the canvas and you'll have this Jo so just write what kind of kpi you want to create so I want to create kpi for sales just create that and you have your kpi there now another one kpi discount just click on that you have the kpi for discount double click once again kpi for profits have the kpi for profits now kpi for quantity there you go that's how you create kpis in power player should you need to arrange the kpi sizes or color or background you can also do that by just simply playing around the sections over here right the background or the the outline section over here you can just play with it you can increase the size decrease the size add a background you have a setting over here you can do anything that you want right now we will discuss about calculated columns 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 powerbi like products sales data returns fulfillment 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 1 is to one 1 is to many many to one 1 is to one Etc and we saw the examples now once our relational model is uh prepared our data model is prepared now our next activity is to create certain additional columns which we want to derive bases the data which we have imported so for example I'll start with my product data set now in my product dat set I want to introduce a column which basically categorizes that if any product which has a color uh you know red black or gray I'm going to tag it as a colored product rest I'm going to say not a colored product right so all these are like example of B type product skus so for that now in order to introduce a new column you just need to 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 see a uh formula section typical to like you get in your Excel now I'm going to say that the name of my column is going to be bik 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 no okay so now you can see Bas 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 basis the pricing of my products I want to associate certain discounts which I'm ready to give to my customers Bas is the product category like what 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'm seeing if my product price is less than 100 then I will give 0% of uh 0 percentage of uh discount so zero into product price just to keep it consistent now I'm seeing else if less than 100 than zero else I'll check again that if the price is less than 500 then I'm ready ready to give 1% 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 0% if it is less than 500 then give 1% less than 2,000 then 1.5% less than 3,000 then 2% and otherwise else less less than 3,000 if it 2% else 3% right now after this column is created now you can check right so this see the product price for this particular product it is less than 100 so that's why there's no discount it is uh between 100 to 200 then this has been given a 1% discount so like this all the discount column 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 uh create a column called as cost 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've 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 powerbi is the related keyword so this will pick up the 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 okay now I'm going to create another conditional column over here called as order status I'm seeing if any order whose order quantity is greater than two then for my organization it's an urgent order else it is a normal order oh sorry I lost it so this is my order status column and I have my order quantity urgent or normal so any order which has order quantity one is normal any order which is having order quantity as greater than two is is urgent you can see this so there's a this whole powerbi 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 discount which I have created so I'll say [Music] discount discount will be order quantity into related product discount right so the discount calcul 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 there's a 25 uh uh you know for 25 rupee discount at the cost is 100,000 rupees okay and what is the order price now so I have taken the order cost the discount now I have to create a column called 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 right per order how much so first I'll calculate per order how much revenue I'm generating so now I have to generate a column called as revenue revenue is price minus discount so 1700 - 25 1700 - 25 2071 - 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 around with the number numeric values numeric 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 now let's take a look at other different variations of custom columns uh I'll create certain columns for text based custom columns calculated columns uh using text based 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 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 diff now date diff so I want the difference between the birth date of the customer and as of today in years okay so this customer as of today 68 year old one is 74 68 57 Etc so this is one derivation of calculated column of age let's take another example now this is a text based column where I want to derive the full name name of the customer now here I'll say first lower case in lower case I'll concatenate the prefix then ENT space and mcent first name ment space ment last name and closing brackets Etc full name okay so this is an example of full name in low cases now another calculated column additional column at the customer level I want to identify a flag which says who is my Target customer veres the demographics shared over here Target customer so I'll say if the marital status is equal to M and total children three and total children annual income 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 his marital status is married Logan Dias and age is less than 50 else 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 uh you know which help you which will help you understand how 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 1st Gen 2015 is 13 gen 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 the format this is like a currency cost is currency so I can go and uh 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 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 8 months prior to the expiry date with is which is uh coming up in 8 months so I'll create a column called as 8 months expiry and then there is a e date function I'll use that I'll use my date in the data set comma I'll say eight so now this date column will append 8 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 dddd format of it so it will give me the day name the day the day name of on that particular date next ears 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 and 7 years 2015 to 2022 then last date of the month so if I want what is the last date of this particular calendar month I will use a function EO month which is there available in the powerbi so I'll say last date of the month equal to EO month then just select the calendar CSV date comma zero in months and enter change the format 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 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 week number first week of the year second week of the year Etc now another very good example is whether the week day is a week day or a weekend right so what is it's a week type okay so I'll CH I'll put a if condition and there is a function called weekday and I'll pass the calendar if it is less than six it means it's a weekday else it's a weekend so all Saturday and Sunday day names will come as weekend G else everything else will come as VD 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 that yes there are n number of functions available in powerbi uh but uh what I have tried to Showcase over here is some important functions but basis your utility basis your problem statement you can uh look up for a relevant function in the powerbi dictionary now with this introduction to calculated column this is the base for us to now get into our next session where will be we will be talking about creating Dax measures and Dax functions we will be using powerbi 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 powerbi let's also learn about Dax expressions and that's basically your data analysis Expressions so it's basically collection of functions operators and constants which can be used in formulas or Expressions to calculate or return one or more values now we can basically add a new column so we can do that or we can also create new measures now usually there is a shorter way of doing it you can go into modeling and here as of of now nothing is highlighted because there is no data uploaded we see just a new table what I can do is I can go to home and I can let's get our data but this time I'll take the simpler data although we can work on the same earlier data set which relates to IP addresses and all that so that also can be done and to learn we can take up this particular data which is global super store which we have used earlier now here I can select orders I can select people and I can also select returns now what we can do is before loading we can just do some quick transformation on this particular data so that opens up my power query editor so here if you remember we have worked on this this is returns so what I can do is I can basically go in here I can say use first row as headers now that's one transformation which I would want to do let's go to people and it basically has person and region we will still do the same thing use first row as headers so that gives me the name of the person and the person belongs to which particular region you have then your orders data set and here things look fine so we can select different countries we can select different product categories we can basically work on your different use cases here like we have seen earlier picking up a category as technology picking up a subcategory and we can also create models based on these three data sets so this looks perfectly fine to me so let's go ahead and just do a close and apply and let's have our data quickly loaded now as I have explained earlier it is good to do transformation enough data wrangling so that your data is not huge when you're loading it because then that will slow down your queries slow down your visuals slow down your reports and so on now this is the data we have and say for example I would want to work on a visual so we know how we can do that so I can basically click on my orders I can look at the aggregated columns such as discount might be postal code might be profit and it depends if you have basically not changed the data types now now if you see here postal code and summation really does not go well probably this has been as uh this has been loaded as a data type which is integer but profit and quantity looks good row ID sales look good so we can work on Dax Expressions now here if you go into the modeling it says do you want to create a new measure you can create a new column that will get added to your Visual and here when you are looking into to the table you can click on this and this also gives you an option of creating a new measure or a new column new quick measure and you can work on Dax so when I said Dax let's quickly look at what or how Dax looks like talk about Dax the simplest way to understand is knowing the basic syntax so usually you first have some kind of measure name so for example we can say average about something we can say okay let's say average sales so that becomes one of my measure which I'm interested in calculating now how do I calculate that so for that I use an operator so which shows here so that's your operator which you're are going to use and then you use a Dax function so Dax has or powerbi has lot of functions which we can use so for example I would use a function like average which we can choose from the drop- down as we start typing in now this one as a function then needs a referred data table so basically let's say I would be interested in orders so I can select my table or my data set as you say and then you can choose a particular column so from orders I would want to do an average on say sales and and I can key in the column name here and that basically is my simplest tax expression so we can have a complicated way of looking at it or we can basically say okay I would want to find out average sales and I would want to might be look into a particular City or where I would say it is per City so mightbe I could say average sales and then I could say by city or by year or I would want to look into a particular year if we have the order date so we can say greater than a particular order date so we would want how our sales have got affected after a particular date and that depends on the situation so I can do that and here I can say greater than order date now for that obviously we have to use the operator and then I would want to calculate so I can basically say calculate now this is what I would want to find out so I can say calculate and then in calculate I can say let's take the column which we are interested in so we can pass in something like a measure here so for example I can just say average sales this is what I want but I will give a separator here to filter out the data and then I am going to use my order date for example I can basically say let's take orders and in that I am taking order date and then basically this order date has to be greater than or lesser than something so here you are specifying your data set your table and the column you are giving a filter here and that's basically your Dax expression so your Dax has different functions so when we talk about these functions like as I said you are going for average or you're going to calculate something so you have what we uh usually mean or uh we use is your predefined formulas now that can perform calculations by using specific values called arguments so which are in particular order and structure so always remember that when you talk about Dax this always refers a complete column or a table now there are different categories of functions which we can work on so you have date and time such as date difference or finding out now or finding out lat later time you can use something like time intelligence which is dates between first date and last date you can go for information functions which contains custom data you can have logical functions mathematical functions statistical functions uh text functions you have uh basically working on parent child relationship so there are different function categories which can be used and as as I said we can go for creating a new column or a new measure and we can use this Dax for our data analysis now let's see how we do that so say for example I have my order date now I would want to work on this so let's select say for example go to your data set and you have lot of data here so you can basically go for all this data here which shows me complete information of the different columns and you can choose which columns you are interested in which columns might be you would want to cut short so for example I have order ID I have row ID mightbe I don't want a row ID so I can choose if I want to delete this and you can say okay I would want to delete this now these are some Chang es which you are applying and now if you see there is no uh row ID what we see here as a field you start with your order ID you have your category you have your order date shipment and here you have your segment and for example we have country region Market category subcategory product name sales and then you have your quantity discount profit and all of this now what we can do is I can basically add something here so this is the data which we have and what we can do is we can continue working on this so let's click on home and here you have ways to transform the data you have a new measure you have quick measure you have new column and you can try out these so for example example I can go into new column and that basically says okay what is your column called now I can say for example as I was explaining let's go for average sales so let's call it average sales now that's what I'm interested in now I need to use an internal function so let's type in AV and that shows me okay so there is an inbuilt function called average we can use this one so you can just hit on enter and now it says okay which is the data set you're interested in now based on the reports based on the data sets I've used earlier it shows me options of different tables what we have or different data sets what we have so let's choose orders so this is what I'm interested in you can go for double click and that gets selected and now it says okay you want average on sales but which is the column which you would want to use so we can go for category we can go for Country we can go for customer ID all of these are your different columns so for example I can go for something like country and that basically gets selected now I can complete this by just closing the parenthesis and that's basically my dags function so my D function is ready and if you want you can add details to it so if you would want to continue your function you can always do a shift enter that takes you to the next line if you do not want it you can go back you can add a comment by saying alt shift and letter a and then you can say my first Dax function and you can give in some information depending on what kind of function you you are creating and that basically allows you to create the function so this is done now let's hit on enter and basically it will say the function average cannot work with values of type string and and that's right I mean we basically chose the wrong country I mean you cannot have an average on Country so that's not right I should use something where I have to take a numeric value so for example I will replace this so I will say I want average sales and here I can choose what should I take so for example let's go for sales and that's what I would want so it says order sales and then hit on enter and it basically has populated my new column but then it is basically taking average sales and it has taken average sales overall now we have not we have just selected a column we have not given any filter so it just gives me average and it populates every row with this one now this is fine that we have added a column it has the same average value and that basically gives me sales on average so as I said your Dax function always references a complete column or a complete table and then basically it adds the value now let's see how we can use this if you notice the field or the column has got added here let's get into visualization and what I can do is I can basically choose one of the type of visualizations so for example if I choose this and say for example I would be adding this average sales and that is the data I would want to look into so what we can do is we can just say okay I'm interested in this average sales it shows me the function and we can add it here now this is just giving me the average sales overall now that really doesn't make sense let's look at this one or plotted graph so it gives me what is the aage sales and it gives me some visualization so we can create visualization like this based on our column now what we can also do is we can go back to our data and we can go for different functions so as I said you can create a function that adds a column now let's go for something else so I will say average sales and I would be interested in say a particular product now we have average sales and that is the the measure which we can use so for example I would want to find out um wherein the year or let's look at the date field here so for example we have country as France it is telling me City it is giving me customer name it gives me a segment so let's choose a different field and we can filter by that so for example example I'll say average sales buy or whatever name you want to give let's say segment now what I will do here is instead of using this average so I will go for something like calculate now I can go for calculate I can go for concatenate so let's go for calculate and we know that we have a particular column so let's go for this one which is let's open up a bracket here so let's go here let's give our column name so that could be your average sales so let's look at what was the column name and we have average sales with a SS Capital so let's go for average and then let's go for sales now this is what I want to do but on what so let's choose this and here I'm going to now [Music] choose orders so and then we have to basically look at the column which has the segment value so segment now this is what I would want to use and then we can try giving a value to this so you have segment as corporate so for example let's say let's go for so it says calculate given expression which we have already given that's your average sales or we can basically give the expression what we did earlier and then you go for a filtering value so I'm saying order segment and let's go for something like corporate now that going to be my segment let's close this one so here's the example what you do is you give your measure name so that says average sales by segment you can do a calculate now here you have to pass in a measure so for example I'm saying average and I'm saying take an average for sales that is give me an average value of sales but what I'm interested in is when the order segment is corporate so I'm giving a filter here and then it gives me average sales by segment so that's the column value here and it is basically filtering out based on the segment being incorporate so segment is our particular column here which is showing up somewhere let me see where is my segment column so we have your product now I have uh let's search for Country City postal code customer ID and since we have chosen order sales it is giving me this average value and you can choose a different column or you can say profit is greater than something else so I would be only interested in those values and you can continue adding things to your tax expression and this is how you can do here so basically what I did was we were using a measure which is average order sales I said my segment has to be corporate or basically you can just say segment has to exist so you can say a Boolean value like equals true now I'm saying orders profit has to be greater than 100 and I'm only looking for my average sales by segment where profit is greater than 100 segment is Corporate and now this has calculated the value so you basically see here so it tells me 286 if you look at the profit value so it is greater than 100 what I can do is I can even add a filter here so for example I can say greater than and let's give some value here so for example let's go for 100 and let's see if it has done the work so you see all the average values where it is greater than 100 and then we have to also look at our other values we can filter it so segment has to be corporate and let's apply the filter here so I will uncheck I'll say corporate now this is what I'm interested in so it tells me the corporate is sector now you see the sales value which is already showing up here which we have we have a sales by segment which is telling me that you have corporate segment and it gives you where the profit values are greater than 100 now we have this Dax here we have applied we have the filter and what we can do is we can go to quickly visualization and what I can do is I can choose this I can add it here and then that says me average sales by segment however we can go for different kind of visualization which can give us more meaning here and look at the data so this is how you can simply use your tax Expressions now this is a simple quick uh session where you are looking at the Dax expression so when you look at this symbol it means it is a Dax expression so you can obviously double click on this one and that shows you what is your tax expression now as I said I have given two filters here so always remember once you have given a measure you give your first filter and then you can give any number of filters you can just order it in a different line that should be fine let's say average sales by segment okay so let's say average sales by set segment okay profit greater than 100 so I can give some kind of name which basically identifies my column and that gives me the value now if you see here we have applied these filters and that's why we are only seeing the relevant data which is profit which is a particular segment and you can create any number of Dax expressions like this using some inbuilt functions so anytime if you would want to see if your Dax expression is working fine what you can do is for example I can say test function okay now I would want to create a function and then I can basically choose the available functions as I said you have logical functions you have statistical functions you have mathematical functions you have information functions so you have all of these options here and you can choose one of these so for example we were going for average you have and and or for example I can directly go for and but that would not make sense in the beginning you can go for absolute value of something so for example let's choose absolute and now I want to work on orders so I go for orders and here I can say okay I'm looking at orders but I would be looking at sales value I I would want the exact value so I can choose this one right and here now once I am creating a function so I can be selecting a different column so this is basically one column now you can add calculation you want to do but this basically says unexpected expression because this is a new dag for a new column so what I can do is I can take this out from here I I can go ahead and create a new column and then or a new measure so I can do that and it works perfectly fine now I can save this and we can call it whatever report you would want to call so let's say super store with new Dax and you can share it you can publish it you can continue using it in your reports so you can create any number of functions as such and then just save it so for example I have set of Dax functions and I can upload it on a GitHub link so here for example if I go in here and if I click on Dax so that tells me the different kind of visualizations I have based on the standard data so it depends on uh my file which is which should be existing here so I can go in here and this should actually work but the location has changed and I just click on open report I can do a browse report I can go into powerbi content and here let's look into this one and just say open so that will not only load the report it has different visuals and uh different visuals with the standard data set which comes with Microsoft and it basically has different kind of visuals and each of those visuals again have different tax functions so you can use these also so what we have learned is working on a store data set working on some thread hunting data sets working on our own created tables and these are some of the pages what you see here so this is based on the data which comes in with AWS and you can always look into one of these and you can search if you see here look at this icon and it tells me okay this looks like a tag function and let's double click on this one and that shows me the function what you're doing so you're creating a function called age it uses State difference it works on dim customer table on the birthday date and you would want to basically find out the date difference between now and the birthday and you would want the year so this is is the data what you have you can obviously choose this you can look at the result of this and you can see the visuals so these are some simple tax Expressions you can create lot of data expressions like this now this is here you have date function so I can choose which are the tax functions which are existing here I can just load them so for example this one is is one of my other data sets and then you can play around with the data here so this is how you create your Dax expressions and this is just this was just a quick demo on going for some simple Dax Expressions you can create different kind of visuals and you can then save those reports you can publish it to powerbi service and and you can then gather insights based on this so this is how you work with powerbi wherein uh you can use Dax you can use powerbi service to gather insights you can use desktop to work on loading your data transforming your data in different ways and then basically analyzing it so these days you might be hearing a lot about PBI we do have a lot of bi tools in the market like Excel Tableau click view a lot but people are talking about powerbi the most but why so we will be knowing that in a while so let's get started with uh building a dashboard first and eventually in the process you will definitely understand it so making a dashboard in powerbi is just like a breeze so before we get started with powerbi I would like to explain you a few fundamentals that you might want to take a look at before getting started so data cleaning is a much needed process so you can do data cleaning using public query or you can just simply open your Excel and try to check the data types for blank cells any inappropriate datas eliminating them right so simple basic fundamental data cleaning process if you have some irrelevant data in your data set just eliminate that row or try to fill that with the proper data that was supposed to be there and if there are any blank rows or blank cells eliminate those blank rows and blank cells so that your data is up toate and accurate and apart from that uh let's check out some data types for example the first one which happens to be the r ID here so this is the data set that we will be dealing with today so this particular Road ID has a general data type so it can be either a number or a float and when you come to the ship date over here the order date over here right so those are date data types and they might follow different formatting let's say they might follow uh date number that is day number and for month they might add the alphabetical name like Jan or Feb and the year or a few others might format with date time and uh you know we will be having date uh and at the time which will be describing the hours minutes and seconds right so there are a lot of variations where a data from date data type can be saved so it's your fundamental you know responsibility to make sure that all the elements from a specific column are of one single data type so that the the entire data set is inte you know it's very uh good and it should be intact that's the whole point of it and if you have any inconsistencies in your data set that might result in you know abnormal predictions abnormal dashboards or charts whatever you make there will not be uh those dashboards cannot be relied on your reports cannot be uh reliable so that's the whole point of it so just make sure that you go through the fundamental very basic data cleaning things like eliminating blank rows blank cells maintaining a uniform data type for all your columns you know based on the data it's not like you have to maintain character data type for your dates so you need to make sure that what kind of data type suits what kind of data and you need to check that and make sure that all the elements in that particular column follow one standard data type so I'll be going with date data type for audit date and sh date and ship mode will be character serial that is row number will be serial number or row number will be integer and uh everything else will be C data type and when it comes to the last four rows sales quantity discount and profit so those will be the uh you know float data type and when you check out the location here we have country north so these will be segregated by your bi tool either Tablo or powerbi whichever you you're using so those will be segregating these particular location type data types and it will show them as map right so this is the major thing which you need to make sure before starting with your you know building dashboard so that you know having said that let's begin with the powerbi let me start the powerbi bi tool here it is just click on it it should take a while so here we are we are on the you know first page of the powerbi so you can import the data from Excel you can import the data from SQL Server you can just copy paste your blank tables so you can make use of some apis and plugins and get connected to the data sources from web or any other sources and you can also import the data from that particular source so today we will be dealing with the Excel data set so we will be importing the Excel file and before that here you can see some options which are filters data visualizations and uh data so we haven't loaded any data so you can see the data over here and if you are on table they will be available on the left side and you will call them dimensions and measures so you can apply the same uh analogy here so you'll be available with the dimensions and measures once we import the data and here you have a wide variety of charts and graphs that you can just you know drag and drop and create so once you have the data over here you can just drag and drop onto the canvas over here and then it will automatically generate some type type of a data set or chart let's say it'll give you a tabular chart you can just click that particular chart and come over here over over here and you can select any one of the charts available here let's say you wanted the P chart you can do that uh you can do a donut chart you can do a dream app right so once you h on to it it will tell the name of that particular chart and you can select that like it's based on your choice right so this is about it basically now you can also do some drill throughs that's again for a session in another time another day so that's a session for a different day for now let's try to create um dashboard in a routine way remember I'm telling you or I'm specifying in a routine way because we also about to learn why powerbi is being uh so popular currently in the bi industry right so there are some tips and tricks that will help you in the long term so let me close the filters close the visualizations close the data and now let's click on this particular option which says import data from Excel right now we are in the Excel data set so this is the file where I basically store my Excel data sets that I'll be working on so you can also have access to this you can just let me know and we will drop down the tribe Link in the description box and you'll have the accessibility to all these data sets so remember I spoke about data clearing right so this Excel data it is completely cleaned every data column has a uniform data type and it follows that data type throughout and if you check out this particular original data set which is sales European sales over here so this is the original data set which follows different nomenclatures for data types for example if I come down to the date column there are different types of data types some of the cells have only dates they follow date month and year and some of these cells have date month and year along with the time so another clitch here so a few of the cells were following the uh you know 24hour timing nomenclature and a few others were following 12 hours this is specifically mentioning am and p.m right so this causes a lot of trouble when you're creating a report trust me I've been there so what I did is I clean the entire data set removed the cells I mean the blank cells remove the blank columns remove the blank cells remove the blank rows and in any situation if there is um you know uh discrepancy followed in data types I've also cleared that and I saved it as Excel data right so we will be using this particular data now and if you need these data sets I can provide you with that using the Drive Link and you can have access you can also try your data cleaning by following our tutorial which is linked in the description box below for data cleaning and you can do it and you can start with your dashboards now let me just extract this particular set just click on open and you will be having it on the screen just in a moment so when you check out the original data set we have a lot of sheets here right so these were some pivot tables that we created for data analytics using Excel so you can also check out that video now we will be doing the same data analytics and creating an entire interactive dashboard on PBI with the same data set so you can see uh powerbi is trying to load all the tables and all the sheets available in that particular file but we just need one out of those you can also click all of those or you can just click get one out of those so for now I just need one out of those so here it is and you can check the data you can have a quick preview of how your data is looking right and then just click on load and the best part is powerbi will also perform a data cleaning operation from its end and identifies if there are any rul or any cells that I missed out to clean and it will notify you if there are any errors and it will also I mean it is also capable to you know um fix those errors for you you can see I have seven errors right so you can just view errors and clear them or I can just close so that in the background PBI will take care of them for me now we have the data right so here you can see the data tab got opened and here you can have a new drop- down if you click on that you have all the data elements present in the data set or in terms of T you have dimensions and measures so basically wherever you have a sumission or any kind of mathematical operation present in front of the column name that particular element or that particular column is considered to be the measure so which is which is basically dealing with the numbers and all the others which do not have any other mathematical symbol like equals hashtag or sumission those are the dimensions basically the character data type or the date data type whichever uh the data type it is but not numbers right so here we have it so what you basically do is completely similar to uh Tablo click view or any other business intelligence tool basically just drag and drop right now let's say I want to find out the um region wise sales what can I do here is I can just drag it onto the canvas and again I have sales over here I can drag sales to the canvas I can just you know H it onto the existing box you can see this particular box right you can just drop it inside the box so that powerbi will understand that I'm trying to uh you know extract the region wise sales and it will perform the sumission of all the sales region wise and it'll give you a table let's say you're not satisfied with this particular table representation you can just hold your table and here you can open the visualizations and open the filters right right and here you have visualizations over here if you want to segregate that in form of a py chart you can do it right now it will give you the sum of sales based by region now want to calculate the U let's say audit date you know uh date wise sales then I can also do that you can just drag the sales over here and select that particular table and you know whenever you are dealing with date data type right when you're are dealing with dat data type then what you do is you try to use the line cop it's the best way to represent your sales how they are happening right and uh few other things let's say I want to calculate uh the profit by city or country which country is facing the highest number of profits we can also do that just drag and drop you know how it is and okay it's giving a lot of countries but but still it's not a big problem for us you just Dragon drop the sales and here you can select any one out of these let's go with the bar graph and here you have it right now so for so good right so far we so good now if you um worry about the interactivity you don't have to basically when you're using Excel or any other W let's say if you're using Excel you might have to use slices right so if you're not aware of what am I talking about you can just go check the data analytics of building the dashboard using Excel where I have completely explain the day data set and they have you know built an interactive dashboard there so it'll give you a better idea about it so before that um let's get my let's Circle back so here uh you don't have to create a slicer and you don't have to report the connections between each and every chart it's automatically done in the background for example let's say if I wanted to calculate the sales of central region if I just click on the central region it will automat Dr matically change the display of the entire dashboard that will give me the central region sales right and uh if I just go back it'll you know it'll give me the entire sales dashboard let's say if I want to calculate the west region I have my best data right so that's the best part of pobi and now we are going to identify why this this is one good reason why powerbi is popular but there is another good reason why powerbi is so popular right now so popular so easy and so effective that even a schoolgoing kid can build a dashboard trust me a schoolgoing kid of grade 11 grade 12 or a candidate who is just graduating or a fresher or any person who doesn't have a computer knowledge but has some good knowledge about numbers and how businesses work they can definitely create an interactive dashboard using powerbi even if they are new to powerbi so we going to just see just that let's create a new page or you can just go back to the same page okay let's create a new page now let's say I don't know uh how to create a chart I don't know let's say my manager asked me to create a complete report of the sales happening and I don't know powerbi and I don't know anything about how to create a chart and how it's work right but I have certain idea right let's say I have a certain idea I want to find out the regionwise sales I want to find out the country-wise sales I want to find out the performance of sales date on date year on year month on month quarter on quarter whatever it is you just want to create you know you just want to extract the you want to build an Insight on the date wise sales and uh you want to find out um you know profits based on certain regions or certain categories right which category is the highest performing category which is the majorly used shipment mode right there are Limitless possibilities you know what to build but you don't know how to build right in such situations powerbi is here for the rescue just right click on the canvas just click anywhere right now you okay it was not the right click it was the double click so just double click anywhere on the powerbi canvas and there you can see something called ask a question about your data let me expand that for you you can see ask question anything about your data now let's say I want to create the same region wise sales right you can just type on the region wise sales and it's already giving you a uh you know it's already giving you a bar graph now let's say you don't want the bar graph you want a p pie chart or a donut graph right donut chart so you can either select any one of this I'll go with pie chart for now so you can just select on that and here you have your pie chart right how simple is that how easy is that right and now you can just place it anywhere on your dashboard check the size alignment and good to go let's close these filters for now so that the canvas is a little more better visible now let's say I want to find out ship Moree profits and uh the type of graph I'll go with the bar graph there you go sales that happened using a certain ship mode and you have the data right here and I'm choosing the bar graph now let's say I want to calculate the month-on-month sales right so double click anywhere on the canvas sales audit date and remember we want to represent the okay it's too intelligent it already gave you a line graph right just click that select that and uh just place it anywhere on your chart you can expand it there you go there you have it right and now let's do let's try to find out a few more things let's try to find out uh which of these segments gave me a lot of profits category wise profits category profit and let's try to build uh we have a pie chart so let let's build a donut chart there you go place it anywhere on the canvas like whever you need it now few more things statewise okay now let's make it a little more interesting State fail map you wanted a map right so just select on that and you will be having the map okay some problem here so I think let's go with country sales map okay let's enter now let's select this particular chart okay no interactivity I need right now select this and here let's select map and map and fil map visuals are disabled to enable them go to a file options settings options Global Security details okay let's try this file options and settings options global global security use map and fi map visual I think they should fix this okay let's refresh this should help us there you go you have your map let's place it anywhere in r in the dashboard now we have the region wise sales or country-wise sales and shipment mode subcategory and uh let me know what else we can build let's have the kpis here now let's go to the sales just drag and drop the sales sign anywhere on the table and I want the data presented in visuals I have the card now let's have profits and that should be a card again and let's have U quantity that should be a card again that's our kpi profit sales quantity discount is another one where which you can add and just add number card again there you go you can go to the insert option insert text box and there you can just write down sales dashboard align the text to Center and that should be good there you go so far so good and in case if you wanted to add a few more visual athetics to your dashboard you can also do that you can just click down yeah I think you remember your PowerPoint right just go to view and you can select any of the backgrounds you want and there you go you have it if you're not happy with this you can also have some images in the background you can add some images which will make it look a little more interactive right so that's how you build an amazing interactive dashboard in powerbi and uh that's the reason why uh the Simplicity why powerbi is being so popular amongst the all other remaining bi tools and now let's get started with the customer analytics dashboard using powerbi so we will be using the Amazon sales statuses from 2023 and 2024 for this particular dashboard using powerp so let's quickly switch between the PowerPoint presentation and the D set that we are going to use today so this particular data set is available on kagle and it is actually the data set between 2020 and 2021 so we carried out a little bit of data cleaning process and we enhanced the data set like removing the blank spaces removing the blank cells and the blank rows basically so and after that changing the data type of dates so you might be wondering there might be a few discrepancies in the way the data is formatted because there are many ways for example if I just select this column and go back to data type and if I go to the available dates there are many formats so you can see year month and date date month and year and there is an alphabetical representation of month right so there are many variations where you can represent the date so we are going to use one single form format for the entire data set so that it can be easier for us to identify the week on week month on month quarter on quarter and yearly uh analysis so that's the whole point of it first point just try to eliminate any kind of blank cells rows or columns in your data set so that the accuracy of the data that you are using to perform analysis is intact and good right so apart from that just check if you want to eliminate any of the decimal points just select this and here you can see the option to elim Leading decimal numbers right you can use this option over here so there are about 2 lh86 393 rows in this particular data set so it might take a little while to uh reflect my actions on this particular data set and apart from that if you could just hover over through this particular data set you have a lot of things to deal with you have regions you have username discount percentage and uh you have first name last name of the user quantity order price value so why are we basically going through this particular data set is is when you understand it is when you can quantify what's happening with the D set that's when you can plan out what kind of analysis you want to perform right let's say you did not get any kind of prerequisites from your manager you just have the DS head and you need to perform the exact uh you know analysis which could benefit your organization so they also want some insight so that they can make out some decisions out of it right some business decisions so in that situation you just have to go through the DAT at once and do the data cleaning process and then understand what exactly has been mentioned in this particular data set and what kind of uh insights you can extract out of this data set and you can proceed with that so basically we have quantity order price value and uh total apart from that you have category you have uh different payment methods you have uh the year you have the dates and uh so when you have dates you can also create some trend lines out of it and apart from that um let's say you have zip codes region right so you can find out region byse sales and a few more things what you can basically do out of it right so having the data set understood let's go back to powerbi so this is our powerbi platform so basically you just select this particular Excel work option and import it data I've done it basically here you have it so we have the age category City Country everything presented over here now let's quickly create the visualizations using this particular so I would firstly go with u a bar graph okay let me choose this uh bar graph right over here and uh let me create gender Dev sales let's let's have this [Music] here so we should be having something called as a price or a sale so we have price over here you can quickly draw and drag this and drop it on the xaxis okay we y axis and uh gender on the x-axis so we don't want this filter over here you can eliminate this so now you have um gender wise sales right now next let's use a pie chart now you can find out the region why sales quickly add the region into the legends here you have the region by sales so you have Midwest Northeast South and southwest regions so these are the sales uh line graft which can help us to to okay so just click on the canvas and then double click for your drop the sheet now let's use the audit date and drop it onto xaxis and um rise on to Y axis now if you could uh use the drop down and use the date hierarchies you can use the year on year month on month so let's go with month on Mon quarter okay let's go with quarter on quarter now you can see how are the sales running quarter on quarter in the for this particular data set so we have two years that is 2020 and and me yeah 2023 and 2024 the quarter on quarter comparison for year on-ear sales figures right now let's click on the canvas once again and now you can choose the over here and uh so since we have two genders and I think we also have a column for age it should be somewhere in the list so basically you can derive what age groups have made the maximum number of sales right yeah so here we have it and the sales so basically it'll create a bucket right uh age group from 18 age group from 19 20 21 and so on now just go through the orders or you can go with the price so just drag and drop it in values and you have it here and you created a donor chart of it and uh let's try to create a field map now uh you can understand which uh area or which region was the highest uh sales uh we received so just go through the data set over here so we have the country just drag and drop the country and then have the price or orders you can use anything okay let's go with the orders quantity order and add it in the values so here we have it so if you could expand you can find out which region was the one which giv us highest number of orders and there is another simple way to do it as well if you just double click on the [Music] canvas it will give you a prompt where you can write map country wise map okay map state so we have a state uh column here sales or price and uh it will automatically understand your query and create a map which will show you the highest number of sales from the regions or the region or statewise sales brilliant now if we proceed into the final chart okay we have a waterfall chart so now you can create a waterfall chart so you just name it waterfall so we have U category some yeah forall category wise just enter price or sale and we have a waterfall chart over here based on the categories you can just drag or you can know expand the canvas to have a clear picture and uh yeah so far we have dealt with okay we have a scatter plot we can do a scatter plot scatter plot for quantity of orders received against discount offered so we should be having quantity ordered ut5 against discounts or I think we can go with order rties so it'll give us a unique uh representation there has been a small discrepancy order ID or you can also go with customer Ting against discount offer so we have a huge data set 2 lak plus so might take a little while to respond to the requests that's completely all right now what left out is the kpi so you can also create some kpis so just double tap and uh write a kpi for total sales write a kpi toal orders 29,000 API for total discount kpi for Discount percentage or we can use kpi for gender which can tell us how many number of females or how many number of males did create an order then we will be having a number here Order writing if you could just close these visualizations over here we can expand the canvas and you can also Al have okay we can also minimize this particular area and we can also have another chat or bar bar graph for types of payments payment types payment method right so we will be generating a bar graph with highest number of payment methods used that is cash on delivery online payment Etc so it will take a little time to reflect on this particular screen that's all right now if you can just go to view and here you will be having various options for representing your data set just click on the best way to represent your data set there you go so basically that's how you create okay let's try to rearrange our charts a little bit so that the kpis and kras are clearly visible there you go so that's how you create a customer analytics dashboard using powerbi powerbi makes data visually appealing it has easy drag and drop functionality with features that allow you to copy all formatting across similar visualizations powerbi fetches data from Factory sensors and social media sources to get access to realtime analytics let's see what Tableau really is tblo is a powerful business intelligence tool which manages the data flow and turns data into actionable information it can create a wide range of different visualization to interactively present the data and showcase insights tblo has the feature of drag and drop which allows its users to create interactive visuals quickly it can also build interactive dashboards with just a few clicks so powerbi was originally designed by Ron George in the summer of 2010 and the initial release was a available for public download on 11th of July 2011 the key components of powerbi are powerbi Desktop powerbi Service powerbi mobile apps powerbi Gateway and powerbi Report server tblo software was founded in 2003 in Mountain View California and the tblo desktop 1.0 was released in 2004 on 1st of August 2019 Salesforce acquired tblo tblo products include tblo desktop tblo server tblo on online Tableau visible Tableau public and Tableau reader now let's see how expensive these tools are powerbi is way less expensive than tblo software powerbi professional version costs less than $10 per month per user the yearly subscription comes around $100 powerbi premium is licensed with dedicated Cloud compute and storage resources and is priced at $4,995 per month on the on the other hand tblo is more expensive where the pro version of tblo comes at more than $35 per month per user the yearly subscription costs around $1,000 tblo Creator costs around $70 per month while tblo viewer is priced at $12 per month if you are a startup or a small business you can opt for powerbi and then upgrade to tblo if the need arises now coming to Performance powerbi is easy to use it is faster and performs better when the volume of data is limited powerbi tends to drag slow when handling pul data but tblue can handle large volumes of data easily it is faster and provides extensive features for visualizing the data tblo doesn't limit the number of data points in a visualization or enforce row or size limitations so you can have a complete view of your data tablo's wide range of built-in analytic capabilities allows you to spend less time worrying about manually creating calculations designing visualizations and formatting dashboards now let's discuss the user interface of these tools the user interface of powerbi is highly intuitive and it can easily be integrated with other Microsoft products powerbi interface is easy to learn and understand it is user friendly and allows you to operate better powerbi desktop provides three views which you can select on the left side of the canvas the first view is of the report View where you can create reports and visuals the next is the data view in this view you can see the tables measures and other data used in the data model associated with your report and transform the data for best use in the reports model and third is the model view in this view you can see and manage the relationships among data in your data model tblo has an intelligent interface that enables you to create an custom czee the dashboards according to your requirements easily it has an inviting workspace area that encourages you to experiment with data and get smart results the workspace area has different cards and selves toolbar sidebar data source page status bar and Sheet tabs with that let us now talk about the different data sources that powerbi and tblo can connect with another important feature of powerbi is that it supports we ious data sources but has limited access to other databases and servers compared to Tableau some of the examples are Microsoft Excel text or CSV files folders Microsoft SQL Server access DB Oracle database IBM db2 mySQL database postre SQL database Etc tblo software has access to numerous data sources and servers such as Excel text file PDF Json statistical file and Amazon red sift cloud data Hadoop Google analytics Dropbox Google seats Google Drive and lots more now let's talk about the ease of use powerbi enjoys a slight Edge in terms of ease of use because it is based on a user interface that has its roots in Microsoft Office 365 which most end users are already familiar with tblo provides some essential advantages for exploring and visualizing data in detail Tableau is also incorporating natural language capabilities into its software this will help us in finding solutions to complex Problems by understanding the data better next let us understand how powerbi and Tableau differ in terms of programming support powerbi supports data analysis expressions or Dax and M language for data manipulation and data modeling it can connect with our programming language using Microsoft Revolution Analytics but it is available only for Enterprises level users compared to powerbi tblo integrates much better with our language tblo software development kit can be implemented using any of the four programming language such as C C++ Java and python by connecting two these programming languages you can build even more powerful visualizations now coming to the most important category which is data visualization powerbi provides an easy easy to use drag and draw functionality it provides features that make data visually appealing powerbi offers a wide range of detailed and attractive visualizations to create reports and dashboards using powerbi service you can ask questions about your data and it will give you meaningful insites tblue also allows its users to customize dashboards specifically for a device it delivers interactive visuals that support insights on the fly it can translate queries to visualizations and makes you ask questions spot Trends and identify opportunities no coding knowledge is required to work on Tableau as tblo provides inbuilt table calculations to build reports and dashboards now talking about machine learning and how they are different from each other powerbi enjoys the advantages of Microsoft business analytics that includes platforms such as Azure machine learning SQL Server based analysis Services data streaming in real time and many as your database offers it helps to understand the data and analyze the trends and patterns in the data you can also forecast the data to make future predictions tblo supports the features of python machine learning this enables it to perform machine learning operations over the data set finally let's talk about customer support Microsoft powerbi is relatively younger in the market than Tableau and hence it has a smaller Community while tblo has over 160,000 active users participating in over 500 Global user groups and over 150,000 active customers participating in the tblo online community next on the screen you can see the Google trends for both the tools Over The Last 5 Years From 2015 onwards as you can see tblo is a clear front runner and is more popular these days with high search volume now talking about the Gartner magic quadrant for analytics and business intelligence platforms so for 13 consecutive years Gartner has recognized Microsoft as a magic quadrant leader in analytics and business intelligence platforms and it has recognized tblo as a leader in business intelligence after Microsoft you can see there are other popular tools such as click thoughts spot cens Oracle SAS sap and Salesforce now I will tell you how simply learn can assist you in learning powerbi and tblo and help you become an expert in business intelligence so let me take you to our website I'll open a new tab let me search for simply learn.com I'll Now search for powerbi in the search bar so let me type powerbi here I'll click the first link which is the powerb certification training course so this course will help you learn about Microsoft powerbi desktop layouts you will learn how to build business intelligence reports dashboards powerbi data analysis Expressions commands and functions so this is the course content you learn about desktop layout features the views connecting to Common data sources you learn about the query editor and its functionality then you will learn how to create relationships in your data model you learn about calculated columns and measures built in aggregations you'll also learn about data visualization practices reports and dashboard layout creating a sales analysis report and lots more more let me go back and now I'll search for tblo I'll click on the first link so this is the course for TBL certification training so it's around 56 hours of in-depth Blended learning there are four realtime industry projects you'll get lifetime access to self pce learning videos to simulation exams on the right you can see the skills that will be covered as part of this course we learn Tableau statistics building interactive dashboards this arithmetic logical and LOD calculations which is level of detail learn about heat map waterfall pero clustering forecasting and here you can see the entire course content after learning this course you can go ahead and take the Tableau certification and with that we have reached at the end of this session on Papa bi full course should you need any assistance PP or any other resources used in the session or if you have any queries regarding any of the topics covered in the session please feel free to let us know in the comment section below and our team of experts will be happy to help you as soon as possible until next time thank you for watching and stay tuned for more from Simply learn staying ahead in your career requires continuous learning and upscaling whether you're a student aiming to learn today's top skills or a working professional looking to advance your career we've got you covered explore our impressive catalog of certification programs in cuttingedge domains including data science cloud Computing cyber security AI machine learning or digital marketing designed in collaboration with leading universities and top corporations and delivered by industry experts choose any of our programs and set yourself on the path to Career Success click the link in the description to know more hi there if you like this video subscribe to the simply learn YouTube channel Channel and click here to watch similar videos to ner up and get certified click here

Channel: Simplilearn

Views: 8,314

Keywords: simplilearn, power bi, power bi tutorial for beginners, power bi tutorial, power bi dashboard, power bi desktop, power bi advanced, power bi training, power bi basics, power bi for beginners, What is Power BI, power bi full course, power bi crash course, power bi course, introduction to power bi, what is power bi, power bi certification, power bi charts, power bi projects, power bi vs tableau

Id: 1PeesfibPbM

Length: 354min 14sec (21254 seconds)

Published: Sat Apr 06 2024