calendar showing data

Helper II

5 years ago

https://www.youtube.com/watch?v=smcYYMsZcco&t=487s explains how to get a one month calendar, I still wonder how to make it return a one year calender? But here is the M-code for a one month calendar:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Column1]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Start of Month]), type date),
#"Inserted Day" = Table.AddColumn(#"Inserted End of Month", "Day", each Date.Day([End of Month]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Day", "date", each List.Dates([Start of Month],[Day],#duration(1,0,0,0))),
#"Expanded date" = Table.ExpandListColumn(#"Added Custom", "date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}}),
#"Inserted Day1" = Table.AddColumn(#"Changed Type1", "Day.1", each Date.Day([date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day1", "Day Name", each Date.DayOfWeekName([date]), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Day Name", {{"Day Name", each Text.Start(_, 3), type text}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each ([Day Name] = "Sun")),
#"Day 1" = #"Filtered Rows"{0}[Day.1],
Custom1 = #"Extracted First Characters",
#"Inserted Integer-Division" = Table.AddColumn(Custom1, "Integer-Division", each Number.IntegerDivide([Day.1]+(7-#"Day 1"), 7), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Column1",

"Start of Month", "End of Month", "Day", "date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Day Name"]), "Day Name", "Day.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"})
in
#"Reordered Columns"

se2
Helper II
5 years ago
Put this range from excel in power query, I now realize that I should have preserved dates as in 1/2/21, in the above; and the date in the below table should be formated as a Date note Date + Time. The next step might be to do a join on the date fields, from both tables?
Start Date-----Step Number--Task Name--Duration
1/1/2022 12:00:00 AM 5 Midway 5
2/5/2022 12:00:00 AM 8 Near End 6
3/12/2022 12:00:00 AM 9 Final Step 10
- se2
  Helper II
  5 years ago
  Calendar with full dates
  Sun----------Mon-------Tue---------Wed--------Thu---------Fri-----------Sat
  The following produces the above with dates that can be used as the common field
  let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
  #"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Column1]), type date),
  #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Start of Month]), type date),
  #"Inserted Day" = Table.AddColumn(#"Inserted End of Month", "Day", each Date.Day([End of Month]), Int64.Type),
  #"Added Custom" = Table.AddColumn(#"Inserted Day", "date", each List.Dates([Start of Month],[Day],#duration(1,0,0,0))),
  #"Expanded date" = Table.ExpandListColumn(#"Added Custom", "date"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}}),
  #"Inserted Day1" = Table.AddColumn(#"Changed Type1", "Day.1", each Date.Day([date]), Int64.Type),
  #"Inserted Day Name" = Table.AddColumn(#"Inserted Day1", "Day Name", each Date.DayOfWeekName([date]), type text),
  #"Extracted First Characters" = Table.TransformColumns(#"Inserted Day Name", {{"Day Name", each Text.Start(_, 3), type text}}),
  #"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each ([Day Name] = "Sun")),
  #"Day 1" = #"Filtered Rows"{0}[Day.1],
  Custom1 = #"Extracted First Characters",
  #"Inserted Integer-Division" = Table.AddColumn(Custom1, "Integer-Division", each Number.IntegerDivide([Day.1]+(7-#"Day 1"), 7), Int64.Type),
  #"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Column1", "Start of Month", "End of Month", "Day"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"date", "Day.1", "Day Name", "Integer-Division"}),
  #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Day.1"}),
  #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Day Name"]), "Day Name", "date"),
  #"Reordered Columns1" = Table.ReorderColumns(#"Pivoted Column",{"Integer-Division", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}),
  #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"Integer-Division"})
  in
  #"Removed Columns2"
  - se2
    Helper II
    5 years ago
    The above produces a table of dates in this format:
    S           M         T           W        T F         S
    1/1/21 1/2/21 1/3/21 1/4/21 1/5/21 1/6/21 1/7/21
    
    The question remains, can power bi be used to iteratively check for matches between a table in the below format with one in the above format?
    
    Start Date    Step Number       Task Name      Duration
    1/1/21            5                         Midway             5

Forum Discussion

Related content

ERROR: Calendar Visual by MAQ Software showing error message

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar

Show last calendar month of data

1/1/2022 12:00:00 AM	5	Midway	5
2/5/2022 12:00:00 AM	8	Near End	6
3/12/2022 12:00:00 AM	9	Final Step	10