Custom sorting a column from a Direct Query data source

[shadowsong42]

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.

[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-aatheeque]

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.

[lbendlin]

That's not something you do in Power Query. You modify the data via code, not via direct edit.

[shadowsong42]

I thought I solved it by creating a calculated table using DATATABLE, but the one to many relationship I created between my new Sort Planning LOB table and the Direct Query Product table on Planning LOB doesn't seem to be working. It's marked as active in the relationship viewer, but testing the relationship with NATURALINNERJOIN('Sort Planning LOB','Product') gives me an error saying that no common join columns were detected.

Here's the calculated table:

Sort Planning LOB = 
DATATABLE(
	"Planning LOB", STRING,
	"Sort", INTEGER,
	{
		{ "Surface Devices", "1" },
		{ "Surface Accessories", "2" },
		{ "Xbox Console", "3" },
		{ "Xbox Accessories", "4" },
		{ "Games", "5" },
		{ "CnE Devices", "6" },
		{ "PCHW", "7" },
		{ "PC Hardware", "8" }
	}
)

Why isn't the relationship working?

[lbendlin]

NATURALINNERJOIN is finicky about lineage. You need to have both tables with lineage, or neither.

[shadowsong42]

Is there a way to create a relationship between a direct query table and a local table that will let me sort a direct query column by a local column?

[lbendlin]

I don't think so. But you can normalize the Direct Query column into an import mode reference (dimension) table, sort it there, and then use that column instead.

[shadowsong42]

I'm not sure how to do that. I thought the whole data source had to be either Direct Query or Import. Are you saying I can import a specific column or table out of my Direct Query data source, and the relationships between Direct Query to imported to calculated will function properly, unlike between Direct Query and my calculated table?

[v-aatheeque · Accepted solution]

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!

[v-aatheeque]

Hi @shadowsong42 ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[v-aatheeque]

Hi @shadowsong42 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @shadowsong42 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[lbendlin]

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?