Power Query for Accountants & Finance People

let's get started we we have about sixty minutes of video training on various things of power query that relate to accountants and finance professionals hello everyone this is me Chandra from New Zealand now Wellington today is a nice and clear there's and it's already starting to feel warm I thought I'll just show you a glimpse of how it is outside and and then we'll get into the good stuff so this is how it looks outside it's it's not quite sunny yet but you know it is clearing up quite a bit so it's it looks to be a great good day let me just start the screen share and then we will we'll get into the topic for the day so hello and welcome to a power query for accountants and other finance people master class in this session we will I'll have a brief introduction and then we will talk about five topics what is power query a quick tour this is not supposed to be an introduction level training so if you have no experience working with power query then while you can see the benefits of it in this probe in this course you will not be able to understand how certain things are happening so I'm assuming you have some experience with power query it doesn't have to be very in-depth experience we will then understand how you can extract data from a bank or credit card statements how we can combine different monthly files it could be ledger files or it could be you know any statements or transaction details or whatever split up in monthly or weekly or whatever basis and how you can combine all of them into one data set based on some rules how to reconcile paid what's a sent invoices or any of those kind of things and how to build a live currency exchange rate Galatian using power queries so those are the five things that we will cover a brief introduction my name is Chandu I run a website called you do not org where my mission is to teach people excel and power bi and make them awesome in their work I've been doing this for a little over ten years now and I really enjoy this this work it is my full-time job running the website and helping people and learning and sharing my my thoughts and ideas I'm super happy to share the information about our query with you on on our YouTube live even today and I live in New Zealand right now but I'm originally from India and when I'm not working with Excel or for bi I love cycling and walking in building stuff with Legos or reading etc you can follow my website on Chandler or Todd you can also visit me on Twitter at the rate R 1 C 1 or many other social media platforms so this is I thought I'll just show you a glimpse of where we live this is one of the places in Wellington where we occasionally go for a walk as you probably know New Zealand is quite beautiful so not everything can fit into one slide so this is something panorama that I took on my phone and you can see my wife and and our twins in the shanty and actually it was a hard day so you can't even see their faces but there they are alright let's get into Excel and escape this and we will jump into Excel before we proceed with things I'll just check if there is any messages it looks like there is no audio issues or anything so I'll I will continue from time to time I will check the chat window so in case you have any question or you feel like something is not clear please please feel free to ask the example files for this video will be available on my website I will share the link with you in the chat window as well and you can go ahead and download the files later but while this video is being recorded the file is not yet ready so once it is all done maybe around six ten hours from now you can you would be able to download the file as well so we will get a quick tool of power query and then we will understand how to extract data from a bank or credit card statement let me first show you one such statement obviously you know usually we receive bank statements or credit card statements as comma separated by our value files CSV files or text files or sometimes even as PDF you can read any of those kind of things except PDF as of this moment if the data is available in a PDF document then you would need to somehow copy that into Excel or notepad and then connect it to power query so what I've done is I have made up a an artificial bank statement using random values like this in a spreadsheet assume this is the data that you receive from the bank so we got some transaction dates processing dates type of the payment details particular scores and reference you know various kinds of columns that your bank may decide to add and the vital information amount right let's say this is for a small business and you would like to load this statement into into a spreadsheet for doing some further analysis or auditing or whatever may be the case so we want to connect it to this and load it and as you can see there are some problems with the date columns there is a lot of information and we would like to set it up through power query as a connection and then load it while explaining that I will also talk about a quick introduction of power query so this is our bank statement again it's not much there's nothing much to read here because it's quite small on the screen but there are several columns and there is an amount column which is of main interest but we would also need to have other columns to understand what is that amount and just like a typical bank or credit card statement both positive and negative amounts are all in the same column so both debits and credits are in one place like negative 250 negative 100 like that and then there is a positive value this must be a credit and those are the debits so we would like to bring this values to power query and when we do that we would like to split this into two sets of data debits and credits so that we could then go and do further analysis on individual basis so I'll close this I don't need to save this file and I'll open our workbook for the day add a we don't need to add a sheet we will go to the data ribbon and then setup a new query so this is where you will start accessing power query go to data and access new query button from get and transform area or power query area depending on which version of Excel you are using you know the place to go and click the button will be slightly different for example I am using office 2002 thousand sixteen Excel 2016 here and this is where it is and it also has these get external data button so we don't need to use them we will just use this ones whereas if you use office 365 you may not see that button you will directly see these things and starting 2019 version of Excel this might itself be called as power query rather than get M transform so from here we go to data new query and we will tell power query how we want to bring our bank statement in this case we would like to bring this data as as a from a CSV file a comma separated values file so that's what we will do we will just say get the data from a CSV and and then you can point to the folder where this this data is I have this data in my master class 0 to folder that's my bank statement dot CSV which is what we be with the Lord so I'll click on import while this is doing let me quickly check the chat window or anything everything looks good so we will bring this data now to power query and at this point our query will show as a preview of the data and this is just so that we can we can make you make sure that everything is coming through as as required it looks alright to me so I'll go ahead and edit it so that we can go and set up some rules on top of this all right so this will open up the power query editor screen where we will be able to look at the data and set up some processing rules to work with the data so now that we are looking at the power query let's quickly understand what power query is and you take a quick whirlwind tour of power query power query is a data processing software you can use power query to bring data from anywhere it could be in a database it could be in a file it could be in a web page it could be in a cloud storage so bring the data and then set up some rules on top of the data you could tell true power query you could tell things like you know show me only the debits show me only automatic payment kind of transactions show me only things that have happened in the month of March 2017 whatever may be the case so you could tell power query such rules the rules that I just described are very basic rules you can also go ahead and do some advanced and rules so that's what it does it is a data processing software it acts as an interface between your your data and your your analysis right so often when we want to analyze the data whether you are an accountant or a finance professional or a marketing person or a HR person whatever may be the case when we want to analyze the data the biggest challenge that we face is our data may not be clean or in a desired State so in such cases what we do is we spend a lot of time you know wasting in either Excel or whatever may be the software processing the data so using power query you could save all of that time you could just tell power query how you want your data as a process and then set up that process in power query once that is all done whenever there is new data you just have to refresh right there is a refresh button inside Excel you can refresh and it will bring new data power query as a data processing software is available both in Excel and in power bi so you could use power query to clean and reshape the data whether the data goes to excel or it goes to power bi and then you could use those tools excellent power bi to do your analysis as people who work in accounting and finance demand tend to be predominantly spending their time in Excel we will learn this entire process through Excel but whatever ideas you are learning you can apply them in in in power bi as well so when you open power query editor this is how it looks it kind of shows you a preview of whatever query you have selected you could have more than one query you could have connections to different files as throughout this video we will build multiple connections you will see right now there is only one so this is what it is if you want to see what queries are there you could use the left hand side panel which is collapsed called queries expand it to see what are the connections that are there and as you select something the preview of that will show up here this preview kind of looks like Excel but it is not Excel it is a preview of your data right it's just showing you how your data looks at that point in time and on top of your preview is is your standard ribbon interface where which is again we are familiar with ribbon through Excel PowerPoint Word or Outlook or any other office software so the ribbon is is just like a menu but everything is spelled out you can use ribbon to access things there are different kinds of ribbon the home ribbon is where most of the common functionalities are mentioned and then you could do two of the main things whenever we work with data we would like to transform the data that is changed the nature of the data or we would like to add stuff we would like to add a new column that tells us whether this is a debit or credit you know those are the kind of things so that's why those two will be there and then there is a view ribbon using which you could customize the way this power bi power query editor window looks apart from all these things there is also a query setting pane that opens up from the right hand side which gives us some detail of what is going on what is the name of the query and how many steps have been applied so as a said power query is a data processing software so the data processing happens in steps step number one locate the bank statement file connect to it step number two bring the data and use the first row as headers so those are what these steps are you could also enable optional formula bar in power query and then once the formula bar is there you could actually see what is going on behind scenes as you apply these steps to enable that you can go to view ribbon and click on formula bar and then enable this this is a one-time thing once you do it on your computer you don't need to do it again and again it will always be on and you can see the formula bar power query uses a special language called M which is letter mm for mother and it is the language using which everything that you do in power query happens you don't need to learn this language most of the things that we want to do on databases can be done with the menu options right there but learning the language will help you do some fairly advanced stuff ok again this session is not for learning M language so I won't go into those details anyhow we got our bank statement now let's define some some simple goals goal number 1 is let us say for this analysis we don't care about a transaction of the type transfer this is because the transfer is a if a transaction that appears in the bank statement which is simply moving money from one account to another within the same user thing like this is a small business so they have multiple accounts on one checking account or one savings account and from time to time they move money between these two which are address transfers and we don't want to show those transfer amounts so what you could do is you could go and apply a filter is simply select the little filter icon just like how we do in Excel and tick the transfer and click OK and now it will add a rule that says I'm going to filter with type should not be equal to transfer that's what it should it shows here so it will it will not show the transfer type of transactions and then the next thing that we want to do is we want to look at the amount values this is my amount and based on the amount we would like to categorize these amounts right if the amount is between 0 and $50 then it is a $15.00 it is a small transaction between 15 and hundred is a medium and anything more than hundred dollars ease is a large transaction that way you know you could when you are doing analysis or whatever you may be able to focus the effort on the large transactions to see if there is any fraud happening or anything suspicious going on something like that but because then amount itself can be both positive and negative we would like to first create an absolute value that is what is the actual amount so we can select the amount column go to add column and then see if there is any functionality that will give me the absolute EMA there are several standard automatic things from number so we can see here there is nothing there is in the scientific area there is an absolute value so you can say add a column of the absolute value from the selected column so it will give me absolute value that's the amount the negative sign is gone essentially and I can see this now based on this we would like to categorize the amount whether it is between 0 to 15 or 15 200 or 100 or more this is what we call as a conditional column that means the value in that in that column the category is a condition based on this number so select this and then say conditional column again we are adding a column and we can give a condition so this conditional column name shall be category and the column name the absolute value is less than fifteen and this is a small transaction we could add one more rule if the absolute value is less than 100 that's a medium transaction if these two are not the case then it would be a large transaction this is similar to writing any formula in Excel but here you're doing it as a rule that means if there is new bank statement available with more transactions and you refresh all the statements will data will come and then new categorization will happen it so every care every amount E is now categorized as small medium or large now that this job is done we don't need the absolute amount anymore so I can right click on the absolute value and then say remove that column is not necessary anymore because it was only needed to do the categorization so once that then it is gone and these are all steps so you can see all the steps getting added here now at any point if you have a doubt you want to see how things look good at some point in the past you just select that step and then our query will show you a preview of how things look at that point so this is the final step right now what we want is we want to split this into debits and credits to do that we can we can simply go here and a debit is anything that is negative so I can go to the amount filter and the number filter is less than or a passing less than zero so if I if I do that now I am only looking at all the debits and that none of the credits are here right so this is my debits if I want to have a list of credits as well we could do that we could do that too you would either revert to the condition or you can do something which I will explain in a minute but before we do it that we split this into credits as well let's give this query a name now that we have done our job and this is only debits I'll give my query name so select the name and area and then here we will say our debits that means our query now we can go to the queries and you can see that there is a debit squaring the credits query is exactly same with the reverse condition so that means amount should be greater than 0 rather than less than 0 so we can right click on debits and then say duplicate that means just give me a copy of the same query it will give you a copy this query we will call it as credits so we now have a new query called credits and in this credits query the last step where we are filtering I will change that so I can click on that little cog or gear icon and change the condition from less than to Creator then and we are now looking at credits so we have both debits and credits that is one file but now split into two one with debits and one with credits now that our job is done we could go ahead and load this data so this is how you finish your job in power query all of the transformations are done everything is clean and we want the data we finally got the data in the shape that we want we could go and say close and load from the home area when you do that the default behavior is at this point Excel will add multiple worksheets because we are now loading two different tables one per on per query and in each stay in each worksheet it will lower the data as a table with the same name as you have given in power query so it's now debits table and credits table but mind you this is all a connection to the file on your computer which or file that so this is the file that is connected to so if I have a new bank statement I just need to replace the new statement with this and and make sure the names are same and once all of that is there we will have to come here go to data and refresh all once you hit that Excel will go and connect to that file bring the data apply all the steps remove any transfers and categorize the amounts and split it into both debits and credits and give us two tables so this is how you can use power query to process data and connect to a file and bring the data as per your requirement you could go ahead and add some advanced and conditions there as well it's just a bit of no depends on what work you are doing and how you want your data let me quickly cross check so someone is asking would you show us how to connect with a banks website or how will that go obviously you can I will show you a little example towards the end of this session where we connect to Reserve Bank website to figure out what is the live exchange rate there are ways to work around it by default power query can connect to websites that are public in in this case because your bank statements would be private that is you would have to log in to access them the way to access them will will depend on what your bank is willing to share and whether they have an API or something else so this is where it is it may be possible but it all depends from bank to bank and certain banks provide a payment API gateway through which you can connect and bring the statements this is probably how many finance software like you know Xero or my online budget or something you know those software can connect to your bank account and bring the statement transactions but you know that is fairly advanced in my opinion it might be possible but depends on the bank alright let's let's see the second one which is combining monthly files now imagine you got a a bunch of legislate mix ledger of values here so one per month I got your May June July 2017 so this is for full financial year 17:18 and you got only three months value so this is how they look each of them have same format I'll open one of them so you can see there are six columns service area description creditor journal date journal reference in the total amount so this is the data and in each in each month you would have hundreds of transactions but the format is same several columns they all follow the same thing and they all sit in the in the folder called ledger write and you've got three three of them now but potentially you could have six twelve ninety five whatever and we would like to bring all of the data rather than looking at individual tables we would like to just get everything and make one giant table so that we could then make some graphs or analysis or whatever in Excel you could also do that through power query just go to data new query earlier we did from file and an individual file but now what we want is all the things in the folder so we will use the folder option and set up the folder based connection it'll ask you where is your folder you can say the folder is excuse me well navigate this oops I think into the wrong place so everything looks weird so we will select the folder where the files are and that's my folder path and when you click OK at this point our query will go and try to connect to the folder and if you show you a preview of what it found it says I found a bunch of Excel files and then the content is here right so if you are happy you can just click on combine and it will do something or you can go ahead and edit right you can go and edit the connection we will we don't really know how this works but we will just give the combine a try and then we'll say combine and edit that means you just combine everything and then show me what you found there so it is now trying to combine the files and it it stops at the first file and then says this is how your first file looks so you can select a a one of the worksheets there's only one worksheet per file and then says this is what so because it doesn't know what to do with the excel file it is now asking you how you want to treat this is what we want and then when we click OK at this point essentially what power 20 you will do is it will go to the sheet 1 the very first worksheet in all the three files and then try to bring the data and combine everything into one giant table and give us this so it is now combining everything right you can see here that is July data is here and then as you scroll down we will eventually come to June and then if you go further down you will see May data so everything is here right but this is a very plain vanilla combination that means everything is taken and then just combined one after another just stitching together but the file name is maintained so that you would know where this data is coming from and you can use that to distinguish the items if you want you could also go and tell power query how the files should be processed in in our case we didn't bother telling we just said my data is in the sheet 1 and it automatically pick it up all the sheet ones and stitch them together but what if you want to exclude or include only one type of service area that is within each file there are multiple service areas and we are only interested in maybe children's family services or travel expenses expenditure right you could do that you could go and see on the queries area when you did that we thought only now there is only the three queries because earlier we had debits and credits and now you have ledger so there should be three but no power query creates a entire folder structure here with with something called sample query this might seem a little technical but essentially what this is is this is an example through which power query is trying to replicate the process so sample query is how it is reading one of the files and if you want to make changes you make change to the sample query and when you make the change it will be applied to all the queries all the files within that folder so for example I am only interested in traveling expenditure I can go to my my transform sample file and this is the file this is one of the files and then here I can say just we are not interested in everything we are only looking at traveling and vehicle running and maybe telephone calls and training expenditures right so we set up some arbitrary filter condition you could also add some journal reference filtering or amount basic filtering any of those kind of things so we what we did is we made a change to the behavior in one of the files that's the sample file now if you come back here you can see that immediately you know this is only showing those expenditures so earlier it had hundreds of rows nine hundred plus rows but now it only has two hundred and fifty eight because it's looking at those particular types of expense expenditure category in bringing that okay so we have done that job it will now close and load this this data will also come and sit here as a ledger table right now I'll show you how this works let's say from this ledger table you make a pivot table you know I want to do some analysis on top I will just create something very quick and simple we want to see the account description and a journal date and total amounts so this is the total amount and B we make a simple chart now one of these amounts are very high but so we create this right we can see where the money is going obviously vehicle running costs are very high and that's the money amounts in May June and July now all is good and we suddenly realize how wait a second I have August data right I quote to August and then copy and then paste this file in the folder so now the folder has 4 files not tree and then we come back to our data here and then we refresh all and see the magic happen as it refreshes it will try to bring everything what happened see if the data is coming that maybe nothing else in August oh there is ok so it is there it's just maybe the Refresh was not true yep so as you refresh I think you may need to have refreshed twice or wait you can see what happened in August as well so without even making any changes to any of the calculation or pivot or the power query as you add new files same rules will apply on that and it will bring the new data so this is how you can combine the data this is the same this is another problem that people especially in finance or many other domains face right you have data but it is kind of scattered all over the place and we would like to bring everything stitch them together but if you make copies of the data in a spreadsheet like copy-paste the data and do it then it's manual work again next month or next week or next year you would have to repeat that whereas through this method you will leave the files as they are as long as they follow some sort of a meaningful pattern we would be able to combine them let me just check if there is any interesting questions coming up Krishna is asking is it possible to write sequel and extract data from database or multiple databases of course yes you can there are different types of connections as someone has replied you can have from from file from database or you know there are other sources and you would simply write a blank query or or an Ori see connection through which you can write the queries right if you are connecting to an established database like a sequel server or something you will have an option to either connect to a table or a model or you can just say let me do the query myself and then you can connect unfortunately I do not have any databases installed on this computer so I cannot demonstrate that but it is possible I have built some solutions like that where we can take the beautiful thing is not only you can connect to a database through a query but you can bring the data through sequel or something as a query and then combine that with something else so this this is another common pattern often some of the data is in in sequel some of the data is in ASAP some of the data is in SharePoint in it to do our work we need to merge everything together through joins if everything is in one place we could write sequel and and apply some joins to connect the data but what if your data is in two different systems that are not compatible well you could use power query you could connect table one from this system table two from that system bring both of them and within power query you can you can tell join these two tables which is what we are going to do next term reconciling sent versus paid invoices so for the reconciliation example I have two tables one table with sent invoices invoice number client name and Emma and another table paid invoices where I have my invoice number and how much amount is paid as with real life there are some twists here just to keep things simple I have shown them in Excel tables but this approach and technique works just the same whether you are connecting to two different tables in Excel or twos files or two different database queries so what happens is we have an invoice for example for three hundred and ninety five dollars seventy cents here it could so happen that that invoice is paid but maybe the amount is different so there is an amount mismatch there is also an amount exact match scenario or two and sometimes there would be duplicate records that means same invoice number number up twice either either because this system is inconsistent or there is a human error or the client themselves genuinely overpaid whatever may be the case but we have a combination like this two different tables what is sent what is paid and we would like to reconcile both of these two and then understand clearly which invoices have been paid in full which invoices are ever having amount mismatch and which avoidance have not been paid or paid multiple times you know those kind of things so to do that what we will do is we will have to first bring these two tables to power crane so when you have data within the same spreadsheet where you are you want to do you can also bring the data to power query just select any cell inside the table and go to data from table that's what we will use so we will load this data to power query it will come and sit here nicely and if you observe from the source step it's essentially bringing a table called I NV dot sent invoices sent so this is my sent invoices right I will what we want is we would also like to bring the not just sent invoices but the paid invoices as well because once both tables are there we could then combine them on invoice ID to see what is happening with the invoices right to do that you can just say close and load but remember whenever you close and load the default behavior is Excel will create a new table in a new worksheet with the same data as you see in the last step right this is what it does but we already have this data we don't need one more table of this so this is one of the pains of working with the table that is already in the spreadsheet when you when you have a table and you load it to power query when you close and load it will create one more table which creates unnecessary duplication of data so you could use closed and load there is a little arrow then load to option and then what we want is we don't want Excel to create a table just leave it as a connection so this way it will be there in the power query but you don't see it on the screen within Excel we will do the same step for paid invoices and we now have two tables invoice paid invoice and you can see them here I'll collapse these things so in my sent and invoice paid both of these tables have annoys information and they both have invoice ID as a common parameter that means same ID here can be found there can be I'm saying some of the annoyances would not have any payment record but there is a commonality there now we are not going to discuss about what if we send the invoices but we get payments but we don't know against which in WoW is they're paying that's a bigger problem and occasionally it requires a little more engineering and math and everything which is to be honest beyond the scope for what we are trying to achieve in in this one hour but assuming you got some invoice IDs here and then invoice IDs there we would just like to merge these two tables marry them so that we can see which have been paid which have not been paid so I'll go to invoice and this is my master list all the invoices that we have and we would like to combine that with invoices that are paid so select this go to the invoice table and then from home there is a merge queries option we could merge as a new that means it will create a brand new query or we can just merge them here we'll just merge them here and it will open up your your dialogue here so table 1 is invoice sent table 2 is invoice paid and we would like to join them on on invoice ID column so this is typical similar to writing a sequel join or other kinds of things we are taking invoice ID column from there and there and merging them the usual the default behavior is it will use what is called as a left outer join it sounds technical but what it means is all the values in this table will remain if there is a matching value here you will get that if there is no matching value you'll see a null right next to that that's that's what all from first matching from second you can change the joint kind you can for example you're only interested in unpaid invoices that means there is no record of that invoice in the paid list you could use I think it's yeah rows only in first that means there is a ROI here and invoice here no matching record there right you could we could change these just have a read and within brackets there is an explanation of what it is I leave it to left outer for now because we want to keep our master list intact and just figure out what happened with each of those invoices and click OK you will get something like this it says pay but a table has been found against each row this is power queries way of saying I found a table the table could be just one row or multiple rows even if there's multiple payments against that invoice all of them will be there in the table you can select any cell in the power query especially if the cell has a table or a list select that and you can see the preview for example invoice two thousand twenty thousand two is empty that means there is no payment this one they have paid twice same amount twice this one they have paid to two hundred and twenty five dollars twenty cents but they paid two hundred and twenty seven dollars twenty cents so there is an amount mismatch here right so all sorts of things are there but we are able to see what is going on you can go ahead and expand this for example just select the column there is a bi-directional weird arrow thingy which is which says what do you want to do just expand and we can expand we we don't need to see the same invoice number again we just want to see how much amount has been paid that's the amount and you can exclude the use the original column name as prefix option because it's the same column name and it will give you this right wherever there is a null that means the payment has not been made wherever there is an amount match that means it has been paid in full and whenever there is an amount amount difference that means they have paid either partially or overpaid so we can do that only problem with this approach is there are some duplications right some invoices have multiple values right so what what to do in such case this is just to show some real-life variations I have implemented at this point you could take a call for example you you your scenario is the duplication is happening because of a human error right they are not paying duplicates it's just someone is typing in the payments received so they might have made a copy pasted in an error so in such case what we could do is we could go to invoice pay table and then before doing anything else here we could remove the duplicate so right click on invoice number and then say remove duplicates any invoice ID that is duplicated just get rid of that so this way the human error problem will not be there and this query remember any merges that you do will happen on the final step so it they this merge is looking at the latest version of invoice paid it would not have any duplicates and it will show like this so let's say this is the case and you know is paid amount and amount are there and now I can go ahead and tag these invoices I can go and add a column we can add a conditional column the conditional column is status if amount equals paid amount then we want the status as made in full this amount is less than paid amount that means we asked them 500 and they paid 700 so this amount is less than paid amount this is overpaid if amount is greater than then this is underpaid if all these conditions are not true that means it's it's null so we could simply say not paid we'll check this because I'm not really sure how this comparison will work with null but if it doesn't work then we will have to change the order of these rules so if we get these errors fixed because we are trying to compare three 75.5 with null and null is not not technical value so we are getting some errors unless there is a paid in full so we could go ahead and change the order of these rules we can double click on that little cog icon and and then we can say it to power query know first check if it is null if it is null that means it's not paid and then apply these conditions so we could go and say if invoice paid amount equals null and the output we want is not paid then we check less than greater than conditions and then hopefully that that will work there may not be any errors yeah but it looks like there is no error and we now have a clarity on all the invoices at this point if for your work if you are interested only in unpaid invoices you could even have a filter here but if you are only interested in unpaid then you could just use a left hand I join and get that information directly rather than doing all of these and once this is done we could go ahead and close in the Lord at this point it will it will create obviously you know the invoice end table is where our information is it created a table for invoice paid we don't want this table what you could do is right click and just delete the worksheet I sorry you don't want to believe because we want the information there just as a connection not not loading so right click on this and click on load to and then say don't give me a table just created connection there in click okay so now this connection is there and this one we will just load two as a as a table and and we get this so this is our invoice list and in the status everything is dynamic here if something changes you would go and change it in the reconciliation page for example they realize that there is something wrong with that amount it is 430 you know you can come and change this information go to the place where it is and refresh and it would it would update the status I don't really know where it is what it would update alright let's go to the next one I was just checking the comments to see if there is any any other feedback live currency exchange rate calculation this is another very useful thing especially if you are dealing with accounting or financial calculation where the information that you get is from multiple countries or multiple currency rates currency codes and you would like to fetch the exchange rate and then do some multiplication based on that you could do that right you just need a source where this information is available because I live in New Zealand I thought let me just check of where I can find a reliable information about exchange rate between New Zealand dollar and other major currencies turns out RBNZ which is Reserve Bank New Zealand publishes these rates I'll just open up this page so I can show you they they published these rates every day I think they so they published them 3 p.m. every day so the last release was on 29th of August 2018 3 p.m. and then again today in a few hours they will be publishing a new rate there is a lot of information here for example you can download historical exchange rates from 2014 to current if you want to build a date based checking like the transaction happened and what was the exchange rate as of that date you can download this file and build that conditions on it you don't even need to download look at the URL for this the URL will always be I'll just copy link and and show it to you in Excel that's the URL and if you notice the URL there is no specific information here it will always be at that link they just replace the file so you can you can make a connection to that excel file through power query data new query other sources web and and connect to that excel file on the web and bring the exchange rate information directly we will because we have been connecting to excel files already few times in this masterclass I thought I'll show you how to bring the data from a web page further down on the web page there is information that we want there is a lot of information but if you look at this let's say for this analysis you are just building against United States dollar so the rate against USD is mentioned for the past 10 days and the last column is what we want this is 0.67 0 80s is the bit that we are interested in so that's that's what we will connect to it bring to excel to do this we will just go to new query I'll just copy this URL that's where the information is so you need a source from where you can get and because it will be easy for me to show from Reserve Bank News website I am showing that whatever website you connect to it should have public access that means you know it will it will give you the information when you connect not like it will ask for a password or it will show some ads or those kind of things and then take you to the information because that way you will not be able to build a reliable connection so from web and what is the web web address the address is here that's my address and when you click OK the power query will show you this box asking how you want to connect so you can just use the anonymous connection and connect here is some clues as well if you are connecting to an internet page where your colleagues share some information you may want to use Windows authentication that way it will use your Windows network ID to decide and tell the website that this person is connecting in the web and internet sites work on that basis usually so they will let you there are also some API based on accounts and organizational accounts and things like that have a play with them but we will use anonymous because this is public site anyone can connect to it and we will connect to it and this will show a navigator page usually whenever you connect to anything in power B power query unless it is very simple you will come come across the Navigator screen where you have to make a choice what do you want so it found three different tables and it is asking you know which table should do you want me to bring document is the main table this is probably not what we want symbols and conversions for summary this is also what we want they will 0 this looks interesting I think this is what we want except the information we want these somewhere in the middle that's the information we want but we will we will go ahead and lo edit this this has the information that we need and it will show a preview now while power query is really useful and quite powerful it has some annoying niggles that I find you know if you ignore they will create unnecessary problems for you later this is because everything happens on a step by step fashion so if there is an error in one of the steps then the next steps cannot run and it will just stop refreshing so this is why one of the things that power query automatically does is it it adds a change type step every now and then I don't like this because this assumes several things for example here change it type assumes that the column names will always be 16 August 17 or 20 August that's not the case tomorrow or today afternoon whenever they release the new rates 16th August won't be there anymore 17 will come a and then 20 will go here so this change type is is a is going to create problems first so we want to delete that step anytime you don't want a step you just select the step red color tick mark click on it it's gone alright so this is the cable the data that we want is right here right essentially the data that we want is for the latest date whatever is the maximum date they have that's that's what we want that that's really the the thing that we are interested in because of this format the latest date will be the last column now if I go to my home and then I can see that there are several keep options for example I can keep the bottom row but I can't keep the leftmost column or my rightmost column that is not possible but what I can do is I can transform this table I can tell power query hey can you take this table and then transpose it that is turn it upside down like so we will transpose the table from transform click on transpose it will just change the formatting so columns will be rows rows will be columns now that it is done we can go ahead and do what we want but at this point I can see another problem which is this is the rate that we want we don't know that what date it is because we I think having the date is also important without knowing the date we wouldn't know what is the date as of which that exchange rate is true so before transposing what I want is I want to bring this date information as part of the table right that's that's one more thing that we would like to do so I can go to home and then I can say where it says use first row as headers I can say use headers as first true that will demote the headers and make the headers part of the date so now date is also in my table and then you see what happened as I demoted again it added change of type this is what I don't like he'll need to delete that and then we will transpose so now I can I have access to date as well right and then what we will do is we will go to home keep rows keep bottom one row that means we just care about the latest exchange rate we don't want what happened in the last ten days and this is what it is so that's my date and that's my exchange rate so we we won't column one and column five we don't care about other coil ups so select column one hold control and select column five this way you can multi-select things and then right click and then say remove other columns so this is my belt and that is x ust this rate exchange rate with USD and that's the table when I close and load I will now get a table inside my workbook that tells me what is the date and what is the rate right and every day you just need to come and refresh it will bring the new date if you want historical dates you could also connect to the historical rates workbook as a power query connection and bring the data and use that in your analysis so that is the last part of our our our power query tip I'll just go back to the presentation so that I can show some more information to you before taking the questions the files for this video will be available at that link I will copy and paste this link in the comments as well as the video description you just need to visit after 24 hours and you will be able to download if you just visit the Shanga dot or blog chances are it will be on the home page but if not just search and Google or on the YouTube description for that and where do you learn more about power query part where e is a very very powerful software and it is a potentially time-saving and a valuable skill to have whatever you do in power query all the things that you do can be applied to both Excel and power bi so I learn empower query will will give you 2x conference because you can do work better in Excel you can do work better in power bi so I find power learning power query to be one of the most important ways to upskill you in in in today's world so just check out gender dot org slash WP where I frequently write there are many other websites where power query information is available can pulse maintains an excellent blog there are several YouTube channels and in other information as well you could also check out on Facebook where I occasionally post some tips but most of that is against or on the blog first you could even take an online class again you just like this masterclass but I created a a comprehensive online excel training program through which I teach all aspects of Excel not just power query and show you how to get the data through power query how to analyze it how to make graphs how to make reports and everything this course is called as Excel school it takes all the four steps that are that are required for being awesome in your work that is working with data making analysis through formulas and pivot tables and creating outputs like charts dashboards and reports and publishing and making them interactive and refreshable so that's the excel school program if you are interested just visit chandu dot org slash WP and then see the excel school information there and sign up for that program if you are keen to learn more so that's it from me I will now switch back to the comments mode so that we can discuss more about any questions that you have and and talk to you [Music] [Music] I posted the link to download the files in the comments Sandeep says thank you that's for the powerful presentation thanks Andy Pam I hope rest of you also enjoyed this recording of this will be available on YouTube and on the blog so that if you want to have a recap or just skip ahead and watch portions of it you know you would have access to that I made a cup of a glass of cold coffee before start of the session and I promptly forgot about that so let me have a sip nomnom asks how to teach a two-day workshop to my co-workers on power query and wanted to see how the pros do it what would you cover well I find that teaching power query alone is rather tricky because you get the data but then and the benefits of it would not be immediately apparent if you don't see it in the full life cycle so I highly encourage that you build a rapport or some sort of a solution based on power query because that way they can see the entire journey but if I were if i i do teach power query and power pivot and other things in in two day sessions usual things that i cover or i spend a lot of time in the beginner level connecting to files and setting up rules kind of thing and then I would also teach about various types of connections like especially internal sources like a sequel server or a sharepoint page or a folder of files and modifying the sample query and making it more powerful and I would also talk about parameters and how to build functions using power query so that you could teach it a process and then that process can be repeated on different datasets through as a function I would also cover a little more detail about many of the main new features of power query especially things like an pivoting data and group buy and a little bit of em because I think two days is a really long time to have spend on power query alone and I would also talk more about differences between loading the data as a table versus leaving it as connection and how to push the connection to a data model and maybe even talk about optimizing power query process and what does it mean when you are connecting to sequel server and query folding and those kind of things well it is quite an exhaustive system and maybe even teach a little bit of em towards the end in debugging and working with some common issues but yeah ideally I would not go beyond teaching functions and then at that point I will just stop and ask teach more about building things on top of the data rather than spending more time on on data processing Anna says this thanks gendo ten great video thank you thank you everyone thank you so much for joining me I know it's not the most convenient time for everyone around the world but I've been trying to test various timings and see at what point we would have good balance between my sleep or working hours versus rest of the world and living in the corner of the world is not making it easy but I hope to be able to reach out to more audience through these sessions and and share information all right thank you everyone and if you have more questions leave them in the comments and I will monitor that as well and then there is there are comments on the YouTube and if please visit the power query for accountants page so that you could download the file and and some more resource links and everything and watch a recap of this video thank you bye

Channel: Chandoo

Views: 12,131

Rating: 4.8811879 out of 5

Keywords: chandoo, chandoo.org, Excel, spreadsheets, power query, accoutants, finance people, query, sql, web connection, how to use power query, #power query

Id: fEiEbSq6uN4

Length: 65min 19sec (3919 seconds)

Published: Wed Aug 29 2018