Uploaded August 2023 | Updated November 2026, 17 hours ago
CALENDAR Tables are a must if you want to analyze business data in Power BI. In this video, let me show you how I create my calendar tables out of thin air πͺ
In the video β±
============
0:00 - Why you need a calendar table in Power BI?
0:26 - Using "Blank Query" to make calendar table
2:00 - Adding useful date columns (year, month, weekday etc.)
2:28 - Is weekend column
3:00 - Adding "start of the month" calculated column
3:17 - Year month column with Custom Power Query formula
4:08 - Type of the month (this month, previous month, next month, etc.)
7:10 - Loading the calendar table to Power BI
π Sample file & code
==================
Get the full M code (Power Query steps) and more from here -
chandoo.org/wp/power-query-calendar-table-best-method
πΊ WATCH NEXT
==============
Using Power BI to make a dashboard
youtu.be/mXD0Peot4hY
How to use Power BI (10 min intro)
youtu.be/9tF1IrfLflg
Beginner to PRO Power BI Class
youtu.be/uwe8C7K8fXY
Learn Power Query in 15 minutes
youtu.be/UAFExySaSPY
How to create DAX measures
youtu.be/PQ5lALdFHCU
Star Schema in Power BI
youtu.be/1CgG0t59WcM
π₯FULL Power BI Course
chandoo.org/wp/power-bi-course
π©βπ» Power Query Script for Calendar Table
==================================
Here is the M language script. Paste this in "advanced editor" in Power Query π
~~~
let
Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
#"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
#"Removed Columns"
~~~
#powerquery #powerbi
~
Why did the Power BI developer never go past first date?
Because he lacked "date intelligence" π
CALENDAR Tables are a must if you want to analyze business data in Power BI. In this video, let me show you how I create my calendar tables out of thin air πͺ
In the video β±
============
0:00 - Why you need a calendar table in Power BI?
0:26 - Using "Blank Query" to make calendar table
2:00 - Adding useful date columns (year, month, weekday etc.)
2:28 - Is weekend column
3:00 - Adding "start of the month" calculated column
3:17 - Year month column with Custom Power Query formula
4:08 - Type of the month (this month, previous month, next month, etc.)
7:10 - Loading the calendar table to Power BI
π Sample file & code
==================
Get the full M code (Power Query steps) and more from here -
chandoo.org/wp/power-query-calendar-table-best-method
πΊ WATCH NEXT
==============
Using Power BI to make a dashboard
youtu.be/mXD0Peot4hY
How to use Power BI (10 min intro)
youtu.be/9tF1IrfLflg
Beginner to PRO Power BI Class
youtu.be/uwe8C7K8fXY
Learn Power Query in 15 minutes
youtu.be/UAFExySaSPY
How to create DAX measures
youtu.be/PQ5lALdFHCU
Star Schema in Power BI
youtu.be/1CgG0t59WcM
π₯FULL Power BI Course
chandoo.org/wp/power-bi-course
π©βπ» Power Query Script for Calendar Table
==================================
Here is the M language script. Paste this in "advanced editor" in Power Query π
~~~
let
Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
#"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
#"Removed Columns"
~~~
#powerquery #powerbi
~
Why did the Power BI developer never go past first date?
Because he lacked "date intelligence" π

![Automatically Extract BOLD Text with VBA [Adv. Excel]
It seemed impossible to do in Excel. I had to extract Bold portions of 1000s of cells as part of the data cleanup process. Then I came across a simple VBA fix. In this video, let me share the solution and expose you to the limitless possibilities of VBA in Excel. You may want to put on your socks, cause this one is going to knock them right off π§¦π
π©βπ» Get the VBA Code Solution:
Visit https://chandoo.org/wp/get-the-bold-portion-of-a-cell-in-excel/ to get the full code and understand how to use it.
πΊ In this video:
The problem: How to get Bold text values from cells automatically.
Solution: using VBA custom function
Explanation: How to write your own getBoldText function in Excel and use it
β±Video Chapters:
0:00 - The impossible problem - getting the bold text values from cells
1:00 - Creating getBoldText function yourself
5:00 - Testing the function with sample data
π» MORE VBA EXAMPLES & VIDEOS
Learn more about VBA coding and automation with Excel using these videos:
Learn VBA in 20 minutes - https://youtu.be/5k-KpSkrROw
VBA Masterclass - https://youtu.be/7hd1Nn4cNyI
VBA Playlist - https://www.youtube.com/watch?v=7hd1Nn4cNyI&list=PLmejDGrsgFyCcACC_FRUFc03XYf1Mxf9k
Full Length VBA Classes - https://chandoo.org/wp/vba-classes/
#VBA #excel #datacleaning
~ Automatically Extract BOLD Text with VBA [Adv. Excel]](https://i.ytimg.com/vi/wXJju5N4h58/mqdefault.jpg)








![How to Self-Join Tables in Power Query [Step-by-step Tutorial]
Use Power Query self-joins to solve tricky data challenges.
In this example, I share how I am using self-joins to get the prior weekly rental and prior move out date from rental agreement data with Power Query.
Sample data file: https://chandoo.org/wp/wp-content/uploads/2025/08/self-join-example.xlsx
Need Power BI Skills for yourself?
Join my Power BI course - https://chandoo.org/wp/power-bi-course/
~
A data engineer got divorced. When friends ask her what happened, she replied,
I left the join because it wasnt the right join. π
#powerbi #powerquery How to Self-Join Tables in Power Query [Step-by-step Tutorial]](https://i.ytimg.com/vi/yPTbywOkXIo/mqdefault.jpg)