Dates in column out of order

[jcbutts]

Hi,

I have a dataset with a purchase order column that is grouped and indexed by the date.  However, the dates won't show in order which gets the indexing wrong.  The data are showing with "10" coming after "1" instead of "2" coming after "1".  I tried changing the dates to text both before and after the grouping, but nothing seems to work:

Date          PO           Index

6/1/21       8671        1

6/10/21     8671        2

6/11/21     8671        3

6/2/21       8671        4

6/3/21       8671        5

6/1/21       9888        1

6/3/21       9888        2

6/1/21       1214        1

6/10/21     1214        2

6/11/21     1214        3

6/2/21       1214        4

Any thoughts?  Is there a different date format I can use so that the dates in the column are in order (and the indexing correct)?

[v-kelly-msft]

Hi  @jcbutts ,

You need to change the dates to date format,then create the rank column:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[jcbutts]

Hi,

I tried changing to dates first but it still doesn't seem to work.  I was creating an index column (what you called rank) within power query.  I see that you did it as a measure, which I also tried.  However, I get an error with the "earlier" function that states that the earlier row context doesn't exist:

Is using a measure better in this case?  If so, what am I doing wrong?

[v-kelly-msft · Accepted solution]

Hi @jcbutts ,

Earlier is a context function,it can only be used in calculated column,if you wanna create a measure ,use "MAX" instead.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[jcbutts]

I've tried both "date" and "text" and neither of them make a difference.  The dates, and regional settings, are in US format.  It recognizes the date properly....I can check in the desktop view and see that it's being interpreted correctly.  I just can't get the dates sorted properly in power query and that is causing my index values to go out of order.

[AilleryO]

Sounds really strange...

Can you tell us a little bit more...

Anyway we agree on something, regarding your goal you need your column to be Date type, to get the sorting using time scale, and not alphabetical.

If you sort (using the drop down menu on column header in Power Query) does it change something if you choose Ascending ? or Descending ? (in case you have other sorting on other columns in place ?)

Is it the same behaviour if your column type is Text and Date ?

It is a "normal" sorting order (on your screen copy) if your column is considered as Text,

but it should work fine with your column being type Date.

Let us know...

[AilleryO]

Hi,

No easy o nswer as we do not know what is your regional settings. Looks like your dates are in US format (mm/dd/yy), and it seems they are not recognised as Date by Power Query ?

If I'm right, first thing you can try (if not already done), check your data type (with the drop down menu on headers of columns), is it Date ?

If not and you can not set it as date, try using on Transform tab the button Column from example, and type in the date as they should be according to your regional settings.

Hope it helps, let us know...