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"
Helper II- Greg_Deckler
Community Champion
danielgajohnson 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.
- danielgajohnson
Greg_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?
PaulDBrownHere is one way (apologies since I'm not proficient enough in M to do this solely in PQ.
Add a column in PD with just the Start of Week Date: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 [Year]*100 + Date.WeekOfYear([Date],Day.Monday)), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "DateStartofweek", each Text.From(Date.StartOfWeek([Date],1))), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"DateStartofweek", type date}}) in #"Changed Type2"Now add a rank column for this field in the table, and use that to rank the "Start of week" column by
