Okay, so this is not our first time coming, so I think this is the third time, so for some people this is their first time. Yeah, so yeah, last month, last month, last month, last month, last month, okay, last month, so our new participants, one, two, three, okay, okay, cool. So one thing, the Power BI user group leaders, anybody have seen Microsoft Ignite programs, who have noticed Microsoft Ignite programs, conferences happening around the world now, Ignite conferences who noticed. So Microsoft Ignite conferences are the conferences we do in different locations all over the world. So they talk about the Power Platform, and the Power Platform is very into three, we have the Power BI, the Microsoft Flow, and the Power Apps, the Power BI, the Microsoft Flow, and the Power Apps.  So I'm already building Power Apps though, but the real thing is that, so what is Microsoft Flow, so let me just explain. So trying to automate some processes that you do repeatedly is Microsoft Flow is trying to stop. So you know that you see some images, it can work with anything Microsoft. So either you are working with Atmook, Excel, for anything. So you are just trying to connect one thing to another, and everything just drops immediately. So all these rules and rules on Atmook here, rules on Atmook, rules you do. So you just say anytime anything is coming, drop in one place or send it to. You know that that thing that you did is a flow, you get it. So that is Microsoft Flow for you. So Microsoft Flow is trying to automate a process, a process. So now let's now look at what Power Apps is. Now Power Apps are no code applications. So what are no code applications? You need to become a software developer to produce or to create an app that what Microsoft Flow is doing. I mean that what Power Apps is doing. So Power Apps is this. So I'm creating an app right now that has to do with registration.   But this is what I want to do. I want to create a Microsoft Flow that connect that in every month. It just populate it into our own database in weekly. Are you saying how it flows? So that is Power Apps for you. So Power Apps, where do you create anything in Power Apps? So yesterday I was reading about this basketball. Is it Lakers or something? I can't even remember now. I don't even know on Twitter where they use Power BI to analyze. Now they're going to start a new season now for basketball lovers. So now immediately they release their timetables for their matches. Then they use Power BI to analyze how many people will come from now to the next 40 matches. So people think Power BI is about visualizations. No, it's more about the analytics, not the visualization. It's more about the analytics, not the visualizations. Just beautify the analysis itself. So now you can look at what these are. These are the reports. We do our reports. These are the analyze when we train in Dibra. And we notice the loop in there is the analysis. The viewer is just giving the beauty. It just gives you the beauty. So Power Apps, you need to create anything. So the sales team are going out in a bank to bring in new customers. And you don't want to wait until 4 o'clock. See, they bring in the names. And you know those paper things that are used to meet them now. You don't want them. So you don't want that process. So what you do is you create an app. You give it to them. They know, they insert everything. When they insert everything, it drops into your own office, where you are sitting in the office. All the database is already coming in. Now there is one who was reading what they use Power Apps for is this. So they are generally using language. So some are speaking Spanish. So now the problem wasn't that they don't understand what to do. But before you even tell them what to do, they are already having a problem with the language problem. So I am talking to you. You can't even understand what I said you should do. The language is the problem. So what they did was they created a Power Apps that solved the linguistic problem. So the key words they are going to say to tell you to do something is what the language interprets and gives you a sponge. So you see the whole process immediately and you know what you need to do. So have you seen how Power Apps works? So that is how it is. So now that is Power Platform. But we are using one for the next classes of months for Power BI. And unless the Power BI is just new, and we are trying to make sure people use it. It is just four years old. So next year you will become five. So we want a whole lot of people to understand what Power BI is before even diving into the big analysis of them. How to use it? So now one of them is low cost. Low cost. So now one thing is you have to pay a ruler to use it. Now apart from the payment, the visualization can use it more. The visualization can use it more. Now some of these BI tools have one thing over the other. Some have visuals but don't have analysis. Analytics part of it. Some have analytics but don't have visuals. But Power BI has been able to bring both of them high. So if you look at the conjuncts for this year, you will notice Power BI is so high that is beating table. Just using four years to beat table. And table has been since I can't remember. So you need to understand it. So imagine Power BI uses the same years of table. How powerful is it going to be? So that is Power BI for you. And Power BI is going aggressively to everywhere. So that's what I was talking about Microsoft Ignite conferences. They will be speaking in Vietnam. Austria. That is nice. Two weeks. About the same conference too. So it is big. Power BI is rich. And they are going to create a community of users itself. So you are not saying you should just learn the scale. We want you to use it in every of your things you do with this. We don't want you to be just every time you come once a month that is when you will put your Power BI. No. We don't want that. So that leads me to the next question. Who didn't visualization with the last month data? Okay. One. Just some. Just some. So what did you see your analysis? Is there any problem? So 90% do not do. Okay. It does not. It does not. So is there something about it? I think it goes from somewhere. Okay. We are trying to be in detail for the countries. Okay. And I would not say this to the world. For the past five years. So I don't know what I'm writing. I just left it. You just left it. Okay. So this one I did. A pilot. I'm just interested in the six countries. Okay. So I have to do some cleaning to make sure my six countries were what I needed. So I... How did you do your cleaning? I did the... Oh, okay. I just feel like I put that in the rooms that I need to talk to that I needed. Then also I knew what I want because there was something that contained something I'm trying to match. Okay. So I found that there were a lot of plant space, you know, you understand this. Making these plants to go by way of visualization. And I wanted to get some beautiful visual. So on LinkedIn too. On LinkedIn too. Yes, on LinkedIn too. Oh, okay. Sorry. I forgot your latest. I have commented but I don't remember. Yes. Okay. Now, another thing is this. LinkedIn, Twitter, we are both in those two which I am as possible to post. So I posted what we did last month. So Power BI is ready for our pictures today. Yeah. Power BI is... I wish I can show you the pictures actually of our charts with Power BI. So Power BI is ready for our pictures today. So that is it. So let me understand that Shuchi Media is also helping us for people to understand what we do. So my name is David to Barbara David Brown. I am David Ibu. So I am David Black. David White is working. So we are trying to work. Yeah. So what we are doing in the next 45 minutes before we go for our tea break is this. So we noticed that some of you might not have come last month. So what we did is we created a PBIX file on the user group. You say a file, a Power BI file. So just download that one. So for those of you who came last month, don't worry. Just download that. So that one has everything that we did last month. So you just just don't stress yourself. So just go online, download the Power BI, go to pod user group slash leaders pod user group slash leaders and download the Power BI file. Okay. So let me go online today. Wait. Okay. So let's go to the website. Pod user group user group slash leaders PBI user group slash leaders If the network is not working, just use your own to connect. It's just connected. PBI user group slash leaders. Yes. User group slash leaders. You say PBI. You say PIB. Yes. Okay. Yes. There is Wi-Fi. So guests. Then this is the Wi-Fi. So let me. Yeah. You don't need to do it. Don't worry. It's okay. It's okay. It's okay. It's okay. It's okay. It's okay. It's okay. It's okay. It's okay. It's okay. Those should be 100. So how many people are connect connected to the Internet? You downloaded it. Okay. How many of you have connected to the Internet? Okay. The password. The password. X, 8你说. The size X. Then, equals to 7.5-3.團cker g u e s equal to 753 uppercase x lowercase gu e s so you connect and go to pbi log slash ligus in the number 5 you see September September life lab September life lab ဢ ဆ ဆ ီစ Ṭွတံ, ဉ ြွာာဟဟာ ာီဪ . వత్ Tammyత్ева ఊకిక్ని మృత్తానొాానం ద్న్sticks 느낌😟 మూభ్నా వాతి పఊరింది సాప్దొాలి మోచ eram�మలూది మోచాగిని కంట్నిక్ొ హోక్చ Milea స్స్వత్లివ్వ్ల్లాతాల్లిందింద్రియస్దార్గస్యవత్వా. చాదాబిదారేప్నదారిదువందేటి. మాయికిసందె. మానీనిసందెదిందిల్ల్. సేల౪ల్లుటా కంగలా సిరూమిక్లులెంటి లోతోమిక్లూడికకరెటికోటా సిమిలు సిలులులాలుమికటికిసిలులుటికర్లికుసిఌట్లాటహంటల్దరిమౚ� థచి మక్టోమోచృిఇ massive మరినో మక్టోటరా౎ Юుస్సియతియంపా delicious predator వాపా lamp gentlemen ఇవాదిట్యుటిన్టేENA మ� ban에요閇చ్యలివుయె� 무젱జలం ! లాత trzeba మాతం గానాయతూ Strategic please ok ok so page ok so ok okay silent yo yos and okay adomo cribe点  regression  amplify ohn సర Thousands వివో వివో సివలర పౌటం నాడివైభా హ౉ Ċాarma い Office సివర్కఠ వలరం చాటుోన俺 assets పత్యమ rzecz  Jab sa  simplified          我們                 co   ar  d  le  t,                                                                          AUiki has no journey for Saint Precept, today we do the law. మధహోణకం groß concer నాస్టిమినండతి మిచి ront of mountains మాకినకూ. మినిచి mortal so ling stop saying H Alexander and one of those visuals that can help us analysis is what we call the key influencer visual I don't know if anybody have used key influencer visuals so now it affects everything so it's going to look at properties, income, health, transportation so it doesn't mean that because Nigeria is kind of among the poorest country in the world doesn't mean that we don't have multiple travel are you getting what I'm saying now? so we are going to relate a transportation and property together so those are how we want to understand what influences what what influences what so now we are looking at health can we use health also and analyze wealth so we are trying to see how all of them go so we are going to build up to like six if possible six indicators all over the world so we are going to see how all of them affect each other how all of them affect each other and also I want to thank Nigeria's business school for giving us this opportunity to use their whole we want to thank you guys thank you very much can you put your hands for Nigeria's business school thank you very much we appreciate and D Brown consorting for giving us the guys that are available here so we have from my top and right guy so he's the guy that does every video you see on D Brown every video Zima so he helps us with all your videos recording all every day you see D Brown does every video just know that is the guy that is behind the camera yeah that is him behind the camera so that is how good he is so we are behind the scene that uses the camera so now that is Wally everybody knows Wally already so Wally is a trainer he is a party eye trainer an exiled dude guy so now we have Sam Singh and Lekong that helps us with registration so thank you guys thank you thank you so we are using that to work for everybody to download so that we can work yes do you have power BI or your system have you done a power BI okay why have this software have you used to you can I don't have the software can you download it from just go go download power BI yes you download it you can start yes so if you have your data your hands are please if you have your data if you have your data yes this is on the website it is on the website it is on the website it is on the website check it it is in your introduction but it also starts you can go to the website and download it so can we start oh actually a little bit okay so I am going for 5 minutes please no no no October is a fire September is a PBI that is what we did in September so now we are going to bring October data into September so if it becomes larger so that we are trying to do yes okay so this one is called October it is called September I downloaded this one this one? no it is weak this is what David it is a fluid like 20 cent an ounce of gas one this is September this is the same this is the two things I wanted yes that is it so I have done my sense yeah have you gotten it now? this one is for the last two minutes come the fire thanks so take just a little from here do everybody have the fire here now? okay okay have you seen it? okay this one is for the last two minutes what do you have in mind? they are just too fast yeah they are just too fast yeah this is it too so can you copy it and put it on the system please it is just too far so what we are doing now is that for next month also because we need to be expecting new people too so we put on what we have done for the past three months put it and bring it put new fire for that month too so that will be going so this September yeah everything is there okay this one no it will give you now it will give you now yeah so you share your slaps up let me share so do you have the fire? yes do you have the fires now? yeah inside is flash let's take it from there maybe the faster that way yes okay so let's go so kindly open your September open it open the September life lab vision you also have something like this so this one we stopped last last month okay thanks who have this you have data now too so everybody opened do you have the flash the data yes but can you just no let me give you flash one thing about sharing fire is this so if you claim a data in power query you notice that you can give somebody those quotes in advanced editor you get those quotes and you send it to somebody that quote the person can take it and put it in same power query and everything populate back yes so but the real issue will just be that where the person save that same document is where the person should just change and that is the first thing you will do now now we done with everything from online from somebody who dumped it there so your file itself supposed to be here on your own system so we are going to change the source file we change the source file and make it clean and then we bring in the other ones so some of you have already done what we did last month already I have seen it so that is fine so it makes it easy that way so that will first you are going to do so I am going to start to wait do we have all our files we can work now ok so let's work so now if we want to go to so we are doing this all together actually so I am not changing I am just facilitating what we are doing so let's go so we want to go back to Power Query so how do I go back to Power Query then edit queries yeah so everybody home edit queries so I am in Power Query home edit queries so if you notice now that there is a problem why? because the source file is coming from somebody else system so it is not connected with my own system so I need to change that I need to change it so how do I change data source so please let's follow that way we are all doing it together so let me wait do we have the file we have mission files and I think I already have one system so how do I get these folders to complete back to the source so is in our internet so just download from there go go back yeah so I think some people also download this one so you can just create it from them so this September this one this one click so you are going to see the files okay this one this one goes down and over this is the first one you see the file there so can I help you yeah I know you don't have power BI itself you know if anybody has software does anybody have power BI software the software itself does anybody have yeah the set of you have so I am coming can I can I yeah so please for those also for those who don't have all the exercise files that we used before can they have them on your system too because that is where all this will get their data from so for those who are here last month you are fine but this is your first time coming please can they have those exercise files how can you get it is also on the site no no no no it is not a must there is one good thing about power query power query connects to on address of data sources so it is not it must everything must be in the same place so I can be doing my analysis and maybe my country is already in a web file I can run get it from a web then I can run get all my other indicators from another place entirely maybe and as yours database so you can work from anywhere is just you connecting getting a connection so now what we want to do now is to this data itself you put it online but the database is a way it is it is not our own system so we want to connect this data into our own system so it doesn't give us a first then we will now bring in October data so bring in October data because now it is telling us errors we can't work we have to change the source file so the source file we want to change now have you seen it please stop it thank you so it is coming for them that needs power bear so for those who have fly job you can help and put September all a September exercise fly into your flash please give it please do it for us so we can move very very fast by getting behind schedule a little bit so anybody who needs it we can just shoot it round do we all have last month files but they don't have do you have okay you are last month you change your system okay okay you have last month file can't give it to you you have to if you have no problem you will do it okay thanks so so you just use that yeah yes yes okay so let's let's roll okay so how do we change you said I should go to source okay that is all okay so I go to source so everybody click source so there is one setting one setting sign besides source so just click that setting sign you should bring this dialog box for you are we there okay so now the file part of the person who put it there which is wale that is his own file part we need to go and bring our own file part into this and it will work immediately so now do you see browse so you can click browse click browse so if you get response what are you trying to bring in you have been in a population so now do you see our population is population total so I click population total click where your population is click population total are we done that your population so you just go to your directory where you have your population click it click open click open after that just click okay now do you see what happens now see what I want to do I go back to my last applied step then this is it so I've changed the file so it's not more wale's file it's now my own file that's what I just did now so what I did my first step I went to my source I click on the setting then I click browse then it goes to my computer to where I saved the files then I connect it there then I bring it down here and we are good so can you do the other ones no from just from source yeah so I'm using the second one we're using live expectancy to do it again so we can get familiar with it so this is live expectancy now I click on live expectancy I still have the same problem so I go to source see the source I'm going to can you see the source see my applied step the first one my applied step yeah my applied step so my applied step then I go to browse then I bring in live expectancy to then I click open ok then click the recent one in our applied step so we are here your last applied step should be rename column something like that yeah just click on it yeah just click on it everything will work like this everything that we did in that applied step is called the M language so everything we did was called M language so now let me show you the codes for M language everything that we did here just look at me so I'm going to view codes that you need to write to make your data clean is what we written here but we don't need to write then your partner will tell you change this is the code so for view and editor advanced editor so this is all the codes that I was written so instead of you writing all these codes if I clean it I don't need you to pass through the problem again so I just copy all these send it to you so you just go put it on your own advanced editor too everything populate change the source you are fine are you seeing it now so that's how it works so you don't mean what Wally has been doing no I don't have that problem I don't have that problem so that's how you share okay okay okay so what do you now say next what do you now say next okay okay so go to error yeah so now it's about to do in so hope you are bringing population audience important population first but your permission is having issues permission is having issues permission is having issues so do it again start from again after browsing picking up nothing you are fine so for you to know that it is clean you are fine fine but make sure you click on the last applied step to be sure so your last applied step click on it to be sure is the data clean it's fine you are good so now if you want to know if your data is clean this yellow sign we change to a table sign there is a yellow sign sorry but I yellow sign we change to a table sign so you change to a table sign you are fine you have all the files so can you read for all the other files income countries imagine for those two your part file will work you don't need to do part file now I will tell you why as you want so that make sure you bring it in live expectancy data not population data okay I need for population first okay do population yeah okay open okay so click here yeah so go to live expectancy we have to go to live expectancy yes thank you for income yes I will explain why so don't do one on one so make sure you just click on your query before working on it the query we are clicking on is what is appearing here so even people on live expectancy see this what is appearing if I don't click on it and I'm still going to source and I'm doing anything on it it doesn't work so click on it first and then who have finished it oh cool okay so let me meet you guys but you notice that immediately you do population live expectancy your part file works what do you take upon thank you thank you so what we did was this you know your income population and live expectancy they are one one indicator agreed? now we just appended the three of them into a new file and call it our part file so those the fact file itself doesn't work in those other three don't work so that's what we did there so I'll show you how we do so this income so I go back to source okay so now my part file has worked see let me open my part file for you for you to see if you see that nothing has happened in my applied state it's just a source check your part file so you see it's just a source what we did was to append all our query how we understand append queries and merge queries append queries and merge queries so for every other person see this one append and merge means so can you see merge and append queries or by right hand side why this is this? so I want to merge query I am merging to the right columns you want to append I am appending below so if I am merging so this is what I mean so there are three people here and I want my columns to be more than three so if I want to put the other two here which one would I use merge or append because I want it to be five because I want to append I want to merge and do some queries whereby ABC they are all together all would I click on append you understand so that's what we just did so we appended the queries so you notice that everything worked perfectly so for the countries same thing I will do so which one for countries country is a different set on its own was it this one you used last month okay you used it last month I find no problem then do you want to if you want to this is not showing the right time okay this thing I don't know why close it and open it again close it and open it again now this is what we will do very fast everybody is the data not clean yes it's clean don't worry so now this is what we will do we are going to bring in poverty data now we are going to bring in poverty data so I want us to live perfectly very fast so everybody let's bring in poverty data new source new source so is excel that we are using a bit so click excel we are going to see October 19 exercise file so new source excel then it takes you connect then it takes you to go and bring it in then open so we take this then we click okay transform transform so now we are going to do a team job now so they are going to give me steps on how to clean this poverty data so I am going to picking five people so just do ready I am picking five people to clean this data okay you are the first person I am picking so we are give me one step transform so wait I am picking him so you have to click transform then you have new special as edas okay cool thank you new special as edas let me see I am picking next okay next step next step next step next step okay so now you will notice at this point first point we have these are incoming so we have countries in our first world and it doesn't make sense it is really getting part of our data it doesn't make sense so we need to take all our column edas we take it all up so the first world itself we need to use it as an world so you take go to there are different ways to do it actually it says you should go to transform I use first world as edas you can do that but do you see this table on top of one do you see one table sign on column one you can use that place too also to do it if you right click that place you are going to see use first world as edas you will see it there and one thing about parkway is that there are so many places you can do so many things so many places you can do so many things so you don't have to just pick which one you want to use and which one is faster for you to use so my brother what am I doing next now what's on people's table is anybody thinking of that thing on people to that column on people to that column on people anybody have idea first of all those don't know okay they know okay now everybody always remember that none is actually not a problem sincerely when you say no nothing is a problem it's not it's just an absence of data all is not a problem it's just an absence of data so in excel you know those empty empty cells those empty cells comes in as no so when you say no you do not trust they don't know we give you a problem it will be no first of all that has data first so let's say someone has data if no one give us problem we will know if you don't take us away there is a problem but none is most of the time not a problem and nothing I can give you a problem is maybe we are seeing those minus sign it's so annoying you will not see it when minus sign then you will not be seeing data format error copy are you seeing data format copy is it bad so now we are going to unpivot other columns not unpivot columns unpivot other columns okay so now this is it so how do we do unpivot other columns everybody let's follow right click on country column okay so let's do that way my boss was holding camera he was moving at the computer but he looked at the screen he looked at the camera again he looked up he didn't understand so he need to ask so how do they do it so now remember I said that none are no problems they are no problems now one of our 7 body rules is that all gates must be in a single column not rule so what we are trying to do is this all our gate headers we are trying to convert them and change them this way they are in rows so that's what we just do so we just did unpivots so you can browse about the 7 body rules so the first body rule is that there should not be empty rules no empty columns all gates must be in a single column all columns must have their own unique data not total or sub-total can somebody give me the 2 more no obstruction around your data and one more last word one last word so let me go back you can remember the last one so no empty rows no empty columns all your gates must be in a single column no total or sub-total then we said all columns must have unique value then number 6 no obstruction the main one thank you one rule of headers one rule of headers so one rule of headers that's the last one so what we did was just to change them upside down that's what we just did so how we do it is this we click on it, right click and unpivots other columns okay now if you want to know when to pivot or unpivots this is it most of the time we do next you remember that when we brought in the data we noticed that all headers were actually on a rule not up and you said that we should have we should take them up when we took them up you notice that you now have country and all your year was like that so now maybe you know that then you have to change so which is unpivots so you are just unpivoting them like that so you are just unpivoting them like that no it's not as transpose it's not it's different see transpose transpose is taking the whole data too and converting them same way but unpivots is taking selected so and that selected means that this in transpose in the transpose we follow the countries will follow it becomes scattered then but the one that helps you to still have one that is constant and all your data will see it just follows in when you still change is unpivots so you have to know when to use both of them so you are turning the whole data upside down use transpose but when it becomes that you need to have some set of selected data still standing and you still need to change all others they use own unpivots that works they use unpivots so now but there is something with this data that is not complete you know why in our population we have 5 things in our life expectancy it is 5 in our income it is 5 but you know that this property is also an indicator is an indicator also but we have 3 how do we make it 5 what are the other 2 things that is not present in this because let me do 2 things so we have indicator name and date and we did indicator name last month we brought in indicator name so we are going to do that again we are going to bring in indicator name and somebody should tell me how we bring in the dates so we are going to do that so Kasodi reminds us how we did so I will be talking to my madam by my left yes please yes please so let's go now in our population it is called country name but in poverty it is called country I remember that the семь is case sensitive everything that you write must be the same so let's change change your country to country name double click the name are you going to change it yeah click enter change your attribute to her click enter so we have to have to be well first so now what you bring in indicator name one bring in the indicator name so what do you bring in so let me everybody go to add column add column tab click it have you done that do you see custom column click it  Beverag   apocalypse                                                                           మాసికాక్ని,లిండిమినికాకినినాచాని యలి దికాచిల౿విల౿లాసిని మపినియాసితింక౾చిదినియాస౿ంచిని.మపికినించి విమాసికిసిలాసి.కída�� let me tell you how we do it now we are still in that column do you see column from example the first one do you see click it do you see from selected column from example then column then from selected click from selected do you have something like this do we have it now we need to be careful here I pick from selected which means that there is one of the column that I want to use to help me work in the column that I am creating that is the meaning now and if you notice that is your indicator name, is it the one that has been highlighted for you or what was highlighted yours is country look on the top of that country name do you see that it was ticked it means that it wants to work on country name and you know that is the one we want to work on we are working on the year because which one is ticked for you indicator name indicator name like mine too indicator name no problem all of them so now everybody make sure the only thing that is ticking is your year so make sure your year untick all others make sure it is year year the add so add add column click it so add column then add from example from selection so make sure I don't have this indicator name indicator name you have not done indicator name so just cancel so add column change it to indicator name so go to the inside so do your your inverted commas I need inverted commas alright ok and click ok then do stop here from selection now do the other one from selection so now one journey to date now see what we want to do now we have ticked year so now see the first one which is column one are we done now now type so now this is what we will do type one type one ask what you like in normal dates do you have something like this that comes up for you so which one do you think we are taking dates from year are we so now everybody click date from year I just click one I just type one date from year click enter ok so everybody see it one on one 1996 that what I just did I just picked that for some people they might not have it if you don't have it there is no problem so I will move this so that for people that don't have it this is it one on one why speaking is this is changing all these years into dates that what I just did is changing all the year into dates so that is just what I did so now when you click ok it is supposed to work click ok it is supposed to work when you click ok it is supposed to work so mine is doing some match things that I did not tell you to do so that what you have on my own but as yours worked so even you know that one is having date month year date month year yeah straight down date month year you didn't go to your scene on my own screen just flow date month year pick this yeah from selection so take measure you are taking here take it here see it here yeah take on take this type it date month year one on one yes so it depends on which one you want to pick if you notice I gave us so many differences that I want to pick so it depends on the one I want to pick the one you pick that you click ok then it's populate so it's looking at your year to get the other date you have a question okay that's query that we put earlier where we are signing property where we really found the property we didn't know poverty far we all ever delude a poverty far we all ever delude a poverty far you never delude it again the flash is very flash is it in the optimum yes where it is there okay but how did that come out of free impoverty yes is the same yes so if you notice your column had a change to date data type already is so by date is intelligent enough to know that you are talking about date if it sees as text it don't think text if it sees number it don't think all number so it knows what you are doing first one remember i said something i said that there are so many ways to do many things you get it now so now we have done our query everybody click close and apply let's go to power bi close and apply let's go to power bi so let's go for a tip break now yes close and apply so you go for let's close and apply go to o click your o the first thing you see is close and apply so i can make you sleep i just want to confirm that you are fine yes yes you are fine so like my friend said he said that there are different ways so which is true there are different ways this is the second way you can do what he is saying that we should do i click on my ear i right click you make a duplicate column you notice that but i don't need that so i change this to date you make a change to date i change this to my data type i change it already to what to date so so in the different ways in Baku there are different ways to do different things so let's go for a tip break so let's go for a tip break if anybody comes up a new song or this magic this magic so now we need to really on the whole now so now once we now talk about the dance how dance works so i will just use like 2-3 minutes you know the song in excel what is the same way song in dance so song in excel you are using it for two songs you pick one, you pick the other one you have your addition if you don't click on your allies there are everything works together fine now those dance things separately now dance is the language of party party votes it thinks separately so it thinks with it thinks full on tables it doesn't think with self doesn't think with self it thinks with full on and tables it doesn't think with self so if you are having something that goes like this you remember our people's table will you go with people's table people's table table table if you remember our people's table column column column can you try to decide what to do stay with that time party vote party vote party vote party vote it thinks column it thinks table it doesn't think with self you are not putting it with self it has to be as a column or as a what table a column or a table so what we will be doing in the next 5 minutes is for you to see out of that out of that so I will give you the floor to Wale ready that's our list I will spell it for you so why don't you continue from there okay I don't know I don't know if you heard of DAX DAX DAX who is very good at DAX on the first time 1 2 3 4 David now DAX on the first time oh okay so DAX is data analysis expression data analysis expression okay so just like some advanced let me say some more advanced formulas do you use excel can we say I did not use excel you have never put excel you have never put excel you have never put excel are you please business say at all it's not business say okay so excel just like excel there's nothing in DAX it's very very simple just like excel but the difference is that in excel we use excel very well excel is a special application so in a special application you have different cells in excel I have something like this table I have this so I have this what I have A B C D this is a special so A is what A is representing what in this special a column so A is representing what so a spreadsheet consists of columns and rows and this box I have what A is what a cell call what A1 so this is the address of this cell every cell only have their own address so the address of this cell is called A1 right what is the address of this guy C2 right because there is a column and there is a row so in excel whenever you are using functions or formulas you reference a cell you reference each address I want to go to C4 or you reference a range for example a range means an aliting model one cells so I reference this place and this place but for Power BI it doesn't work like that because it's not a spreadsheet application whenever you can reference a particular cell you are just working with a table so anytime you are working with a table not a spreadsheet if you don't reference that table or you reference the column a table is always like this a table is always like this right this is a table and this is a spreadsheet right so whenever you are referencing this old table or you are referencing this particular column let's say this is column A B C and D right and A I have 2, 3, 4, 5, 6 if I want to sum this column what am I summing I want to harder this column what am I summing I'm summing column A but in excel if you want to sum all this number what do you do you highlight from A1 to A6 do you get that so in Power BI it's different you are referencing a column a table so look at these are data now data we just brought in if you check the data up here we have different indicator can you mention those indicator we have income we have like expectancy we have population and what and poverty but if you look at these fat cells look at these fat cells the fat files that you have look at the fat file if you look at this fat file very well you can see the 14 in that table is what continue what's the second one date what's the third one the fourth one is what and the last one is what inside this value remember we have different indicators so inside this value we have 4 or 5 different indicators we have population inside the value we have life expectancy inside the value we have poverty inside the value we have poverty inside the value if you take our population population is in numbers our life expectancy is in what the life what you are expected to live by it's actually in age it's in age you are expected to live by maybe 55 years in UK you are expected to live by 60 years so you have different values here so I haven't even worked with that before I've worked with DAX let's see your hand I know all the gurus in the house so don't hide your face so I've worked with DAX can we see your hand with DAX give me one function you've written in DAX so there is a function called calendar auto is it auto calendar or calendar auto give me another one can you add up just some just some almighty kakuni almighty kakuni actually in power bi there is something called calculate do you know the reason why you call it almighty because that's actually the powerful calculation in power bi if you know how to use calculate you are already a guru so let me put it here calculate so it's a guru and you are sitting beside him you better calculate his number calculate give me another one give me another one count there is count just give me we are talking about top 5 DAX just give me any DAX that you know is top that you know is very well full term there is a function called full term but we are still running for this one to get the top 5 give me another function you've used DAX before you've used DAX give me one function in DAX okay that's good same for your last year so you do time intelligence a lot any other function okay for those who are using excel they are not familiar with DAX tell me one function you've been using in excel salt you use what again excel group average do you know what we mean by average today let me just put it up here average okay give me another one excel group I'll be going to tables from here give me two functions maybe excel or DAX give me another one two functions sum is already here mean okay so there is a function called mean to give us what the minimum number what's the normal distribution I've been using excel for for 7 years now I'm not used to what's the normal distribution not it's mean but I am a statistician or T R S T we need to learn this from them okay so I'm used to this tell us tell us tell us V I N O S T you're a statistician too see I need to learn from you now okay give us another one from this table another function maybe in excel or PAH BI ceiling there is a function called ceiling I think I've used it once ceiling okay give us two here give us two here index oh that's the excel but you know we don't need index again what do we need now thank you let's look over but let's put it down index another one contact contact you use concatenate concatenate excel concatenate concat concatenate you know this concatenate is open in excel now people don't use it again they use text join ground exploit these are off functions so don't be scared just the way you are doing it in excel is the way you do it in PAH BI and for those that are very good already in PAH BI they know how to use all this stuff all this function you have in excel they are in PAH BI as well but for today you want to pick any top 5 here which one are we picking from here sum is very very important to me in any analysis it's because for any analysis that you will do is it that you are summing or you are counting do you know that for most of the analysis is it that you are summing something or you are counting something right so sum is part of our top give me another one that is top avert is also part of our top because we do avert today avert is very very important to me avert give me another top function cant cant is very very important to me I am counting our transaction we are counting the countries we are counting different stuff okay filter of sorts filter of sorts filter of sorts will be very very important in excel just look at this guy look at this guy it's a master of filter this guy is a master of filter I want to filter anything so let's add this guy to part of our top 5 okay calculate one two three four we have four already let's add one calendar yeah it may be top but we are not creating calendar so let's pick calendar one how can we be left to have anything don't mention this to that way because I don't know I don't know what it is don't mention this to that way I don't know what it is you have to forecast and understand you have to forecast and understand I can do that different ways man no it's fine I will not pick it pick another one give me another one we have some average count calculate and what mean if I want to know the minimum number should we add it to our top 5 no okay total some is there already but there is something called total itity we are not using that I don't know who have used that before okay any other one oh she based on this one I should add normal one distance count oh so there is a function called distance count I want to count the distance distance value distance count count is count well let's just leave it okay maybe we need another way remember we are closing by 12 on the dot so all this top 5 right we can do maybe some average today but if there is time we can do the rest 3 you can practice all this on your own right do you get that and if you have any question you can actually ask with this your data okay so for this now let's do average let's start with average right let's start with average open a new page just open a new page for those who are already don't worry we will still do it together we will do it together okay open a new page let's have a fresh page like this 1128 okay let's have a first page we are just going to create a simple dashboard with this new page but before we do that let's just write some DAX first okay so I want to write before I do write anything pick a table visual look at this if you are opening part behind for the first time these are called visuals you are just like your chart in excel to express yourself click this one look at this visual click this one this visual click on it click on table now go to your fact sales fact sales right are you in fact sales tick value tick country name and tick value tick country name for a fact word tick tick tick tick country name for your fact first from your country and continent face look at country and continent up here yes tick country name just tick it like this tick country name just tick it they are lost okay look up look up yes not under fact so you can pick it from the dimension they have already linked it then tick value as well just tick value please okay so tick tick country and tick value from your fact file so you are pressing in yourッ your neighbor is�ają this is a user group now can help each other help your neighbor if they are not doing it yes so just tick value from here take value from your fact tick value యానికోటిండిఖారనిలిభ్లటాకోమ౱ిందాస్య్లౣవానాడిపూత్లిక్సినిమీదిమౚిరి.  reckless can see facts so in fact he didn't పారనినివూలుత్లినించిఢి. మాడ్కత్పచికుటసొత్ంలులిలులురోంచిదాక్లు. వాకరరనికినిందిందా. మlictటారినిసియా నిల౿నియార్యానిమాచానిందిచికింది? మనికి అలిరినిక౿ మధౕందిందియాన౿క https://www.cindu.com. world.org మ్ఎవ్నిసిందివందిమేంందిందినెవంద్ం దాకిల్జ్ఎగేత్గేరూయిమ్స్యాట్ినిదిఙసిత్ఀంANNAత్టియాట్విదిలికిమ్త్నికిలిత్రెందిందినోర నింర్రాలు,లునిన్నలి నరునిక్ద్లి ఇరుదరుతరానితరాకంరంతోరాానిమినిమేపెటాలున్చిసిదింారారిని. పనామెఢిరిథెంది. మిభాకోశిసిక�  câmera  газ sat per  camera  context  centre er per ga కికస్. ఎతూళిలెడి。 కికూ煔가ఌ . . కికోవిదా఻కిపర్నూలిధ débutఆండే,ఆ . కంలంలంఱకు. కూలేగిండlane�ె నగ్నికొనాధికండతె ーい anutanuhu 1 roy two Professor are rather rogenist no passions per న౛ిలూత్గత్టి ¡ melee చాడరం ఞ్లచింటం కనక్సెకుటి� though చ్పా. క్రిలిలూత్గర்శ пользовతో త్� foarner� scale మానైట్ట్టతో. పువగి కనహ్ programmer spinner confort కరన్టలూతితో. Look at it. Now this, I'm filtering this value by income. So this is the income of all the countries.  Right? If I click on population, for example, like this, I'm filtering this value by population. What if I now need all this indicator as a different, as a different value? Maybe I can use them for different analysis. So I want to see population here. Instead of having value here, I want to see population. I want to see income here. I want to see live expectancy beside each other. Right? But before we do that, you can see that this thing is just showing, like, just summing all the stuff. That's the default.  What do you see? A summation. So that's the default. It's summing the outfit. So we need to do average. We need average, right? Okay. In Excel, what do you want to write the formula? What do you do? You just type equals to any cell, right? But there's no cell here. So what should we do? There's no cell now. And it's query. We're not going back to query. Okay. So every application only speaks their own language. Excel language is functional language. That's what they call Excel functional language. But part BI speaks a DAX language. Right? Function in Excel is called Measures in Power BI. Function in Excel is called Measures in Power BI. I'm writing... If you're telling somebody you're writing in function in Excel, right? I'm writing in function in Excel. That's what you say, right? But in Power BI, I'm writing in measure. Not a function. I'm writing in measure. Not a function. You are writing in measure. I'm writing in measure. Right? And if you want to write in measure in Power BI, right? In Power BI, you have just four tasks. You have own view, modeling, and help. Is that what you have? Can you go to modeling? Go to modeling tab. Go to modeling tab. I have a problem with my computer. Okay. That's fine. You are more advanced than me. Okay? Click on modeling. I just want you to be in modeling. Click on modeling. Are you in modeling tab? Everybody, this table. If you're in modeling now... Are you in modeling? Check your table. Check them out. Are they being fine? If you're in modeling here too... Are you in modeling? Okay, so in modeling, can you see new measure? New measure. So we are writing in new function just like in Excel. You are writing in new function. So in Power BI, it's called new measure. So click on new measure. New measure. Can you click on new measure? For the gurus in the house, they only know what this means. But for somebody like us, they are still struggling. Right? So this is called measure. Can you see this thing blinking up here? Measure and showing equals to... Can you see this? Let me expand my screen. How you see measure equals to? So Power BI is asking, what is the name of your measure and type equals to? So what is the name of our measure? Average, right? So let's just type A-V-G. Average, right? Have you typed A-V-G? Now, put equals to. In Excel, when you are writing in function, you put equals to right to start. Okay, so if you want to do Average in Excel, what is the function that you use in Excel to do Average? Average, Average. So there is a function called Average. So can you type Average? I see there is a function called Average. Is there a function called Average? Okay, so we turn the Average and it allows you to mean of all the columns. Right? Of all the columns. Can you see cells there? Of all the cells. Can you see cells? Remember what I said that darks work with what? Column and table. Okay? So I don't want to type this Average finish. I just want to open a bracket. What should I press on my keyboard? Tab, right? So tab helps you to open a bracket when you see a function. Make sure that it works and press tab on your keyboard. Press tab on your keyboard. Are you there? So we are looking for the Average of what? Average of what? Look at this on our table. Fact fact. Average of what are we looking for? Yeah, the value. We are not even separated yet. So we are looking for the Average of the value. Right? So where can I find value in my table? The value is in which table? Fact fact. Fact fact, right? Can you type value? Type value. Type value. Can you type value? So in any type value, can you see fact fact value? What does that mean? What does that mean? Fact fact value. What does that mean? The fact value is what? The table and the value is what? Okay, so the column inside the fact value is called value. So that's why you can see fact fact value. So press tab again to open that. Press tab on that again to open it. Sorry, is that kind of seem too fast? Okay. So what that means is you can find value in two files. Yeah, I don't get it. It seems too fast. Okay, so I have... Yeah, that is you guys voting poverty. Okay, so the one we are using is fact file. Fact file. It's fact file. We are supposed to append the poverty and you do not need it but it's fine. So it isn't value in fact file, right? So press tab. So to fill this formula, what do I do? This measure. I close the bracket, right? Just like the Excel. Please close the bracket. Or even if you don't close it, it will work. Because this guy is smart, right? Enter. Have you entered? Have you entered now? Just finish your formula and just click enter. Have you even entered? Okay, so where is your formula? Can you see it? Where is it? Check your table here. Can you see it? Can you see it? Look for it. My own is owned by countries and continent called OVG. Look for your home. Can you see yours? Can you see yours? Your face. Can you see that you can't see it? I can read your face. Can you see it? Yeah, that's it. Okay, so look up, look up, look up. So look at this AVG. Can you see a sign beside the AVG? What do you see beside the AVG? Calculator. What's the difference between this AVG and this value? Look up. This one has a sign of calculator and this value has a sign of summation. So that's the default. So this calculator that you can see here it means that we are the one that wrote this function, right? So now this value that we have inside here, click on your table. Let's remove it and put our own AVG. Click on your table like this. Click on your table. Have you clicked on your table? So here remove the value here. The value, just remove it here. Remove value here. I just click like this. I just click like this. Just like this. Click on the table like this. Have you removed value? So now tick AVG.   Have you tick AVG? So instead of having summation now I have what? Average, right? I don't want the default. That's why we actually write our own measure called AVG, right? So if I filter this income by a lot of transparency I can see the average. I can see population. I can see the average. I can filter by different stuff, right? But remember what I said? If I click on income can you see income? Let me click on it. Income. Can you see income? So you click on the table like this, right? Then you go to this place. Look at this side. This side. So just to move it from here. So by click on income this is income, right? If I click on life expectancy this is life expectancy, right? If I click on population this is population, right? What am I doing by clicking? I'm filtering the table by what? By the indicator. So I'm filtering this table by all these indicators and ensuring the average for this. But can we do something? Maybe there's a formula we can write instead of filtering this thing myself. And we have a column for population and we have a column for income and we have a column for life expectancy. Instead of filtering this stuff one by one, right? Yeah, so we have all the data. So there's a formal function. I like XLU. It's not called function. There's a measure in Power BI called this guy. See, this guy. Calculate. Can you see? This guy called calculate that our guru just mentioned. Those are the gurus in the house. In, in, I know them. I can see their face. Can you see calculate? So there's a function in Power BI function. There's a measure in Power BI called calculate. This measure allow you to alter a filter contest. In this table all the value is there. Right? For this measure we allow you to filter something. Just like I want to calculate income, for example, I want to bring income. I have just a Power BI. Calculate this my average if my indicator is what. Just normal sentence. Calculate this average. I want to see this average, right? Calculate this average if this indicator name is what. Income. If I want to calculate life expectancy, what would my expression be? Calculate average if the indicator is life expectancy. What if I want to calculate population? Here's not the same thing. But I'm saying that instead of having only average, I want to have population differently so that I can be able to report by population. I don't want to filter. I want all of them separately. Do you get that? So I can do top 10 or maybe top 15 for population. I can do top 15 for income. Top 15 for life expectancy because the value is different now. Right? So what's the expression for population? Can you tell me the expression? Calculate average if indicator name is what. Is population. Okay? So what I said this guy is very powerful. So let's try it. If I want to write a measure, where would I go to? Here's a new measure now. Modely. Financial Modely? Where's Modely now? Okay, this is Modely. Okay. Thank you. I don't have productivity. Where is it? Here, right? Okay. So there's a new measure. It's the same thing. You come here and right click on the table. You still see new measure, right? So click on new measure. Let's write another function. I don't like why I'm calling it function. I'm used to Excel. Sorry. Okay. So have you opened new measure? So what should we call this? Let's start with income. So we should call it average income. AVG AVG AVG income Then equals to umm... Remember our creation for income? What is the expansion for income? Arpler. AVG IF Indicator name is equals to income. Okay. So the execution of the same way you call it now is the same way you do the calculation. So there's a function called calculates, can you type? Calculate? Can you see that function? What is the definition of that function? Evaluate an expression in a context modified by filter. Our expression is our average. I want to evaluate my average and I want to modify it by my lot. What is the filter? By what? Indicator name. Do you get that? I want to modify the expression by the filter called indicator name. So inside that indicator name I want to pick only what in common. I want to pick only in common from indicator name. Can we press tab on calculate? Have you pressed tab? Press tab. Have you pressed tab? So it is asking for expression and filter. What is the expression? Expression is what? Average. The other average. This type is AVG. Can you see your average? Press tab again. Press tab on your average. Have you done that? So now to get to the second argument. You put comma right? Just like I said. Put comma so we can work with the second argument. Have you type comma? So the second one is acting for filter. What is our filter? Remember your expression again. Calculate, average. If into the code name is equals to income. So we don't need to put if. Just type indicator name. Indicator name. Now you can see indicator name. Fact file indicator name. Can you see it? Fact file indicator name. Press tab again. Fact file indicator name. Press tab. Are you guys there? Help your neighbor now. You are not there. You are not saying anything. Are you guys fine? So you have this right? Calculate, average. If fact file indicator name is what? Equal. So can we put equal sign? Is equal. Is equal. Are you there? Is equals to what? Income. That means we can try income inside here. Is equals to income. But remember we are typing a test. Anytime you are typing a test inside a formula. What will you do in Excel? You put up a double code. Whenever you are typing a text inside a formula. To specify to that function that this is a test. You put it in a double code. So open your double code. Open double code. Double code. Have you done that? So now type income. Income. Have you typed income? Type income. Have you done that? Close your double code. Close it. Have you closed your double code? Now close your bracket. Close your bracket. Have you closed your bracket? Have you closed your bracket? Now press enter. Have you pressed enter? Working on it. We are working on it. Have you done working on it? Yes. So let me now drag. Click on this table. See click on your table like this. Click on your table. Now type average income. Put average income inside your table. Put average income inside your table. Have you put average income inside your table? Yeah I know. Put income inside your table. Just take income inside your table. So we have the value and the average and the average income side by side. Click on the table and type average income. Have you done that? Do you have average and average income inside your table now? Yeah. So how do you put in major? How do you put in major? How do you put in major? How do you put in major? After you write it on the top. You put in major from that. How do you put it in the top part? Just close your bracket. Enter. Click on your table. Click outside the formula. Click on your table. Click outside. Click on your table. Now type average income inside your table. Type average income. Have you type average income? So if I change this filter now. Look up. This is population. This average is showing population. What I decided to show you what? Income. Let me take income for example. Can you see they are the same thing? Take income. Take income on your own too. And check your average and income. Are they the same thing? Are they the same thing? So we just use this function called calculate to bring out only what? Income. So they want to do analysis based on only income. I can actually pick. Let's do that together. Pick a Let's just drag this visual here. This is 1153 By 5 minutes we should be done. Okay. So pick this visual. This first one. Pick this first one. Yes. This first one here. Pick this first one. This first visual. This first one. Have you done that? Now take country name and take your average income. Take country name and take your average income. Take country name and take your average income. Yes. In your fact. Country name in your country is on continent. Sorry. Your dimension. Take country name and take your fact. Yes. No country name in your country is on continent. In your country is on continent. Then average income in your value. Okay. Have you done that? You have to question. Thank you. So what is your average income? Average income. Your average income is on fact file. Yes. Is in countries and continent.  Then you can still move it. It doesn't matter. They can be anywhere. So if you want to move it, just click on it. If you click on the function like this. Look up. Average income. Average income in your chart. Just click on the function like this. Then you go to modeling tab. Modeling tab. But click on it first. Have you click on it? If you go to modeling tab, look up first. Can you see this on table? Up here. On table. So I have on table countries and continent. So you just change it to fact. Then it will move. Then it will move. But if you don't want to move it, it's fine. Do you want to move the function? Okay. So I click on this one. My home is under countries and continent before. So I click on the function. I go to modeling tab. Yes. You need to select the function. Not the table. Select the measure. See, I just selected AVG. See, I click on AVG here. I don't tick. Don't tick. Just click on it. AVG. Just click on AVG. Are you there? Then I will go to modeling tab. You will see on table here. On table. Look up. Look up. Look up. Look up. Look at mine first. Can you see it now? So that's on table. Okay. So have you done that? Okay. So there's not really time. This is 11.56. You can actually try expectancy on your home using the same formula. Calculate average if indicator name is equal to live expectancy. Calculate average if indicator name is equal to what? Population. So you can actually separate all the values like that on your home by checking the average that we just did. And for this top five that we agreed on congratulations to us. We've drawn average. That's fine. But we have some as default. We have some as default. But we can still write it on our own. That's fine. So we only have two out. Hand and distant can't.  This is a person. Only one person. So you can always go there if you find anything difficult type it there. Your colleagues are here. That's why we are group. There are plenty of gurus in this room. This is one guru one guru one guru there is guru here too. So they can always answer you when you type something on that group. So guys we can actually continue this by the next time next month maybe you can actually try and create a dashboard on your home. Let's see your dashboard. So maybe by next month we see we have the hot dashboard. Right? Or maybe we should just post it on you in the entire group. Well let's see. Do the dashboard with this with all your separate values. So thank you very much. It's already 12 o'clock. Thank you very much. Thank you, thank you, thank you. So just like we used to do pictures I'm sure we know that we are taking pictures just like we used to do. And again this program you know that it's not a connection of various and deep one right? So you can also sponsor the program too if you want to. Hey guys, guys, guys you can also sponsor the program too. People used to donate for our snacks and all that. If you want to donate you can actually do that. Just go to the user group because it's allowed. Right? You can also sponsor us by making too. So it means next month but let's take a picture first. Let's take pictures first. Our time is up. 10 years we are counting this thing for us. By 12 o'clock they will chase us out. So let's take our pictures. Any program you want to sponsor? Yeah. Yes, yes, yes. Because you will be using this next month. Please save it. Please save it. Please save it. Please save it. Please save it. I think here we got that. So let's come here. Let's gather here. Okay. Let's sit down here. You can join those sitting down. Okay. So let's join them here. Let's sit down here too. Let's sit down here too. Oh. Please stand here. Please stand down here too. Come sit down here. Come sit down here too. No, right? You can join them here too. You can join them here. Peace!