PIPFA Webinar on “Data Analysis & Visualization in Power BI”

saw them calling Assalamu alaikum participants, this is Ali Austra. On the behalf of PIFA Faisalabad branch committee, I welcome you all. Let me introduce our trainer Mr. Tang Aziz. He is certified public accountant and management accounting accountant USA. His masters in accounting and finance. He is an experienced and passionate finance professional having experience in airlines, air cargo, air logistics, business and textile industry. He has ah 15 plus of experience in management position dealing with finance and management reporting. Expert in ERP system, implementation, business performance analysis, data modelling and reporting in power BI. Currently working as a senior accounting specialist team leader, budget planning, forecasting in Saudiya airlines, cargo company. I welcome you sir. Now it's over to you. Thank you. Assalamu alaikum. Everyone Thank you so much and thanks to the Bifa Faisalabad branch committee.

appreciate their effort to arrange this one. Okay and for the training of the power BI group. And I say my thanks to Mr. Ali and Mr. Neve Akhtar. Whose efforts are ah very much to arrange this one for you. Inshallah we go through whole of the ah power behind project. This tool we will learn about this one. What is the effect of this one tool? And how it will change our workings, our professional workings in the finance and accounts department.

Not only the finance and accounts department, we can use this one tool. In all of our departments like HR, production, and finance and specially accounts sector. Okay, participants are coming in of increasingly and then we will just go to start. Let's start our introduction of the power BI. First of all, the power bear tool is ah lost back in two thousand 13, July 2013 and initially it is for the data analysis.

Okay, that's the, explain the how we are doing. Powerway tour is launched by the Microsoft. And in 20 13, July 2030 and it is ah basically used for the data analysis. We have a lot of data in our daily life. Whether it is in the finance finance profession or the statistics profession or anywhere. We have a lot of data and we have to arrange that. That will draw data in meaningful information. When we process the broad data and some through a process through a some analytical tool then it comes in the meaningful information which is used for the decision making. In the market at this time, Tabello, data analytic or the other tools for the data analysis. the power behind tour is enlarge by the Microsoft that's it is a very easy and flexible to use. And we go through this one power wear tool with the slides I will explain through the slides as well as may be you have received the data sets for practice. You can practice on that data set at home as well as if you want to do with along the training session. You can do also. I will explain everything.

During that our course outline will be first I will introduce the power BI and then connecting cleansing and shaping the data then we will go to further creating the data model and after that ah some of the tax formulas we will ah check some of the main debts per rulers and after in the last but not the least in visualization of that data in the reports form as I told you about that with power BI is the Microsoft tool. It is a stand alone Microsoft business intelligence product.

Which includes both the desktop as well as the web based. Because we have to work on the desktop. Tool and we will create our data model. We will create our reports and then publish to the online web portal. And it is also on the same power BI portal. We can share the reports with the others through the web portal. Why we use the power BI? Actually flexible to use. It is easy. It can handle millions of rows of data. It can access through a different type of platforms. We can connect with that sir. We connect with the CVC files. We connect with the text files. We connect with the Sab, we connect with the other database, ODBC or we can connect with the Oracle, any forms, we will discuss ah in the next session.

That which are the data sources which we can easily share with the connect with the power BI. a power where we can define the complex analysis, expressions which we are not, cannot be used in the excel, pivot, power pivot, we cannot use that one techniques. In that tool, therefore, the power BI is flexible for that one. We will use that one technique to analyse our data. The same thing as we know that the visual or graphical presentation is much more easier to understand. If I will ask that two hundred thirty-three thousand fifty-five people are studying in the or the in the or the city then it is not the meaningful if I will ask that the 25% of the total population is studying in the then it will be meaningful word. In this way, in the visualization by the presentation of graphical, it is easy to make reports and present that were reports.

Leader among all other like the tablo and the Google Analytics and the other analysis softwares powered by his main because it is a Microsoft product and it is free. You are only just download the power BI on your system and launch that one, Microsoft Power BI. If we will see the journey of the power BI, first we ah know that the in 1998 the excel simple axle we were working on that one excel then excel is advanced, it is modified, it is change, upgraded so many times and now 2016 excel we are working on that one. We use the simple formulas then we use the pivot table then pivot charts, maps and two functions we use that one. But after that there is also advancement due to the power pivot. From the excel we also use some of the macros we use in the macros. I remember that in my audit ah training period. I was ah a lot of used of the mattress for calculating of the ah yawn calculations and the other consumption reports.

I used that one macros. Macros help me to automated my workings. I don't have to again and again work out all the working. Just ah play the macro and it is working. Now, it is advanced. It is goes to the tables which summarize the data. After that the power pivot. In the power pivot, we use the tabular form of the data. We mix the table, makes the relationship between the tables and then create our reports in excel. But it, these all functions are in excel form. We are playing with the data in the axle. But, after that, we, the to power BI. From power to what to power BI. Power BI is the graphical representation of your data. You will make the analysis of the data. Within that tables as well as with the graphic also. And some of the techniques which is not used in the power pivot and power pivot tables are the excel.

We will use in the power VI. And we will discuss on that one. That's the dex formulas. Which we will discuss. We I'll already share with you the data sets along with the ah power BI. Microsoft. com website from where you can download the Microsoft desktop and install on your system. It is not the big deal. We have to do the data set.

Which is the adventure works, cycles, global manufacture and summary of that one we will create there the reports related to sales, revenue, profits, comparison of the regional profits, profits, performance and the product wise analysis. And we will also prepare the targets and compare the targets with actual performance. objective our, what are the, our objective with this one example, we will connect the data, transform the data, and prepare the relational data model, and then by using of the dex formulas, we will create our reports.

when you will install the power BI, you will see face of the power behind like this. Then you will find all of the screen. And what is the screen is meaning that If you will find here, there is the visual report sign. data table sign and the third one is the relational. On the left side, you will find three options which will provide the visual reports, data tables and relationship, relational, relationship model. Data model. On the top, you will find the Kurban. There are also many options. Which we, we, which we will use, when we will create the reports. Right side you will find the two options, filters and the visual plan. Filter is basically we will use the filter slicer like the slicers in the axle. We will use the filters. Filter out the data on the what conditions on the date wise, month wise or on the sum of the product conditions. And right side in the last visual pen. Visual pen is used for the graphical presentation. We will pick the graphs from this one visual pen and prepare our reports.

Now the power bill work. How it is working? Let me explain this one. When we will pick the data from any source whether it is a excel, whether it is a PVC file. Ah CVC file, a text file. First thing is the power BI will connect the data from that one sources. After that connecting the data, we will use the query editor to cleansing for cleansing of the data. After that, we will create the after cleansing the data, we will ah extract the tables and then make the relationship between the tables.

Why we will make relationship? I will explain. So in the next session, when we will use the data model session. And in the last we will create the reports called the tattoo basis. In this way, the power wave workflow is connecting the data. Prepare your model and then present your reports. You can get the knowledge also from the Microsoft, WBI blogs, Microsoft, YouTube channel, Microsoft groups are there also and so many things you can prepare from there. And ah one thing more here after all the section, after the first session of the connecting data, we will give you the time, raise your hands and we will allow you to ask the question. In this way, question answers, session will be there. Five to 10 minutes and after that, we will go to the next session. Okay, let's start our first session here, connecting, cleansing and transforming data.

how to connect data. question is our connection of the data. I will show you how to correct the data. Data is connected with the different sources. Let I have already opened the Microsoft Power BI. Let me open that one. Here we have get data. If you will open that one, get data, you will find so many option. There is a tons of options are there. Excel workbook. PowerBI database, power via data flow, data server, SPL server, TXT file, CSP files, from web directly you can get the data like the Facebook, data analytics, gig, and the other so many ah softwares are so many websites are there which are giving the data online and you can get the data from their sites.

OBT data and if you will click the board, you can get the options also, more options, How to get the data? There is a save XML file and also we will connect with the directly folder. We will ah it is a very important one, the folder, then we will connect that one our data in the example. Ah to the folder and check how it is connected. SPL server and IBM and we the so many things are there. There is no need to study all of these work. Just we will connect the data. It is a same function whether it is a excel, whether it is other file, whether it's a CSV, the data will be connected through this one procedure.

after connecting the data, it will come to the theory. If we will connect the data, there will be one file, it will show like this the excel file or the excel table, then we will transfer, click on the, I will tell you about that one. Transform the data. It will go to the query adita. When we connect the data, it will go to the theory editor and we will mostly our main function will be in the editor because your raw data having a so many unusable data. which is don't have to be used in your reports. That's why we need to believe that one data.

We need only that one data which is useful for our information, useful for our reporting purposes. Useful for our dashboards. That's why we will editor is the best thing where we will use to clean the data and reshape the data. some of the formulas are also we will use here and very important thing here and very useful thing is here at which we will do the steps to further cleansing of the data for prepare the data it will be recorded in this one QE editor when you will add more data when you will add more files the same process automatically do you have not to do again and again to clean the data in your report it will automatically updated everything it means only one time you have to do this one.

Next time, it is very easy, only just refresh your data, your reports will be updated. It is the very useful technique in the power game. It is it will give you immediate results what you want. Don't need, there is no need to again and again prepare the data, prepare the the refresh of the data, prepare the ah changing in the data. No, just you have to update your data and refresh the data. It will give you the results which you have already recorded. In the QA data, let me explain the QA data. Here we have the other top QRE tools. After that, you will down from the QR tube, you have the formula bar, where the some of the codes are written. It is called M code. There is no need to learning of the M code. It automatically back and automatically what, what you will do the process, it automatically generate the code. And on the left side query list, here is the files which we are connecting with the data through the power VI. Here the list of network files will be there. And on the right side you will see the table.

Table name, there is a properties that down. There is written the table name and then after that down, there is a return applied steps. Applied steps means when we are cleansing the data, we are using some may be we are deleting some columns, may be we are changing some column, may be we are using some of the formulas, the steps will be recorded here. And the when we will update the data, automatically these step process full of the data. And it will be easy for us. To do the next things. Theory editing tools. As I told you about the curated tools on the top. There is a three tabs. First tab is home. In the some of the functions Which all the data will we will use for that one, whether it is, we have to change the, remove some of the data columns, separate the column.

We will explain one by one different tools. Second tab is transform the data and third one is add columns. Three tabs mostly we will use and we will use that one tabs to prepare our data. basic transformation properties. From these ones specific tools, we will use the basic transformation properties. And in these basic transformation properties. Main thing is our how to remove the columns, how to make the headers, how to make the data sets, their types, how we can, we can manage that one.

We will check with this one with our example. will come again this one ah slide again. The same way the tax and number related properties we have so many tax and number related properties where we will use the same technique, statistics techniques and ah there is ah ah standard techniques along with the other number related like the sum minimum maximum at absolute values. So many ah properties are there. We will not discuss all of the properties. the main properties we will discuss. Inshallah. Let's see, we will use that one. I'm going to jump into the demo mode and gets 100 team practicing with these tools. Excel file. Which I have explained, I will now show you practically. You will click here all files, it will show you the some of the files are CVC file, some of the files are excel files.

I have changed the pattern of that one, that every file can be correct easily. connect the adventure work product. See here We have the adventure works product. It is showing the name and file. We have clicked, it is showing a little bit about the data. What is the data? Check that one data. Okay, all the columns, product, price, product cost, product style, everything is there. Model name, name. It is a product at all. Actually, our objective is the company Adventure Works. Even as the sales data and we have to analyse the sales data. as we will analyse the sale return with the dashboard for our management. Here we have the three options as I told you previously, there is a load transform data cancel. Cancer obviously it will be the cancer load, it will directly load into the power VI two.

This data, this table directly imported to the power BI two. But we don't need to directly load into the power BI. We want to transform data first. If you will click on the transform data. See here, it goes to our query editor. Here is the file, adventure works, file purify, Here is, you will see, here is the applied steps. Here properties, table name, and this is the data view. Where you have the data. All the columns, product key, product, sub category, category, product, name, product description, product colour product size, product style, product cost, all the colours are there. We will do some of the functions here and learn about the properties of the QNE editor.

In this way we can clean and reshape our data for the data analysis. Further data analysis in the power BI. Okay, as we have seen in that one, basic transformation, we have checked that there is a remove columns, add columns, Remove column is used to the review unwanted columns. Which we don't use, there is no need for the in our data analysis. We will remove that from the columns. And the other options, we will see that one also. If we will go to the auditor, you will find here applied step.

The curate it will automatically three to four steps, it automatically applied. Here is our M4 that is explaining all of the process which it is automatically done behind the scene. Source. If you will see source, it is our source. From where it is table, it is picked. From the sheet. Biden. table form, it is table form and adventure was product. It is the source from where we have taken the sheet. The helpers of BBI training material data set. It is giving me whole of the source of that one file. Next, navigation, it is navigate the data, connect all the data from that one source and put it in the fury editor. Promote headers. Sometimes what happen if you will see on the screen, there is a, on the top on the header, which is going to be yellow, column one, column two, column three, column four, column five.

It is a business intelligence tool. It is a BI tool. Automatically, check. What is the header if it is not giving the header, we will use here the option on the top. We'll use the option here. Use first row as a, as a headers. We will use first row as a header. But it is a business intelligence tool. Automatically detect that these are the headers. And it is promoted to the headers automatically. But then change if there is any type, change the type automatically. If it is the numbers, it will show you the numbers. If it is the text, it will show you as a ABC like this. The a text and every type it will show. It is a whole numbers one, two, one. 2, decimal numbers or whole numbers which are the currencies, you can pick the currencies, if it is a dollar, we are using the PKR or the other branches, that's why I am not using here, the currencies, only just numbers.

Okay, let's start. With it. we have here first thing to let's check okay here we don't need the colour product size if you will see the product size here is the zero thirty-eight forty-four and LMS it is a mix the numbers and the text. But I I don't need this one. Small. that you can see easily. Okay. I want to delete this one. I will go here. Transform and we can from the home we can also delete that one.

Columns. Values, transform health columns, so many options are there. We will use that one. To delete covers. remove garland. If you will select this one column, it will remove this one. There is other options also remove other columns. It means if you have selected one column and you don't want to remove this one column, you will remove the other columns. Here, we are going to remove our this one column. And interesting thing is here See, here is the change type. Product size, we don't need this one. We have removed this one. Product size is removed. If you have done anything wrong or you want to be again make that one step. You can just remove that one step, your data will be the on the same position. It means, it is very easy to make that one steps and reuse that one data also. If there is anything, if you will add the more data, it will be go through whole of this one all steps and clean that on that.

Okay, let's remove that one. Okay, we'll have to move the corner. Got it? Because the name of the table. Let me change the name table small. POW I will explain the lookup table when I will go to the next session. Some of the tables we will meet the lookup and some of the tables we will make the data tables.

Okay, now we have the name. close and apply. When you have done your steps here in the query editor, you have updated your data, you have cleaned your data, then go to the close and apply. It means your data is now, you are closing your career data and you are applying that one all steps on that one data. As we have already know that, we have here the three steps. It is the canvas and in the canvas page one, it is our page one canvas. Here we have the report. We will create our reports here. It is our data table. If you will see here, if I will click on the AW product lookup table. You will see here our data is the same in the table form. It is. Remember that in the power BI always use the tables.

Automatically from theory we will transform the tables. And if you have the directory tables in your excel, then we will pick the direct table. I have the date table as a direct table, then we will pick that one. We will see these all the steps are there. And if we will go to her relationship, we will find here our front table, now generated there. It is our product look up table. in this way, we have got a one table from our data source. transform it to the curio editor for cleansing, reshaping our data. After that, we fetch that one data into power BI. We'll go to again our shape. Now, there is a so many other functions. Which will one by one we will discuss. And we will use that, how we can use these one tools. Also I am checking ah what is the next steps we have to plan. basic transformation data we have ah check that one we have removed the data we have upload that one data and the next is the text and number related properties the tax and number related properties we have the other it is there is a two type tabs one is the add column and one is one is the transform some of the properties are same in the both tabs but it has the difference and I will explain how to we deal with these wonderfuls let's pick up one other table.

To your data The results with other files, I will pick all files. Let's pick the customer table. Okay, I have click on the customer table. Here is the customer key, prefax name. Okay. Good, data is there. All incomes, total children, educational, optional, homeowner, okay. Transform data. It means I am taking this one data to a my query auditor. Cury editor, we will transform reshape and change the data shape. Okay. Let's go. Here, let's see okay email address, everything. What is professional data is there. The same thing is here. The applied step source navigation and change type some of the types are automatically ah curated data has changed. It is numbers always check whether it is a text, whether it is a numbers. It is very important. You have to check that one. Okay, birth date. Here you will see that birth date. Here is written. Whether it is text or whether it is a number. Okay, we will change that one. Click here. Data type, any type change, it will be the date. Because it is a birth date. As we have changed, there it is change all as the birth date of the customer.

Okay, if you will see here the steps. Here, change type. We have changed the type of that. That's first thing we have to check here. If you will see here the prefects. all the words are in capital form. All the words are written in the capital form, okay? Now, we will go to the transform. Transform means we are transforming the data. It is existing data which we are working on that one. If I will go to the air data, it means we are adding something. We are adding columns. We are adding something here. But in the transform, we are modifying our data. This is the difference between the ad columns and the transform. Transform, we are modifying the existing debt. Okay let's go to first format.

This is our option which we have studied in the slide also, the same. Text and number related. There we have to format where we will format the data whether it is a uppercase, lower case, trim, clean, add prefix, add suffix, we can use that one. Here, it all the ah words are Mr. MSS or whatsoever it is written, it is in the capital form. We will capitalize each data. If you will See, if you will capitalize, it is the capital, first word will be the capital.

It means it is reshape your data. In the same way, we have here first name, last name, separately written. Okay, we will use, if you will shift, plus shift and then the click on the column, it will give you the both column will be selected. And the same extract. We will use capitalize each word. It is again, all the capital words ah reshape in the proper way like the first word is in the capital and the other the small Other words are in the swan. Okay, I know that these one chapter verse, it is a first name, Mr. Britain and first name and last name, but I, I want in the one column this all. In one column, I can, in one column, the full name. In this way, now we have, we, I am preparing the new column with the full name. That's why I will go to the add column. And custom column. In this way we can make your first name, second name and third name or you can make this one.

This is one option. There is a two or three options you can make this one. Or you can highlight this one. Both columns and use number. If you will see other options are in the grey. And only working option which is on option is the merge column. You can merge column. Easily. But we want too much. With the we want to match with the space and new column because I am in the add column option. That's why it will generate the new column. If I will say merge column option, I will use from the transform. It will not create a new column. It will just merge the three columns. It depends upon the user. How he want to present that one. Here I am writing the new column name. Full name. No, the existing columns are the same, in the same shape. And if you will see in the last, we have the new column with the full name. In this way, we can create the full name column.

It is from the add column. Remember that we are using the same techniques from the two ways In the transform data, we have also here seen the merge column. It means transform data means we are transforming the existing data. If I will click on this one, merge column, highlighted colu merge in the one column and show the full name. It depends upon the user. How he can use this one? In the add column, the same option, merge column is there and when I have merged this one, then it showed me the new column with the full name option. And the same step here is available. Inserted merge column. We cannot understand what is the meaning of this one? We can rename this one also. Here. insert it. and also the same. We can use this one technique also. To name the column. In this way we can easily understand that this is a new columnist.

With the full name we have inserted here. Okay. Now we have learned about the some of the techniques that is the how to merge the column, how to create the new must column. Let's go to the next technique also. Here we have the email address. It is a long. We want to know about the user name. Because it is a email address is the product. Just I am ah telling you about the ah ways, how to clean your data. May be the different type of data is there. You need to extract the sum of the figures from that one data. You have to extract the sum of the words, some of the numbers, some of the ah useful information from that one column. How we can extract that one. We have the same in transform, we have the except. We are, now, if you want to add the new column, then you will go to the add column.

If you want to extract from here, you can extract from here. But we need from this column, extract only username. Okay, text before delegator. diameter is at the rate. There is also the advance options. No need further from the start of the input from the this one zero numbers noted for this one. It is very important. What is the telemeter? What is it is a dot, whether it is a ah other dash, anything. Here we have the add the rate. It means username is before that one email address. We want the username from this one column.

At the rate, when we will use okay, here we will see. our text before diameter is there. We will change the name. Using me. in this way we got the user dip from here. It is very easy. Just apply a one step and your data will be clean. Okay, let's move. We need only adventure works. The middle name, domain name. Okay, how we can extract same process, extract, text between telemeters.

Which telemeters? At the rate, Between this we want the data. Okay, click then if you will go to the last, we have the adventure works. All the domain name is there. We name this one. in this way, our data is clean now. We have the domain name, username, everything. We have the full name of the customer. But if you will see here, adventure works is the same. There is a dash is there. Okay, we need the adventure works only. And it should be in the proper way. let's go to the format. Capitalize. Okay, but there is a dashes also. You have to remove that one dash. Okay, then go to the home. And there will be the place. Yes, replace. What we want to replace? We want to replace dash with one space. So guys, you have seen here that we have clean the data with very easy steps.

There is no need to rewrite on the course. Just you have to from your tool bar, you have to make some steps and all the steps are recorded here. When the more data is added, this one file. Maybe one thousand customer is more added. You will just add the down your data table. The main data table in your excel file. in your file in main data table you will just copy paste the new customer if it is one hundred one thousand automatically all these steps process the data and the result will be there. It means you have just copy paste them. There is nothing else. Okay, just now let close and apply.

and you will see here in our tables we have the adventures work and adventures product look up table and the customer table We have now two tables. So, I forget that one. We have to change the name. The small name. In this way, we can easily always use the small small name because in the we are, we have to use that one. No problem. It is very easy. You have the option in the power BI transform data. Go to data. and trust from data, again you will click. Your editor will open again.

When you will see your editor, there is the same tables in the query pen, you will see the tables. Product customer, adventure works, customer table is there also. The same tables are there. It is very easy again to modify your table. Okay, I forget that one. I will go to the customer table. I forget to make the name. Let me make that name here. customers. Customer underscore looked up. but it again I will make close and apply. And your changes will be there. You are there, your seat, your table name is AW customer, look up table, DW product, look up table.

In this way your board tables are there. let's go to the other options. we have the number options are there also split, we have split the columns with the delimeters by using the delimeters we use the upper case capitalize the words, we have used this one, let's again go to our tables and check. The numbers. How we will use the numbers? Data transforms. Okay, here we have the lookup table. But we see the plan. What is the next? the transform data we have now again pick the product look up table and understand the some different concepts also you see here the product key let me start of this one we can sort out from here we have the startup option on the top here also on the home tab we have the sort sorted option you can see.

From here we can also from the right click. From here also we can use the sorted option A to C. We can sort. As you will perform any function in the it will be recorded in the applied steps. This is our data. on the number options. We have to transform the number options. Statistic, standard, scientific, trigonometry, rounding off, information. Related to even odd sign up. We are not dealing with all of the data. Some specific options we will study here. This is the product cost, product price. I have picked the both and rounding off. We have the rounding up rounding down. Which is the mathematical function we have rounding up to the upside figure. If more than the file, we will round up to the next zero and rounding down we will be running down to the below one. And round up to the two values. It will ask us two, three or four values. Okay, let's see the round up. For two decimal places. Okay. this giving me a round of option. No, our data is wrong. And the, the step is recorded here. Okay, let's see.

To the next step, this one is the rounded off. If I want to see What is the unique values? These are the unique, two, one, four, two, one, five, two, one, eight, two, one, nine is a unique value because it is a product key. There are so many product keys and unique values. That's why these are the product. You can make here the unique values also. If you want to check product price, which is the higher product price. You can also check that on. Product price. the statistics we can use the sum of the product price, minimum, maximum, okay to check maximum value here. If we will click here, the maximum you will see three thousand five hundred 7-eight point twenty-seven is the maximum value. But in the queue editor, there is no need to use these one.

I will delete this one step. My data will be in the same form. In the same way, if we are transforming data, statistics, we use the minimum, what is the minimum price? What is the minimum product price? We can use here. minimum It will give us the minimum. Average price, we will click for the average four, four hundred forty-one, point 5, five five is the average price. we don't need here only one price because we need the complete data which we will make some of the calculation on that one data that the meaningful information. In the same way, in the transform data standards, multiply subtract, divide Some of the functions are there.

If here, I will add the discounted price. Here is the product price. Okay, let's see. Multiply with the point nine, 90%. I have 10% discounted price. I put in the okay. Then you will find it here. thirty-four. It is Let me delete this and I have wrongly. Put it on the product key. Let me on the put it on the product price. Product price and multiply by the point. Oops, it is now it is correct.

Hopefully inshallah. This is the product price. Thirty-one point four nine one. Okay, make it round. personal places. Okay, in this way we have ah product price, discounted product price. Yeah. Product price, discounted product, product price is thirty-one. No need for this one. Delete. Because it is, I have given in the transform. It is wrong because I have given in the transform. It is modifying my column. I need the new column. I delete that one. The same product price is there. Okay, I will go to the add column and then again, I will use multiply. Here, point 9. Okay Now, it is giving me the after multiplication, it is giving me the discounted price. Okay, rename this one. Discounted price. In this way, I have the new column. Now, you got the concept of the add column and transform column. When I have done the same transform column, the same procedure. It is change by product price to thirty-one. It did not make a new discounted column. When the same option, I have used from the add column. It created the new column, discounted price column and given me the new column.

Okay, let's go to the home, Apply, close and apply. All the changes are there. Perfect, we have cut our data. Customer and product. There is no report, data tables are there. And if you will see customer table is also there. Product table is also there. Let me save this one file because I did not save the file. Okay, let's check where I am saving this one. Yes, training, okay.

It is a So Whenever you create this one, your tables will be there before creating the reports, we will save our data in the file. Just take a 2 minutes break and we will come back with the next steps, next formulas. And I will explain you about the next formulas. How we will use that one? The next tool bar and the next formulas. Okay, sorry for the disturbance because some of the participants asked us, there is a noise Because the, we have changed the room and inshallah may be know it is the better. There will be the no noise. And we will continue this one. That's why it is a unannounced break we have made I think you got the concept of how to change the things, how to change the cleansing of the data, reshaping of the data, how to produce that one.

Let's again go to our file. The same procedure, same file I will work on that one. Transform data. Sometimes, what happened? We have, we have to create a unique keys. To make the relationships between the two tables. Or among the other tables. We have to make the relationship of one table to the other. May be some of the keys we have to create. We have to generate. Then is a one option in the add column. There is a one option that is index column. If we will create the index column, see here it is created. The index column two in the beginning from zero to if I want to make the again recorded column here I will remove this one. Again, I will create the index column. From the add columns, I will use the index column. Index column, it is asking will from zero, from one or custom. If you are too much crazy about the, how to make the sun, you can pick the custom. But, best is from zero or two one. From zero, if I will pick, it is giving me the index column. We can rename the index column as well as if you will see from zero to one, two, three, all in the sequence.

What, what is the use of that one index column? Maybe, I will contact using the contact in it or merging the two columns, I will make a unique key. For my further use. But in this practice, exercise, we don't need this one, that's why I am deleting that one. Index column. This is just for your concept. Go to and close and apply. If you will see here still we are not working on the reports. Here we have the tables. And in the tables we have customer lookup table, product lookup table. We have the board tables. Here we have the relationship. There is no relationship between the tables. Let's go further. We have studied here. How to make the text, how to make the numbers, how to make the ads, subtract all the minimum and maximum. Same formula is used, just you have to pick the minimum for the minimum, maximum for the maximum. These slides will be shared with you after the training.

