Power Query: Using M Functions – List.Dates & Date.Add

In this video, Angelica Choo Quan, a trainer at Pragmatic Works, demonstrates how to use the List.Dates and Date.Add functions in Power Query to create and manipulate date tables in Power BI. These functions are invaluable when you need to set up calendar or date tables, enabling users to perform advanced date operations in their reports. Below, we will walk through these functions and the scenarios where they can be applied.

Creating a Simple Date List with List.Dates

The List.Dates function is used to generate a list of dates based on a specified start date, number of dates, and step value. In this example, Angelica demonstrates how to create a list of dates for the year 2022.

• Step 1: Begin by selecting Get Data in Power BI and choosing a blank query.
• Step 2: In the formula bar, type the List.Dates formula to specify the start date, number of dates (365 for the full year), and the step value (1 day increment).
• Step 3: Once the formula is invoked, the result will be a list of dates for the year 2022.

After generating the list, Angelica demonstrates how to convert it into a table and rename the query as Calendar Year 2022.

Manipulating Dates Using Date.Add

Next, Angelica explores the Date.Add function, which allows users to add or subtract months, weeks, or days from a given date. This function is especially useful for creating new date columns, such as "6 months ahead" or "6 months prior."

• Step 1: To add six months to each date in the calendar table, a custom column is created using the Date.AddMonths function. The parameter is set to 6 months.
• Step 2: The new column, named "Six Months in the Future," will display dates that are six months ahead of each corresponding date in the "Calendar Year 2022" column.
• Step 3: The same process can be applied to create a "Six Months Prior" column by subtracting six months using -6 as the parameter in the Date.AddMonths function.

This functionality allows you to dynamically generate date columns that can be used for time-based analyses or comparisons in reports.

Advanced Date Operations: Adding Weeks and Quarters

Angelica also demonstrates how to apply similar operations using weeks and quarters. For example:

• Adding One Week: To calculate a date one week prior to each date in the table, use the Date.AddWeeks function and provide -1 as the parameter.
• Adding Quarters: By using the Date.AddQuarter function, users can add or subtract quarters from a given date.

These advanced functions enable users to adjust the granularity of their time-based calculations and generate insightful reports.

Why Use These Functions?

The List.Dates and Date.Add functions in Power Query are powerful tools for creating and manipulating date tables. Whether you're working with fiscal calendars, tracking project timelines, or generating reports with specific time intervals, these functions are essential for:

• Building dynamic date tables in Power BI.
• Performing date-based calculations and comparisons.
• Enabling powerful time-based filtering and grouping in reports.

By mastering these M functions, users can create more accurate and efficient Power BI reports, driving better insights from their data.

Conclusion

In this tutorial, Angelica Choo Quan has shown how to use M functions to work with dates in Power Query, a key skill for creating date tables in Power BI. With functions like List.Dates and Date.Add, users can easily generate and manipulate date data, providing the foundation for powerful time-based reporting and analysis.

For more tutorials and advanced tips, check out the Pragmatic Works learning platform for on-demand courses and videos.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Query and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.