Data transformation in Direct Query

[Anonymous]

Hello everyone,

I'm encountering difficulties while attempting to sort data in Direct Query mode. Specifically, I'm looking to sort the data based on two columns: "Cohort_month_year" and "Orderdate_month_year," both of which are in the format "mmmm yyyy." I tried using the 'ORDER BY' clause in SQL, but it doesn't seem to be supported. Additionally, I'm unable to perform any transformations in the Power Query Editor. Can anyone provide suggestions or alternative approaches to achieve the desired sorting in this scenario?

Thanks in advance!

[Thejeswar]

Hi @Anonymous ,

When you try to sort the column with entries as 'mmmm yyyy', I doubt it will work as this will be a text column and text can be sorted only Alphabetically.

In this case, you should create one more column that gives the Month No. If you have it already available in the source, import it into your data model. Using this column you can sort the Months.

Although you can do this sorting in Power Query, it is better if you can do the sorting at the UI view rather than at the Power Query. Not very sure about how accurate the sortings that you applied at the Power Query will reflect in the UI View.

[Anonymous]

Hi @Thejeswar , 

as I'm using direct query mode I'm not able to do any transformations in power query

[mahenkj2]

Hi @Anonymous 

If 'Order by" clause is not allowed in sql query you are using for direct query, you can sort the multiple columns in Power query. First, connect the table with direct query (don't use order by in sql query) and then at first sort one column as PQ transformation. Then edit the code slightly by adding second column in sorting command, somthing like below:

= Table.Sort(sqltable,{{"Column1", Order.Ascending},{"Column2", Order.Descending}})

2. or Can you create a view in ssms with sorting applied as needed and direct query that sql view source? or Create an additional sql table sorted in that manner with the help of ssis or some other means (if sql view is not possible or efficient?) 

Hope it helps.

[Anonymous]

Hello @mahenkj2 ,

I have tried sorting columns in PQ, but i get errors while doing it 

[mahenkj2]

Hi @Anonymous ,

I can use these transformations well.

Connect to sql server > select table > press Ok > sort one column > edit command to add second column.

I am using May update of Power BI, but I don't think sorting might become a limitation. 

Hope it helps.