Dataflow - Sort order based on another column

[wvuthha]

Hi guys,

In PBI Desktop, you got to the data canvas, choose a column and you get the Column tools in the menu.

Here you can set a sort based on another column (-> Sort by column).

This is useful if you want to sort daynames after their daynumber in the week and not alphabetical.

I have created a dataflow, where I want to use this feature, too, but in dataflows, you don't have a data canvas.

Normally no problem, it is also only M. But I am not able to figure this out .

If I sort the column in "a normal way", I get this:

Table.Sort(#"Changed column type 5", {{"Aktueller Tag DEU", Order.Ascending}})

But I want to do the sort order based on the column "Tag der Woche". Could you help me here?

Best regards

Thomas

[BA_Pete · Accepted solution]

Hi Thomas,

There isn't a native way of doing this for the same reason there isn't a native way of doing it in Power Query:- because sorting by another column is a display/format function, not a data mashup/transformation function. I.e. there's absolutely no point doing this during ETL in either Dataflows or Power Query. They're designed to mashup/transform data into efficient structure, not to make it look pretty.

You're essentially wasting resources by doing anything in either Dataflows or Power Query that is not specifically focussed on getting the data into an aggregated and/or efficient structure. This even includes basic 'prettifying' functions such as Sort and Reorder Columns etc. These all just get scrambled once sent to the data model anyway as Power BI changes the sort/structure of the table for Vertipaq compression.

Pete