Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([Date]), Int64.Type), From beautiful hand-stitched designs to miniature wooden houses, reusable advent calendars will delight kids and grown-ups for years to come. Single-use advent calendars may look new and novel, but they are hard to recycle and promote unsustainable shopping habits we should be moving away from. 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. 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))

Added Custom” = Table.AddColumn(#”Fiscal Source”, “Date ISO8601”, each Date.ToText([Date], “YYYY-MM-DD”)) For more ideas for an environmentally-friendly Christmas, read our ultimate guide to a sustainable Christmas, start your eco Christmas here with us. With that in mind, then, we're off to try and choose which of the best reusable advent calendars will be gracing our home for years to come. Now the John Lewis Christmas advert 2023 has landed the countdown is well and truly on. So even if you are not a sustainability advocate you should still think about creating a special treat for your loved ones this Christmas,' she says. FAQs: What are the benefits of a reusable advent calendar?Added Custom1" = Table.AddColumn(#"Added Custom", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])) Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text), I therefore added an integer based ID column to identify each month over all time. This new column starts at 1 and increases by 1 forever. (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month(Date) Copy the Code to Reuse the Power Query calendar table

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 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. 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. Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text), 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.Let’s take a closer look at the best UK eco-friendly advent calendar options, perfect for your green Christmas. Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type),

Eco-friendly advent calendars are perfect for those of us who want to enjoy the Christmas period while also reducing our environmental footprint. In this article we’ll showcase the 7 best eco-friendly advent calendars to make your December that little bit greener. Power Query will only add the days after they are converted to a “duration”. When I made this change, it worked for me as you can see below. I added a new custom step again and entered the M function DateTime.LocalNow without any parameters. (remember, if you just type the function name, the documentation will appear.mini bars in a mix of four scrumptious flavours (Smooth, Hazelnut, Fruit & Nut, Salted Caramel Truffle) You will be able to choose every item which mean that the person receiving the gift will feel extra special 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 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).

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? As well as being something you can treasure rather than throw away after using, a fabric advent calendar allows you to choose bespoke token gifts the recipient will love. Whether favourite chocolates, festive nik naks, mini toys, stationery or even little notes with Christmas activity ideas, there is so much scope for how you can make this a personalised advent calendar. Plus, filling your own advent calendar can often be cheaper than buying one from scratch. 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).