Forum Discussion
Data transformation in Direct Query
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.
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.
