Forum Discussion

danielgajohnson's avatar
danielgajohnson
Helper II
4 years ago
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...
Need Help
  • danielgajohnson's avatar
    danielgajohnson
    4 years ago

    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's avatar
danielgajohnson
Helper II
4 years ago

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"
edhans's avatar
edhans
Community Champion
4 years ago

Ahh.... then please mark one or more answers as the solution. I didn't know the thread was solved.