Forum Discussion
Helper IISort by Column Error
edhans what I posted earlier is working fine:
let StartDate = #date(2012, 1, 1), EndDate = #date(Date.Year(DateTime.LocalNow())+1,12,31), CurrentDate = DateTime.Date(DateTime.FixedLocalNow()), ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)), Custom1 = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Year", Int64.Type}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Start of Week", each "Week of " & Text.From(Date.StartOfWeek([Date],1))), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "YearWeekNumber", each Date.Year(Date.StartOfWeek([Date],Day.Monday))*100 + Date.WeekOfYear(Date.StartOfWeek([Date],Day.Monday))), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"YearWeekNumber", Int64.Type}}) in #"Changed Type2"
Community Championdanielgajohnson I had a feeling, I have battled this in DAX at various times. Like in Week Starting - Microsoft Power BI Community.
One fix is to use something like Sequential. Sequential - Microsoft Power BI Community
But, if you want it in Power Query, I could give it a shot but more than likely someone like mahoneypat or edhans or ImkeF could solve it in like a quarter of the time.
Helper IIGreg_Deckler the M expression I pasted above (and here) for YearWeekNumber is working and not producing any errors:
Date.Year(Date.StartOfWeek([Date],Day.Monday))*100 + Date.WeekOfYear(Date.StartOfWeek([Date],Day.Monday))- Greg_Deckler
danielgajohnson Right it needs to return 201301 for December 31st, 2012 instead of 201254. Maybe ISO dates would solve it?
- danielgajohnson
Greg_Deckler @ The YearWeekNumber column works fine with what I posted because I don't care if it's technically accurate, I just care if the YearWeekNumber value is greater than the preceding value because I'm using the "Week of 12/31/12" as an axis value, so it's the sort order I care about. It's true, in my expression 1/1/13 corresponds to YearWeekNumber 201253 and 1/7/12 to 201302, so it skips 201301, but that's fine for my use since the sort order is correct.
- Greg_Deckler
danielgajohnson OK, but you have 2 different values for "Week of 12/31/2012" so that is why you are getting your Sort by error being thrown. So you either need to have that single day be "Week of <something else>" or fix the YearWeekNumber so that it returns something the same thing for 12/31/2012 as 1/1/2013. Yes?