Sort Date.EndOfWeek by Date

frankee_gee

Board Regular
Joined
Mar 3, 2008
Messages
144
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hoping for some help on sorting Date.EndOfWeek by Date

excel 2016

created a calendar in power query. the final column :

Code:
= 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)

Code:
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"
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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:
Code:
WeekEnding=[Date]+7-WEEKDAY([Date],2)
. Works as expected.

Thanks Again!

Frank.
 
Upvote 0

Forum statistics

Threads
1,223,322
Messages
6,171,446
Members
452,404
Latest member
vivek562

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top