SwiftGlimpse Large A0 Blank Reusable One Month Wall Calendar Wet Erase Laminated Monthly Wall Planner (122cm x 91cm)

Product Information

My new code (1 above) correctly calculates the financial year (shown in 2). Note I also changed the default column name to “Fin Year” to avoid confusion with calendar year. Add Other Columns Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday)+1, Int64.Type), My approach to teaching people to use Power Query is to always use the UI where possible. I first use the UI to do the hard work, then jump in and make small changes to the code created by the UI to meet any specific variations required. Keep this concept in mind as you read this article. There is a lot less risk of being underwhelmed by the content of the calendar since you will be the one choosing everything What should you fill a reusable advent calendar with? Note: The benefit of invoking the function is that it manages all the syntax and parameters automatically for you using the UI wizard – much easier given there is currently no Intellisense capabilities in Power Query.

Added Custom1" = Table.AddColumn(#"Added Custom", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])) I simply replaced this code (3 above) with the actual date (1/1/2016 in this case), and renamed the step StartDate. Note there is no equals sign used, just the date entered as 1/1/2016. This new step called StartDate is a hard coded step in the query that stores the required start date as a scalar value. You can tell it is a scalar value because the results pane below the Formula Bar is displaying the scalar value, not a table (1 below). (Note it is possible to use a parameter instead of a custom step, but this would create an additional query – I prefer to have everything embedded in the same master query). Here is the new code. I also renamed this step to “Length”. You can see in the image below that this is now an integer value.Added Custom” = Table.AddColumn(#”Fiscal Source”, “Date ISO8601”, each Date.ToText([Date], “YYYY-MM-DD”)) The second Query (shown as 2 below) called Invoked Function has 1 line also: = Calendar(#date(2016, 1, 1), 100, #duration(1, 0, 0, 0)) To create a calendar table in power query, I’m going to start off by creating a blank query. Get Data\Other\Blank Query. This simply creates a new query with no data in it, and importantly no data source. I created columns for fiscal year, quarter, month, etc. so I assume calculating this once, as above, is more efficient than doing the calculation for every fiscal column. (I may be wrong – Microsoft could be optimising the additional recalculations away).

I added a new step using the “Add Step” button (1 below). This created a new step (2 below). Note how this new step automatically references the previous step (3). This is the default, but it doesn’t have to be this way. Be aware that the M language is case sensitive, so list.dates will not work – you must type in List.Dates I simply added a custom column with the following formula to create this column. =([Fin Year]-2000)*100 + [Fin Month] From the documentation above, I can see that there are no parameters required for this function. So I just added () to the end of the function to return the current date/time. You can see once again that this is a scalar value (not a table) because you can “see” the value below the formula bar.Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Alessoa goes on to note that choosing a reusable advent calendar over a disposable one also 'gives you an opportunity to really show the person you are gifting the calendar to that you know them well and you really care about them'. Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text), Now of course you need to “know” that this formula exists to be able to use it – but now you know, so you can use it to create your calendar table in power query. At some stage in the future we are all hoping that Microsoft will deliver Intellisense to Power Query ¹. If that ever happens then this type of coding will become a lot easier.

These eco-friendly advent calendars offer a sustainable way to countdown to Christmas while supporting ethical and environmentally conscious brands. By choosing reusable and refillable options, you can create a greener, more eco-conscious holiday season that brings joy to both you and the planet. Designed to be reused every year, wooden and fabric advent calendars make brilliant Christmas decorations. Some are shaped like Christmas trees, while others have been designed to hang along the fireplace (such as this beautiful design from Ginger Ray). Before we dive into the eco-friendly options, you might be wondering what to fill your blank, reusable, or refillable advent calendar with. The possibilities are endless! Here are some of our favourite ideas: To teach you how to create a calendar table in power query, I am going to use Power BI Desktop as the tool for this, but of course Power Query for Excel will work just as well and the process is identical. In fact the calendar query at the end can easily be cut and pasted between Power BI and Power Query for Excel. Step 1: Create a list of dates The next problem is that this is Date/Time format. I only want Date format, so I wrapped a DateTime.Date() function around the code to extract the date portion (shown below). I renamed this step Today. Each time the query is refreshed, this step will automatically update to reflect the current date.

The UI is Your Friend

Just to make things difficult, you can’t specify an end date for the calendar. Instead you have to specify how many periods (days in this case) the calendar should run for. I want my calendar to run up to and including the current date every time the calendar is refreshed. This implies I have to find out today’s date and then subtract the start date and hence calculate how many days are required in the calendar. Great. Now the user can manually change the StartDate step to any date, and the calendar will auto grow in length. Actually this code will grow up to yesterday’s date. You can simply add 1 to the Length to make it grow up to and including the current date. Now the calendar is done, it is just a matter of copying the code and saving it somewhere so it can be reused later. I keep mine in Microsoft OneNote and just cut and paste it when needed. Just go to the Advanced Editor, copy the code and paste it somewhere for later. Here is my code below. Feel free to copy mine and use it in another Power Query Calendar Table if you like. // Note there are 2 copies of the calendar table here, one for fin year, one for calendar year