Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sort by Column missing a column option

I have a Power BI file that is pulling in data from Sql Server through a custom sql query. I have a column [Office] that is a combination of integer and text and is a text column- ex: "22 - Mankato". I have extracted the integer at the beginning into a separate column [OfficeSort], through the sql query, and have formatted it as a whole number. I wish to sort the [Office] column by the [Office Sort] column. For some reason I can't figure out, that particular column is not showing as an option in the Sort by Column list of columns.

 

Has anyone run into this? I can't seem to find an example through the forum/google of this particular issue. As far as I can tell, it doesn't have to do with the data formats of each column, as other whole number columns seem to be available in the list to sort [Office] by.

(see below)

SortByColumn.png

10 REPLIES 10
parry2k
Super User
Super User

@Anonymous do you otherwise see officesort column in your table? Make sure to refresh the table? Also sort by column selection there is scroll bar, wondering if it is positioned at the top.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thank you for your response.

 

Yes, it is showing in Power BI, in the table. That column is derived in the custom sql query so that is why it shows up at the end of the table rather than in alphabetical order.

Refreshing doesn't make it show up in the sort by column list.

It is not anywhere in the scroll bar list of columns- at the top, in the "o" section of alphabetical, or at the bottom as shown.

@Anonymous Im not fully sure why it will not show up in sort by column list? Did you hide it? Although doesn't matter

 

can you put a table visual and put both these fields int he visual and send the screen shot.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

pic of Office and OfficeSort, plus Count of OfficeSort. OfficeSort is not hidden.

SortByColumn2.png.jpg

Anonymous
Not applicable

Ok, I figured it out. I thought maybe there is some limit to the number of columns that will appear in the sort by column list, so I deleted 3 columns I didn't really need, and after that, the OfficeSort column showed up in the list.

 

So, the only thing I can think of is that there is a limit on the number of columns and I happened to hit that limit, and since it was the last column, that was the one that dropped off.

@Anonymous interesting, can you tell me how many columns you had in the table when it ws not showing? I would like to replicate it at my end to test that theory and if tht turn out the case would like to report it as a bug.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

I believe there was 282 columns when it didn't show, and 279 when it did. It is possible that I slightly miscounted.

 

Haha, and yes, i'm aware that is a ridiculous number of columns and intend to cut it down, but the users are still trying to figure out which columns they need and which they don't. Pulling crm data is always ridiculously wide tables...

 

Thanks for your help and let me know if you end up being able to replicate it.

wow that is lot of columns, will try to replicate , atleast i know the approx number.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous i tested with 290 columns and it worked, seems very odd that it didn't worked on your end. Anyhow I jusst created  sample data file with 5 rows, not sure if that make a difference. I would really like to see why it was not working for you in case you are able to share pbix file (remove any sensitive detail) before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous is it possible to share pbix file? Remove sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.