Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Ok so I have spent an hour searching the forum and reading, coming tantalisingly close to finding a solution but not getting there....
I have a table in my report thus:
I wish to present the data sorted by the Date column. The first issue is that it is prefixed with the day and so sorting is by the first letter of the day name rather than the date. So, it would seem I need to sort my table by a cleanly formatted date column. That's no problem, I have a date column. So in the Data view I Select my date column with days, then on the Modelling tab I selece the "Sort By Column" icon and choose my date column to sort on:
But I get the error: "Sort By Another Column Error We cannot sort the 'DateWDay' column by 'Date Cleand'. You can't have more than one value in 'Date Cleaned' for the same value in 'DateWDay'. Choose a different column for sorting or update the data in 'Date Cleaned'." I don't fully understand this because the DateWDay and Date Cleaned columns are derived from the same source (DateRaw) column. Can someone give me a clue as to how to progress this please. I presume that if I can get my data in the DateWDay column sorting by the Date Cleaned column then the table visualisation on the report should sort correctly by date.
Thanks,
Karl
Solved! Go to Solution.
I'm guessing that you "Date Cleaned" column is just the date and since your DateRaw column has date and time, then you technically have multiple values in DateRaw for a single entry in DateTime. This is just a guess because I don't see your DateCleaned column anywhere in your post. A few different ways to solve this, you could potentially translate your DateTime column into a new Duration column and then you should have a 1:1 mapping. A better way would probably be to cleanup your DateRaw column in your Power Query "M" code and let it come in as a pure Date or Date/Time column. Power BI will add all of the rest of info like weekday or you can use a simple DAX formula to get it.
I entered some test data in your DateWDay format. I was able in the Power Query editor to change the column from Text to Date/Time and it switched it successfully so that is all you should need to do in order to get it to sort correctly.
I'm guessing that you "Date Cleaned" column is just the date and since your DateRaw column has date and time, then you technically have multiple values in DateRaw for a single entry in DateTime. This is just a guess because I don't see your DateCleaned column anywhere in your post. A few different ways to solve this, you could potentially translate your DateTime column into a new Duration column and then you should have a 1:1 mapping. A better way would probably be to cleanup your DateRaw column in your Power Query "M" code and let it come in as a pure Date or Date/Time column. Power BI will add all of the rest of info like weekday or you can use a simple DAX formula to get it.
Thanks for your reply @Greg_Deckler, actually my Date Cleaned column is TimeDate:
and is just a DateTime formate of DateRaw column which is formatted as text in the source data. I'll take a look more closely at your suggestion later on.
I entered some test data in your DateWDay format. I was able in the Power Query editor to change the column from Text to Date/Time and it switched it successfully so that is all you should need to do in order to get it to sort correctly.
@Greg_Deckler thanks for that observation. Now that I have created the column in the Data view rather than the query and changed the column type to Date/Time I can sort by date correctly with the day at the start.
My next question is why does FORMAT([Date], "ddd, dd/mm/yyyy hh:mm") not result in AM/PM (12hr)? It outputs in 24hr format no matter whether I use hh:mm or HH:MM. But someone may have already asked that question so I'll dredge the forum first.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |