Forum Discussion
Sorting Date Slicer in Power BI
I have created a seperate date table and joined it with the main table to resolve the issue. Thanks for all your help.
Hi,
I cannot convert it to date type since of the values is "Current"
Is there any other way to handle this scenario?
Regards
Faiz
Hi writetofaiz ,
Due to you have text value "Current" in this column, this column is in text format. Here I suggest you to add an Index column in Power Query, and then sort this column by Index column. So you need to convert the connection of the table which contains this column from Direct Query to Import. Power BI doesn't support us to add columns in Power Query in table connected by Direct Query. Or you can add an Index column in this table in SQL and load the new table into Power BI.
Firstly add an Index start by 1. Then add a custom column by If function.
New table should look like as below.
Finally sory Date column by NewRank column in Report View.
You may refer to this blog for more details.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Your solution is close to what I am looking for, but when I tried to sort, I got the below error.
As you can see in the data below, for same event date, there are multiple values in "New Rank" field.
Hi writetofaiz
Try Group By function in Power Query.
Firstly Group "All Rows" by [Date] column, sort [Date] column and then add Index column from 1. Then do same steps as above, add an custom column by If to show 0 if [Date] = "Current".
Finally, expand all rows and remove columns you don't need. Result is as below.
Or you can create a new table without duplicate values like my Table1.
You can download my sample for more details.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
In my dataset there are multiple columns.
When I grouped by Event date, I got this data and my report went for a toss.