Shulou(Shulou.com)11/24 Report--

The original title: "how is such a beautiful dynamic calendar made with Excel?" (not a PivotTable) "

Hello, everyone. I am studying the practice of calendar.

Unwittingly, a few months have passed since 2023.

As we introduced earlier, we use PivotTable to make calendars.

Also introduced the use of functions to make calendars.

However, a little friend asked, can I use PQ to make calendars?

I suddenly thought of the PQ method to make a calendar, but I don't seem to have introduced it to you.

PowerQuery (PQ) also has many date-like functions, and you can also make calendars. (it's just a little more difficult than a PivotTable, and several M functions are involved. )

Since the friends want to learn, today Xiao Shuang will take you step by step to write the M function formula to make this calendar.

1. Before making the construction data, we first construct a query table, the cell of the month, and we can use data validation to set a drop-down list in advance.

Specific steps:

❶ imports the query table into the PQ editor. Select the A1:B2 cell range, and under the data tab, click from Table / region to enter the PQ editor.

Click fx to add a new formula step. (the subsequent new steps are all here.)

Move the mouse over the step you want to change, right-click, and click rename to modify the step name. (the subsequent renaming steps are all here.)

PS: naming a good step name helps to improve the readability of the formula.

❷ added a step to get the first day of the middle-aged and month in the query table, and the name of the step is "the first day of the month".

= # date (source [year] {0}, source [month] {0}, 1)

Little Tips:

Date (year, month, day) is mainly used to construct a date.

The source [year] {0} gets the year in the table.

The source [month] {0} gets the month in the table.

❸ new step to get the last day of the year and month in the query table. The name of the step is "the last day of the month".

= Date.EndOfMonth (first day of the month)

The Date.EndOfMonth function returns the date of the last day of the month.

The ❹ new step extends the first and last day dates. The step is named month date.

= List.Transform ({Number.From (first day of the month).. Number.From (last day of the month)}, Date.From)

A simple explanation: in the M function expression, the list is represented by {parentheses}, as shown in the following figure, {1 ~ 2}, which is the list formed by 1 ~ ~ 2.

If you want to represent a list of 1 to 9, it is {1, 2, 3, 4, 5, 5, 7, 8, 9}, which can be abbreviated to {1. 9}, as shown in the following figure:

Since the nature of a date is a numerical value, we can first convert the date to a numerical value using Number.From, and then expand it. Finally, the date can be changed by using Date.From.

{Number.From (the first day of the month).. Number.From (the last day of the month)} now, we have made the date of the whole month.

Looking at the calendar, we can find that we also need to get the relevant data about the days of the date, the number of weeks, and the number of weeks per month.

So our next three steps are to get the contents of these three pieces.

❺ create a new step to get the number of days of the date. The step is named get Day.

= List.Transform (monthly date, Date.Day)

Date.Day can get the day in the date.

❻ create a new step to get the number of weeks. The step is named get the day of the week.

= List.Transform (monthly date, Date.DayOfWeekName)

Date.DayOfWeekName can get the number of weeks of the date.

❼ create a new step to get the number of weeks of the current month corresponding to the date. The step is named weeks.

= List.Transform ((month date), Date.WeekOfMonth)

Date.WeekOfMonth can get the number of weeks of the current month corresponding to the date.

So far, we have got the three pieces of data we need.

2. Turntable perspective because the calendar is a table, we also need to merge the data together to form a table.

Create a new step for ❶ and assemble it into a table. The step is named data.

= Table.FromColumns ({number of weeks to get days of weeks})

Table.FromColumns can be converted to a table by column.

The ❷ calendar is two-dimensional data, so we also need to perspective the days of the week (the Column2 column).

Select the [Colum2] column, under the "convert" tab, click "Perspective column", the pop-up window of the perspective column appears, select the [Column3] column for the value column, and click the "OK" button.

At this point, we find that the days of the week are not sorted according to the effect we want.

You only need to change the second parameter to change the order of dates.

The original formula:

= Table.Pivot data List.Distinct data [Column2]), "Column2", "Column3", List.Sum) modified formula:

= Table.Pivot (data, {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}, "Column2", "Column3", List.Sum)

Of course, if you want the date to start on Sunday, you can also change the order of the second parameter.

The last step of ❸ is to delete the Column1 column that we don't need, that is, the column that shows the number of weeks of the month.

Select the Column1 column and press the right mouse button-Delete.

Now, our date is finished.

❹ finally loads the calendar made by PQ into the worksheet and is done!

Click the File tab-[close and upload to], select existing worksheet and specify the cell location.

3. Automatic update because every time PQ changes the year and month of the query table, it needs to be refreshed, which is troublesome.

So, we can add a worksheet event to update all the tables when the values of A2 and A3 cells change. 👇👇👇

First, save the table in .xlsm format.

Then, press and hold the shortcut key [Alt+F11] to enter the VBA editor.

Under the current worksheet.

Enter this VBA code.

Private Sub Worksheet_Change (ByVal Target As Range) If Intersect ([A2:B2], Target) Is Nothing Then Exit Sub ThisWorkbook.RefreshAllEnd Sub

Because of the VBA code, we must save the file in xlsm format, otherwise we can't use it.

Now, each time you change the year and month in the query table, the calendar will automatically refresh.

4. Summary: this article mainly introduces the PQ practice of calendar, which involves the following date M functions:

❶ uses # date (year, month, day) to construct a date

❷ Date.EndOfMonth (date), you can return the date of the last day of the month

The date on which ❸ Date.Day can get the date

❹ Date.DayOfWeekName can get the week of the date

❺ Date.WeekOfMonth can get the number of weeks corresponding to the date in the current month.

There are also functions that involve table rotation (Table.FromColumns) and table perspective (Table.Pivot).

Generally speaking, the practice of PQ is similar to PivotTable in making calendar tables.

The PivotTable method is to obtain months, days, weeks and weeks as data sources through the date function, and then create a PivotTable to make a calendar table.

The PQ approach is similar, but slightly more complex than the PivotTable approach.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.