Forum Discussion
Solved
Sort by Column Error
Hi All, I'm trying to sort a text column based off a numeric column and I'm getting the "Sort by another column" error saying it can't sort my text colum by my numeric column because it thinks the n...
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"
danielgajohnson I had the same thought as PaulDBrown but in my testing I couldn't replicate the situation with either Whole Number or Text YearkWeekNumber columns. I agree with Paul that from the image your YearWeekNumber column is Text.
I suspect that perhaps you are running into an issue with year roll-overs. So that perhaps when there are 53 weeks in a year something is going haywire. Hard to know for sure though with limited amount of data.
danielgajohnson You might want to give the "Start of week" a trim and clean just in case there's a rogue space in there somewhere
