How To Import & Clean Messy Accounting Data in Excel | Use Power Query to Import SAP Data

today let's talk about a very useful excel tool that will help you clean up your data now this is especially helpful if you work in accounting because as an accountant you probably find yourself downloading data from other systems like sap oracle and the like and you need to clean these up to be able to prepare your reports so the tool that i'm going to show you is like a magic box it can do a lot and it doesn't require that much effort from you i thought the best way of introducing this to you is with practical examples so let's get to it let's take a look at the data that we need to import into excel and analyze we have an sap extract which comes from our european entity it's the income statement but take a look at this our numbers are all over the place they're not even recognized as numbers because the data is coming from europe it's using a dot for the thousand separator and a comma for the decimal place now they're also not properly aligned in a column we have a lot of information here that we don't need and a lot of gaps in our data we need to bring this to excel and clean it up and our aim is not just to clean this up but also to get a report that looks like this we want to get the current year-to-date information for revenue expenses and profit and loss and compare this to last year and in case we need the details we want to get them properly here so that it's easier for us to analyze the numbers now what would happen if you just copy this information and paste it into excel let's try it i'm going to press ctrl a to select everything ctrl c let's go back to our sheet here and let's create a new tab and ctrl v this is how it's going to look our numbers are still problematic we also have a lot of white space in front of some of these numbers and take a look at this here we have main revenue there is no number here the number is showing up in the next column and we have a third column here sometimes it looks like the numbers show up in the third column for the majority of the cases they are in the first column now this information here is for the current period so that's the reporting period and this is the comparison period it looks like we have the same issue where some of the numbers are in the second column and sometimes they might go to the third column now this last column here is the absolute difference so that's just going to be the difference between these two numbers for our analysis we're not really going to need the absolute difference because we can make that calculation easily in excel now how do we clean this up though well in excel you have different options from the data tab you have features like text to columns remove duplicates you have functions like the trim function or search and replace to clean up this data if we're going to be honest this is going to take us a lot of manual work and if this is a task we have to repeat on a frequent basis it's going to cost us a lot of time every single time now instead of using the tools available on this side i'm gonna use the tools available on this side because this way i get to save the steps i do so that every time i get new data everything will be updated automatically the moment i press refresh now before i get there let's just quickly take a look at the way we would clean up this data set if i scroll to the left there are a lot of columns that i don't need so the first few columns here until the income statement i don't need those i would be able to get rid of them i also have some complete empty rows here which i can get rid of the next two columns also look empty we can just double check there isn't much there except this semicolon this one also looks empty so i'm just pressing ctrl down and ctrl up to take a look there isn't anything there that we want to keep then we have the description this is something we definitely want to keep followed by our numbers here now for the numbers we have a few things to solve we have to combine them into one column and translate them into numbers that our excel understands this excel here uses us regional settings okay so now that we're clear on the type of cleanup we need to do let's go ahead and do this i'm gonna delete this and now let's go and explore the left side get and transform our sap data is in a text file so from text csv the file i want to import is sitting in my c drive it's in the info folder and that's the file just select it and click on import here i get to see a preview of my file we know that we have to do a lot of transformations here so we're not going to load this directly instead we're going to transform the data now we're inside the power query editor here we can choose the different types of transformations we want to do we can give our final result so the final table a different name i'm going to call it clean data and here are applied steps so any steps that we do to clean up the data is going to be recorded and remembered so anytime our source changes the steps will automatically apply whenever we refresh our final report in case the name of your source file changes or the location of the file changes you can click on the gear icon and browse for your file or just update the name directly in here and then click on ok now power query went ahead and applied a change type step here so that it defines the type of data that's inside a column i was thinking that pretty much everything is a text column but that's not the case right we have a lot of numbers here so we need to update that but it's too early to update it we're going to do it later when we have proper column headers so i'm just going to remove that step first let's go and take care of our numbers here so remember we had the issue that sometimes the number was here and sometimes it was even in the third column like in this case we want to bring this number to this column and this number as well in here for this i can use merge select this column hold down shift select column 13 right mouse click merge columns separator is none because i either have something in the cell or i don't the new column name let's call this current period and ok everything is now merged in one column next we need to repeat this for the previous year which is these three columns here so i'm going to select hold down shift select column 16 right mouse click merge columns leave separator as none call this previous year period now let's get rid of the columns that we don't need actually in fact since the columns that we want to keep are less than what we want to get rid of let's just pick the ones that we want to keep i want to keep the income statement the descriptions so this time i'm going to hold down control select column eight the first set of numbers here are my current period and the second one is the previous year period so you have to ignore this we're going to get rid of these in a second i don't need anything else from this so with the columns that i've highlighted i'm going to right mouse click and remove other columns okay so so far so good i don't need all these rows here let's remove them remove rows remove top rows 10 i'm going to remove all the way up to here and okay now things are starting to look better i have these empty rows that i can get rid of so let's go and filter them out by removing the blank rows here and okay now notice anytime we add a new step we can see power query writing these steps writing these functions for us it's always good to keep an eye on these so even if you don't really understand them make sure that you can see these steps in case you don't see it go to the view tab and activate the formula bar so in case yours is not activated you're not going to see those steps place a check mark here so you can follow these along okay so now let's take care of these numbers if i just go and change the type of this column to a currency it's not going to work so let's remove the step instead we have to tell it what's the origin of this number now since our issue is with this one and this one i'm just going to select them both then right mouse click change type and go to using locale here i can define the origin of this number the data type i want is going to be currency the locale here is not english so let's scroll down until we get to german and we have german austria when i select that i can see a preview of how the original number should look like in this case it doesn't look like that because it has the dot here as a thousand separator so instead of austria let's go with germany that's the number that i'm dealing with so now let's click on ok and everything is properly transformed this is a text column this one is a text column as well so i'm going to leave that as is now one thing i noticed here is i have the colon after the text and since in my final report i don't have these i'm just gonna remove them select a column go to home replace values look for call in and replace it with nothing and click on ok this step is recorded as well so everything that we did got recorded anytime we need to make a correction to this or include a step in between steps we can go and do that there is one last thing i need to do here give these proper names and i'm done so let's test this out close and load close and load two we can load it as a table on a new worksheet but in this case i'll just put it on the side here so i'll go with existing worksheet and let's go with f1 and ok our data is clean now if at this stage you notice some things that you want to remove you can always go and edit your query so you can just double click here go back to the query and add a step so for example i can remove the bottom rows i don't want that last row so i'm going to put one and okay and then just click on close and load and it's going to refresh the existing query so now that last row is disappeared in addition to cleaning up the accounts we also want to create our summary report we have all the information that we need from here all we have to do is use a function and grab what we need because in this case the labels are identical to what i have in my source data i can do a direct lookup on this in case you have different labels you can use a helper column as your identifier the type of lookup function you're going to use here is going to depend on your version of excel because if you have excel for office 365 you're probably going to want to use the new x lookup function which is easier to use than vlookup if you have other versions of excel vlookup will work our lookup value is this value right here the table array is our table so i'm just going to go to the side to select it the column index number we want to return is the third column that's where we have the current period so i'm gonna put a three last argument is if i want an approximate match or an exact match well i want an exact match i can either go with false or put a zero in here close bracket press enter and i have my value now i need to reverse the sign here because revenue in the system is a credit so i have to reverse the sign for these now just so that i can copy this and also paste it to the next column i'm going to be mindful of my cell referencing for a4 let's fix the column reference the rest remains the same because i'm using table references now let me also go and update the number formatting and remove the decimal places okay so revenue total will be from here material and services total as well so are these so i'll just do these in one go i'm going to hold down ctrl select them and then press ctrl v to paste in my formula next let's calculate the percentages here i want to take material divide this with revenue now again i'm going to be mindful of the cell references because i want to be able to copy it not just down but also to the next column so for revenue here let's press f4 two times so this time i'm keeping the row fixed but the column dynamic this reference remains dynamic so that every time i'm going to be referencing the cell above the percentage let's adjust the formatting to a percentage and copy these to the other cells now that i've done everything for this column let's just copy the whole column paste it here there is some part that we need to change because instead of getting the third column we want to return the fourth column i'll just do a quick replacement here highlight these cells press ctrl h i'm gonna look for comma three comma zero just to make sure i'm in the right part of the formula and replace it with a four replace all okay and we're done the great thing about this process is when i get new data because all i have to do is refresh this so let's say i get a new file i'm just going to grab that file and drop it in the same location as i have the previous file they have the same name so i'm going to replace the file in the destination now i just need to go back to my report right mouse click and refresh now keep your eyes on column b data all the steps run automatically and i get the latest data now in case your file has a different name or is in a different folder you can just go back to your query go to the source tab click on the gear icon and update the file from here click on ok close and load and your report is done before we wrap up i want to show you another example what if our data is in excel and we need to create a pivot table from it so in this case i have account date transaction number supplier and amount and i want to create a pivot table with amount per supplier in this case i'm just dealing with sample data but imagine i had a lot of this if i was cleaning this up manually it's going to take me a lot of time so i'm going to use power query instead first thing i'm going to do is transform this into an excel table so let's press ctrl t and see what range is picked up it just thinks that this is our data set because we have gaps in the data so let's correct that and select our sample here my data has headers click on ok under table design let's update the name of our table and let's also remove the table style to go back to the previous style we had before in case you want to remove these drop downs here uncheck filter button so everything looks like it did originally now that this is a table and we have a proper name for it go to data from table range and send this to power query we can remove the change type step at this point and add it later on to clean this data i want to remove any of these rows where i just have no values so i'm going to click on this drop down here and uncheck null and ok so everything that doesn't equal to null is included next let's filter out the totals because if i leave them in there the pivot table is just going to include them as data i need to get rid of them let's use text filters does not equal and type in total so power query is case sensitive i have to be mindful of that and okay next let's fill these down i need to have an account for each row so i'm going to right mouse click fill down now what i'd also like to do is to remove the code here and there are different ways i can approach this depending on your data set and what is consistent in your data set in this case i can go to transform split column and go with something like by digit to non-digit this is going to give me multiple columns and then i can get rid of this if i always have just three digits or i can also base it on the number of characters so in this case i'll go to split column and use by number of characters specifying the number of characters used to split the text column so i have one two three four five and six including the space i want to split this once as far left as possible and okay the split is done power query thought that these are dates i don't even need this at all so i'm just going to press delete and remove it let's rename this column to account now we can take care of the column types this is a text column i want this to be a date only this can be a whole number supplier is text and my amount is currency everything is done let's update the name of our end pivot table and call it clean report now go to file close and load to this time i want it as a pivot table report let's put it in the existing sheet and i'll put it right here on the side and okay put amount in the values field and the supplier in the rows field and let's take a look we have our pivot table let's update the number formatting right mouse click number format number use a thousand separator and zero decimal places now because it's a pivot table we can look at this in any way we want we can also add the account and supplier here or just put account as the filter anytime our data source updates we just need to refresh this to get the latest information i hope this video gave you some ideas on how you can use power query to clean up your data now there is a lot that power query can do and i have a complete course about it in case you want to go deeper thank you for being here thank you for watching till the end if you're new here don't forget to hit that subscribe button give this video a thumbs up and i'll see you in the next video [Music] you

Channel: Leila Gharani

Views: 148,747

Rating: 4.9824772 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, microsoft excel pivot table tutorial, sap, clean data, import data, pwoer query, power query tutorial, pwoer query tutorial, accounting excel spreadsheet, accounting excell tutorial, excel 365 power query, import data into excel

Id: TjGbOoKvZ7M

Length: 20min 38sec (1238 seconds)

Published: Thu Mar 04 2021