Hi, I'm trying to create a report that is Week based. So it wants to visualise the data by week number mainly.
I found this fantastic post to help with the Power Query to get ISO Weeks: http://geekswithblogs.net/darrengosbell/archive/2014/03/23/extending-the-powerquery-date-table-gener...
This gives me 'WeekEndingFriday' but the dateType is a 'Date'. I want to get Date Ending Sunday and have the time element as being the last posible micro second. Looking at the documentation Date.EndOfDay() should get me that, but when converted to a DateTime it loses it (if it ever has it): https://msdn.microsoft.com/en-us/library/mt260657.aspx
I suppose I can just use the following Monday through Add Days and use that for any Where Date is < EndOfCurrentWeek type calculations (my data range spans multiple years so I cannot simply use WeekNum).
Haven't spent quite a lot of time on it, why can't I get Date.EndOfDay to give me the correct time element when converted to DateTime. Here is the Power Query Code with the modified extra line highlighted:
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertDateTime = Table.AddColumn(RenamedColumns, "DateTime", each DateTime.From([Date])),
InsertYear = Table.AddColumn(InsertDateTime , "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEndingFriday = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertWeekEndingSunday = Table.AddColumn(InsertWeekEndingFriday, "WeekEndingSunday", each DateTime.From(Date.EndOfWeek([Date], 1)), type datetime),
InsertCurrentThursday = Table.AddColumn(InsertWeekEndingSunday, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,
InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
if [CurrentThursday] < [ISOWeekJan4]
then Date.AddDays([CurrentThursday],-3)
else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
,type date),
InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10) + [DayInWeek]),3)),
InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),
fnPeriod445a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=1,Q=1]},
{(x)=>x<9, [P=2,Q=1]},
{(x)=>x<14, [P=3,Q=1]},
{(x)=>x<18, [P=4,Q=1]},
{(x)=>x<22, [P=5,Q=2]},
{(x)=>x<27, [P=6,Q=2]},
{(x)=>x<31, [P=7,Q=3]},
{(x)=>x<35, [P=8,Q=3]},
{(x)=>x<40, [P=9,Q=3]},
{(x)=>x<44, [P=10,Q=4]},
{(x)=>x<48, [P=11,Q=4]},
{(x)=>true, [P=12,Q=4]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,
InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
in
RemovedColumns
in
CreateDateTableInsertWeekEndingSunday = Table.AddColumn(InsertWeekEndingFriday, "WeekEndingSunday", each DateTime.From(Date.EndOfWeek([Date], 1)), type datetime),
Thanks for any help!