You can easily understand this one. If there is any question in the last, we will discuss. On that one. Add column properties, we have the add column properties. Somewhere we use. aware we use that one add properties do one. Again, we should go to the okay. go to the product Here we have the Let's Let me check with this one. We have transformed data. And here. No. I think we have to pick the another column. and we will add the more columns and then we will apply that one. Okay, in the ad column let me explain that on ad column and then we will use in the ah our example. Add columns properties we are adding some of with the conditioner. We will use the condition. Sometimes what we have we are using the conditions in our ah power behind tables by using the text formulas.

Sometimes we are using on the tables. Sometimes if we know properly about our data metrics we know that the data then we will use that web technique in the query editor also. Let's get the more data there, then we will use this one. Don't go to the gut data. Excel. all files. Here we have the sales table. Okay, let's pick that one sales table.

If you will see here is ah the different shape, there is the data is because sometimes you will pick in the files from the CVC, TXT or the excel tables or excel files. Then every time it is showing the ship, where the file origin, comma, the page, some of the rows it is showing in the table. What is the data type? It is the columns, everything is there. Then you will make the same procedure will be the same. Note or the transform. And best is transform the data. It will go to the queer editor again I went to the editor, okay, they have the same files, Now, we have the three files. Here, product, look up, product, customer.

And adventure works, sales table. Okay, let's change this one. the name AW underscore sales 20 17. Okay. order numbers are there, product are there, customer key is there, every data is there, harder quantity is there. Okay, some of you will see here in the part of quantities, some of the orders are two, some of the one, some of the one. Okay, let's share TAD conditional column. We have studied the index column. Now, we will study the conditional column. If I will pick the conditional column, custom name of that one conditional column. Let me order. Type. Then you will see you are already familiar with that one. Ah if condition, the same is very easy to use here, column name, order, quantity. Okay, equals to one. If it is equal to one, then It will be single if we will add more class, you can add so many clauses here. It is the if condition that easily applied here. Order quantity equals to a greater than a less than is greater than one.

It means the multiple items. multiple items. Let me write down single item And if there is others, then other. if you will see here the same procedure if there is a order quantity is true it is giving me the multiple items If there is auto quantity one, it is giving me the single item. If, in the whole scenario, you can see it automatically will change your as per your given condition. The sales table has given change all according to the given condition. concept of the conditional column, how to we can make the conditional column usable here. Let's go to our slides again. Along with the other, there is also so many options in our tools. It is a pivoting and unpivoting merging queries, a pending queries. I will explain this one. To how to use these one things. First thing is the merging urines. Merging queries is curious allows you to join tables. Based on a common column. If two tables have the same values, same name, same value, same numbers, like, as we are using the, we look up or match index in our excel files, in power BI we use the merging queues.

It is the cover leak, common relationship between the tables. We will use the link and merge the common values. Let's try here. If we will see here, in our table, there is product look up. In the home tab The home tab there is transform just let me check such queries It is we have the much curious, there is two options. Murch curious and merge curious as new. When women merge query, it means your existing table will be the same. and you will add the values from the other table to this one table. If you will choose the option merge queries as new. It means your existing table will be remain as it is and there will be the new table will be generated by adding the new values. Existing values plus the new values. In this way, your merging curious means you are row wise, columnal wise, you are adding the data.

It is giving you table, expanding the table. It means it is expanding the table. Before you have the ten columns, then the 15 columns, then the 16 columns you are on the basis of the sum of condition. That sum of the common values you are merging that one in that one table. Let's ah check this one. First option. Okay, we have the sales table. Got it. This one says table. And here you will see the product key. And also have the product look up table. Look up table product, look up table, it means if I will pick from the product lookup table, we have also the product is there. As I will click there, you will see here selection batches could be nine thousand four hundred 31 of the 29, 481 rose from the first table. It means perfect, it is match from the sales table to the product lookup table.

In this product lookup table has the same products which is in the sales table also. We have the data is regarding the sales, it is there is the order of the specific products and the same products are we have the in the other tables. Which is a unique values having the product name, product, specifications, product, key and the product ah colours size product different types of ah this one is available there. And which is matching with that one. If we will make Okay. Now, you will see here, we have AW product lookup table. We have merged that one table with the sales table. We have merged the product, lookup table with the AW sales table. If you will see here, the double arrow side Here we have the tables.

It means table is merch. But we don't need the table is merged. We need the data From that one. Table which is on the basis of our product key. Here is the product key. It means five, two, nine, two, one, four, five, 40, five, two, nine. These product key, the same data should be here. If I will expand this one, this is the expand button. I will expand this one. It will ask me the options.

Which column you want here? Which columns are required? To merge with this one table. Which columns having the data on the basis of the product key. You want to merge with your sales table. In this by table will be expand. Okay, let's see. I don't need all of the column. I need the product key, product name and any other product any other way I can pick that up. I will meet them, okay? Then you will see. All of the colors are there. Okay. Here, I think the product color we have. Red, not applicable. Okay. Got perfect. Okay. This one is also. there. Five, two, nine, product. It is equal to five to nine. Five to nine is equal to 500 is there. Five to nine PT speaking. It means, if you will see there is the three columns are added in this one. It is the merging of the tables. It means expanding the table. We have two, three options, merging tables, merging queries.

Merging queries always merging the tables from one, two, with the other table. In this way, whenever you have the different types of tables, different types of data. We can merge on the some of the conditional bases like the unique keys, like the product keys, we have to create that one and we will merge that one to make a one table. The other option is a pending queries. Appending queries allow you to combine First take the table that share the exact same column, structure and data types. If your headers are the same, if your columns having the same name, same characteristics. Then you can append that one tables. Appending is below the table. It is not expanding the table. It is adding more data below that the first table. In this way we can that one table. Let's check this one. For this purpose, let me delete this one expand. I have already explained you how to expand. I will delete this one. It is deleting. The next step is also deleting the same data is there.

That is added traditional column. I am also deleting. Now, my raw data which I have extracted in the theory. That is the sales table. For this purpose to understand the append, we'll go to our new source. From here also directly we can pick our data. In the query, editor. Previously, from where we have picked the data, we have directly connected through the power BI two. From here. Let me show you. From here we are getting. Now, you can also get the data from the power queue. users, excel file, okay, sales data. where there is not cellcutter, may be it is the other type of file. Yes, it is the CBC files actually, that's why. I am adding the 2016 file. It. Just picking the data, okay, that's nice. Extract using the same data, data, data quantity, okay. Get it this one. Let me change the name.

With a new sales 2 thousand sixteen. I am explaining the theory. How we are adding more data. Now we have previously 2017 data. Now, 2016 data. to our Check where is the option. and click here. Now, the same here, two options, append queries. If I will append, it will add the both tables collectively or I want to prepare the new one table. In the mercury and the merch new, the same in theory and the let's try to the second option previously inverse query we have ah check the first option. If I will a print queries, click on the apprentice is, if you'll ask me. Okay, 2016 festival. 2017, we will add this one table. Okay, two thousand sixteen, first table, second table is 2 thousand seventeen. Click. Okay. It is giving me the name append. I will make it name. sales data. Now, you will see here, there is no further more columns added.

But whether the data is added in this one, let's check. Click on this one. Order date. Okay, two thousand sixteen, it is showing less data here. Load more. It will give us the more data. Wow, it is two thousand sixteen is data is there also. And if you will see here, 2017 is data is also there. It means it appended the data. It added the data. In this way, we can add more and more datas. In our theory editor, and the same can be transferred to the next level in the power BI for further analysis.

But main thing here is, every time you have to do go to the new source and pick the two thousand seventeen, 16, 15, so many data. Every time you have to do and just refresh and it will update your data, sales data. There the best option. Rather than this one, there is a best option. We use this one option where we are copy paste the data on the first table.

If I have the sales table 2 thousand 17 and I am copying the ah two thousand sixteen, 2015, 2014 or the 2018, nineteen, 20 data on the same file. Then it will be refreshed the same data. But there is a more option. There is a good option than this one. That is the folder option. For better control use always further option to append the files. What happened in that one? If I will go to our my query, that's delete two thousand sixteen table. but it is giving the message, delete query. The query AW 2017 cannot be deleted because it is being referenced by another query. It means actually we have appended these two files 2016 to make a new file due table that is sales table. Sales data. That's why it is not deleting this one. First, I have to delete this one from white table. I believe two thousand sixteen. I will delete 2017 also. for the concept how to take the data from sales file sales data we picked 2017, 2015 and 2016. And understand the concept of the merch query, append query.

We have understand the concept of merch query and apprent theory. Now, we will go to the folder option. We have the folder. We have the folder of sales. What I am using? It is a very useful technique. What we are doing? We are always with and we throw our file. We are putting our files in one folder. Automatically, the folders will be upgraded with the new files. We will use the sending the new files in the adding the new files in the folder and automatically it will be updated in the query and by power we are data will be updated automatically.

In this way, there is no need to pick again and again that two thousand five, 17 file, sixteen file then append again, then append again. No, no need. We will use the folder option to get the data and directly it will be change our goal of the model. Let's go to here. More option. older connect Here we are not loading our file. We are connecting the folder.

If you will put the two thousand eighteen file, if you will put the 2019 file, if you will put the two thousand twenty-five automatically it will be updated in your power BI model. There is no need to again use of all the work. there is a very useful tool. I receive a thirty-five in a whole of the month. And if I will copy paste all of the file in the once excel file, it's a time taking. Why I will not use the folder? I will use the folder option. I will just receive the file, put it in the folder, receive the file, put it in the folder. Automatically may I hold data will be updated. How's that one? my PC Just let me go to the folder. training material Let us ask for training. See here, there is option that the boundary code is the zero one code, it's no need to understand this one, name, CVC file, these are the CVC file, that's why I have picked the all files.

Different type of files I have used in this one example to understand the ah concept in the better way. And the date and from it is records, it means and from where it is speaking from the this fold of personal PBI training material, data sets. Okay, transform data. That you will see. We have the options, the same data is picked in the power theory, or the query editor, connect these tables are connected. Now, the port files in the folder, if there is a three files, three files will be connected. Here, I have the two thousand fifteen, 15 and 16 data in my folder. That's why it is connecting to the folder and showing me 2015 and sixteen. At the end, I will copy paste the new file 2017 sales in that one folder. Automatically, it will be updated. If you will see here, we are cannot see the data. Let's click on this one, combine files.

Combine files. It is evaluating the query. See order quantity your headers should be same. The characteristics should be same. If the both files have the different headers, it will not combine because it is combining on the basis of your headers. That's why the same file which you have received like the you are receiving the bank statement everyday and you are a cop that putting in your folder that back statement.

Automatically your data will be updated. If you are receiving the sales, file from the SAP or the Oracle downloading that one and putting in the same folder everyday your data will be updated. Because the same characteristic and same file pattern will be there, should be there. Order date, stop date, order name, product key, customer key, territory key, the same is there. If I will do the okay. Then we have the whole of the data. Okay, good. Everything is good. This source name, we don't need go to here. is highlighted. I will remove that from the corner. That's good. If you will see on the ah Curicide, pure pen, if you will see, there is a sum of the others.

Came here, transform file, why it is because previously it is only the tables, product lookup, customer lookup, sales table. Now, it is the most, the helper purees, parameters don't need to go through these one, just see these one is due to back end. There is the M code is working and your folder, we are picking the data from your folder. It is due to that one. Transform, file from the sales folder. That's why it is coming close and apply And we have our sales data here. Let me check the name. Sales data. Oops, this is, let me delete this one. Because the power BI is the BI, business intelligence. It automatically detect the relationship between the tables. But we don't need to make the relationships. Now we have that table, sales table. If you will see here, I did not make the names here stable as a look up table. it is not the sales table. And the others are look up tables. Because these customer and product tables are the unique values. We have the 10 customers. We have write down the every customer code and customer name everything.

Products we have the specific products course. It is not duplicate in the table. It is it has a unique values. And the sales table it is basically the data. It is factable. factable, a data table. Where our sales are recorded. It means one order can be duplicate three or four times. One order, one product can be ordered three or four types by different customers. One customer in the whole of two thousand fifteen, 16, 10 times ordered. It means there is a more than one values. It is not unique. That's why we have the lookup tables used for the lookup values, unique values and the data tables for our perfectly ah the data where we have the recording full of our data which is the transactional table.

Okay ah that's this is the time for the namaste break. it takes I think 5 minutes. Ah and five minute after five minutes I will join again. Thank you so much for your Hi guys. After the Namaste break Let's start again. little bit ah one concept I will give you ah more after that the question answer ah session will be start then we will repeat if everything if anything you cannot understand we we can repeat that one. As you know that lookup table, look up tables and sales data table. Sales data table is my fact table. Data table. Here is my transactional data. Which I have to match with the other tables. If I need the product, I have the product key here. Previously I told you that best option is to use the relationship. Which in the next session we will create the relationship of ah data. data tables. We have merging the product columns with the sales table and it expands our table.

When we merge our product table, we saw that the columns are expanded. It is so many columns up there. The data will be huge. Instead of merging the tables, we will use the relationship between the tables. It will be fruitful, very easy. And more efficient than merging the difference. Because we have the unique values and other side we have the transaction values. There is a ah one more concept. It is called pivoting and unpivoting. Let me check with this concept if I can give you See, if you will see here, there is a two ah options are there. It's picture is like the table, Blue and with the boxes. There is simple. It means I have created the table here. I have in the excel file, we use the table.

