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 -
[ Ссылка ]
📺 WATCH NEXT
==============
Using Power BI to make a dashboard
[ Ссылка ]
How to use Power BI (10 min intro)
[ Ссылка ]
Beginner to PRO Power BI Class
[ Ссылка ]
Learn Power Query in 15 minutes
[ Ссылка ]
How to create DAX measures
[ Ссылка ]
Star Schema in Power BI
[ Ссылка ]
💥FULL 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" 😂
