Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
danielgajohnson
Helper II
Helper II

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:

PBI Week.PNG

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?

1 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"

View solution in original post

19 REPLIES 19
danielgajohnson
Helper II
Helper II

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!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






danielgajohnson
Helper II
Helper II

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

Week Issue.PNG

Is there a fix?

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
Captura.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

@danielgajohnson Right it needs to return 201301 for December 31st, 2012 instead of 201254. Maybe ISO dates would solve it?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Week Fixed.PNG

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

edhans_0-1630705146840.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
danielgajohnson
Helper II
Helper II

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Yup, tried that. I get the same error.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors