Sort values of a report data table based on date

[mcollet]

Hello everyone !

I'm new on Power BI and after several hours spent browsing the forum i decided to create my own topic. I'm doing my best to learn by myself but i'm hard-stuck on something.

Here is my situation :

I'm currently working on a project that links an Excel file from a SharePoint to a Power BI report in order to display sorted informations on a dynamic screen in a logistics warehouse. It's basically visual management and the dynamic screen works with an intergrated software (MagicInfo), so to display my tables i have to use the Public Incroporated Link generated by Power BI Online.

I have this Excel file on a SharePoint that is where we are modifying the data, it is not sorted and in order to sort it like i want i have to create a macro but i'm not allowed to do that.

 

I've imported this table on Power BI and separated the left and the right side into 2 differents queries (Table Left and Table Right)

Here are my problems :

I want to make Power BI either respect the sorting of the Excel file (by disabling the auto sort per exemple) or create my own automatic sort based on the date (from most recent to last). But...

1. I have no way to interact with the Publicated Report from the dynamic screen to sort it by myself
2. I had to create my own FORMAT on the date (= FORMAT([Date_G],"ddd dd/mm") but now the filter uses the alphabetical order

TL:DR : I need to find a way to auto-sort my tables on my report based on the date but not the formated one, and keep the same sorting while i load the incorporated link on the screen.

I hope it's clear, otherwize tell me if you need more informations.

Best regards,

M. Collet

[mcollet · Accepted solution]

Ok, i think i've found a solution but it's bit weird...

It appears that the default sort is based on the first value added to the table on a report.

So here I have added "Date" first and now it auto sorts the value in the ascent order of Date.

 

Then I tried to move the order afterward and it keeps it.

Problem solved !

I have one last question: Do you have any idea how this will evolve in next updates ?

Thanks !

[mcollet]

No and that's the point, nothing is sorted and filtered but somehow the order of the values is kinda random...

Here is an exemple of what i don't understand, i've created a new project with a new data table on excel and again it sorts it randomly ... See below :

Here is the excel table (not sorted)

And here is the table on Power BI

Help u_u

[mcollet]

Thank you both for your answers, but Power BI keeps sorting the values in a random order. I need him to sort it like that by default (on load) and not manually. Is there some sort of "on_load" commands like in the VB Editor ?

Or maybe it's because i have no key values so he tries to sort it using multiple fields ?

[jdbuchanan71]

Are you setting the sort by property on the visual?

[jdbuchanan71]

You can also make a copy of your date column and apply a custom format in the table design rather than in the measure.  Becasue it is still a date data type rather than a string from FORMAT it will sort correctly but display in the the format you want.

[DataZoe]

Hi @mcollet ,

On your new column you created to format your date, you can go to the ribbon and choose "Sort by Column" and pick the [Date_G] column. That should fix the sort order. If it gives you trouble, you may need to create = FORMAT([Date_G],"yyyyMMdd" column and then do the "Sort by Column" that instead. 

This link may help if I am not clear:
Sort by column in Power BI Desktop - Power BI | Microsoft Docs