Forum

How can I expand (o...
 
Notifications
Clear all

How can I expand (or spill over a week's calendar) to a month's calendar?

2 Posts
2 Users
0 Reactions
259 Views
(@jycccwjc)
Posts: 64
Estimable Member
Topic starter
 
  1. use Power Query and build a table (above, copied from Student sheet). This table contains a week's data, from Mon to Fri)
  2. I can create a calendar (monthly) using Power Query.
  3. 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?Spill-over.JPG
 
Posted : 28/07/2020 9:43 pm
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 29/07/2020 2:51 am
Share: