- use Power Query and build a table (above, copied from Student sheet). This table contains a week's data, from Mon to Fri)
- I can create a calendar (monthly) using Power Query.
- How can I merge them, and expand a week's data to a month's data (excluding weekends), like a spill-over, from the beginning of the month to the end of the month, but excluding weekends?
Hi Jim,
Use this query:
let
Source = Excel.CurrentWorkbook(){[Name="Students2"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Tue", "Wed", "Thu", "Fri"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Mon", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Mon.1", "Mon.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Mon.1", type text}, {"Mon.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Mon.1", "Prefix"}, {"Mon.2", "Days"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Transform({Number.From(#date(2020,8,1))..Number.From(#date(2020,8,31))}, each Date.From(_))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", (x)=> List.RemoveNulls(List.Transform(x[Custom], each if x[Days]<1+Date.DayOfWeek(_,Day.Monday) then null else _))),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns1", "Custom.1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom.1", "Date"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Week-Day", each Text.From(Date.DayOfWeekName([Date]))),
#"Inserted Month" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Month", "Week of Month", each Date.WeekOfYear([Date]), Int64.Type)
in
#"Inserted Week of Month"
From the result of this query, build a pivot table to display the data as you want.
