Learn how to create a Calendar Table in Power BI using Power Query and M language in this step-by-step tutorial for beginners. You’ll build a proper Date Table from scratch with List.Dates, add Year, Month, Quarter, Week and Day columns, and prepare your model for time intelligence calculations.
π₯ Practice File for This Lesson:
https://excel-bonanza.kit.com/createcalendartablepowerbi
π₯ Download the FREE Power BI Course Data Files:
https://excel-bonanza.kit.com/e603d04e14
π Get the Completed NorthStar Power BI Dashboard (.PBIX):
https://excel-bonanza.kit.com/products/north-star-finished-power-bi-dashboard?step=checkout
πΌ Corporate Training & Data Analytics Consultation:
https://Excelbonanza.com/Contact
This lesson is part of my complete Power BI course for beginners, where we build the NorthStar Electronics Power BI solution step by step.
π
WHAT YOU’LL LEARN
β
Why every Power BI data model needs a proper Calendar / Date Table
β
How to create a Calendar Table in Power Query
β
How to create a Blank Query in Power BI
β
How to use the Power Query Advanced Editor
β
Introduction to the Power Query M language
β
How to use List.Dates to generate a continuous list of dates
β
How #date works in Power Query
β
How to use Duration.Days to calculate the number of dates
β
Why +1 is required when generating the complete date range
β
How #duration controls the interval between dates
β
How to convert a List into a Table
β
How to create Year and Month Number columns
β
How to create and shorten Month Name
β
How to create Quarter values such as Q1, Q2, Q3 and Q4
β
How to create Week of Year
β
How to set Monday as the first day of the week
β
How to create and shorten Day Name
β
Transform vs Add Column in Power Query
β
How to load your queries into the Power BI data model
π‘ WHY A DATE TABLE MATTERS
A dedicated Date Table is one of the most important parts of a professional Power BI data model. It gives you a continuous date range and lets you analyze data by Year, Quarter, Month, Week and Day.
It also prepares your model for DAX time intelligence calculations such as Year-to-Date, Month-to-Date, previous-period comparisons and other time-based analysis that we’ll cover later in the course.
In this lesson, we create the DimCalendar table in Power Query for the NorthStar Electronics project. The source sales data covers 2023β2025, so the Calendar Table covers complete years from January 1, 2023 through December 31, 2025.
We then create useful date attributes including Year, Month Number, Month Name, Quarter, Week of Year and Day Name before loading all our queries into the Power BI semantic model.
In the next lesson, we’ll start building relationships between the tables and continue developing our Power BI data model.
ββββββββββββββββββββββ
π COURSE RESOURCES
π₯ Practice File:
https://excel-bonanza.kit.com/createcalendartablepowerbi
π₯ FREE Course Source Data:
https://excel-bonanza.kit.com/e603d04e14
π Completed NorthStar Power BI Dashboard (.PBIX):
https://excel-bonanza.kit.com/products/north-star-finished-power-bi-dashboard?step=checkout
πΌ Training & Consultation:
https://Excelbonanza.com/Contact
βΆοΈ Complete Power BI Course Playlist:
https://youtube.com/playlist?list=PLMabY8pBxWZI&si=BLD6_jX_i4hAizOk
ββββββββββββββββββββββ
π FOLLOW EXCEL BONANZA
π Website:
https://www.ExcelBonanza.com
βΆοΈ YouTube:
https://www.youtube.com/@ExcelBonanza?sub_confirmation=1
πΌ LinkedIn β Abdelrahman Abdou:
https://www.linkedin.com/in/abdelrahman-abdou-60240150/
πΌ LinkedIn β Excel Bonanza:
https://www.linkedin.com/company/79950431
π Facebook:
https://www.facebook.com/ExcelBonanza
πΈ Instagram:
https://www.instagram.com/excelbonanza1
π΅ TikTok:
https://www.tiktok.com/@excelbonanza
βοΈ Telegram:
https://t.me/ExcelBonanza
π Twitter / X:
https://twitter.com/ExcelBonanza
ββββββββββββββββββββββ
π If this Power BI tutorial helps you, LIKE the video, SUBSCRIBE to Excel Bonanza, and SHARE the course with anyone learning Power BI, Power Query, DAX, business intelligence or data analytics.
For the best results, download the practice file and build the Calendar Table alongside me.
#PowerBI #PowerQuery #PowerBITutorial
source
