Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to create a date table in Power Query that returns the dates of the current month and the next 2 months in addition to the Week of the Year for the current month and the next 2 months, may I know how I can go about doing this please?
Thanks for the help!
Solved! Go to Solution.
@Anonymous , Try this power Query Code
let
StartDate = Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())) ,
EndDate = Date.EndOfMonth(Date.AddMonths( Date.From(DateTime.FixedLocalNow()),2)),
Source = List.Dates( StartDate,
Duration.Days( EndDate- StartDate) +1,
#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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StartOfWeek", each Date.StartOfWeek([Date],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartOfYear", each Date.StartOfYear([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date( DateTime.FixedLocalNow())),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom", each if Date.IsInCurrentMonth([Date]) then "This Month"
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "Next N month", each Date.IsInNextNMonths([Date],2)),
#"Added Custom16" = Table.AddColumn(#"Added Custom15", "FY ", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
#"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "Day of Year", each Duration.Days([Date] -[#"FY "])+1),
#"Added Custom19" = Table.AddColumn(#"Added Custom18", "Month Diff", each Number.FromText( Date.ToText( Date.AddMonths([StartOfMonth],-3),"yyyyMM") )
- Number.FromText( Date.ToText( Date.AddMonths([#"FY "],-3),"yyyyMM"))+1),
#"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fy Month", each if Date.Month([Date])>=4 then
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12),
#"Added Custom21" = Table.AddColumn(#"Added Custom20", "WeekofYear", each Date.WeekOfYear([Date])),
#"Added Custom22" = Table.AddColumn(#"Added Custom21", "YearWeek", each Date.Year([Date]) *100 +Date.WeekOfYear([Date]))
in
#"Added Custom22"
@Anonymous , Try this power Query Code
let
StartDate = Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())) ,
EndDate = Date.EndOfMonth(Date.AddMonths( Date.From(DateTime.FixedLocalNow()),2)),
Source = List.Dates( StartDate,
Duration.Days( EndDate- StartDate) +1,
#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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StartOfWeek", each Date.StartOfWeek([Date],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartOfYear", each Date.StartOfYear([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date( DateTime.FixedLocalNow())),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom", each if Date.IsInCurrentMonth([Date]) then "This Month"
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "Next N month", each Date.IsInNextNMonths([Date],2)),
#"Added Custom16" = Table.AddColumn(#"Added Custom15", "FY ", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
#"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "Day of Year", each Duration.Days([Date] -[#"FY "])+1),
#"Added Custom19" = Table.AddColumn(#"Added Custom18", "Month Diff", each Number.FromText( Date.ToText( Date.AddMonths([StartOfMonth],-3),"yyyyMM") )
- Number.FromText( Date.ToText( Date.AddMonths([#"FY "],-3),"yyyyMM"))+1),
#"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fy Month", each if Date.Month([Date])>=4 then
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12),
#"Added Custom21" = Table.AddColumn(#"Added Custom20", "WeekofYear", each Date.WeekOfYear([Date])),
#"Added Custom22" = Table.AddColumn(#"Added Custom21", "YearWeek", each Date.Year([Date]) *100 +Date.WeekOfYear([Date]))
in
#"Added Custom22"
Thanks amit! The code works well
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |