Reformat Reports with Power Query

in this video we're going to use power query to transform this ugly a holiday leave entitlements report which was exported into Excel from a popular Australian accounting system called MYOB we're going to put it into a tabular format which will be much more useful now this report is not only ugly and appearance that it's also ugly from an excel point of view because it's virtually unusable with any of excels tools like pivot tables aggregating functions like sum if and count if to name a few so the first thing we want to do is select the data we want to work with now I've actually already formatted it into an Excel table so there it is there and then on the power Cru tab we're going to select from table this opens the power query editor window and the first thing we want to do in here is start removing some of the rows that we don't need so in the entitlements column we're going to get rid of the nulls and the blanks and because my window is small you can't see the bottom so I'm just gonna make it a little bit bigger and then we'll try again okay so no nails no blanks and we don't need the totals either I'll click OK now I should be able to resize my window okay and in the values column we want to get rid of the zeros okay now we are left with just the name and the type of entitlement in the entitlements column and then the values so now what we want to do is move or copy these values up onto the rows with the names but first of all we need to get rid of the nuns in this column so I'm going to replace them on the Home tab we can go replace values just like in Excel so I want to replace star and on now CalPERS case-sensitive so you have to make sure you type it in as it appears in the cell and we're replacing this Mel Mel in power create null is the equivalent of blank now I've got our nulls we can use the fill tool to fill up those values into the null into the null cells so on the transform tab we'll go fill and fill up okay so the next thing we want to is split out the name and the entitlement type into two columns so to do that we need to add the column for an index column and we can just use the from zero that's fine so that will just number every row from zero through two however many rows we have and then from there we need to add another column so that the index selectors and the add column tab will go to standard what we want is the modulo column so I'll select that now it's going to ask us to enter a number in here from which to find the remainder for each value in the column right so it confusing these this way for me to remember this if we want to enter the index number for the first line of the second record so this is the first line of the first record and this is the first line of the second record because this is the second person and the index number is 2 so the value in turn there is two now click OK so now every row is numbered 0 & 1 so every record the name is number 0 and the entitlement type is 1 so now what we can do is actually pivot this entitlements column and split these names and entitlement types out so if the modulo column selected on the transform tab we're going to pivot and we're pivoting entitlements column and in the Advanced Options may actually want don't aggregate because obviously when you pivot something it wants to aggregate the data we don't want to aggregate it we just want to pivot it so we separate the two types of information in this column so I'll click OK alright so now we have our names in one column and our entitlement type in another we're going to choose the fill up tool to copy the entitlement type up to the row above with the name so fill up now I can go ahead and get rid of the extra rows with which contain null so I'm just going to filter those out okay we're getting there so we don't need the index climb anymore I'm just going to select it and delete it and I want to rearrange my column soms going to click drag them into place I'll rename this column name just double click the column header to rename entitlements okay and the next thing I want to do before I'm ready to load it into Excel is to give my query a name entitlements now one of the amazing things about power query and it's very similar to VBA when your recorder macros power crew records all the steps that we've taken you can see them here so we haven't had to learn any complicated programming language it's all been very point-and-click kind of stuff but next month when I get my new ugly entitlements report I can simply use this query again I don't need to go through all these steps again it's going to remember all these steps and I can just refresh the query and it will pick up the new data so I'm ready to load it into Excel I might just do one more thing let's sort these names in ascending order and I'll just set the data type for the value to a decimal number okay so now I can close in load and I've got a couple of options I can load it into a table or just create a connection to this original source I'm going to put mine in a table and I just need to tell it where to put it so let's put it on a new worksheet and I'll click load so there I have all my data for my holiday leave in a proper tabular format I'm go ahead and use all of the Excel tools the way they were designed to work and easily summarize and analyze this data and next month when I get my new report I can reuse this query put my new data in here or copy the query to another file and simply refresh right click I can't see it's off the screen right click and refresh the query and it will update this report for me I don't have to redo any of those steps so they have power query transforming this ugly and wiry report into something much more useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 18,358

Rating: 4.9638553 out of 5

Keywords: power query, excel power query, reformat excel report

Id: AQsEwVt6Oyo

Length: 6min 44sec (404 seconds)

Published: Mon Feb 08 2016