Hi Erik,

Have you set the Slicer to sort using a custom list (I presume that's what you mean when you say 'I have made a list in settings').

Also, I've found it doesn't sort immediately. Try sorting it in descending order and then back to ascending order.

Mynda

Hi Mynda

Excel does not give me the option to use a custom list - only alphabetic from a - z, z - a or sorted as in the datasource.

That means the days are shown in a random order - not very logical.

I tried to select the slicer when I made the custom list, but that didn't do any difference.

Could the reason be the transformation in Power Query from a date to a weekday ? It almost seems like Excel handles it as text.

Erik

Hi Erik,

If you've already set up a custom list by going in to:

File > Options > Advanced > General 

then right click on the slicer > Slicer Settings.  Bottom left of the pop-up box is the option to use custom lists.

Regards

Phil

Hi Phil

The issue is not there - I have set up a custom list and so on, but the slicer settings does not give me the option to sort according to the custom list.

Only options are a-z, z-a or according to the order in the data source

I have tried making a simple table with manually typing a number of weekdays in a random order and a column with sales. Then inserted a pivot table and pivot chart - and Excel automatically chose to sort in the order from my custom list.

The picture "Slicers settings" is from my Power Query file, while "Slicer settings New" is from a the simple table.

My guess is, that the transformation from a date to a weekday in Power Query editor doesn't match the danish names of the weekdays. They are shown correctly, but as mentioned - Excel won't sort the correct.

As you can see, my Excel 2016 is a danish version, and the data the same. Monday is "Mandag", tuesday is "Tirsdag" in danish and so on.

Regards

Erik

Hi again Phil

I took the simple data set and used it in Power Query - same problem - Power Query sorts weeksdays ( in danish ) in alphabetical order a-z and doesn't give me the option to sort according to the custom list.

Guess this means, that the problem is more fundamental in Power Query in the danish version of Excel 2016 Office professionel - I hope you have got a workaround to solve this problem.

Regards

Erik

Hi Erik,

It shouldn't matter what the sort order is in Power Query as this is just the tool to get the data, it's once it lands in the PivotTable that the sorting becomes essential for the Slicer and that's where you can use the settings for the Slicer.

Please share your file so we can reproduce the problem. You can anonymise it if required.

Mynda

Hi Mynda and Phil

I have made a simple example - Pivot tables from data - danish and english - no problem sorting weekdays in the correct order.

But with the danish set in Power Query and transformation of dates to weekdays, the only possibilities for sorting are a-z or z-a or from data source order. None of them gives me the "right" order.

My original files is with classified data, but the issue is the same when using Power Query.

Regards

Erik

Hi Erik,

Thanks so much for sharing the file. It's much clearer now. The key information missing is that you're creating a Power Pivot PivotTable. The sorting of dates within Power Pivot requires a different approach.

For Power Pivot you need a Calendar table that stores the sort order of the week names. See attached. Note the weekday in my PivotTable is coming from the DK field in the Calendar table.

I hope that points you in the right direction.

Mynda

Hi Mynda

Thanks a lot - knowing a bit about databases, I should have known about the Calendar table.

But - tried to use the same data in a new Excel worksheet, but no luck on the sorting order.

I think I am missing your point with the red text - "Power Pivot Sorted Dates" ?

Erik

Hi Erik,

In Power Pivot you need to set the 'Sort By Column' for the weekday as the 'Number' column. This tells Excel to sort the weekday text by the numeric weekday number. You do this on the Home tab in the Power Pivot window.

Hope that answers your question, but let me know if you're still stuck.

Mynda

Hi Mynda

Guess my next online course will be your Power Pivot course 

After a lot of try and error, I finally got it - thanks a lot.

Erik

Well done, Erik! I'm sure you won't forget how to do it in future 😉