Slicer with duplicate values after sort by column

[campelliann]

Hi there,

So I want to sort colum yearmonth by date. The thing is that yearmonth has duplicate values, so I need to temporarily concatenate the date to this column to get distinct values allowing the sort (example 2023-01-01-12-2023). After the sort is allowed I remove the concatenated date. 

The thing is that the slicers that are based on yearmonth get duplicate values, for instance 2023-01 appears 30 times or so. 

How to work around this without the need of creating a new sort table?

Many thanks!

[Arul]

@campelliann ,

Most of the time it might happen due to some additional spaces in one of the source column. Can you check the source column and perform trim & clean in Power Query Editor?

Thanks,

Arul

[campelliann]

@Arul Hi mate. I did, it's not it as the duplicates (for the same column) disappear if I sort by its own column. This is really from the sort. Perhaps my only solution is to create a year month/index table...

[visheshjain]

Hi @campelliann,

Please can you tell me, why do you want the YearMonth column sorted by date?

All the dates of any month will have the same year month and in order to sort by another column, every value in your year month column, should correspond to a single value only.

I think you need a YearMonthKey column and sort the Year Month by this key column.

Not sure why you are getting multiple year month values in your slicer though.

What am I missing here?

Thank you,

Vishesh Jain

[campelliann]

Hi there, and thank you.

The thing is that I want the slicer to default on the previous Month - but still allowing the user to be able to change it. So in the year month column, which is text - I have some rows with "previous month"/ "current month". So this makes the current month to appear after 2023-12 for instance. So the data is something like, 2023-05,2023-04,"Current Month", etc.

[visheshjain]

Hi @campelliann,

Apologies, I do not understand what the default slicer selection have to do with sorting of the values?

Please can you share a sample file and a desired solution for us to better understand the problem.

Thank you,

Vishesh Jain

[campelliann]

Hi there! This is the slicer. "Mes atual" = Current Month (because we are on 2023-03) it should appear after 2023-02.  
This is a way for the slicer to show as default the current month. If had 2023-03 for instance, when we change months on april, the default filter would still be on 2023-03, but this way the filter will be on april. I cannot go with the filters options of showing the current month, as this would disable the user to see previous or future months.

The problem is the sorting on the slicer, which it seems to me I only be able to solve it if i create a sort table connected to the calendar and without duplicates... A solution I am seeking to avoid...