Power Query Calendar Table - including Financial & Non-Standard Calendars

in this video we're creating a calendar table inside power query but we're not going to stop there oh no no no we're going to make that thing Dynamic we're going to add Financial periods and financial years for when our financial year end is not in sync with the calendar year end and we're also going to look at non-standard calendars such as 445 or 13 4 we periods the good news is it's exactly the same whether we're working in Excel or pobi we've got a lot to get through so for our power query and let's get started with starting with a blank query so from the home ribbon click new source then other sources then blank query now let's rename this query to calendar not only are we starting with a blank query but we're going to open the advanced editor and write some of the code ourselves don't worry you stick with me we've got this so from the home ribbon click Advanced editor now let's write our code we want to start with the let keyword and then we want to declare what our start date is so we will enter start date equals then using the date function we can use the year the month and then the day to create a date we'll then add a comma and then create a new line for our end date for which we will also use the date function and we're going to enter 2024 12 and then 31 we'll enter a comma at the end of that line and now for the magic we can now create our date list list so we'll say that date list equals and we're going to use the list. dates function the first argument of list. dat is the start date will we already have that that was our start date step the second argument is the count this is the number of items that we want in our date list for this we're going to use the duration. days function if we enter the end date minus the start date that will give us the number of days in that period but we also need to add one to make sure that we include the end date in that calculation the final argument is the step this is how long each item in the list should last for this we're going to use the duration function this accepts the number of days the number of hours the number of minutes and the number of seconds therefore if we want one day we'll enter 1 comma 0 comma 0 comma Z right we've now written all of our code we just need to make sure that we return our date list so we will replace source with date list and then click done and we've got our dates as a list let's convert that into a table by clicking to table and then in the dialog box we can click okay we've now got a table let's rename the column from column one to date and change it to a date data type now we can add add any useful columns that we like so let's suggest we want to add a year column with the date column selected we'll click add column and then in the date section We'll add a year column let's do that again and add a month column so we will select our date column then from the date section we can select month and that's it we can keep adding columns as we need to make sure that our calendar table is perfect for our scenario currently the dates used within the calendar table are hardcoded but let's suggest that we have some data and we always want a calendar table to be in sync with that data therefore we need to get the earliest date from that data and the latest date from that data to do that we can change our start date and our end date steps so let's click start date and then in the formula bar we can type equals list do Min Open brackets then from our transactions table we want our date column we can repeat the same thing for the end date but rather than list. Min we use list. max so we'll select end8 and enter equals list. Max then the transactions table and then the date column and now when we go back to the last step in our calendar table we can see everything still calculates correctly so when the data inside the transactions table changes our our calendar table will update accordingly for many of us our organization's Financial calendar does not start on the 1st of January it might still be calendar months but the year end might be the end of March the end of June or the end of September or any other month for this we can add the financial year and the financial period into our calendar table to do that we click add column and then custom column let's call this financial year and then the formula that we want to use is date. year so that will return the year within that we want to use date. add months so this will add a number of months to our date so if our year end is March we need to uplift three by 9 to make it the 12th month we can close those brackets and then click okay that adds the financial year column let's change this to a whole number without adding an extra step in the M code for the last bracket enter a comma and then type int 64. type this will change the data type of our column to a whole number now let's add the financial period for this we'll need another custom column and let's call this financial period and then the logic is the same as the financial year so the formula will be equals date. month so it will return a month number and in that we want to use the date. add add months based on the date and because we've said that our financial year end is in March we need to add 9 months we can close those brackets and click okay and that now adds the financial period okay let's change this into a whole number data type by adding the comma and then the int 64. type now if we scroll down that calendar table you can see that we have our financial periods and our financial years based on a March year end if you work with non-standard calendars such as a 445 or a 134e period you know how difficult calendars can become because there's always the 53 we year that happens from time to time so the way to handle this is by being explicit about our calendar and when those periods start and when those periods end I have another query called period end dates it contains the date the financial year and the financial period And this is based on a 445 calendar to use this we can head back to our calendar query and then click home merge queries the first table is our calendar table and we want the second table to be our period end dates and we want to merge based on the date colum because the start dates for each of our calendars are unlikely to be the same we need to include all the dates from both tables therefore we need to use a full outer join and then we can click okay okay we now expand that column adding the financial year and financial period but we don't need the column prefix and then we can click okay because our period end dates table only included the period end dates we see null for any date that wasn't contained in that table so all we need to do is to click transform fill and then up any of the dates from our period and dates table which were outside of our calendar table will return null in our date column so therefore we can now filter to remove those null values and now if we scroll down that calendar table we now have the financial period based on our non-standard 445 calendar and that's it that's how we can create calendar tables inside power query no matter what kind of calendar we have if this video has helped you then please let me know in the comments then after that why not subscribe and then click there for more power query goodness thanks for watching and I'll catch you next time

Channel: Excel Off The Grid

Views: 5,581

Keywords:

Id: GYajmVot1f8

Length: 8min 36sec (516 seconds)

Published: Thu Jun 27 2024