Log In
Register
Home
February 20, 2019
Offline
- 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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
