Forum Discussion
Resolver ICustom sorting a column from a Direct Query data source
The text column Planning LOB comes from a Direct Query cube connection. Sometimes Planning LOB values are added or deleted. The values appear to be listed in alphabetical order.
I would like to always sort a subset of those values in a particular order, and then let the other values sort themselves out.
However, I would like to be able to adjust the sort if a new Planning LOB value is added that I care about.
For my first attempt at this, I exported the Planning LOB column to Excel, sifted the ones I care about to the top, applied my sort to the ones I cared about and then incremented the rest of the field as appropriate. Then I re-sorted alphabetically, used "transform data" and "enter data", and pasted my updated table from Excel into the Create Table dialog. I created a one to many relationship, with 'Sort Planning LOB'[Planning LOB] filtering 'Product'[Planning LOB]. In my visuals, I used 'Sort Planning LOB'[Planning LOB] where I would originally have used 'Product'[Planning LOB], and everything showed up the way I wanted it.
Then the "Games" LOB was created. I couldn't find a way to add it to my created table and adjust the sort column to put it in the right spot.
Is there a way to do this that will take the live Planning LOB column, sort my specified values to the top, and leave everything else sorted alphabetically, without having to redo it from scratch every time a new value is added to either the column or the subset of things I care about?
Please note that I can't add columns to the Direct Query tables.
Here's the desired sorting of the ones I care about right now:
| Planning LOB | Sort |
| Surface Devices | 1 |
| Surface Accessories | 2 |
| Xbox Console | 3 |
| Xbox Accessories | 4 |
| Games | 5 |
| CnE Devices | 6 |
| PCHW | 7 |
There are 91 values total.
Hi shadowsong42 ,
- you cannot create a direct relationship between a Direct Query table and a calculated table, you can import specific tables or columns from your Direct Query source.
- Once you have your reference table in Import mode, you can create a relationship between this table and the Direct Query table.
- This relationship will allow you to sort the Direct Query column based on the values in your imported reference table.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
14 Replies
- lbendlin
Super User
Your approach with a reference table seems reasonable. Why would you be unable to update that when the new LOB came in? That will be a regular maintenance process anyway?
- shadowsong42
My temporary solution is to load a table containing only the sorting of the ones I care about. That correctly filters the ones I care about to the top and leaves the rest in alphabetical order.
This is temporary because if the list of things I care about changes, it will still require me to delete and re-create the table, the relationship, and the columns being referenced in my visuals . I could probably link an external file, but this dashboard is going to be transferred to a new owner using a different workspace, so I don't want to require a link to either my personal cloud storage or to the old workspace.
Is there another way to get this data into my data model that will let me edit the table values and rows after the fact?
v-aatheequeCommunity Support
Hi shadowsong42
Thanks for reaching out to Microsoft fabric Community Forum.- Navigate to Home > Enter Data and input your key values along with the desired sort order. This table will be integrated directly into your PBIX model, eliminating the need for any external links.
- When updates are needed, go to Transform Data > Power Query Editor, where you can directly modify the rows or values. This approach ensures your visuals and relationships remain intact without needing to recreate anything.
- Just ensure the destination is accessible to the new owner’s workspace.
If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!- shadowsong42
v-aatheeque wrote:- When updates are needed, go to Transform Data > Power Query Editor, where you can directly modify the rows or values. This approach ensures your visuals and relationships remain intact without needing to recreate anything.
How do I directly modify rows or values in Power Query Editor? If I right click on a value in the table, I can choose "replace values", which appears to be a find and replace across the whole table. But I don't see anything about adding or removing rows, or pasting new data over existing data.