We, I have made the table actually. Transform this one. The same procedure. We will go Then you will see we have the column one, column two, column three and here two thousand one, 2 thousand 2, 2000 and here is the nerve value. See We are understanding the concept of pivoting and pivoting. In the action, you have used already transform from column to rose, rose to column. It is little bit same but with the advanced level. If you will see this one, how do we will use this one? Ah pivoting and unpivoting.

Let's go to check the option because every month ah power BI is updating. Because it is a very advanced level ah of the there is a too many changes in the power BA and every month they are updating their ah version. That's why some of the options are here change. Let me check. This is option because previously there is written the unpifting and fifteen. Now they have just given us the ah picture here fifteen hundred and fifty.

This is the option. We have the unpaid columns, unpaid other columns, unprepared only. Column. See we have here the unit unit sales. Mostly whenever we are working on the data. For the analysis purposes. The most common is the tabular form. It means your data should be in the table form and in the columnal form. Then your calculation will be easy. Your tax formulas which we will study in the next session. Ah it will be easy to apply on that one columns. If there is so many columns, it will be very difficult to apply the formulas. now we are unit As we have changed that one, you will see here as we have unpievet the table. other column. I have clicked on the one column and the unpavoured the Qatar. Unit sales and revenue, I have make it the same. Here, we have the values two, 2001, two, three, four, five, year wise. And the column is here attributes. And the same is here. other revenues from here. In this way, it made the two or three column to make a only two column.

Here I will put the description, ears or something like values. Let's check on the mute this one. the first Let me check this one option. 150. sub columns. stuck with this one because the option has changed here Start. from the wedding space. if I am stuck with this one, let me try once again rather than in the last over try from my previously practice example. Yes, got it this one. First we have, our, table, promoted the headers. First, I have to make the headers. When I have put it as the headers, but ah, it is not the typical one the, we will make the haters first. We can delete that one column or row also.

After that, we will go to our ah option. columns, I have selected this one and unprevared the columns, other columns. I have unprivered the other columns, then it will unpifted that one, it make the attributes 2001, two, three, four and on the basis of the column, unit sales and revenue. But I don't need the again come unit sales and revenue again and again, it should be in the one column. Then again, I use the pivot option.

Pivot columns. Then it is scripted, then it is here by ears. It comes years and unit sales and revenues. In this way, we can use the pivot and earn it pivot options to clean our data, make the data in the sequence. In this way, we have the ear wise, unit sales, revenue and we can easily apply our formulas tax formulas on that one tables. But we don't need here, it is just for the concept. Ah because ah I was confused with that one options ah previously it was not the same.

It is updated now. That's why it is coming on the this one shape with the different ah with the screen shape, the picture shape that this is the unpifting columns if you will want to unpiever the column you will use the unpievered column. If you click on the column and unpiever the other columns or the selected co you can prevent two or three options are there. In the same way, if you want to make the other option, pivot again to make the in the sequence, the data, in this way you can pivot against that one. It means the transform of the data is little bit different. It is not taking the unique values. Here, in the pivoting and unpivoting.

It is working on the unique values. If you are pivoting and unpivoting the data, it will work on the unique values. It convert into the unique values also. If there is a two thousand one, three times are coming, it it will make it one type, 2000. But we don't need this one, that's why there is no need to is a demo data we don't need this one here separate or you can delete this one also with some mother.

With my file. In this way, now we have explained, as per our, tools. We have explained mostly all the tools, home tap, transform, add columns, mostly we have discuss ah common properties which are using when we are transforming our ah data sets into ah tables and reshaping, cleansing and transforming of that one data. Now, if there is any question, raise your questions, we have the session for the questions. If you are practicing along with that one, you have such of time now, you can make ah cut ah from your which is shared with you ah the example tables. You can fetch that one data. In the curio data, perform that one operations and check. If there is any question or there is any problem, then we can discuss on that.

please ah raise your questions ah ah raise your hands that we can ah start the session of question answering ah anything you want to ah and if there is any ah debate of the concept, if there is need, any repeat of the concept, I will repeat that one. Then we will go to the next ah creating data model. You have the five to 10 minutes. Please raise your hand for the questions.

Then I will reply you with that one. This one will do this. Like ah ah there is a multiple tables we integrate with ah ah for the presentation purpose. So is there any possibility to get ah data from multiple branches and ah present in a one stream. Like to integrate a multitude data in a one table. Is there integration on a point of view? Just we need to upload the data from Actually, I told you that the, there is a different types of data, multiple streams. You can, one file, you can connect with the SAP. Fun file, you can connect with the Oracle. You can take from the CVC file, you can take from the TXT file. It means the different sources you can connect with the data model. So it's ah okay so it's like source we use.

For example we have SAP business one in ah multinational group and they want to integrate all the branches from different countries. It means we can extract all the data from multiple countries, different countries. And ah like ah in a in a graphic representation one screen. Yes, actually what happened? Ah when you are connecting the data with the power BI. It means if there is a 6 countries, seven countries you are getting the data from there.

There will be one file from each countries. We are receiving. We are fetching that one data From ah that one country and making the relationship between that one tables. Then you can create by using the formulas, you can prepare it the ah presentation in the graphical. In the graphs you can prepare the reports. If for example America and ah Germany and then Dhaka then you can say Bangladesh and then Pakistan you are getting the four files from there. And if it is the connected directive from the system. You are getting the direct funds from ah files from that one countries from their systems from that system. May or a central system then from one system you are one central source you you can get that one data. It means four countries, four files you are using then you can merge that on files or you can different files with ah make the relationship with the one table and prepare your with visualization.

With prepare your workings on that one file. It means or you can say if one file is ah coming from of the station like the if I have the sales data from Germany, Pakistan, Bangladesh, India, Saudiya, then what we will do? We will make a one file where this, there is a country's name is there. We have the one unique key with the country names and we can make the relationship between the country and the sales data. In this way, we can prepare our visualization. Alright. If that's good, if it means, it means we can create. Yes, please, please, good. Yes, sir. Yes. Yeah, yeah, please. Ask. I just clear. I just need to actually means we need to create a one folder in visual bases to integrate all the branches in a different countries and it automatically update our power BA as well.

Yes, it will be. Why I have preferred the folder? Because if you will not prepare the folder, folder is, the option is the good option that you will just put it to your file. The file should be the same characteristics, same call of same headers. It means your file will be the same. If you have A, B, C, column, then the country file will be ABC column. The data is no problem. Data is different. If you will put it in the same folder, automatically all the data will be updated. You no need to make the four or three ah tables and then join with ah one another another term. But in the folder option you have just put it that one all which you will receive, put it on that one photo where all the data will be automatically updated.

I will show in the next session ah when I I have picked the 2014 and fifteen ah the 2000 15 and sixteen sales data and 2017, I will ah put it in that one specific folder and you will see that the two thousand 17 ah data will be automatically updated. Okay, that's right and the last question is sir, so it means for example, sometime it happens if company required like a sale and asset report and it consist of five columns, gradually, but at some point, for a meeting, they exclude one column and put in the same folder. So, it means, we need to create for the separate dashboard for that.

In visual file, this is not automatic because previously, we have a pipe column. The sale analysis report but after some point, companies say, okay. Now, you have just report column, height or exclude the fifth one. So this it will, it means this will not effect on may be this will affect on our dashboard as well. Right? Well, the one column, you will not, the same file without, for example, you have the five column. You delete the one column. For the next file, you will put that one, it will not work. Why? Because when the curio editor will read that one files, it will match all the headers and then make the table.

What you will do? Why we are using the data cleansing and reshaping the data? Same file should be there. And delete that one column, the management, no need for column delete in the curio editor. In this way your column will be ah your data will be cleaned and in the power BI it will be automatically updated. The files in the folder should be the same. Alright sir. Thank you. Work on, work on queuery editor. Which you want to ah delete which you want to add, which you want to function.

It is easy in the queuery editor. Then when you will save, it will automatically update on your power VI. All the reports will updated. In the power we had ah reports you can easily update that one. If you will delete the ah file. Previously your file ah excel files which is in the folder have the file column and the next file has a four columns.

It will not work. Because the curio editor will read five columns and match that one columns and then update the data. Oh, yes, I got it. Thank you so much. Thank you. Thank you. Next question. Okay, here, one question. Where is that one question for? What question is asked that the option is not ah when picking the gut data. I think it is there is the issue that is not ah clicking on. Ah see the screen ah I think ah the screen is in front of you.

When you are cutting the file like if I will pick the adventure works return file, open it As per the question, the load transformed data, it is not active. See here, the same is here is not active. I think you got the concept Load and transform data is not active. What you have to do? You have to select the file. This adventure works file. It will show the whole of the data preview. It will show you the preview. Then the option load and transform data will be active. Let me transform the data. Return. It will go to my queue editor, okay? And I will change the name. AW returns. and close and apply. AW return table is there. It is a data table, that's why I have not written the lookup table.

I think I hope you got the concept, why it is not active? When you will click on check the table, then it will show you the, data in the review, and also the option will be activated. This next question. one question is there the can be power BI is installed on the map if you have the windows on the map then it can be installed otherwise not Mac is not spotted with that one that's why a power BI is ah ah why we use the power BI power BI is the tool of the Microsoft it is launched in the 2000 July 2 thousand 13 that's why because every system mostly the people has the Windows now Windows 11 and that's why the power Microsoft tourist flexible with each and everything. Which every system it can. But map system, I never saw that one. That's why I think the, I have seen in the blogs also. The max system is not spotted with that one. Recorded copy, copy of that one and slides will be available.

Ah, after the training, Mr. Ali can ah share with you the recorded session and the ah slides as well as ah you already have the example. If there is any issue, if there is any question, you can ah raise it through the I will support you. Okay, here's the question is there the two thousand 13 MS actor is not supported for this formula. 2012 13, MS excel is not the old version. They come to ah change ah your versions along with ah ah updated versions like the two thousand sixteen ah office. As well as now people are using the office three sixty-five. Why you are using the two thousand5 hundred thirty.

how to sort the name in proper form instead of upper case. There is a one option capitalize ah option which we have in the ah power behind ah which we have made that one. In this way one is the in the upper case and ah all other are in the ah door case. It can be easily handled with that one. There is a two or three options. Upper KS, low case and other options are also available. You can use one by one all the options. It will give you the properly. Which you which required. guys has asked for the ah lecture in the Urdu. Ah because the some of the people are ah attending from the out of Pakistan from abroad. That's why ah we are ah given training in that reviewing training, training in the English. If you have any question, no problem, you can directly contact with me. You can send me the email or anything. Easy day we can. Handle that one. I can give you the full support of on that person. Assalamu alaikum sir. Ah MS office to 2013 air is not some formulas. Because some formulas is not going three sixty-five.

So please can you tell me about updated version and have all updated pharmagrass. My dear 2012 and 13 is the old version. Now, you should shift yourself to the 2016 excel and office user 2 thousand sixteen office. You, so many things are there which is not working on the old version. Even in the power BI. It is ah two days before my power BI was the for the old version and surprisingly when today I am seeing that the power BI, it is change.

The options are changed and I am confused from where I have to pick. That's why I have to confuse with that one and 15 and 15 options. Previously there is written properly fifteen hundred and fifteen. Now, it is the option is change. That's why every month a power BI is updating the same way the excel is updating. Always use the updated version. So many formulas are not working on the 2000 ah thirteen and ah 13 and twelve ah excels. And it is starting from the two thousand sixteen. To use that one because it is ah ah launched by the ah Microsoft, they cannot change that one. 2016 has the new formulas, everything and pivot tables in the 2 thousand12 and previously you will not use the power pivot. In 2 thousand sixteen, you will use the power pivot also. I told you about the journey of the power BI. How from Excel, Macros, Pivot Tables, then power pivot tables where we make in the power pivot table we make the same thing in the relationship.

Data tables, desk formulas, all are used. And then we use the In the accent form, we use the our workings. We use our records. But in the power BI is a advanced version. We use the same thing and creating the visuals. We create the graphical presentation of that one working. I hope so. Assalamu alaikum sir. Sir, can you tell me that how to change the data type of certain rules? For example, in the date column, there were certain rules where the date was already mentioned but in some roles, there were numerical figure like four, one, four, nine, one, double, three, six.

So, I only want to change ah the four, nine, one, double, three, six and not the actual date which is appearing. So, how can I do that? Thank you. No, the date is different in the because if you are ah picking from that table, the date, how you can change, let me explain on the ah screen. Yeah, because there were two dates ah appearing on that sheet. Ah one was in the format of like zero, one, zero, one, two thousand and 1. And other format was four, nine, one, double, three, zero, six.

So, I only want to change that four, nine, one, one. See here in the customer table, I'll have change the date of birth there. change that one date of birth. Let me check and explain you. Yes, sir. See here, the date. Previously, it was not like this. Okay, because in the queuing editor, you are working on the columns. That's why I always ask you the column And how you can change the date.

Here, right click and you can repeat. If there is any dash or anything, you can replace the values. And best option is go to here, change, date. And it will change your date. In the period data is the best option, you can from that data type, you can change that one. It will change more of the column date. Only not selected a roast. It will not work on the selected roast. Because date will be the same for all the columns, all the table. It doesn't mean that the one crore have the different date with the dash, other row has the with the ah dots and other road with the ah slide slider the bar. It cannot be cheap. Then you can only change from the data column and change it. Date and time. If there is a time you can change also. You can option take the option from here. That from right click on this one, then you can get or data type.

