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 - will this work?
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)),
AddSortColumn =
Table.AddColumn(
#"Added Custom3",
"SortColumn",
each
let
varDate = Date.From(Text.AfterDelimiter([Start of Week], "of "))
in
Date.Year(varDate) * 10000 + Date.Month(varDate) * 100 + Date.Day(varDate),
Int64.Type
)
in
AddSortColumnStart Of Week successfully sorts by SortColumn.
Ahh.... then please mark one or more answers as the solution. I didn't know the thread was solved.
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"