Excel Power Query for Data Analysis 1

for this particular project a company wants to analyze their data and recommend which staff to suck or who to appoint as regional manager they submitted this data set of their sales in different countries over a period of two years the objectives for this project are who should be fired and who should be appointed as the overall sales manager which region country should they set up a second branch and why if the company wants to increase production which products would you recommend and why which month has the highest sale what is the total sales in 2021 and 2022 total number of white chalk boxes sold in 2022 who sold the highest number of Monaco onichoko in Australia in 2021 use Excel to get and prepare this data for modeling okay so this is the data set we got it from chandu.org you can follow Chando on YouTube and other channels it's very good okay so this uh the names of the salesperson the geography the different regions where they operate the products the dates the amounts and the boxes this data is on the platform will also add this link so you can download it via Google Drive click on file go to download into Microsoft Excel so we have the file in our downloads Maxell is open I want to plug in the file go to your data tab and go to get start from file from workbook the file is in my download this is the file and I click Imports so this will work if you're using Excel 2016 2019 if you're using Excel 2013 to come in a different format I have the file here table one I'll click on transform data because I want to claim the data this will open up my power query editor we have the salespersons the geography the products the dates the amounts and the boxes so the columns are okay now the next thing we want to confirm the data type you can click on this button to check the data type we're okay with this data type as text we okay with this data type as text as text this data type should be as date but here is showing as numbers so we need to change it to date so change the data type to date replace current good you have the data type this data type is amount supposed to be in currency and we change it to currency so please correct and this data type is the number of boxes it should be whole numbers this is okay there's one thing we need to do we need to check the total number of rows and columns that we're working with for this in this data sets so if I go to transform I can go to statistics and I can count the values we have a total of 3791 cancel this to go back so now that we're here let's start the cleaning process when you have data sets that is like this always use load more to be able to see all okay we have chess and chest but no this is the same person and we have an area that came in so we need to remove that Asterix so what we can do is we can select this right click on it copy cancel the steps click on the I will say replace values so we want to replace values with this I'm going to click on okay check again okay next we check on the geography column now we can see this pieces here and we can use trim the trim function to remove those pieces so I do trim just to make sure that yes we have a safe line here I'll Transform all this to uppercase and let's do if there's any error Australia Canada Indian New Zealand UK USA this is okay then we come to this section and we load more okay we have an arrow here 99 Dash dark so the correct answer should be 99 dark and pure so we want to correct this error so click on column and replace values good I noticed one other one okay we have white chalk and white chalk okay let's loot more okay we have um and this is also replace this okay I'll just trim this and I'll just trim this now let's look at the date column this column is okay I will look at the amounts column the amount column is okay then look at the boxes color okay in the boxes column we have zero so let's see what is in zero number of boxes so we have no box was sold here no amounts on this particular day so we need to remove the data somebody initially asked me why should we remove the data if you don't remove this data it will affect us when we're calculating the total average because it include these two persons these two rows of data this actually means that it was not sale so we don't need to include this in our data sets so how do we remove this simply we are check Zero from the data sets so we have our data this way at this point every table you're working with is normal practice that there should be a primary key there's no primary key on any of these tables so we'll add a primary key so go to your add column tab you will see index column you'll see from zero from one let's add the primary key from one so this is our index key and let's move it to the beginning move to beginning we have this data set cleaned and ready for modeling let's check the total number of rules that we have now counts count values now we have 3789 and one two three four five six seven seven columns we can change the name of the table to sales sales now that we're true you can close and load this will load the table to excel you have a table set and we are ready for modeling I'll see in the next one

Channel: Learn with Etuk

Views: 2,172

Keywords: beginners, beginner, teencoders, skillahead, tutorial, skill, tech, course, youtube, etuk, anietie, data, analytics, excel, sql, python, powerbi

Id: FGWMJuAjEGw

Length: 8min 11sec (491 seconds)

Published: Sun May 07 2023