Hope it is you got the for the fifteen and fifteen we in the last we will ah make the again this one in the ah in the last week I will explain again pivoting and here the one more question the can we make the balance of profit and cash flow and notes by the updated trial balance or notes yes sure you can make everything where if there is a data you can prepare your reports I have also prepared the profit and loss balance sheet and budget working on the power pivot as well as the power BI you can prepare there is an other training course for that one It is in the last I will tell you about that one. Advance reporting in the power bill. It is totally related for for the ah related for the ah finance professionals who want to make from the trial balance to profit and loss, balance sheet, notes and analysis and other things. So many ah ah reports are there related to that one. It is the advance level and just we that this training is for the purpose of giving you the concept of the power BI.

How it is ah the workflow of the power BI and get the concepts to choose your normal ah data to prepare the dashboards for management. Yes, we have already shared the excel data, demo excel data which I am ah working on that one which I am showing you the example. The same data is ah sent by the email by Mr. Ali and you can ah practice on that one. If there is any issue, send me the email. I can help you. ah what do you mean by the financial model? Ah I you want to mean in the balance sheet power ah income statement and Reshwin else is and budgeting and forecasting Yes, we can, we have the training session for that one, contact, on my email, we will, we will, in the next month, we will start, we will start, ah, badge, ah, for the, this one advanced level of financial model, in, in which we will train for the, train the finance professionals.

How to create the, how to make the reports of the profit and loss in the power behind from the trial balance to the dashboard. Taking the data from the trial balance and prepare the balance sheet income statement, cash flows, ratio analysis. We will ah give the proper training on that one. We have that one data material, training, everything. But next month we will start. Ah we will start the batch. You can you can join that one batch because it is the advance level. This training purpose is only to give you the expertise to go through the and understand the power BI tool and you can prepare your reports.

Easily. You can not only prepare the sales report, you can prepare the so many reports on these one basics, the concept or the same. The data will be the same. Only the data tables, names and data, raw data will be changed. Otherwise, you have to through, go through the query, you have to go through the data model, you have to go through the tax formulas. In this way, you can prepare your reports. I think the positive questions are answered. If there is any question, let me know. What is type of the monkey language? Let's start with the next That's a creating data model. As you know that we have here our tables. Customer table, return table, sales table. Let me pick the other tables also and show how to make the model. How to make the relationship between the tables. That we can get our results. get data. Again the same procedure. Tables, product categories These are the product categories that will transform the name categories. like have not written that book up table let me change again this one name. is not the necessary to write on the lookup.

It is just for the ah making your concept clear. I have put it the look up to you. there is no data is showing here, the your load and transform data is not active, when you will click here, the table, it will show you the data and you can easily transform the open data. Options will be active. category. I am not writing the lookup. If you will see here on the field side, all the tables are coming here. I am deleting this one relationship because the BI is ah business intelligence which is automatically detect where it has to be mental relationship, it is making a relationship. But we will do our salary. If you will see in the report, we did not work here. In the table form, you have all the lookup table, product categories, product lookup, sub categories are there. Returns are there. It is our sales data is there also. all data territories. changing the name just let me make it short.

chapter one more table is there. Which is very important. Cuspur we have picked. Categories we have picked, sub categories, products, retails, sales. We have picked from the table, sales data is here 2 thousand fifteen, 16. Already we have connected with the sales folder, sales data folder. That's why the name is also coming the sales data. You will see here. The sales it is showing you the sales data. and here our file is KW training which we are saving Time to time.

And here is main date team. there is the excel sheet, here is the table form, because I have the table. Table form this one. Directly I will load this one. If you will directly dote the data, no issue. You can again go to the ah query editor and you can see the name is table fifteen. No, I don't want to table fifteen. I want to name it as a date. And we did not pick in the transform data. Directly in the query editor. We have directly pick in the power BI. That's why now we can go to transform data and you will see here. There is the same table is there I can change the name. Red tables from the internet. It is very easy. There is so many day tables are available. we will understand the concept of if you will see here I am just arranging that one. That are table is also the look up table. just arrang ing that one In that position, I am arranging that easily.

I can make the connections. guys ah you can easily understand that there is no relationship. If I will go to the report. Let me become one report, one graph. I have picked that work graph. If the sales table from here. there is date order, everything, order quantity. I have picked the order quantity in values. I will explain after that. It will be very easy to understand that one. Just for this one, it is giving me the values here.

Quantity, thirty-8 thousand eight hundred sixty. Okay, let's famous, I want to make month wise. Lexus. It is giving me the same value. Why it is giving the because there is no relationship of the date with the seeds. It means, we have the sales month wise. Here we have the delete table. If I will create the relationship between these two tables, then my data will show properly. Here if you will see all the values will be the same. Okay, let's check in the other way. I am checking. The metrics if since order quantity values. 860 is the value. Okay. If it is the month wise, then we have to pick month wise. Year wise, okay, let's ear wise. If you will see here, the values will be the same. Increase the size of venues. See, 2015, 16, 17, 18, nineteen, 20 is the same values. As per the data, my date table is from starting from the 2050 to 2024.

That's why it is the same values. Why it is the same values? Because we have not a relationship here. Let's go to the slides. In the data model. We have to make the relationship. We have to make the relationship to make our reports. This screen is showing without the relationship and other screen where we have met the relationships. data tables and look up tables. I already explain you, we use data tables, that is the fact tables and lookup or dimension tables. What happened actually? For example, we have the order dates here.

In the left side, we have the order dates here. And here we have the date table. Here we have a specific date. So many dates, five, 7, 7, seven but in the look of table, we have unique values. If there is a one, eight, five, 7, 2015, it will not repeat again. It will 5, 7, 2015. It will unique well. It is in the data table. It will come once, one time. And but in the sales table, which is the fact table, a data table, the values will come again. May be one customer has five, 7, 2015. A customer has ah ordered and the B customer has also ordered in the five, 7, 2015. See customer has also ordered. It means five, 7, 2015. Will come again and again in the sales table. It has multiple values. But in the date table, it has the unique values. You can also ah get the concept of primary care the foreign key in the data models. In this way, that the in the, in the sales table, there is a foreign key and the main.

Primary key is the date. That's why we use the lookup tables. We use the dates from the lookout tables and data from the sales table. In the same way we have the product table, where we have the product key, which is unique keys. Two, two, one, two nine to 10, two, 7, three. These are the unique keys. In sales table, it come multiple times. That's why we use the relationship between the tables. Important points, you can read that one, when I will prepare the data, I will explain that one, there is a ah two main types of the ah models relationship model, that's the star schema and the snowflake schema. I will explain ah, while I will preparing the tables, and there is the relationship which is three types of relationship, one to one, one to many, many to many. And mostly we will use to want to marry a relationship. It is very easy to make the relationship. This is my date. There is order date. Let me explain this one little bit. I will fetch the date and attached with this one, ordered it.

If you will see here, there is a relationship One, it means one value in the date table is connected with the multiple values, multiple dates in the sales table. In this way, we will create product lookup favor. Okay, here we, we have the product key. Okay, and here we That's this one. match from to the product lookup table or from the product look octable to the sales level. It will create your relationship. Here is your relationship. Product to product key. If you will click here, it will show. If I will click double click on that one, it is make this relationship active. There is product key and here is the product key. In this way, our single and one to one.

Many to one relationship is there. Many to one I will make the look up table above and the down. The sales table down, it will give me the other type. One, two, main. One, two, many, the relationship is there. Customer look up table. It means my customer values. Why are, we are using this one. Why it is used beneficial instead of merging the tables, we will use the relationship. In this way, your model efficiency will increase. When the engine will work, when the engine will ah read the data It will not, it, it has to go whole of the rose. When there is a relationship, the arose and the columns will be reduced to a minimum level and the efficiency of the data reading and efficiency of the working of the model will increase. That's why we use the tables and their relationships. customer customer. Where is the customer working and here is the we are creating our data model. It means we have fetch all the data in our power BI and now we are creating the connecting the tables with each other.

Mostly important thing is one to many relationship you have to do ten. Stars scheme wise good. Star schema means the one is the central table and others are around you are connecting with the other tables. Here we have only the one and there will be a snowflake it means one table will connected with the other table and other table is connected with the third one table. Third one table is not connected directly with the sales table or first table. It means A is connected with the B and B is connected.

C is not directly connected with the A but we can pick the data on these one bases because A is connected with the B and B is connected with the C. It is snowflake ah relation ah schema. It is snowflake schema to make the relationships. To make the connecting the data with each other. subcat egory subcategories with the subcategory. teacher. And the next one that will create the computer categories related to Please observe, here, I have created the relationship. Sales is related linked with all the lookup tables, data table, product lookup, customer lookup and here you will see the sub category and product category B. Here, product category is lit with the sub category. Sub category is linked with the product and product is linked with the sales table. It is slow flake schema. In this way, we can, if we will make this one, from here we will pick the things. The data will show in proper form. I will show you how to, it will show you the proper form. Let me check in the relationship. Check it is correct or not.

is attached, it is attached with the sub category, I am deleting this one, it should be subcategory, should be attached to the subcategory and here I have made the ticket here, category name. tables that we arrange this one table and then it will be easy. have made the relationship between the Okay, let me prepare the again this one table. Relationship. product, category name. Here we have the index model, product description, key, Products subcategory name. Here is the product sub category name. sub category name One to one.

It should be one to many relationship. It will be good. back in the presentation what we have prepared sub category two So that's the, from the category lookup table, to the sub category at subcategory is attached with the product. category to subcategory. Means, this is our category table. This is our sub category. and here subcategory two one too many relationship. Okay, it's nice we have created the relationship here. And here is the territories. returns Okay, from the return table, we have here. and if you will see here the date is not showing like this that will check in the table what is the issue with the date table data transforms been dead. Okay, option is the date is same, okay? The name is started and date.

Cotton. ATC. Close and apply. and attach the same. From here turn back to date. It is one to many relationship. If I will highlight on this one, you will see here the, the color is showing here and the option. On the date table, it is highlighted the date and the down table it is highlighted the return table. Product key will be attached with the product key. and will be attached with the our relationship is created now. We can work on the next step. just making in the same file. If you will see here, the arrow signs. Let's see one to many. Aerosine is important. It is showing one too many. If it is in the ah select ah sniff lock ah relationship you will see here it is from the one. It is one to one is it is at this time is creating. Then from product sub category to the lookup table. Product table. It is one too many. It means the data will flow from this to the next and then to the next level.

In this way we will create our data model, relationship mostly the relationship should be one to many. In this way, we can create our model. It is very important for that one. The lookup table have the unique values and the transactional table has the multiple values. I told you about that one, the movement of the arrow. If you will see the movement of the arrow It is from down to up to down. Let's see. this truth is. From territory to return. Here from look up to return. In this way the arrow is forming that way. If you will pick the data in the wrong direction. It will not work. It will not show the proper results. Let's check. This one how it will work. But the filter direction shown as arrows in each relationship by default. These will point from the one side of relationship because to the many side of data. The same thing from one side unique values to the many side of the data.

When you filter the table, that filter contacts is passed along all the related downstream tables. It means when we are filtering that one data, it will go from the top to down. And it will give us the results. Filters cannot flow up because we have the direction of that one filter, when we are filtering the data, it will flow from up to down stream, from to the return table. It will not flow from return to factory tables. I will show how it will work. Let me explain first.

Filters cannot flow upstream against the direction of the arrow. Here we have the lookup table, return table and sale tables. Let's try to make this one. Next page me pick the one We have here the sales table. Sales table, okay. We have here the sales territory key. If you will see here, sales territory keys. Whether we have connected this one. Let me check sales director key. Here we have one. This and here also we have the sales strategy. sales director key, this will also it means one too many here. This relationship is created with the and the territory table. And here return table and the tractor table. Only I want to give you the concept of the flow of the stream, flow of the data with the arrow.

This arrow is representing that the flow of the table. Flow of the data. That's do here. If I will pick. the in the rose. that we increase the size. This is better tricky. Okay. the order quantity. This is my order quantity values. Now, there is a relationship of the order country and territory key. I am picking from the same table. There is no relationship, there is no report because if you will see here the sales table and I have taken the order quantity from here from here also. If I am picking the returns, return quantities. If you will see here, it is giving me one eight, two eight, one eight, two eight, one, eight, two, eight, one, eight, two, eight. Same values. Instead of this, it should give me the touch revise. One, two, three is the third three key. It should give me the what is the return from the territory one, what is the return from the territory two, what is the return from the territory three? It should show me like this.

But it is giving me a one bedroom. Let's check the in the other way. copy, paste, the same table. If I will pick the territory from the return key, okay, I am removing the treasury from here. Please give me the data and if I will pick the from the return key. It is giving if you will observe that, here is the order quantity is fixed. It is giving me for the first battery, fourth battery, fifth battery, only the order quantities. Why it is doing this one? What is the reason? Reason is the flow. What I am doing? I am doing here. I am picking that territory from the sales data. And it is upstream going to the return data and the tertiary. I not picking from the same from this one return quantity is showing we here with the sales data in this way Here, the sales data is showing me this one pattern. Return pattern. But if I will pick from the sales territory, it will not give. Because the sales directory arrow is working from here. The stream, the data stream is from sales predatory.

Let's give me more clear concept of this one. May be it will be more clear. That's one moment just. Again, the third one. If I will remove the territory from here. And from the territory tables. I will pick the sales territory key. Now if you will see there is on the first entry 5 thousand seven hundred twenty-four, second sixteen, there is no return. In the third territory, there is the debt of 16 other quantities. But there is no return in 6 territory. May be or the country name I I can also pick that one. Because if there is a relationship of the one field is a relationship with the other table In the other table, if one relationship is created, you can pick all the data from other table. Because there is a relationship is created. In the three, from the three metrics. It is the metric chart, it is called metrics. In the three metrics, you will see here, if I will pick the territory fee from the sales table.

It will give you the return quantity wrong. Total of the ah return quantity. Why it is? Because the flow of the data is not from the sales to the territory. Actual in the relationship our flow of the data is from territory to the return territory to the sales. This is the flow Upward steam, it is not working. It will give always the wrong wrong result. If I am picking in the second table, if I am picking, the territory from the return table. It is also giving me return is correct because I am picking the return territory from the return table. That's why it is giving me the ah ah return quantity is correct.

