Hello and Welcome to ExactPRO ! Today I will be sharing with you a very useful tip for Pivot Tables report with the help of Power Query suppose you are dealing with muti currency reports and you want to create your pivot report showing various other currencies based on your home currency as you can see on the screen, I have multi currency report where AUD is my home currency which is Austrian Dollar, and I am able to covert AUD to other currencies like EUR, GBP, NZD and USD.
The exchange rates are like exchange rates which are pulled from website which is called www.
Com we will using Power Query to setup the data source for this pivot table.
If you have no idea what Power Query is, then I would highly suggest you go through many free tutorials and articles available on the internet and youtube would be the best resource to start with.
in short it (Power Query) is the best data cleansing and transformation tool developed by Microsoft Products, I wont be going into the basics on How to use power query but I will walk you through process of building multi currency database for our final report in the Power Query and if you are really curious and master Power Query, I highly recommend a Book called " M for data monkey" which is available for around 10$ on kindle or pdf.
links for buying this book are in the video description.
so lets get on with the process and I will show you how to create this kind of report.
so first step is to open your sample file, here I have named it sales currency data demo, Power query is not not readily available as tab as you can on my screen, I am using Excel 2010 so if you are using Excel 2010/2013, it is not part of your package, you have to download it from MS website which is FREE and if you are using Excel 2016 it is under Data tab in get and transform section so I have downloaded the Powe Query and I will links to download PQ in the video description as well so once you have downloaded the PQ, it will appear as tab, and to start with we have to link to all individual workbooks which are in Excel file format which for different different countries, the source data which we going to use are in this folder and these are for five countries data Canada, France, Germany, Mexico and USA, now we want to import all the files in the power query as single database, so these files files have individual sheets in which we are interested in, I will show you how to import them so for that we need to go to power query we go to file, import from a folder, we will be pasting the folder path and as I showed you before and click OK Power Query window will open and we need to go Edit and here is the folder details file names, the version which we are not interested only column important is Content column, we are going to select that and remove other columns now if we gonna click this, we wont get the data, because currently Power Query does not have capability to import all the records from Excel files (xlsx) as source file(s) but suppose if you have CSV, Text files, if you can import them directly but since our source files are Excel files, we are going to use custom column with a formula so I will add custom column using formula called Excel.
Workbook I am going to select Content column and click OK now the new column is added named TABLE so we dont need this content column and now we need to click on this button with double arrow like this, click expand now you can see all the workbooks we have imported have various tabs which you can see here so whatever we are interested we going to filtering that in custom.
Name column so lets select, Canada, France, Germany, Mexico and US Now column we are interested is this custom data column which has double headed arrow lik this, we we going to be selecting that and remove other columns now we are going to expanding the column because this has all the data we need and we are going to click on double headed arrow we are going to select load more and click OK now first thing we need to do is to promote the first row as a header so lets use that and since we had lots of headers, in five different files having same headers we have to filter out the duplicate header details so we are going use the filter and we will deselect the date row so that headers will not be repeated further in the data rows, now we need to transform the data, we need to change the types of data so first column is date so we are going to covert this column data to dates, and all this columns from segment, country product are text so we will covert them to text, and these two columns are financial figures so we are going to covert them into decimal number now lets unpivot these two column, because we need these two columns as unpivoted format, so we are going to select these columns so as you can see I have already selected them, right click and unpivot columns now they are unpivoted, so we are going to name this as Measure and value to AUD (home currency) now our data set is ready, now we need to add more columns for other currencies as I showed you before so we will add four more currencies, NZD, USD, EUR and GBP now to do that, first we need to create another query as live currency database linked to a currency website so to do that we will go to home now we will go to new, other sources and web in XE.
Com we have to type AUD which is are home reporting currency sign of the dollar, lets select AUD here.
and now we got the AUD versus other currencies figures here we are going to cleaning it up currencies we don't need and will be keeping currencies we want copy this particular URL and go to Power Query window and paste it here and click OK now we are not interested in document table, we are interested in Table 0 so lets click that and click OK first step would be to name the query, I would call it currency, and we will be cleaning up the data so lets promote the first row as header, just ignore 59 for a while we are going to remove it anyway.
and the row we are interested in is 1 AUD vs other currencies so we are going to filter that and now we got the AUD vs other currencies rates and we are interested in other currencies like INR Thai baht Malaysian Singaporean and BHD so let's remove his columns and lets the data types, select this , go to transform and decimal number and this is text, you can name this as well Currency now this table is ready to be loaded.
We are not going to load it as table (excel table) rather we are going to covert it as connection only lets go to home, close and closed and load to.
connection now you can see, two queries are loaded as connection: sales data now we are going to be merging sales data and currency queries, before we do that we need to bring in common lookup column because we are going to merge query and we are going to need a common lookup column which would be temporary so to do that we are going to add column, custom column and name it as helperCol.
column and the value would be "1 AUD" I will tell you in a second why I have put " 1 AUD" and click OK here now column has come, the reason why I have made this column why because we need to lookup this particular value (1 AUD) against our currency table, so we go to currency I have same text hee " 1 AUD" against that we are going to lookup all these four columns this is tempoary column we are removing it later on, so lets merge these two queries, we go home merge queries and we are going to select currency, and which are the column colum here, it is HelpCol in sales data query and Currency in currency query and click OK so this will bring it a new column with content as Table and this has our data we want, so we are going to expand it by clicking double headed arrow.
and whatever we want to bring into this report we need to click that, I dont want currency column, I only want the GBP, EUR, NZD and USD USD so as you can see all the four columns are here properly data type is converted now we can remove this helper column now we are not still done yet, we need to multiply these new imported columns with our home currency.
we will be creating four more columns, so to do that go to add column custom column, first column would be GBP, we are going to multiply it with our home currency to get the equivalent GBP values, click OK as you can see 32370 the Austrian dollars is equal to 19941 GBP, similarly we will be creating EURO Currency column multiplying with our home currency AUD so to do that add custom column and name it EURO AUD x EURO Rate Click OK and we got the equivalent EURO column for our home currency similar way for other two currencies.
so Add custom column and NZD multiply AUD with exchange rate of using NZD and click OK and last column would be USD, which would be AUD our home currency vs exchange rate of USD now let's remove these four columns which are for the exchange rates and we dont need them anymore and rename these four columns to currencies name to EUR now lets covert the data types Transform > Decimal number now we got equivalent value of other currencies compared to home currency which AUD.
so GBP, EUR, NZD and USD so lets select these five columns and unpivot them, and this attribute: should named currency and value column should be named Amount right, now our data set is ready to loaded into Excel.
so we go to home, close and load, close and load to.
since it was loaded as connection only it wont show as Excel Table, so we can edit it immediately, by right clocking this particular query and load to table click OK as you can see 7000 rows are loaded we dont need to format this value data, because we are going to create Pivot table report as this is just raw table this is database is complete flexible and dynamic so if I right click and refresh the table data the query is running in the background to get the latest exchange rates from website with this currency values so lets summaries this with a Pivot Table, we are going to click this particular button, you can quicky create Pivot table and lets create it on new worksheet are are going to hiding our Power query pane window, lets pu country into row headers, product in to row headers, measuring into column headers amount into values.
Let me fast forward this particular section of the video, where I will be formatting this table according to the way I want, so bear with me for a while now formatted my pivot table the way I want to look at ! now I will show the trick where you can put here the Slicer to change the currency display so currently it is showing all the currencies together as SUM, so the data values are completely false so we need to select a currency in which we want to see data.
so to do that we need to go to option and insert slicer in selecting currency click OK lets modify this quickly we have total five currencies and lets increase the width like this and lets remove the header which I dont want to see and shrink it a bit now if you click AUD, you will see AUD values, if you click EUR you will see EUR values, if you click GBP you will see GBP Values, if you click NZD you will see NZ values and USD now the beauty of this report is completely automated, now if next month whenver you have more files for more countries, you have to go that root folder and paste them there even if they are excel file, just paste them there and your report will be updated automatically, and since currencies rates are refreshed every day actually every hour and every minute, you can always do that, to do that you need to go to data > Refresh All the query will run in the background and you will get the latest rates from the XE.
Com website ofcourse so this is method you can to automate your reporting in multi currency format whenver you have your home currency and you want to see comparative other currencies as well this is completely automated as I have said before, if you use Power Query you need to set it up once and you can always generate this reports all the time with the latest exchange rate, so thank you very much for watching and hope you have enjoyed this tutorial and please share it on your social media and other places whenever you think it will be useful for others also I will be coming up with new course soon free course on Udemy I already have a course on udemy called Master Depreciation Accounting with pro excel template, which is linked in the description below, you can enroll it for free, I have prepared depreciation template with SLN method, which takes care of most practical scenarios in depreciation calculations so once again thank you for watching and I'll see you in my next video.