Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Data transformation in Direct Query

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_...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Thejeswar , 

 

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

mahenkj2's avatar
mahenkj2
Solution Sage
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello mahenkj2 ,

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

     

    • mahenkj2's avatar
      mahenkj2
      Solution Sage
      3 years ago

      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.