But the order quantity is wrong. Because the both three three tables are related to each other. If you will not pick the proper stream flow of the data, it will not give you the result. In third, because we have know that the sale territory, we have the unique values. This table, we have the unique values and we have attached this shared territory values with the sales table and return. When I pick the territory values from the territory table, then it is giving me order quantities, exactly and adjacent to the order quantity related to that same territory written country.

In this way, keep in mind the flow of the data. Flow of the stream of the data. Sometimes what happened? We are getting our visuals, we are creating our ah ah analysis and it is giving us the wrong result because there is a flow of the data is the issue. In this way, whenever you are creating, always try to make the clear from the look up table to the fact table. And what is the unique values? Which tables have the unique values and which have the multiple values. I hope it is clear. This one concept. That's it. Practice, I have all best practices. I have already told you about that one. So, what you have to make? How you have to make always use the small tables. Always use the tables. Don't merge the data with one and other tables. differentiate between the unique having tables, having the unique values and data having a multiple values. Differentiate, make the difference, differentiate, segregate your lookup tables at the data tables, lookup tables, always use for the relationship, for the unique values, and data tables always having the transactional data.

The data with the multiple values. We have finished that one our models. Now we will go to the next session. For creating the the reports from this one. It is our data model of the PJ. We have done pick the data from ah we have ah picked the raw data. Clean that one data. Transform, reshape that one data. And fetch into power BI tool. And then we make the model of that one data. And now we will prepare the report at the end of this session, if there is any questions, you can raise the questions. If there is any concept to be revised, I will revise that one. After that we will ah get the concept of the calculations and preparing the reports. Please raise your hands. To make the questions. Thank you. Under your mic. kindly tell me ah how to ah manage these relationships automatically for instance when we open this relationship tab at first the relationship were made automatically but then we deleted them. That is why I I have done it. Actually what? Power BI is very intelligent tool.

What happened when we picked the data? automatically create the relationship. Because it is the data analysis tool. It is the BI business intelligence ah That's why whenever I have picked the data, I have made the tables. It is automatically detected that there is a lookup tables, there is a unique values. Is attached with the multiple values. That's why some of the times it is created where it saw the data, there is date, it is create, attached with the date, dim table with the sales table automatically. Because it knows automatically that there is a unique one is that there is a multiple values. Why I have deleted? To give you the concept of how to make the release Sometimes may be we don't need that one relationship that's why you have to prepare your relationship.

Sometimes it is automatically, it is fine. If you see that the relationship is good, it is enough, it is ah useful for your purpose, then remain it. Otherwise, delete that one relationship and create as per your requirement. Yes, please. I hope the concept is clear for everyone. Let's move to the next session. Calculations. It is very important session. Backbone of the whole of the analysis. That's calculations. We have that all the tables, relationships here. Now we want to make that one reports. Here I have picked the values. From the tables, if you will see here in this one table, I have picked the values from the return, return table, there is return sum, sum, sign is there. Return quantity. In the same way, if you will, in the sales quantity, you will see order line item, order quantity, there is a sum.

On the date, you will see the calendar type of screen. Calendar type of picture is there. Others, where is the data? It is automatically power BI tour is automatically detected that this is the data and it is making that one data. As the sum. So, we can pick the data directly in our report. It will give us the ah answer. The sum of that one data. But we have to calculate the data. How we have to calculate the sum, calculate the other things. As per our there is ah taxes ah short form of the data analysis expression. It is a language which we are used. It is not the difficult one language. As you are using the functions and formulas in the excel. It is the same. Just you have to comma or the practice is the different. Rather than everything is the same. It is very easy and it will give you it is helpful for your complex calculations.

We have two of tax. One is the calculated columns and one is called measures. We will work on that one also. The both. One is called calculated columns. And one is called measures. calculated columns refer to entire tables or columns and generate values for each row. Which are visible within tables in the data. We have here the data. If I will go to the data table, here we have the report level. Here we have the data level. Here we have the relationship. In the data level, here we have the data. Sales data is there. If you will see, here is the sales data. What happened actually? When we use the calculated problem. it goes through each and every rope. And make the calculations. If growth, it goes through each and every column and makes the calculation. It means the whole table will be read by the formula and it will make the calculations. It will go through that. circulated columns always understand the row context. Row wise it can be calculated. If you will put the sum, it will not work.

If you will put the count, district count, it will not work. Let's see. If I will click here, right click. new column. Let me check. quantity. If I will put it as sum of sales table and order punch. Here is order punching. If I, I will close here. you will see the amount is every row is calculating 3-8 8 hundred sixty. Each go is calculating. But what is the formula? Sum. In column, we don't use the aggregate. Some count, distinct count, we cannot use that one. For that one purpose, we will use the mayors. Mayors, the safe urbanizer applied in the measure. In the columns, we will not use this one. It will, if you will see, it is giving me whole of the values of the because whole table is showing the thirty-eight from the 8 sixty order quantity. That's why in each row it is representing the thirty-eight thousand eight hundred sixty.

Instead of this, if I will make the other. is greater than is greater than one then people items. Otherwise, single and you can see easily that if on the order quantity when I have picked that one, if it is equal to one, it will show me the single item. The customer has ordered only a single item. If it is greater than one, then it will show me. A multiple items. See here, I have picked the two multiple items. Remember that we are working on the power wheel, not on the query editor. Cury editor recorded a structure. It is not recording the steps. If I will pick again. This by single items and the double atom. It means on the row context. If I am applying the formula on the column, it is the raw context wise.

It is reading that this row has the one order quantity make as per accordingly as per formula. If here is a two, it will make accordingly. It means every row, engine will read every row. And then make the result. If we have the merch tables, if we have the more data, the efficiency of the table will be reduced. Efficiency of the power BI will be reduced. That's why we mostly use the measures to calculate these one things. If we save the you got the concept of the calculated columns or tax formulas used to generate new calculated values like calculated columns, measures reference entire table or column. We can make the tax formula on entire table or on the one column.

Unlike the calculated column values are not visible within the tables. As we can see here, when we have applied on the calculated column we have applied the formula. We have applied the formula. Text formula, we have applied on the table column. It is showing, it is visible to us. It is showing, it is single or multiple. It means, when we are using the dex formula on the table, on the column of the table, then it is visible to us.

But in the mayors, whenever we use that one formula, it is not visible in the table. It is just working on behind the ah table. It is working in the engine and calculate the values and show us the values and represent the values. On the report. where are evaluated based on a filter context. We will use the filters, filter values. We can use row column levels in the different patterns. We can pull the data in the matrix form in table form, in by chart, in drafts, in bar charts, anywhere we can use that one column. Ah that one by your my years. Which is the tax formula. Dex formula, measures are used with the references. If the data is on the other table and we are using the formula with the relationship of that one table.

If there is a relationship between that table, we can easily fetch the data, we can easily get the result from the both tables with use of the measures. We will use the mayors. To calculate this one. There is also ah one thing ah let me explain. There is ah the sum is written here. It is called implicit measure. It is called implicit measure. Implicit measure means in the table there is a data is there and you are adding that one for not preparing the desk formula. It is already added giving you the sum of that one that data ah by the BI tool. That's why it is the implicit measure. Sometimes we use the explicit measure which we will calculate. Which I will tell you. Ah data I will tell you, this one explicit mayor. Which we will make the mayor tax formulas to calculate these one figures. There is also one option for the quick measures. There is no need to use that one quick measures, quick measure is ah just simple options.

quick measures. It is a simple option. We will calculate, we will select from here average, various, maximum, minimum. These are so many options are there from which table and which column you want to apply these one formulas. We will apply these one. Time intelligence, date wise, sales wise, any filters you can apply that one. But it is not the correct way to use. We will use the text formulas to calculate our values. Let me little bit explicit and implicit. I have explained and let me in the detail let me explain the some of the common formulas and functions.

functions categories we have defined here statistics some average maximum minimum we will use these one formulas just right on the average and ah context I will tell you how to write on the formula then it will be very simple we can write on these one formulas there is also the data rater functions that is the sum X average X max X minimum X ranks now tax what is the difference of some and sum X I have already highlighted this one sum and submit sum X is calculated row wise.

If one row is calculated, then second row is calculated and the resultant will be sum up. In the sum, it is only just sum of the table. If I have the two columns like the price multiplied by other column is the order quantity, Then, if I will sum the formula, I will apply the sub formula for the price, it will give you the sub of our average of the price. Multiply with the total of the orders, the value will be not the correct. If I will make the sum X, then it will be correct because in the sum X, it will row wise in which it will calculate the price multiplied by quantity, the result and figure.

Price, the next row, price multiplied by the quantity, the resulted figure. And then some of the resultant figure. It is a difference of the sum and summit. The logical function I have just applied the if and there is also the if error and or not switch. So many functions are there. Tax functions we can concatinate the two columns. We can format, we can get the figures from the left or the right.

If you want to get the figure first figure from here or if you want to get the ah order number only the numbers from here the last four digits from here you can it is simple like the axle working. We are ah picking the figures from the left or right. filter function which is very important which are the very important and useful for the working I am discussing only those one ah functions. calculate function, it is very important we will use this one filter function, we will use, I will show you how to filter what happened in whole of the table, we want only the bike seals, we want only the values of the bike seals, we just filter that one from ah pipe, we will use the sum function with the filter or calculate function with the filter to calculate the bike sales in that one whole of the data. In the same ah related it is important or late it is where you have the relationship between two tables.

Here we have the product at sales table. Product table has all the prices of the products and in the sale table we have all the orders for that one sale products. Related table is helpful to combine that one. To joint that one. Multiply applying the mathematical functional on that one. If we have the ah prices on the other table and if we have the orders on the other table, we can multiply these one in the text formulas. There is no need to merge the table and ah make it huge table instead of we will use the related table and just with the formula we can get our result.

In this way, the efficiency of the your model will be increase. In the distinct table, distinct values, we can calculate There is so many orders. others have the two or three lines or two or three products they have ordered. I just want to make ah want to know the distinct values. What is the net orders? It means if there is a 2 thousand orders and from five orders having the six or seven different type of products but the order number is the same.

It means by order will be the same. It will not calculate the seven times. It will give me the one value only. It means distinct weapons. That if the diet at time function, it is used for the previous year, third year and ah quarter, it is in the date table we have already in the date table, all the dates we have here. Date day, month, year, start, year. We can work on this one. Easily, it is the day table we can download from the internet also the day table or you can prepare the date table also in the QR data.

Let's try to understand the text formula, how to write the text formula, it is very important. For example, we have return quantities. Some of AW return quantity. This is the name of the formula. We can make the name and here is the function which we are using. Some maximum, minimum, calculate which we, we want, we can use this one. This is the table name. which the table name. Table name is here. We have the all the table names. This is in the brackets.

We have the column names. whenever we apply the sum, we will apply the sum on AW return tables. On the return table, and in the return table, if there is a three or four columns, we are applying the formula on the return quantity only. In this way, we will use the text formulas. Now, we will calculate the text formulas. And it will be explained how to do this one. Let us jump in the calculations. For calculation purposes, it is ah how I am using these one things. Best practices go to inter data Make a new table. You will table name is and patience. In this way, you can segregate your tables and your calculation. I can make the calculation in the same table. But ah it is better to make a separate table for the calculations. See here, if I want to make the sale, I will right click here and new measure. I can calculate here with a new measure. I can make here some of the order quantity. I can make here. But it is better to make it in the separate calculation tables.

Right click. You will find here new measure. If you will, it will shown here the column and mayor. It is my calculation table. I put all my calculations separately that I can easily manage my work. Here calculation. my plan. Next formulas or not difficult one, just a little bit practice required. And ah it will not ah problematic that you you will easily you can learn this one. Total order quantity. Sum. Just write down some tap. It is asking me the column. Sales order. I know that order quantity is there. Sales, data, table name and then order quantity. Just click on the tab and you will get here. Order punch. Go ahead and check this one. It is the order quantity. Here is the card. If you will ah hoover on this one ah graph, there is written the card. You will click this one. Pick this one field. Thirty-nine K. Order quantity. Let me check the data levels. And it is utterly it is giving me millions or thousands.

Let me none. 38800. It is by order quantity. See now It is explicit buyer. Let me read this one column only from for this one column. My calculation is separately shown here. And if you will in the sales data, if I will make the same copy paste. Here, from here. The order It is the same. If you will observe, it is the same. It means the, this is the implicit order quantity, this formula is implicit. It is also giving the sum. But sometimes we don't need this one. Therefore, please use always the explicit mayor. Which you are calculating. This one, this is your major, explicit measure. This not the difficult one.

Tax is very easy language. You can easily understand this one. Simple, some, the, same formulas only just ah, you have to use the brackets. It is ah very flexible and it will give you the tremendous ah gain on the control on your data. It will give you the proper results, analysis of your data. Okay, let's calculate the so many things more. Okay, we have here, if I will go to the table, now there are more things in the sales table, you have only order quantity. We don't know what is the revenue.

Okay, if we go to the product, table, product will come. Here, we have the discounted price, product price. Here we have the product price. Yes. crisis here and we have here in the sales table order country. We want to calculate the revenue. How we can calculate the revenue. We have to multiply this one. Order quantity with the product price. Then we can calculate easily. Our revenue. Okay. One question is here if I will make the sum of the product price. Multiply it the sum of total orders. It will give me the wrong result. Why? Because each and every order has the different price. Each and every product has a different price. It means the product price should be matched with that one product and multiply with the order quantity and give me the result. And after that all the result and figures should be sum up. That's why I will use the sum X formula.

