Sort by Column Error

[danielgajohnson]

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 numeric column has more than one value for the same value in the text column. Here's the sample data:

As you can see, there aren't and should never be more than one numeric value for each text value as they're all based off the same Date.StartOfWeek column. Why am I getting this error? Any ideas?

[danielgajohnson]

Querying the PBI world has always helped break down issues to their component parts. The issue was which year the YearWeekNumber column was using as it's first variable at the end of the year. So, like above, 1/1/13 was using the current date to grab the year, when it should have been using the week start to grab the year. I rewrote that to be:

Date.Year(Date.StartOfWeek([Date],Day.Monday))*100 + Date.WeekOfYear(Date.StartOfWeek([Date],Day.Monday))

and that worked! No more error! 

Thanks all for contributing!

[PaulDBrown]

ahhh...that's much more elegant!

[danielgajohnson]

@Greg_Deckler looks like you're correct with the rollover issue:

Is there a fix?

[PaulDBrown]

Here 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

[Greg_Deckler]

@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?

[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?

[danielgajohnson]

Yup, so:

Start of Week = each "Week of " & Text.From(Date.StartOfWeek([Date],1)))

then,

YearWeekNumber = each Date.Year(Date.StartOfWeek([Date],Day.Monday))*100 + Date.WeekOfYear(Date.StartOfWeek([Date],Day.Monday)))

So, both columns are calculating their values based off of the same Date.StartOfWeek([Date],Day.Monday), therefore, it won't spit out any YearWeekNumber values that don't correspond to a Start of Week value, meaning no more error.

[danielgajohnson]

[edhans]

@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
    AddSortColumn

Start Of Week successfully sorts by SortColumn.

[danielgajohnson · Accepted solution]

@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]

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

[danielgajohnson]

Hi All,

Thanks for the replies. I can't share links for compliance reasons, but here's the M code you can copy and paste into the advanced editor from a blank query:

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))
in
    #"Added Custom3"

There error still shows up when I try just that.

[Greg_Deckler]

@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.

[PaulDBrown]

@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 

[PaulDBrown]

Try changing the YearWeekNumber to type  "Whole number" and then do the sort column by...

[danielgajohnson]

Yup, tried that. I get the same error.