Forum Discussion
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 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?
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"
19 Replies
Try changing the YearWeekNumber to type "Whole number" and then do the sort column by...
Yup, tried that. I get the same error.
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
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!
ahhh...that's much more elegant!
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.
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.
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))
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