Here we will use another revenue summits. If you will see on the expression, it is asking me table number. What is that table mean? From where you are picking the values. Table name is sales data. I am taking the sales data. Coma. As per above expression, it is asking you already. It is very easy to understand from that. You are picking that one figures. Ah further ah tax formula. You you are picking the correct figures from the table. Expression. Here we will put it what we are doing. From the sale data, we are summing up the order quantity. sales data, order punch. We are summing up this one. Some X, each row. I am defining the with the text formula, I am defining that each room. From each row, you will pick the order quantity. And multiply with the our relative, which column we want? I need the product price. Where is the product price? It is NY AW, AW underscore product column, Product table. Where is the price? Product cost.

price, here is the product price. And close the parenthesis. If you will understand the formula, we need total revenue. Total revenue is the sum of total quantities multiplied by their product price. It means each and every row. In the table, each and every room should multiply with the product price as per the product category. As per the product product key which is the specific from the product lookup table because we have made the relationship. When we click this one, we have the total revenue. let's see. copy paste and total 15. 7 million if I will want to make this deductible in the median 15. 7, 3 million is the total revenue. Just it is for your understanding, I am showing like this. After that, when we will go to the visual, I will properly prepare this one. Formula, formulas will be the same will be used.

Only we will use the visual. It's just for the calculation purposes. I'm giving you the concept of the dex formulas. Okay, let's, if we have the total revenue, we must have the, total cost. the same sum X table is the sales table. Which column I want to meet? Order of quantity. multiplied by related which column from AW underscore product cost. It is my bottom cost. Close this one. Let's see. Test, just creating the copy paste, you can pick from the new card from here also, or copy paste the same graph, and I am picking here the total 9.

16 million is by total cost. Obviously, after that I will calculate the revenue profit. profit will be already I have calculated the total revenue and the total cost that's why I will just pick the bar and it will show me the I will pick the bracket and it will show me the mayors which I have created already it means I have created the total revenue minus total cost It is so simple. It is giving me the profit. Let me create the new one. If the new one, it will show like this. Okay, and I will click here. compost. And the profit. In this way, we can use the dex formula to calculate our calculations. I am removing this one. Okay now. Main thing is here, it is the two big, how we can use this one, how we can manage these one values. Here we have the properties.

Format, format properties of that one cards. If you will go to the properties, there is the general properties, general properties means here is the disposition, its width and its height. Okay. I will change the height to 200. in the ah width to 200and it is one thirty Okay. by prophet I am changing this one. Data levels. This auto, okay. Tech size 4-5. I am giving twenty-five. Okay. Gold, of, if, if you want to bolt, okay. You can pick any option as per your requirement. These are the so many options are there. It will take too much time to each and every option. I will explain. Just go through this one. It is very simple. Color, I want to category. This is the prophetess return is is the category. I want to make it color. Profit. Okay. Then, 12 size, 14 size. Okay, if you want to change the This one, it will change this one. The font of the return. Ah words, that is the profit.

I want to send this one, the same option here is the format printer. Let me try, this is also the same. I will make this one. Same, I will on this. Total revenue. cost and net profit. Yeah, I, I have these one, three cards. In this way, you can easily familiar with this one. These are the easy cards. Okay, you know that these are the only total revenue, total cost. If I want to check. What is the 2thousand fifteen revenue? What is the 2016 revenue, which we have picked the year? I think the two thousand fifteen and 16 year. We have picked the two files, only 2017 is a separate file. We will, in the last we will update the data with the two thousand sixteen. We have here date deep table. Here we have slicer, filter. You click here, filter, okay. I will pick here From here, you will pick the list. Oops, it is too much. 2015, 16, 17, 18, nineteen, 20, 21, no, no. I don't need too much gears. If you will open this unfiltered, you have here ears. advancement. Okay. Our data should be start from the two thousand is greater than two thousand less than are equal to two thousand eighteen Apply factor.

Now, you can see here, it has restricted that one after the filtering option. You are, if you are working on the more data, you can ah change this one filter options. Here we have two thousand fifty, two thousand sixteen, 17 and 18. Because I have the data of 2 thousand seventeen, that's why I have put it this one. I put that one option here. If I will, now we have the option of the ears here, filter here. If I will click on 2015. You can easily see, observe, you can easily observe, you can observe that when total revenue has changed. It is showing me only 2015 total revenue, total cost and the profit. No need to again calculate to again the go through whole of the data just one click you can get your reports. Two thousand sixteen, okay, two thousand sixteen, 2017, okay, 2017. I don't have the 2017 data. It will be black. We have only 2015 and 16 data.

If I want to check complete, again click on the two thousand sixteen, you have the 2015 and 16 data is here. It's a great ah tool that you can easily within a click. You can get your reports, you can get your updated dashboard with the values. Let's do other things also. It's a just a simple formulas.

I want to let's have ah let me take our two minutes break and ah come back again with ah further details in the formulas and then we will go to the presentation. Sorry for the break. Let's start again. Then we have discontinued this one. we have here the options Year wise, we also can change it to month wise if I will make a one more here. put it from the dating of month. Here we have the months short number of months should be ah month wise Jan Feb, I have, what, what happened? The month name was not in the sequence. I have just clicked the one short name and here on the options, short by column. Which column? Month wise, I have ah sorted on the month base. That's why it is giving me gen fab in the sequence. It is very easy to make this one. Now, I want to check which month you can easily check here. Gen, okay, 1. 0. 2 million is total revenue, total cost is this.

For 2015, it is giving that two thousand fifteen Feb, March, or two thousand sixteen. As you will filter out the year or on the basis of the month, you can get your results. Let's make a some of the ah work mayors. template our reports. We know that the total revenue, total cost profit we have calculated, okay. That's the Yes, we will create previous month or the previous year say. Here we will use the calculate function. how to use the calculate functions and how we will use the formula. There is a two ways to do this one. Calculate We have already calculated the revenue. That's why we will pick the revenue. Total revenue. coma filter. It means we will use our table to filter the data which we want. Filter on the basis of our condition. Whether it's a, it is a product. Whether it is a ear, whether it is a month, whether it is any other condition. Your data was, you will be filter out as per that one condition. Now, I need the previous month revenue. For the result purposes, I will use that one. that the what is the previous month revenue, what is the correct month revenue, third month already we have from the previous month how we will calculate that program.

Total revenue, we have already calculated. we will use the function date at it. Which one date I did? From the team table because here see we have the relationship between the date table and our sales revenue table. We will not go through our sale table states. We have the unique values in the date tables and we will use that one. Automatically it will calculate the previous month, current month or any revenue on the basis of that one linked date table. Date, edit, I have taken. table it's dot and what I will do? dim date minus one. Sorry, minus one. minus one But we have given him here the previous month revenue calculate total revenue which already calculated the total of that one table column total revenue. By adding the filtering option, by adding that one filter or by filtering on the basis of date on a month previous, minus one, it means trade dim, it will pick from the dead dim minus one, it means the previous month it will take on the basis of the month.

If you will see here the month, we can make it by the date, by the month, whatsoever. If you will comma, here we have the day, month, quarter year. We need the month wise, that's why we have picked the month. If we will need the quarter, it will give you the quarter wise. If you will date the year, it will give you the ear wise. there is a X formula is done. Let me show you how it will work here. Let me make the table metrics. To present easily. Their team Short name of month. tenth, Feb, March, April. And then revenue, values. It is the month wise total revenue. 15. 7 billion is the total value, fifteen point seven three million. Rounding of 2. 7 3. It is our total 15. 729 is a total revenue. And if you we will pick the previous month revenue. It will show us the previous month. if you will see here the gen figure is one, zero, one, 7, 7, three, eight, point, five, five.

In previous month, in Feb, Feb is the values worth zero, zero, six, three, eight, nine, point, two, two. Compare with the previous month is one, zero, one, 7, 7, three, eight, point, five, five, which is equal to gen figure. If you will see here, one. 7, one, zero, one, seven is equal to gen figure, one, zero, 7. In this way, it will in front of the fab, it will give you the previous month value. It means our previous month for new rise. Correct. Okay, let's prepare some of the reports. Now, we will start to the reports. Before starting to the visualization of the reports, we should make Assam. If any questions, raise your hands. We will on the text formulas. Ah we can ah I will explain if there is any confusion or anything you want to further ah ask. After that we will ah prepare the reports. On the basis of that conduct formulas as well as with that one data.

Please raise your hands and ask the questions. I hope you got the concept of text formulas. go to our next session, which is a report meeting. Already I have explained the power behind dashboard. is our tool bar and this is on the right side we have the fields which have in the tables adjusted to the fields, we have the visualization where the different types of graphs are available for visualization and filter option is for the filtering of data on any condition on the day, product wise, any condition you can put in the filters.

We can directly put the filters or we can make the slicer or we can make the chocolate. For that one, ah, making the segregating the data or the filtering the data, we can use that on slicers. Whenever here we have the, down, we have the pages. It is called the canvas, all the pages. We can use this one. different pages we can create to the different ah visuals Filter is used for the filtering as a report level, page level and we can also ah make the cross filtering also. Within the visualization we have the drill through the cross filtering, cross report filtering, we can use also. Here we have the values fields.

It is an analytics span where we put the values and we can get the results. Fields, you know that there is a tables and our calculations. Fields has the calculations. Our measures, formulas, tax formulas and the tables on which we are applying the pharmacy. is already we have I have shown you this one, analysis, visualization of some of the ah graphs, there is filtration, filter options are available, PH level, report level, we can apply this one. Also, let's try with the reports now. For the reports, let's see pick from here from the cert Let me insert some shapes or images.

project sales data definitely pick this one local. Now I am preparing the visual. I am preparing the dashboard. It is the CFO dashboard, CEO dashboard where we have the summarize data. We can get the results. We can see, we can analyse our data easily. Behind the all these one visual, your tables are working, your calculations are working. We have picked up this one is the Okay. 2016 is not there. Ah we were data by category wise. Let's see we bought the category wise data. And ah category wise revenue where is the categories? There is sub categories. Category name. Okay. Let's see. Our graph is here. I have picked the one graph Take care the access category and prepare our make the graph like the bar or you can make any other graph. The pie chart. I think the pie chart is the good one. Is showing us the values as per the category I will fix the things in the page. There is my total revenue, total cost, profit This is my first graph. It is ah first graph is revenue by category A. Here we have the components, bikes, clothing, plants, some of the data will be there blank.

Here is the components previous month revenue, I have picked the previous month, sorry, sorry. Oops, I have with the previous month, I have to pick the total actually here. Now, I have here the total value 8. 5 billion component is category component Here blank category, may be there is not the data is available there, category clothing and bikes. In this way, we can cut the data category wise. Okay, some of the data make a one more graph here. Let me check with our month wise. I bought this total revenue There's a general revenue, fair revenue, March revenue, it is giving me the revenue month wise. One point one zero seven if you will see here the figure and see here in the jail figure one point zero one seven. This is the same. Means our graph is working correctly. Okay, for this one graph, I will go to the format. Data colors, if you want to change the data colors, no issue, you can change the data colors. data levels on whether the data levels, Auto, auto means it will automatically fill with the millions if you will pick the thousands or none, it will give you decor option.

Size of the text is ten That's perfect. Border if you want to make the border, no problem, border will be there. In this way your craft will be. Yeah. I am removing this one graph. Remove and here I have If you will see here, it is very easy to analyse the data. 2015. Easily you can analyze gen, Feb, March, April, all the data and the labels are there also. If you will pick the hair, it will give you. If two thousand sixteen, it will give you 2thousand sixteen. If both you can pick here, it will give you the 2000 15 and 16 collectively data. Okay, if it is giving me the revenue, I want to know also add to one line for the profit. I want to know if it will show me the net profit also. Let me change. Here is the line and cluster column graph.

I will change the pattern of this one and pick here. Profit like. Where is the line? Column series. And here line values. I will pick here profit to the line values. Here it is giving me the land rights. What is the problem? 4 million, 4 million, 5 million. If I will pick the in the it will little bit that change the values. In this way, I can make the both. The total revenue as well as my profit line. 2015, which show me 2015 will show me 2thousand fifteen. Revenue and profits. 2016 will show me 2 thousand and revenue. In this way, your desire results will immediately In front of you, the desired result will immediately Within just on clicking on the ears or month wise, you can take it. And decision making will be easy.

Jai. if you will see only month I have picked, it will giving me the month. Here is bikes are the sales is 31 point twenty4 percent of the total sales. And three forty-eight, three hundred forty-eight thousand four hundred thirty-five point thirty-9 is bike sold. And components are sold. Other, there is no other categories sold. Whereas we have the more than two categories. We will click here whole of the years. It will show me the whole of the years. Okay, let's complete this one. rough also but this is prepare photo category by name and other one is It is by subcategory. Territory wise. just adjusting the graph you will see here total revenue by country. If I will over by cursor on the Australia, it is showing me five point five point zero million is sale in the Australia, United States $4. 8 billion. Canada ICL 1. 13 billion. Immediately you can get the result.

Okay, if I am hovering the chart then I can get the results. Let's go to the Alright, data colors and data enables on. Now, you will see here the data values are coming here. Okay, let's ah more clear the data values. Change the colour. it is more prominent I think the white is the best blue. Yeah. It is showing me 4. 81 million in the United States, five point zero 1 million in the Australia. Immediately you can get the results. If I will change 2050. Is showing me 2015 sales. We can easily get our results. Within one click. It is a beauty of power bill. Two thousand sixteen. It will give me the two thousand sixteen. In 2 thousand sixteen, I got the fab. It will give me the fab results. I got them by. It will give me the May results. In this way, we can easily create our dashboard with these one.

