How to Analyze Data using Power Query in Excel and Power BI

and on the agenda we shall start with introducing ourselves to power query as a tool and we shall understand what power query does how to install this particular tool in case you're running Office 2013 and below and we shall also get familiar with the power query editor and later on we shall see how to use power query to do or to connect and load data into Excel how to do some cleaning stuff connecting to file connecting folders CSV PDF files and also connecting to the internet and see how we can automate all these processes and so much more okay um now let us introduce ourselves to this particular tool called Power query this is known as the get and transform data if you're running 2013 you might install it as an add in I'll be showing you where to go install it as an add in you'll have have the power query ribbon tab added on your ribbon and if you're do if you're running office 2016 and above then you'll have it as get and transform or get data whichever you see but again it's found onto or inside the data tab once you click into your data tab you'll see anything that resembles get and transform all those commands that you see there that is the power query we are talking about okay facts about this particular tool it came into Microsoft Excel way back in 2010 as an external adding uh all the way through to versions 2013 you have to install this as an adding but again the bad news is that Microsoft is no longer supporting this particular tool because these 2013 versions are way way too old so you need to upgrade at least newer versions of Microsoft Excel but if you're running 2016 this was now introduced as an inbu future are we together and it was called get and transform but for myself because I'm running Office 365 I may have more features more functionalities than you is running 2016 2019 and other versions because this particular tool has been growing and is still expanding with new features and updates I believe this is very clear and what could be the main role of power query which shall be understanding in this particular class let's move forward why do we need power query you might be asking yourself this particular question why do we need power query in the first place okay uh there's a concept in data analytics called the ETL I believe everyone has heard about ETL extract transform and load data that's a popular that's something you need to know if you want to dive into the data analytics World okay so what does this concept do the extract part of things okay you extract data or you connect your data analytics tool to different sources and pick data either from databases or from text or CSV files you can also pick uh data from Excel workbooks Standalone files you can connect to PDF I'll be showing you some of these things practically in this particular session we can also connect and pick data directly from the internet of the web we could also connect from pictures there is a lot of areas we can connect and pick data and this particular tool is the tool that also runs inside powerbi so when you actually move over to powerbi Tool you'll get even more options of connecting to various sour process now after you've connected and you've extracted that information now you have to make some Transformations you go through some transformation changing your data and shaping it up before you actually start analyzing it so that stage is called the transform stage okay and inside the transformation stage you're modifying your data so that it suits your data analysis requirements we can do things like cleaning we can do things like combining columns we can do filtering unwanted rows we can remove unwanted columns but also these particular transformation steps that I'm mentioning can also be done inside our normal Excel worksheets but again the beauty with power query is that it automates all these processes for you okay so that's the main reason why you may need to work with power qu because it actually automates everything that you do do you set it up once and the rest or in the future when you want to perform the similar task everything will be automated for you there how we do visual Bas or they how we write VBA codes okay now the final stage is what we call the loading so this loading stage is after you've created uh after you've extracted data from various sources as we shall be seeing you go through some transformation processes adding columns removing and wanted whatever you might be doing you load it either into Excel and inside Excel you also have a number of places where you can actually load this information I'll be showing you we can load as a connection Only We can load directly into an Excel table so that we can start our analysis from there we can actually load to um a data model if that is the choice if that's what you already have inside your tool you can actually load this information into a data model I'll be showing you future classes and we can also load this data into Power query if that was sorry into powerbi if that was the tool that you're using to carry on your analysis okay now let me hope that everyone is starting to understand what this power query tool is all about if you're not yet there please let us know in the comment section I'll be able to go there and check all your questions but again in terms of introducing ourselves to the tool let me hope that everyone is on the same page with us okay uh let me check as if Andrew you're saying something here yeah the screen is clear and audio is okay that's fantastic uh let me check as if you're saying the screen is clear so people are confirming that our screen is coming in through very fine the screen is clear and the voice is fine so in case you have any questions please uh use the comment section I'll be able to go there and pop in your questions and answer them on the go so right now I believe we have introduced ourselves to parer let me check Dennis mubanga you saying kindly zoom in a bit your screen text is too small it depends on which device you're actually watching this particular class if you're in a mobile try to change it to landscape mode if you're in a computer please try to go wide screen you'll be able to see uh what we are presenting all right now let's move forward what is a query we talk about power query what is a query we need to understand what a query is before we actually start start generating or working with this power query inside Microsoft Excel okay this a set of Transformations we have discussed Transformations you can apply to your road data to form new presentations or representation of the Old Rad data that you have loaded either into your data model before you actually start the analysis so all the Transformations that you go through removing unwanted cleaning adding more columns doing the twisting everything that you do at transformation level you load it into or it combines into what we call a query so that query is what you use to load your final uh data that you'll be using for analysis okay let me hope that this is clear everyone and we shall be loading our data into these various places that I'm showing here on the screen either the table as a p table report people chat only as a connection we can also load into the data model as i' earlier mentioned okay so the query consists of a set of states that are automatically recorded for you so that every time you run that particular query all steps are repeated automatically this is exactly how the VBA codes or how VBA programming runs inside Microsoft Excel so if you are not good in writing VBA codes then this particular power query tool is the right choice for you because it's actually easy to work with than writing VBA codes so let me hope that this is introducing ourselves the tool and everyone understands what we are headed for okay let us know in the comment I'll be able to move there and check all your questions okay now we shall we shall get familiar with the power query tool in just a few minutes from now and we shall use different examples to connect to various sources of data to test how this particular tool works and later on we shall leave it up to you to go ahead and explore what power query does or how it can actually help you in your field at your workplace right now okay now we shall go through some examples like combining multiple worksheets inside inside power query how do we do that shall show you practically how do we Marge Excel tables using powery so when I talk about tables I believe everyone understands where we are coming from this is a class we started long time ago we launched on data analysis and these are a series of videos or classes that we keep building on what we actually started remember for those who have been in our classes previously we launched a data analysis training for our guys who are in our community for free and this is how far we have gone now we are looking at powerquery as a tool that we use to analyze our our data okay shall go through some examples how connect to PDF files and pick data from there and also how to connect to the Internet or the web and pick some files that you can work with in your data analysis stastics and also we can also we shall also look at connecting to folders inside your computer or inside a SharePoint location and bring that data into one single place so that you can analyze it very very quickly okay so let me hope that this sets ground for for everyone who is just joining us uh in case you have any questions please let us know in the comment section use that particular comment section so much and if you want to grab the files so that you can follow along all the practice files check the description of this particular video or you can check the pinned comments I've also sent you these particular files in your WhatsApp numbers and if you haven't really joined our community please uh check the link that I've put in the description deson so that you can join our WhatsApp group you'll get notified whenever we going to be going live okay so that's the small presentation I had now let's head over into Microsoft Excel and start working out some of these examples all right uh let me just head over to my folder right here for the files that I've actually sent you so the first example that I'd like to show you is how do we combine multiple worksheets using power query and we shall be using the append options so this is the file if you actually downloaded that folder I shared with you you'll see this particular file right there so this particular workbook has a number of worksheets and maybe before I actually start analyzing this data what I want is to make sure that all these worksheets are combined together so that I can an them in one goal okay so you go through all of them you understand them that's the skill that I always talk about that if before you actually start working out on any data analysis project make sure you understand the raw data okay so this seems to be sales data and you can see the DAT column the items the sales reps that did these transactions the quantities the prices and the commission rates that were were given to each individual product okay and we have in total four worksheets we would like to analyze this data in one go and how do we combine it okay of course you would have used a manual method of you know highlighting and copying and pasting coming over to this worksheet you highlight copy and then you create a fresh worksheet here and that would take a lot of your time assuming you are connecting several work sheets okay now let us see how we can connect all these worksheets together using the power one thing that you need to know is or one thing that you need to do is make sure that your tables or your data is converted into Excel tables because remember we said when we were introducing ourselves to this particular session or to data analysis using Excel we said that all data analytics features love to work with structured references and tables are one of the structured references let me just get rid of these titles here so that I'm consistent within my tables okay you can do the same in case you're actually following along so that all the tables are the same make sure that all the column um the column or the field names are the same if you are to connect or to combine these files okay now one thing that I will do is to click anywhere in my table and I've given these tables clean names names that you can easily remember uh so do not make sure that your tables or do not leave your tables in the default naming of uh table one table two table three and so on and so for make sure that you give them a name that you can easily remember like you can see let me activate my curer highlighter so that you can see where I'm actually pointing now I click anywhere in this table the first one and head over to the data tab inside my data tab I'll move over to the gate and transform group of tools here so I can maybe click in here get data and I have a number of locations that I can connect to I can connect to a file I can connect to a database that could be either a SQL Server database a Microsoft access database any other stuff right there you can also do a zoo right there and we can also connect to an Excel workbook a CSV xtml Jon PDF folders all these options we shall look at some of them but for now because these particular worksheets are in one single workbook we shall not use the get data drop down options we shall come over and locate where there's an option called from table orang range okay so click in the table come over over to this particular option right here called from table stroke range and click in here once depending on the speed of your machine this will launch you into the power query editor straight forward and inside the power query editor what you can do here is you can go through some transformations in case you actually wanted to make changes to this particular data set of which we shall make a few changes the data seems uh looks to be clean but we shall make a few changes maybe adding a column or two before we actually load this one into into whatever we shall choose to load it okay now I can see the power query has detected the data type right here it shows me that this is data and time in case you don't want the time to show this particular column you can actually come over right here and change in the drop down here the types the data types change it to De and there you go you can see it will ask me you want to change the column type if I'm comfortable then I'll say replace the current and what happens it is that it will convert this one into into a de that's the First Transformation we have just done and you can see under the query settings here in this particular pain you'll see all the steps every single step that I take here is recorded in the applied steps box here and this is like writing for you an entire code so that next time you want to do the same on a similar data set all these steps will run automatically so this is where they are recorded okay so the other thing that I need to do here with this data set before I combine all of them is I may not easily know this transaction whether it belongs uh whether it belongs to the easn because remember in our row if I can minimize this in our row data set uh we had four work sheets let me just go back one more time uh just bear with me I'll discard so in our rad data we had four worksheets as you can see so after we combine we may not easily know which transaction belongs to which worksheet so that's why we need to create another column that will tell us that this transaction belong belongs to the west east south and the northern region region okay so let me repeat the process I'll go back to the data tab from table or range the power query editor will launch after it gets launched right there I can go through some Transformations and the other one that we need to do is to add a column so that column um I'll come back to the source step here let me just go back to the source step uh uh right there just confirm what I can do is to get rid of this change type so we maintain our original data set so what I need to do here is to make sure that I create a column that will tell me that this particular data set or transaction belongs to the West data worksheet so I come over to the column option here and then I'll say custom column is this option here and of course this one will launch into a dialogue box right here where I can actually create a custom column let me just call this one region I'll just call this one region and I'll come in here and punch in the region this is supposed to be data for West as a region so I'll open quotes and inside the quotes I'll punch in the region name for this one is West make sure that you're enclosing it into into those um how do we call this uh for those people who have been in our classes the double quotes make sure that your text is enclosed in the double quotes and there is no syntax errors that have been detected here confirm if there is an error here then your column will not be added I'll go ahead and say okay and once I do that you'll see that this data has added or we have added the column here which we have called region to make sure that when we combine all the four work sheets we we can distinguish which transaction belongs to which worksheet so you feel free to carry this particular column to any position maybe you can carry it and bring it at the beginning right there and maybe let's also change this particular date to a date type data type so that we can eliminate the time portion and from here we are now good to go let's load this one I'll come back over to file menu load or close and load it to We Shall actually load this one as a connection why because if we actually load this one back into a table it might not really make sense because our end goal is to combine all the four all the four work sheets so we shall load this one as a connection only so pick this op here which says only create a connection and once you do that you say okay after you've done that you'll see the queries and connections pen opening here and it will show you that you have one query that is called westcore Data so we can do this for all these worksheets let me just do for the Eastern region so I'll do this a little bit faster and then we go over to the last step where we need to combine all right so I'll come over to the add column right here custom column This one belongs to the Eastern so I'll just say region these are transaction from our Eastern region so I open double quotes type in East and close double quotes make sure that there's no syntax error and then say okay let's also carry this one and bring it at the beginning or at the start so that it's column number one and right there we can also change this data type to date so that we eliminate the time portion I believe this is what we did make sure that all the transformation steps you go through for all the four files is consistent are we together now we are good to load this one I'll come back to the file menu close and load to Let's also load this one as a connection only okay we shall also come over to the Thousand data set come back to the data tab from table orang and the powerquery editor will launch let's also add a custom column for this particular sou data set and we shall also call it region what we are doing is we are trying to do some trans formations to our original data and then later on combine and analyze it in one go so I'll say this is for South make sure you close the double Quotes no syntax errors have been detected right there I say okay let me also carry this one and bring it at the beginning but again it's an issue of preference you can leave it wherever you decide but of course because I want to be con assistent I've done it for the first worksheet I have to do it for the second and the third and also the fourth but you can leave it in any position of your choice for this particular column we shall change the the data type to a date and eliminate the time portion also this is a preference issue you can also leave it the way it is if that's what you like I will close and load to I'll also create this one or load it as connect connection only of course it takes some time depending on the speed of your machine so I'll create this one as a connection only and let's also do it for the northern data set from table orang and we shall add a column right there I believe everyone is following what we are doing our aim is to combine all the four work sheets and this is one of the methods and I'll be showing you other methods that you might use okay can also use a formula or a power query function uh this is for the northern region I say North and make sure that there's no syntax error and say okay so having done that we have our four queries right here so all our four queries have been loaded let me load the last one here close and load to we also load this one only as a connection and say okay now we have all the four worksheets loaded in here as as queries we need to combine all these four worksheets into one single gigantic file that we can now do data analysis with okay we have a number of options to do the concatenation or the you know the marging and the number one that comes in very easily is Right Mouse clicking on any of the queries remember now this is a query but again it holds all the westore data row data now together this is also another query this is also another query and we have four queries here okay right Mouse click on one of the queries and you choose one of these options here either Mar or append let us do the append because we want to Stack all these four tables together so that we can form one block of data which we can use in our analysis so I'll choose the append and this will open or launch this particular dialog box here called the append dialog box so here we can concatenate rows from two tables into a single table of course if you had only two tables you could leave this particular check mark right here but because we have more than do I go with this option here three or more tables West is a table that has we us it uh to come over to this upend option that's why you can see it's already added right here but the rest I can actually click on all of them or click on one by one and I say add when I click on a table or a query and I say add it's added here into these particular tables options that will be appended or I can actually click all of them in one go you just tap on each individual table holding the control key on your keyboard and then you add them in one go now you have westcore data eastcore data southcore data northcore data all these queries will be now combined into one single gigantic table I say okay and the powerquery editor will launch again and this time when it launches it will show us another query that has been created and this is now called the append one again feel free to move in and give it a proper name for me I'll call this one combined combined data let me just call it combined data and you can see that because we have used the append option it will give you the append one append two append three and so on and so forth so you change the name here have to make sure that it's somehow structured and I believe we are good to go so for now this is our last step we can actually close and load close and load into one single table now instead of loading this uh last particular query we shall now load it into this particular option here so that we can start our our analysis all right let me do that and say Okay so what happens is that we shall get a new worksheet and you can see depending on how large the data set is this will be our final this will be our final gigantic table combining all these four worksheets into one and then we can quickly start analyzing this particular data set by just you know inserting like pivot tables I believe we have touched or we have you know we have had classes to do with pivot tables everyone knows how to to do or summarize data with pivot let me show you just one example here I create it on a new work sheet and I can build a pivot table report from all the three worksheets maybe let's look at each individual sales rep I drop them into the columns here and I'll also check or generate for either quantities or commission let me just go with quantities drop it into values and there you go you can start now to build your analysis and then later on you can visualize your reports okay so that's one example of combining multiple worksheets using power query and we have used the append option okay let me hope that this is very clear I'll go through some of the comments here in case we've gotten new people who joined if you have any question please use the chat or use the comment section I'll go there and check all your questions okay bash Ahmed hi to all if you're saying hi to all respectable viewers thank you bash ahed right there you're joining from qu you're joining from Kiss Kiss may I didn't pick uh this place right there this is kiss Mayo s o I don't know whether it's which country is this please Casey Casey you're joining from Kiss Mayo which country is it so so I don't really understand that abbreviation all right let me check another comment here the blessed beri as if you have not dragged the region column from the north data to the end I didn't remember what I did drag right there this was just a demonstration of how we can build a pivot table report from our combined from our com combined large data set so I don't know what you're asking but you can clarify um in the comment section okay now let us move over to our next example I'll just close over this particular file and I may not save the changes and let's move over to another example how do we merge Excel tables using power query you know there's a difference between appending and marging when you do appending your liter combining all those tables and stacking them together but when it comes to marging it's like you're creating new unique columns inside your existing data set so let us open this file right here and I'll show you this particular example in just a moment so what I mean with marging is for example you have this sales data here have your Deb column your items that we sold the sales rep the quantities the price and the commission rates and you also have a unique table or a unique list of items with their product IDs and you can see within our fact table the IDS are not captured now together and we also have the sales rep showing the regions that they come from right together or the regions where they are selling products from now this in data analysis or in data analytics is called a facts table and these two tables that you're seeing here they are called dimensional tables when we go into classes of data modeling we shall see how do we create relationships still using the power pivot or par to create relationship between these two between these three tables the dimensional tables and the fact table so that's literally what we want to do or that's what it means means when we say marging tables the difference between marging and appending is that marging we are creating new columns inside this particular row data so that we can do the analysis for example right now it is very hard to tell which product or which region is doing the best in terms of the in terms of the quantity sold it's very hard to tell from this table not until you create another column here looking at the regions that we have in this table and append them onto each individual record you can use formulas to do that you can do vook up here and connect right there again that will be manual because our interest here is to automate this things do it once next time you want to do the same it is automatic so creating a column here using the vook up function wouldn't be a wise idea and fix the problem but again power query that's the area where it actually shines how right together so we shall be doing that I'll demonstrate this using the power query to create new columns inside this row data but again we shall not lose the structure of this data because we're going to be working from from the power query itself so let me hope that everyone understands and distinguishes between the append option and the merging option all right now let me click in any of these tables and come back to the data Tab and come back here table arange just use this option again table arrange and load all these tables inside your power query and we shall load these ones as connections one more time so I'll come back here there's no Transformations that I may you know work out this data looks a little bit clean according to me but again I can change this particular data or column to specifically show as a date data type and I'm good to go I can actually load this one into the connection I'll come back to the file menu close and load to I'll load this one as a connection only so when you load your data as a connection only they do not actually occupy space in your worksheet you know sometimes your worksheet will start to be slow you know performance wise because you're loading so many formulas you're loading so so much data power query is here to save you from performance issues because when you load as a connection only if you do not load into the table it will actually be saved into the memory to not be available to take your space but Power query can recognize that data in in its memory okay let's do that for this table and we shall also do it for these dimensional tables so I come and click in the table from table orang is the option you use and for this one there's no transformation that we need I believe everything is clear there are only two columns I'll just close and load and load this one as a connection as well all right let's also load this particular table into our power query and everything looks fine there's no any cleaning that we need to do here I'll just come over to file menu close and load it to and I will load this one as a connection as well now after you've done that now it's time to to Marge these tables okay the first example that we saw we actually did the append which combined several worksheets together but this one let us try the merging option here you can right Mouse click on any of the queries and come over and say merge after you do that it will launch into this Marge dialogue box and in here there number of steps that you need to take okay in most cases you have to select the facts table so that it's on top because that's where we actually trying to create new columns from our dimensional tables so make sure that it's the one that is sitting on top okay it's called sales data and when it comes to this particular option or the second table that you'd like to Marge with I click in the drop down we can start with any we can either do sales reps or the product list let us go with the product list first so this table has only two columns the only thing we need to take tell this Marge option here or Marge command is which column out of these two tables from the facts table and the dimensions table are related that's the column that you need to give which one do you think is related between the products list and the sales data of course it is the item so this is like we are doing a normal V look but this time it's inside power query so what you do is you select this column and you also come into your second table and select this particular column so we have created a connection between these two tables using this particular column called item because item is existing in the first table it also exists in our dimensional table and this is how we can establish the connection between these two tables and the join kind here we have a number of options we can do left outer right outer full outer we shall go into details to understand these options but in most cases you'll use the left outer which is all from first matching from the second so first we are referring to this table all from here that are matching from this particular s table okay so make sure that this is checked or the the selection or the join kind is selected and and make sure that the selection matches or this part this particular um option here does not have any errors if you have an error just know there's something wrong okay it will give you a preview here the selection you've just made matches 324 of 324 RS from the first table I'm comfortable with this marging right here I can say okay and what this one does it will relaunch into the power query editor and give me the Marg verion now but of course we haven't really uh finished the margin because what happens is it will give you this particular column the new table that you're trying to to connect called the product list and you can see it has some arrows facing in different directions one facing to the left and the other facing to the right so this is what we shall use to to actually combine or pick The Columns that we need to be maintained in this new table and the ones that we don't need when I actually click in any of this particular record if I just point to the table you'll see down here it actually Maps looking at [Music] the item column is connecting this particular or doing a v lookup for the product and returned the product ID that's exactly what we are doing okay now to combine I'll click in these double sided arrows click in here once this will expand your selection and it asks you which particular field or column do you want to bring in here our interest is to bring in the product ID because we already have the product in that particular pack stable so what I'll do is to remove the check mark here and select the column we want that's the product ID and make sure that this use original column name as a prefix you turn this one off or you remove the check mark because what happens it will create a weird name for you a weird column name and you may not like it so make sure it is it is removed right there and then you say okay immediately you do that this particular column will change and it will show you the product ID so what we have done we have connected the facts table with our products table and we have added in the product ID column which was not initially there now at this point when we load this particular rad data we can now generate a report where we can you know check which product ID is doing the best okay now this particular Marge one of course that is the default naming you can change it I'll just call this one combined two tables now we have just combined the F table with the products table and I will hit enter key and I believe this is good to go we can actually come here close and load it too because we want to bring in the sales reps region column we shall load it as a connection as well because we can't load it as a final product that we shall use to analyze because we haven't brought in the sales or this particular table right here okay let me just load it as a connection and I'll have this particular query combined two tables it's only a connection now what I do I right Mouse click on this particular table because now I want to combine it with the region to bring in the region column right Mouse click and then I say Marge I'm doing it one more time so that I can get all the three Marg together now you can see in our fact table this query which have called combined two tables we have the product ID column added now this looks fine I'll come over here and merge it with the sales rep table are we together now I need to locate in this particular fact table and this particular table that I want to merge which column which column is actually telling with what we have in the fact table which column is it the sales person is it the region our interest is bringing the region so definitely the sales person here is is what we shall look up for here so select this particular column as well as this one to create or establish a connection and from there you can actually say okay immediately you do that of course the power query editor will open depending on how powerful your machine is mine is doing it a little bit slow because it's not that powerful and you'll get this particular default naming here this square is now called Marge one but again we shall give it our final name let's head over to this sales repb page that has sorry the sales repb column that has been added when I click on one of them just one record you'll see the preview here it tells me that I've found God FR mapped them into this particular Ro data and this God FR is actually coming from the western region I guessing it right yes we are doing a simple V lookup but inside inside the power query now I just click these two double-sided arrows to expand and make sure that I pick the columns that I want to be maintained or added into my final table and eliminate the other the other columns because my interest is bring in the region so I remove the check mark and leave it on the region column only make sure that the use original column name as prefix is turned off and then you say okay immediately you do that you'll see the region column has now been added into our rad data now I can actually name this particular Marge one query into the final I I'll call this one final maybe just call it final data set but anyway find Anya that really Swit what you're doing I'll just call it final data set and hit enter and now we are good to go we are ready to load this back into a table and then we can start analyzing let us do that file close and load to for this one I'll actually load it now that it's the final t that we want to work with I load it into a table or I can just do pivot table report I can load it into the data model let us do a table and I say okay this will now create a final data set table and this particular work sheet name is actually coming from from the query name that we had here we call this query final data set and that's what exactly when you load it as a final product or clean data that you need to use that's how it will also be named okay so this is data set that looks clean according to me we can now start summarizing this with pivot tables and right there you can now go ahead and even visualize your reports okay so I'll try one report here I'll maybe drag the region into the rows and I want to know what are what are the quantities that we sold in each individual region right there you can achieve this by using the power query faster but again if we maintained our tables like they are here it would have been very hard for us to generate a simple report like this that tells us the performance of each region because this particular data is separated we have three different tables okay so that's one example example of using or merging your tables into one and proceed with your analysis let me hope that it is very clear for most of us and I'll go back to the chat and confirm XV um I think this is I don't get this particular name is XV you're saying this is very nice thank you for the comment and then Charles Amon you're saying when doing maging do you hold any key or you just select column from both tables yes when you're doing marging you just select a column but if you're actually selecting multiple columns you might be tempted to to hold the shift sorry the control key and you know match those columns since we are using only one column you just click on the first column inside your fact table and you also tap on the second column inside your dimensional table so Char we do not hold any key on the keyboard now together thank you for the question though and let's move forward now I'll actually close this workbook I believe everyone got these files if you didn't pick these files go ahead and pick them so that we can move together the other third example I want to show you is how to connect the web or the internet and pick data from there and analyze it as well you're going to be picking an example from the internet so let me open this particular workbook and we start to this connection we said power query is a tool we use to connect to various sources bring the data into one place combine or do Transformations and analyze it at the end of the day so what does that one that one mean it means that you can connect to the internet you have literally and you can later come up with a final a final product that particular data you're working from might be coming from seven or 10 sources but when they come into power they are combined Consolidated and then you can do your analysis a little bit faster okay let us do this example what I'll do is maybe the example I can use I'll go to the internet and search for anything maybe let me search for uh let me actually open a browser right here and search for anything that may pop up in my head maybe I'll search for the Premier League let me search for premier league is it called the English Premier League winners maybe let me do that I want to look at the winners for this particular competition from when it started and you know I want to do some analysis on that particular data so I want to pick it from any of these web pages let me go with this list here list of English football champions okay so this is a Wikipedia file it's a Wikipedia page so it has a lot of information our interest from this page is to pick the list of teams that have won the Premier League for example this table right here we are interested in picking this table do some transformation on it whatever you may think and then start doing our analysis okay so this particular action that we're going to be taking is using the power query to connect to the internet of the web pick data analyze it and that's all so whenever you're doing an analysis project you can pick data from wherever whichever information you think of this might contribute into my report I'm generating you go and pick it from wherever it is whether it's on a picture whether it's on a PDF file whether it's on a website somewhere powerquery can connect all those sources okay so what I do I'll copy this link I'll copy this URL contrl C and I'll minimize come back to my workbook so what I'll do is to head over to the data Tab and I have options here actually the from web is an option that I have here in case you don't access it from here from these icons that you're seeing here you can come to the get data dialog box and navigate over to other sources and right here we have from the web click on this option and immediately you do that immediately you do that it will launch into another simple small dialog box where you need to actually declare the URL okay so the URL that I've coped from the internet I'll just paste it here contrl V this is the URL and I'll just say okay I'll show you in other classes how to use the advanced tab but let us go basic I'll say okay and what this one does it will connect to that particular website and depending on how powerful your machine is try to go through the data look at what are some of the objects it can pick from from that data and once it gets done it will open into the power query editor and then it will be our Ru to start picking out information that we need Okay so this Navigator window will launch wait for it actually give you a preview of the tables that it has found out and there you go you can see from that particular entire URL we picked it has given us a number of tables HTML tables suggested tables right there as folders and also text so there are two text files right there if you want to work with those you can actually pick one so if I want a preview I can click on table one and look at the preview right here if this is the table I'm interested in picking from the other page remember this is our page so that stable one could be this particular fast table which is uh football league 1888 toight 1892 I'm not sure but you can explore the data and we also have another table here which is football league 1888 to 1892 I believe that's a smaller table that I've just shown you and also first Division and so on so my interest is to pick this particular table here the Premier League table 1992 to present all together I just click on it and it will show me all the teams that have have won this particular Trophy and the columns are not that many we have the season we have the champion and the number of titles in one column so we shall do some transformation right there also have the runners up and we also have the number of times they have done that or the number of trophies also have the third place teams re and also the winning manager those are few columns that we need to go through and transform as an example of connecting data from the web so what I do at this particular Navigator window is I will do transform I don't go ahead and load this data uh uh immediately because I would have loaded if I was comfortable with the data I need to go through some transformation that's why I not load it otherwise I would have clicked here and say load or load to immediately I will not do that I need to transform this data remember we said power query has three steps extract transform and load those are the three things that the power does let us go to this second stage here of course we have done the connection now let us transform I click the transform option and there number of things you can go through as you clean up your data to make it ready for analysis let us see what we can achieve here very first so the par editor will open and it will show us from this table each season in one column the team the Runners up the third place people and the winning manager what we need to do here is let us look at each individual column and think of what we can do here in terms of clean up I believe members you can suggest what do we need to do here in terms of cleaning up this data to make it ready for analysis again this is dependent on what you want to achieve let me know in the comment looking at this particular Road data what are some of the Transformations you think we can make M let us do that together as I also find what should be done here and then we do it together okay now going through the data set I can see I have in the first record here I have what would have been used as column headers they are now showing as as a record we need to promote these ones as column headers instead of showing column one column two column 3 and so on and so forth we need to say season Champions number of titles Runners up third place and the winning manager up here so what I can do is if I want to promote any column always the first one because it has the headers or the field names I can click right here and the number of options will pop up here this is you know like the shortcut the easier method but again you can do it from these particular terms that we have here for the powerquery editor we have the file the home the transform the add column and the view so make sure that you explore what are some of the options in this particular in this particular ribbon of course the power ribbon is different from the Exel ribbon yes you need to get familiar and distinguish between these ribbons okay now I do as a shortcut is to click in this particular icon and there's an option here that say that says use the first row as header pick that one right there and that step has been recorded instead of column one column two column 3 now we have the headers that we interested in and all the steps are getting recorded for Russia each individual step is getting recorded That's The Power of power query because these steps can be replayed at any time now right together now this particular column looks good according to me let us look at this particular champion and the number of titles I can see that Manchester United has actually won eight of these trophies Manchester United B and so on and so forth so they said in this particular column we need to separate and maybe create another column that will specifically look at the number of titles because they have been combined here so how do we do that let me check in the comments as if chse you're saying uh Charles is saying when doing marging this is a question I think I read uh let me check another one here as if you're say XV you're saying remove numbers I'm not picking this particular suggestion right there anyway we can remove the numbers or we can actually create a separate column because we may want to know how many trophies that each particular team has won so how do we do that we need to separate this column we can do it from the transform tab here or if we don't do that if we don't do that we can right Mouse click on the column and then a number of options will pop up here our interest is to split this column we want to split this column into multiple multiple columns we need to choose that delimer for those who have used the text columns feature inside Excel this is similarly what we are we are doing here inside the power query so select the or enter the delimeter I'll just use is we need to identify the delimeter is it a space is it the Open brackets what can we use here let me know in the comment please can we use a space can we use um the opening brackets to me I would think we use the Open brackets yes if we want to separate these numbers so we can say Open brackets and each occurrence of the delimiter is the default right here so I believe these options are fine for me and I can just go ahead and say okay and see what happens okay let me just say okay and there you go you can see now the team has been left alone right here and the number of titles column is also created now I can go ahead and rename this to the team I can just call it team team name whatever you want to use it's all up to you and the number of titles this is now the column number of titles I'm just doing some Transformations here these are some of the cleaning processes that we'll go through okay but again I can see on this number this is supposed to be a number but I'm seeing a closing bracket we need to get rid of that closing bracket and to do that will be quite easy easy let me check as if XV you're saying separate column that's fantastic and you're also saying here Open brackets I believe you're on the right track you're giving us the suggestions that we need and let me hope that it's what I'm doing on the screen you let me know so let's get rid of this closing bracket how do we do that we right Mouse click on the column and this time you're going to be replacing values let us do replace values and what we want to do here is to replace the closing bracket so I will find what I find the closing bracket you come and punch it here and we won't to replace it what with nothing so I don't put anything here I just simply say okay and there you go can see now we have numbers but again these numbers are showing as text so we need to move forward and change the number or the data type right there so I just click in here and the number or the data types will show up here I can now choose to make it a whole number which I'll do and that is a transformation step we have created a column that was not initially there and whatever we are doing right now we are transforming our data before we load it for analysis let's move over to the runners up we do the same splitting right Mouse click split column by the limit and I'm going to be using the Open brackets at each occurrence of the delimeter these are the options you need to check then I will say okay this will create another column right there and this one will now be the runers up team runner up team and I don't know whether this is the runner up number of times but I'll say number of times let me get just do that number of times they have you know found themselves in the run as a position and for this column I will replace values remember this data came from the internet now it is inside Excel and we are trying to you know clean it up and make it ready for analysis now I replace this closing bracket with nothing I say okay it comes in of course as text supposed to be a number how do we change if I click in this particular column and check the data type it is actually text right up here so we need to change it click here and make it a whole number are we there and you can see by default it will align to the right okay we come over to the third place or the third place I right Mouse click split this column by a delimeter which delimer have we identified is the opening brackets so I use the opening brackets at each occurrence of the delimeter I say okay let me hope that members you're following and this one here we shall call it this is the third place team third place team and change this column headers to any of your choice and the number of times I'm just assuming I'm not sure that these numbers that are indicated here the number of times or are titles whatever it be but let us move forward I'm just showing you how you can deal with these clean up processes okay we shall be looking up for the closing brackets replace it with nothing and say okay all right let us change this one to a whole number and we together now the winning manager I believe everything seems to be fine the data set is fine uh sorry the data type is okay for the winning manager maybe I can change this particular character to Capital winning manager so that I can have consistent column headers so I believe our data is forming into a cleaner one if I'm comfortable right now I can actually rename this power query or sorry I can name this query come here and give it a clean name I'll call this one Premier League Champions Premier League champions I've connected this data from the internet okay now I'm good to go I can come over to the file menu close and load to I'll just close this one and load it directly into a table right there I can do existing worksheet and load it right here and say Okay so this will will load our clean table that we can use to do analysis you get the idea now what happens is that when this particular table or when this particular table on the website updates for example this table let me check what is the the latest uh who is the latest person who won this particular title I believe it's let me check I need to scroll down and check so if this particular table in other words is updated at whatever time what need to do in my Excel file here is to right Mouse click and refresh once I refresh this report and there has been a change on the internet it will feed through I will not need to go through any of the steps that we have done clean up I'll just do a refresh here and all the data will update That's The Power of using power query I believe you've learned something so far let me know in the comment if you haven't learned something I believe you have any un learned that's what that's what I always say here in these classes if you don't learn at least unlearn something all right all right now that's an example of connecting to the web let me know in the comment section what you actually think you can see I believe XV is um but XV you need to tell me your real names X kindly tell us your real names uh feel free to tell me your real name so that I can address you properly and where joining us from by the way is very important okay but thank you for being active and making this particular uh session interactive okay you're saying that you're actually learning a lot from this particular session and let me know where you're joining us from that will really make me comfortable okay again we should be moving forward to the next example let me just close this workbook and I'll say don't save and I also close this web page here and we go back to our folder right here how do we connect to a PDF this time hey let us check can power query connect and pick data from a PDF document whatever it might be whether the document is on the internet whether the document is locally on your machine let us do that I'll come here and open this example number four and and once this particular workbook opens I can now try to connect any PDF file let's do that I come over to the data T and I say get data that's what I have for Office 365 but for you you might have a different naming of these things here I have it as get data and I'll get it from file navigate over to PDF right here so I can pick data from a PDF document let's do that I click in there depending on how powerful your machine is it will open the Navigator so that you can locate where the file is so mine is actually inside this particular folder and I've called this one list of registered Engineers with valid practicing licenses 2019 this is the PDF file I want to connect to so I just click on it and say import I'm trying to import data into Power query from a PDF document okay depending on how powerful your machine is the power query tool will scan through your document try to establish a connection to that particular file wherever it is located and once it figures uh once it figures out a number of objects that you might be interested in it will launch into another window okay this Navigator window right there and it will show you a number of objects now it's up to you how you want to use these objects but let us wait as it's still connecting our PDF file this process takes a lot of time uh depending on the speed of your machine okay finally has shown us all the tables and the pages that that particular PDF file has okay showing us the tables you click on the table and you look at the data that's how it looks like also requires some cleanup process before it ready for analysis for whatever reason you might be connecting to that file you may need to go through a cleanup process okay so these are the pages sorry these are the tables that have been identified in that PDF document and again it shows you the pages themselves you can look at page per page when I click on the page you say this table is empty so these are just Pages the workbook or the file the PDF file has 39 Pages you can see from page one all the way through to 39 pages and for the tables that have been seen you can see table one table 001 on page 21 table 002 on page 24 and 20 sorry 22 and 24 so that table stretches from page 22 to page 24 it's entirely that table table 003 is on page 25 and so on and so forth now how do we combine all these pages together and form one gantic five that we can use to do our analysis are we together we need to do that very fast of course the process looks a little bit similar to what we have done uh previously and the only difference here is that we need to bring all these tables in one go we need to clean them up in one in one go so we shall go through an extra step right there but in case you're enjoying the session please uh type in the comment right now I'd love to hear your feedback in case this is your first time joining us on this particular session I'd like to hear your feedback in the comment and also let me know if you're learning something and the other request that I want to make is please like this particular live share it with a colleague who might be interested in learning more about Excel and if you want to get notified every time we go live we have free classes every single Friday you only need to go into the description of this video and check the link that I've provided join our WhatsApp Community where you can actually start enjoying Such live free Excel classes but let me know in the comment section whether you're enjoying the class are you learning a thing or two and let me know if this is your first time here I love to go there and check so okay Alex you're confirming that you're joining us from Juba that's fantastic uh you're called Alex thank you very much joining us from juba that is sou Sudan right there Paul will this be recorded and shared yes the recording will be avail immediately the session ends the link that you use to join this class is the same link you can click on to watch the replay or the recording of this class okay that's why right thereand you're actually saying hope this is recorded very interesting of course it's getting recorded it will be available immediately the session ends and you can use the same link to go in and PR practice I've shared all the files with you just go ahead and watch the replay as many times as you can as you practice along in case you can't pick some of the things on the live session Emmanuel evil Emmanuel evil today I personally learned a lot thank you very much and this is Don I'm learning about the power query but my windows are different from yours yes if the operating system is different maybe you're running your Excel on a mark IOS operating system or you're doing Linux of course you'll get different interfaces and depending on the version of office that you're running you'll also get different interfaces so again the logic will not change or the actions that we are doing here will not change but what might slightly change is the interface so I don't think that uh you face any challenges and you let us know which Windows sorry which operating system you're running is it Windows is it iOS is it Linux is it wuntu which one are you um are you using okay let me check some other person here pH Mo filipas beautiful work I've been suffering from this and how you made it simple absolutely absolutely if this is your first time joining Mo filipas please head over to my head over to the description of this video join the WhatsApp community so that we shall be inboxing you all these classes as they go live okay let me check another person here this is Ron Sky Ron Sky streaming live from Dubai bro you're doing an amazing work that's wonderful that's wonderful okay so keep the comments coming through and like this particular live keep liking keep sharing with colleagues who might be late and join us for the next few minutes that we are here so that they can learn something okay toah Roland I believe you're coming in again hope this will be found in the forthcoming classes absolutely actually in our forthcoming classes which will be an a full course we shall go a little bit deeper from what you're seeing here with power we shall show you some more stuff par is that wide but the beauty that when you master power Query in Excel you have mastered power query inside powerbi this is the engine that still runs inside the powerbi platform of which everyone of us who are here and you might be interested in data analytics powerbi is a tool that must be on your menu to master so mastering power query will really support you or will really help you because you'll have done you have actually learned a to tool that lives in two in two separate or in two different applications so when you move over to powerbi it will be a work over for you okay now let's move forward let's move forward and combine all these pages that we have just seen inside our PDF our PDF document so what I'll do I'll make sure that I check this option here which says select multiple items this is the first time we are using this option inside our Navigator and I will pick all the tables that I'm interested in because I want to load that entire that entire gigantic table inside the list of Engineers that are certified with practicing licenses okay so I'll click on each individual table just keep placing a check mark like this now together just do that very fast and make sure that all your tables are selected now we have 39 pages 39 tables that we need to transform I'll come over to the transform Tab and click in here once remember we have connected to PDF file this is amazing okay depending on the speed of your machine it will actually go through all the tables and now we can start cleaning up all these tables and later on we can combine them into one file which we can analyze later okay so let do that very first I come over to the first page and I may call this one maybe the name of the query I may change it and say page one okay and hit enter key on the keyboard now let us transform or change what we have on page one can see we have the title here for this particular document list of Engineers for 2019 practicing licenses we may not need that particular title right there so what I do to get rid of the First Column so that we can promote the second row as our as our column headers I'll just click in this option and say remove top rows there's an option here called remove top rows once the dialog box launches you declare how many rows from the top you want to be trimmed okay so our interest is to trim off only one row I'll punch in a one and say okay and there you go you can see we still maintain column one column two and so on and so forth now what we need to do is to promote this particular first record or use the first row as headers and there you go can see now we have number registration number maybe we can change this title and call it serial number you can change these particular field names and for registration number you can change it as well I think my screen went off I don't know whether guys you can see the screen uh can someone confirm that I'm back on as if I went off but confirm if I'm back on as if I got an internet disconnection here can someone confirm in the comment that I'm back and everything seems fine so that we can move forward and clean this particular file here can someone confirm in the comment as if I got a technical glitch here H Zaki Yu I think you're confirming that we are fine yeah you're you're confirming that everything is fine H if that is the case then we can move forward okay now we are still cleaning up this particular tle that we are picking from our PDF file and the other thing that I will do is you can see this column here has some titles that we that we have right here for some of the names just scroll through and confirm so we have the serial number we have the registration number we have this column here that is almost empty I can see null again I have engineer SE right therea so I may not want to lose this type of information what I do is I will actually merge these two tables so that sorry I'll Marge these two columns so that I don't lose any information that that might be in this particular in this particular column so let's do that I believe that will be an idea but again if you delete this particular column it means you'll have lost this guy whose record is actually spilling to this column I this is a 10 activity that you have to go through again you do it once that's the beauty so how do we combine these columns I click on the first one and the second using the control key on my key keyboard and I right Mouse click and say Marge columns we need to Marge them Marge columns and once this one opens or the dialog box launches here I need a separator of course the separator will be a space and the for the marged column I'll call this one full names maybe I'll just say full names is the new column that I want to get and these two will be combined and of course it is a text data type which is fine I leave it the way it is and there you go now for the field of practice I believe this column is doing fine and for the license number this column seems to be doing fine as well this is supposed to be text and for this last column here I believe we need to R delete this column so what I'll do is to right mous click and remove so that column will be gone what I can do for page one is to close I come back to file menu close and load what do you expect we should do here where do we load this one we load it as a connection so we say create only a connection and there you go you'll get a connection right there for all the pages how together and from here what we can do is to Marge these Pages or append these pages okay so let us do that I right Mouse click and say Marge or append let us do append let do append because we want to Stack all these tables or pages and form one single gigantic table so I say three or more and I put all of them make sure that all of them are selected one go and then I say add this is an action that we have or this is an example that we started with and I'll say Okay so this will still launch into the power query editor and we shall have the append one query now inside this particular query we can rename this one maybe I'll call it list of Engineers it's supposed to be list of registered Engineers list of registered engineers and hit enter key on the keyboard now I can come back file close and load to and I load this one now directly into a table and then start doing my analysis I will do existing worksheet and load it starting from this cell address a A4 H maybe let me just put it on A1 right there and say okay once I do that we shall get a final table and this particular final table is what we can use to do our analysis but again I'll open the PDF file and show you where we have come from to achieve this particular data that you're seeing here okay let me just wait a little bit to have my query or extracting the data and there you go now you have your data showing up here we have some other columns that we forgot to remove they've come along um I think we haven't removed some of the columns but let me hope that members you get the idea of how you can actually extract data from PDF files okay let me just check or open the file so that you can have a look at it okay so this is the file that we are looking at and this is Engineers Registration Board and this was a list that was released to the public 2019 and this is the table that we've been extracting using the power query so we ended up with this particular beautiful data set that we can use in our analysis but everything came from this particular PDF file so if you have a PDF file and there's information you want to pick from there power query is here for you okay now let us close this file I'll just say don't save and head over to the last example that I'd like to show you and this is what really um brought us here we want to see how we can Auto automate or connect to a folder and automate combining these files for analysis okay so I'll open this worksheet and I'll head [Music] over to my desktop and locate the folders I might have not shared these files with you they are quite big files and and these are real data from an organization I may not easily the files with everyone but again let us use this data to do a demonstration here okay our interest is to connect to a folder and pick data do a clean up right there and start doing our analysis and this should be an automatic stepes or an automated process in the future when data comes in now to explain the scenario that we want you know to work with is you have sales data that you keep getting on a monthly basis for example you can see in this folder which I've called PQ we have August data that has comeing we have September and October data now if this is the folder that keeps on receiving your monthly files but the way how the monthly files show up is quite messed up you can't do analysis then you can you can automate that process let me just open one file here just for you to see what we are interested in doing okay I'll just open one file and see how it comes up when extracted from the system from any of your Erp systems that you might be using it could be QuickBooks it could be sap it could be dynamic 365 any system that you can download data from can give you data in a different format now when you want to analyze it into Excel or with Excel you have to do some transformation okay the file is quite huge is still loading and this is how it looks like this are transactions that have been picked from a billing system and this particular data set looks a little bit messed up and we we want to go through a number of transformations of course the first thing that I did tell you that whenever you're going to be doing a data analysis project or work understand the data that you're working with okay so what I will do I'll actually go through this particular data set with you understand it and see what the things that we need to deal with what are some of the um let me check as if we have some comments let me check so this comment is stuck on our screen let me remove it so look through the data and tell us in the comment which particular data transformation steps you expect we should be going through here okay of course I can see in the first row we have the order number here we have the Met seral number this is transaction data for an electricity Distribution Company and this is you know this is how it comes from the system can see this column is completely empty you can see from the rows here the rows have different you know different um roow eights you know one of them are very small some of them are very small eight and the other ones are let me check the r for this bigger ones 25 so this is how the system does it automat and there are also so many merged sales here when for example I check in this particular value here this is for example the money that was used to do energy purchases you can see there's a merger here and there's a rra text so all those things are not necessary or are not needed when you're doing data analysis can see there are number of columns that are merged or sales like this one here this is the cash that was received you can see column f and g they have been marged this is a behavior that you get from the system when it comes to excel it shows different and we need to clean up all those things some columns are quite small and some rows are quite small that's one we have margin that we need to deal with we have to transform some columns you can see the data set itself for the numbers we have this green triangles here what do they mean it means that there's a problem with this value it's actually a number but it's recognized as as text so there's no arithmetic that you can carry on such numbers okay and when it comes to let's check the last column here when it it comes to the last column you can see that our date column is combining the date itself and the time maybe when you're doing your analysis you may want to look at which time you know does the most transactions come in so that that time is prioritized maybe the office is always open you have you want to do analysis on your time so you can't do analysis on your time when it actually SS like this if you want to know which date brought in the most Revenue maybe for one reason or the other as a data analyst you may want to dig in and inform management you know that this time between 9 and 10 these hours make sure that your systems are up and running because this is the time when people are are buying the most okay those are some of the questions that we might answer and this data looks a little bit gigantic very huge if you actually try to scroll it down even my machine might freeze but let me try scroll and go over to the last record and then after looking at the number of Records because I can't actually select from here to show all the possible records because they some gaps there are empty sales there are empty rows there are empty sales there are empty columns so the data is quite messed up there's no data analysis activity that can happen on this particular data set now let me hope that we appreciate the problem and let's head over to power query and deal with that particular problem all right let us know in the comment if you understood the problem and once we all understood so we all understand the problem we can go ahead and solve that particular problem all right I'll check through the comments here as our file is still scrolling through and it's doing that because it's actually heavy the file is heavy um taban Oliver you're saying it's okay I don't know what is okay on your side but confirm Oliver you are confirming that we are back and someone I think has a question here bro I'm asking so you this workbook well connected to Internet you know this file that we're going to be using is not connected to the internet um let me check AB SNY Bel great work Mr V that's wonderful let's scroll through all these comments IR you're saying thanks so much and OA your things thank you very much sir and again you have is this a question your your teaching is new every day all right all right thank you for those message so you can see that this particular gigantic file is quite huge we have thousands of records that we need to clean and we have a number of files okay I'll just close this one out and go back to the folder called Pik and you can see I have three files that we want to clean in one go and any other future file that drops into this folder will automatically be cleaned for us by power query and we should be able to refresh the report immediately and instantly our values will update okay let's do that head over to the data tab get data and from file this time we shall point it to the folder option so click on the folder option and in there depending on how powerful your machine is the browse window will open Locate the file m is on the desktop and it's in this folder here PQ so what I want I'm setting up that folder as my main folder where all my reports will be dropping when colleagues share with me their monthly transactions they'll be dropping into this folder okay I'll say open this will now establish the connection between powerquery or the file where I'm generating the report from and the folder and this dialog box opens here the number of steps that you need to to take here of course we can combine and transform that is our interest or we can actually load load you know directly load into a table or load to can pick we can do transformation and we can cancel our interest is to combine all those three gigantic transaction files to into one single file that we can use into our analysis okay so the option that I go for is the combine and transform so you click in this option here combine then it says combine and transform data combine and load or combine and load to so ours will be combine and transform data let's go with that option and right out uh right after that the power will evaluate our fold files and it will try to establish the connection how fast depends depends on how powerful your machine is you can see mine is struggling to establish the connections here but again I believe we shall be able to achieve okay all right all right um You can see it has actually used one of the files because that folder had three files so the first file that is choosing to use is the agust file but of course we have October and September data okay now it has actually located that in that particular file there's a sales report table or a sales report worksheet that's the data it has actually seen so we can click in here to do a preview the preview is evaluating and this is the row data that we had once it comes into the power query you can see everywhere there is nothing it actually shows null null values if a column is completely empty then it will show us null values or all the way through so n is empty and some sale addresses that do not have anything in them also show n in there so we need to do a lot of transformation I'll accept and say okay and in here what I will do when my queries start processing I'll open into the power query editor and start my transformation the things that we talked about the problem that we described when we looked at this particular data at the beginning okay as I wait for this to evaluate and open please let us know in the comment section if you're learning a thing or two if you're enjoying please double tap or you know like the session uh that will really tell the YouTube algorithm that people are enjoying this particular class and it may show to other people like you or you can actually go ahead and share this particular class with a colleague once they click on the link they'll be able to come in and learn with you okay share only with people who are interested do not just share with everyone share in your WhatsApp groups where you come from people will come here and watch the review of this particular class okay so my PC seems to be a little bit slow that's why you're saying that parare is taking its time to go through this entire process but once it's ready we should be able to do our data cleaning and processing and loading it back into our Excel all right just leave in any comment uh let us know if you're enjoying these sessions and if you do feel free to share this particular classes with other colleagues okay now we get our preview here so what Pary has done is it has actually picked the PQ name or the powerquery name from the folder which we are connecting to so this one looks to be fine to me and this these are the steps that are going to be recorded here these are steps that PO has gone through by itself okay looked at the source right there and when you click in here you might be taken back to the source here so this is where we started from so each particular value that you're seeing in this content column is is actually holding the file if I click in here you see this is AUST and it tells us the btes or how large this file is when I click in here you'll see this is October file so each individual record you see here is holding an entire Excel file that you saw that has that number of transactions very huge so power query or power pivot can handle millions of Records so every time your project grows please think of power query as a tool that we use okay so it has actually filtered out hidden files invoked the custom function renamed some columns all these are just automated processes or steps that has taken on its own and it has expanded the table right there this what we had and it has also done some changes in the data type what it thought it could actually pick through it was changed again now we can build from what we are seeing here and carry on with our data cleaning okay let's do that now the source name here has been brought in it's up to you whether you want to keep it or not but we may not need this Source column because we already have we already have the date column the date column will be able to give us the month the month name so I'll right Mouse click and remove this column okay and column one right there can see it is completely empty so that's an empty column in our row data I'll actually highlight remove the column and there you go you you can see now the order number the Met serial number the customer number these columns seem to be doing fine the customer name the amount this one will be amount amount paid okay and the column seven seems to be completely n you can look at all these n values so this is the column we need to remove right Mouse for and pches have column 9 and column 10 all EMP if I want to remove all of them at once I'll click one and then holding control I click on column 10 and right Mouse click and remove columns I go on I'll call it monthly service fee okay and I also have column 13 is completely empty I'll do for column 13 is to remove it entirely okay are we together I remove it entirely just colleagues confirm if my screen is fine

Channel: Open Training Camp

Views: 1,350

Keywords: pivot tables, pivot table tutorial, excel pivot table, excel pivot tables, how to use pivot tables, how to use pivot tables in excel, power query, how to use power query in excel, data cleaning using power query, automate data cleaning using power query, ETL, microsoft excel, data cleaning, pivot tables in excel, power query tutorial, power query for beginners, how to use power query

Id: Ub4JREj9MLQ

Length: 105min 16sec (6316 seconds)

Published: Fri Feb 16 2024