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. Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 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. 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.

Added Custom” = Table.AddColumn(#”Fiscal Source”, “Date ISO8601”, each Date.ToText([Date], “YYYY-MM-DD”))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. Just by looking at the UI above (red box), you can see that there is some strange data format returned. You can probably work out this data is in the format DDD:HH:MM:SS. This “duration” format wont work in the next step I am about to show you, so first I needed to extract an integer portion of the duration so it contains just the days. I did this using a function called Duration.Days below. mini bars in a mix of four scrumptious flavours (Smooth, Hazelnut, Fruit & Nut, Salted Caramel Truffle) Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text), 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.

Note that the start date is hard coded as 1/1/2016 and the calendar is set to load 100 days. Below I will show you how to make the start date easier to maintain and the end date automatically set to the current date. Start Date There are plenty of options when it comes to filling your reusable advent calendar. You can keep it simple with sweets, chocolates, or homemade biscuits, of course, or you could pop decorations inside each pouch to add to the Christmas tree each day. You could also try wrapped gifts (small trinkets and toys work well), or even clues to festive activities that you have planned for the day ahead. The world is your oyster, quite frankly! 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),

I think the secret to make a good reusable calendar is really knowing the person you are gifting it to so you can choose things that they will definitely like,' adds Alessia. What to look for in a reusable advent calendar? Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([Date]), Int64.Type),

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. 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. Be aware that the M language is case sensitive, so list.dates will not work – you must type in List.Dates

The UI is Your Friend

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.