Figures. Now, we have here the data ear wise. is a month making the graphs properly one more calculation that we ah If my total revenue, I want to make a just it is, for your, understanding, I want to compare. Maybe we have that different table for the budget. We have to prepare the budget table and then we can compare easily or let me prepare the budget table here. Target it. If I am giving the target. target is equal to total revenue multiplied by one point one. One 10% is the target. It means 15. 73 is the total revenue. Let me check. This one copy paste. Here I will put the target revenue. Send. 17. 30 is the fifteen point seven three.

Anybody can calculate the 10% of that one, 100, 10% 10% increase in the total revenue. with the comparison graph. Let's hear here we will here our target value, target revenue A and total revenue It's clear now. You see, we have comparison of the targeted revenue and total revenue. We have the target of the 17. 30 million. What we achieve only 15. 7 to 9 million. From the graph, we can easily understand within our one view, we can immediately know that 17. 30 million is our targeted and we have achieved fifteen point seventy-three million. We can compare our our graphs. We can compare our values. the management will not go through whole of the data, management will not go through whole of the values. just they will see, okay, this graph is, this target line is here $17. 3 billion and we have not achieved, it is not ah crossed this one line, it means we did not achieve this one target, it is 15. 7 three million is our revenue. Whereas our target was 17. 30 million. It is better to make the budget a target table separately and attach with the date table and it will be easy make the comparisons.

In the same way as we have created the previous revenue, we can change with this one. Here, we can make the ear wise level. If we will make here, It is called stake area chart. Area chart. Area chart we are picking the area chart. Okay, here I am comparing the previous month and current month. Let me check if it is the same. I have calculated the previous year calculation. Previous year and the current year. Let me make a one more. the other formula that we check here the other formula. GBS a team but Previous year completion. Previous year level. Let me check this one. It is correct. ah putting her this one let me pick the ears. And here calculations 2015, 16. temperature from here. I think the formula is not correct. Let me check the formula. For the previous year. Change the pattern. It is called the same period I have. Previous period I have. Same period last year. same period last year. Actually what happens? I have ah picked the previous formula the previous year only because we had the two years. That's why I have to pick here the previous year the buy this one and the pick the year.

I want to show you the and other formula where we are total, we have the total revenue calculating the total revenue. Same period last year. It means if I have if I will take the jail it means it will pick also the gen revenue from the previous year. If I am picking the Feb, it will pick the Feb revenue from the previous year. In this way, month wise, it is same period will be comparing. That's why I want to show you this one formula And I have changed the date, added formula to same period last year.

Formula. Let me check here with the one short name of month here Now you can see here. This showing me total revenue for the Jain and the previous revenue. For the next month. Last year previous. 2015 and 16. If I will go to the two thousand sixteen, it will show me here. Total revenue four hundred 71 thousand nine hundred sixty-two for the total revenue and previous revenue, previous year the same revenue for the March is six hundred forty-three thousand four hundred thirty seats.

In this way, from this graph you are easily compare by the lines, your current total revenue and the previous one revenue. Is dark line issue that the previous revenue was, the more and the third year you are getting the less revenue. It is very easy from the graph to understand that where is we are standing. Our current revenue is lower. These line is showing the current revenue, the below line. It means the previous year our revenue was less ah our revenue was sometimes it is greater and less 500 total revenue is in June five hundred thirty-three thousand. total revenue blue and now previous year it was more. Six hundred 6nine thousand nine hundred eighty-nine. It means the both if you will ah observe the graph from the line, you can easily judge what is the condition of the revenue. Whether it is we are improving, we are reducing.

This year, as compared to the previous year, we are increased our revenue or reduce our revenue. In this way, we can easily find our revenue comparison. Because why it is coming like this? Because 2016 is compared with the two thousand fifteen. Okay, ah now I will change little bit. Ah let me tell you the sum of the more options. Data levels, category, background, you can change the background colour. Maybe we can pick the colour, background colour, transparency level. the same with a quick. For your understanding, I am picking the different color. any colour you can pick from here. As per your description, as per your requirement, as per your like and dislike.

You can prepare the colours. In this way your dashboard will be ah showing ah very beautiful presentation. It will be very beautiful presentation when you will make your dashboard colourful. Firstly, we use simple one color which is a theme of the company. In this way, we can use. the specific colours. Give us ah this is our total calculations. We have category wise ah category wise our revenue cost. We have if we have the targeted cost, we can compare that one targeted cost. If we have the ah previous year, current year we can compare the current year and previous year. Month wise we have segregation. We have total revenue by country by segregation.

We can make the month wise. We can make the year wise. Okay and that one thing we have more here we have the 2017 data. Data sets, I will go here. Here in the sales data we have 2thousand fifteen. And 2 017 here we have the data of the two thousand seventeen. Let me copy this one data in that folder. I'll just copy the data and let refresh this one. When we are refreshing, discalculating, the whole of the model. What is the data? From where it is picking the data and updating whole of the figures. Wow. You have see here, observe that 24 point9 million. It means, if I will click here 2thousand seventeen, which is previously not. Not available here. 9. 19 million total revenue is increased from here. 2015 and 16, we have the data. What I have done? On a just copy, paste that one file in the folder. My whole of the data was there. There is no need make again the whole of the steps. Like if I will go to the ah transform data you will see here the so many steps are here. in each and every you have the steps are here.

The product lookup, in the sales table, but Here, when you have updated only just you have updated the file, your data is updated. No need to make again full of the calculation, your data model will reflect all of the updates. In this way, SC twenty-seven point 4, twenty-four point nine, 1 million. If the same 2015 will, you will pick 2015 data will be there. Two thousand sixteen, you will pick. 2016 data will be there. If you will 2015. Control and click on two thousand sixteen. It will show you 2015 and 16 data. And then 2017 data. if you will click 2017 data. If unchecked whole of the things, it will show you four of the data which is available in your data model. In this way, you can present your data traffic, by preparing dashboards to your management. And it is not only on the test on. You can present it on the mobile.

This dashboard can be converted to the mobile also. I will show you some of the dashboards which we have already prepared. This all dashboards can be presented on the mobile. Your management, any change, any transactional change. If it is connected with the Oracle, it is connected with the SAP as the entries are there as the data is updated there. Your graphs, your data model will automatically update it. You have to share the data, you have to correct the data, you have to connect with the or whatsoever file you are using. For that one data modelling or for your dashboards. It is your CFO dashboard or CO dashboard. have named it CFO dashboard he can easily judge where we are standing, whether we are Whether we are completing or achieving our targets, whether we are, if we are comparing with the previous year, previous month, then whether we are achieving our targets, whether we are on the higher side or the low side or if the category wise we are want to check which category is more ah profitable, which category is giving us more revenue.

That's give you ah way to make the decision making to focus on that one products which are giving you the more ah profits efforts to do some of more efforts to do on the low revenue products to increase the revenue of that one. Also there is so many options are there like the story telling. Here we have the story telling option. there are so many ah graphs are there also. If you will click on the gut more visuals. Cut more visuals. That you have so many ah graphs available by the power BI tool. Microsoft, Power BI visuals are given there. You can use as per your requirement. In light, storytelling, let me check. The one which I normally use, that one Yes, I will pick the graph from that one. These are graphs are free. add these one graph. successfully. I have cut the data here. This one is the story telling graph. Let me explain in the other page.

If I will pick here storytelegraph total revenues go to your format story exchange size twenty-four point nine, fourteen point four six. Which is the same. Ten point four six. Ten point four six. Okay. Let's we can write on here. Some of the story. Understand? of revenue is and the total cost period is generating profit of we have write down the story little bit story of that one. If you will see here Understand? Thus, it is the better way to present your data.

There is a story, total revenue and total cost. The total revenue for the period is twenty-four point nine one million and the total cost is of related period is fourteen point forty-six million. Generating profit of 10. 46 million. The same here, I will put the ear. Let me put the ear and show you the, how to, how the story is changing. With the change of your filter. Put it as a list. If it is 2015 it is showing the total revenue for the 2015 is this. The total cost for the 2015 is this and the profit is this. If two thousand sixteen, you will pay two thousand sixteen. 2017, 2017. It means your story is changing with your freedom. Changing with your when the CEOs are management want to see it is very easy for them to just click on the 2015 and check what is the result of 2015 and when they will click on the 2016 they can easily understand what is the situation in the 2016 and overall situation is whole of the by unkilling whole of the ah ears in this way they can we can make the useful dashboards for our management there is a of a ton of formulas that approximately 250 functions are available in the text formula.

It is not too much. Just a need a little bit practice. You can prepare these one dashboards very easily. And ah it is very ah helpful as you saw in 2017 file just I have copy paste in the folder. And all my data is updated. There is no need to do all the steps, all the all the workings. If your dashboards are prepared and shared with the management, you have to just update your data and the dashboards will be updated and the results immediately results will be in front of you. As well as on the desktop and you can share with others and it can be linked on the mobile or you can share on the mobile also.

In this way, in your pocket, all of the data, four of the dashboard, it is in your pocket and within a seconds, you can make the distance. You can make the thing very quickly. Let me show some of the different type of dashboards we have already prepared for your ah just for your learning purpose. Let me check. some of the dashboards. This way you can view that how the reports are prepared. Not only the sales data, the purchase data, the stock data, you can you can prepare your financial statements, you can prepare your budget reports, you can prepare any data you can transform to the ah power via tool and with analysis. Make the presentation in the traffic way. In the graphical presentation, you can show your data in the traffical ah way to the management.

Let's show you ah one project we have done. construction company. And we have done this one data. Their requirements as per the requirements. They need the total ah number of number of projects. How many total number of projects? Here is the contract started. How many months are ah taken for that one contract? Who is ah the contacting party? What is the commitment? Ah amount What is the number of contracts? How many approved changes in the order, revised contact value? Here is the gross bidding, net amount paid, committed amount, comparison of the total amount of committed amount. Ah balance to the contract. It is the balance to the contract.

What is the total amount? What is the balance? And the how it is growing the value of the ah advances and the recovery changes. Actual between the plan. Here you can see actual gen, fab, March, what is the actual and what is the plan? How it is they are working on this one? It is the gross winning and net paid by the parties. If you want to check on the one contract, click on the contract, you can see MH18 contact has that two ways and their specification. If you want to go to the detail, you can go to the detail wise. Ah product. You can check here in the detail. This one is a contact.

What is the number of payment how many number of payments? What is the type of construction they have done? Because this is the project of the construction company that's why I am telling you the construction. What is the type of construction, road construction, houses, walls and different type villas, construction, different types of construction, gross building, what is the balance to conduct? What is the net payment? These are the detailed white contact wise. This is a detail of the contract. And the upside, the first is the further executives. It is the summary. It is the summary summarize form. This is the power behind beauty that you can present your data in any. other let me show you the other just go to the two Doctor Martin. for some of the guys ask the financial models we can prepare in the past BI or not.

I want to show them that you can do anything, any dashboard, any data analysis you can prepare in the power bill. But obviously it takes a little bit training and it takes a little bit knowledge. more advanced level of knowledge, then you can easily prepare that one. This is a key factors. This is the same ah graph which I have ah just a few minutes before I have shown you the enlightening stories, story telling. Just I will change the month, it will change the whole of the figures. This is the profit and loss. description, actual budget, variance between these one and YTD. It is month wise, YTD, YTD graph is there, also there is also the monthly scorecard KPIs, different type of KPIs are there. In this way, can prepare our all the, this is the balance sheet, we can prepare the balance sheet also. one more draft let me show you.

for my organization I have prepared the data model and the dashboard fuel analysis and you will see that the colour combinations and other things you can easily ah can do it. It's not the big deal. top ten stations, various analysis and various analysis. It is the price variants and the volume variants. Budget rates, comparison month by comparison of the total total fuel cost, liters and the month wise price, average price per litre. In this way you can make any ah dashboards from your data. 2021, 2021. It is easily changing two thousand twenty-two. You can check all of the data easily.

It means only just you have to update your files, update your data. And the resulting figures will be very easily. And comparable. that one training and if there is any questions, please raise your hands. I am there. If there is an issue, you can ask any question. We can repeat it. If there is any concept issue, I will repeat it again. we have the advanced level of ah desktop training and we will start the batches. In that batches we will ah train with the advanced level of test of power BI which is ah which in which in that training we also learn how to publish that one file, how to publish that one ah dashboards to the service power BI service and in the power base service you can share your dashboard to your colleagues in the organization to discuss on in the advanced level of, desktop trading.

And also we have a one ah more training that is advanced financial reporting and power growth. In advance financial reporting and power BI we will ah share with you whole of the more than 100 pages manual in which we explained and ah on the Zoom we can we will trade you how to prepare financial statement. Profit and loss, parachute, cash flow and other ratio analysis. We can trend on that it is the advanced level of power behind desktop or the power. All the working is in the desktop. But it is the more calculations, more relationship, more tables will be used. And for that one trainings, you can contact on my email. And or the Mr. Ali, you can contact them. They can arrange or we have started the badges.

Then you can ah get the training from that during matches when the batches will be stuck, you can get the training. We will announce that one also. You can add any question, any help for your data analysis, for your dashboards, for your calculation, for your understanding. You can send me the email. I will be available. Every time inshallah. Please raise your hands for the questions. If there is any question, please raise your hands or ask the question. And if there is any comments, any feedback, please send me the in the email. We can improve our training. Program, we will improve our training plans. We, we will improve our training material. To give more and more concepts. Because there is the lack of time. There is ah the some of other issues. We have ah just ah given the basic concept. Beginner to intermediate of the power we are told to familiarize you with the power BI and you can prepare your reports in efficient way. Thank you so much. Dear participants, on the behalf of branch committee, I would like to thank the resource special mister for sharing knowledge about the data analysis and visualization in power BI.

Thank you so much sir for your valuable time and I thank all the participant who attended the training. Thank you so much in a lot of ways.

test attribution text

Add Comment