Sort Date.EndOfWeek by Date

[frankee_gee]

Hoping for some help on sorting Date.EndOfWeek by Date

excel 2016

created a calendar in power query. the final column :

= Table.AddColumn(YrMo, "WeekEnding", each Date.EndOfWeek([Date],1))

in power pivot im unable to use "sortby" column "WeekEnding" by any other column. it's data type shows up as text in power pivot.

If I modify the data type from for column "WeekEnding" from "text" to "Date" in power query and save changes.

I receive the error :
We couldn't get data from the Data Model.
Cannot create relationship from 'Table' to "Calendar' table because the 'Date' column in the 'Calendar table has duplicate values.

the only reason I'm trying to change the data type is because I figure that , that might be the problem...as I'm unable to use the "sort by" column in power pivot.

thanks in advance.

Frank.

here's my full calendar created in power query after changing the data type from Text to Date. (second to last line)

let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2014, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2014,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
YrQtr = Table.AddColumn(#"Renamed Columns2", "YrQtr", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"0")),
#"Reordered Columns1" = Table.ReorderColumns(YrQtr,{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}}),
    MoYearSort = Table.AddColumn(#"Changed Type2", "MoYearSort", each [Year]*100+[Month Number]),
    MoYr = Table.AddColumn(MoYearSort, "MoYr", each [Month Name]&"-"& Number.ToText([Short Year])),
    YrMo = Table.AddColumn(MoYr, "YrMo", each Number.ToText([Short Year])&"-"&[Month Name]),
    WeekEnding = Table.AddColumn(YrMo, "WeekEnding", each Date.EndOfWeek([Date],1)),
    #"Changed Type3" = Table.TransformColumnTypes(WeekEnding,{{"WeekEnding", type date}})
in
    #"Changed Type3"

 

[Matt Allington]

To use sort by, you must have another column with the same cardinality. E.g., if you had a column with days of week Sun through Sat, then you need another column that had values such as 1 through 7, where 1 always maps to Sunday, 2 always maps to Monday etc. If sort by column doesn't work, then the most likely reason is that your 2 columns don't have a 1 to 1 match like described above.

 

[frankee_gee]

Hey Matt - Always appreciate your help. I've decided to remove "Week Ending" column from the calendar in Power Query and create "Week Ending" column in Power Pivot instead:

WeekEnding=[Date]+7-WEEKDAY([Date],2)

. Works as expected.

Thanks Again!

Frank.