cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MatthieuJC Regular Visitor
Regular Visitor

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
Super User IV
Super User IV

Re: Sort by Column missing a column option

@MatthieuJC 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





MatthieuJC Regular Visitor
Regular Visitor

Re: Sort by Column missing a column option

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.

Super User IV
Super User IV

Re: Sort by Column missing a column option

@MatthieuJC 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





MatthieuJC Regular Visitor
Regular Visitor

Re: Sort by Column missing a column option

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

SortByColumn2.png.jpg

Super User IV
Super User IV

Re: Sort by Column missing a column option

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





MatthieuJC Regular Visitor
Regular Visitor

Re: Sort by Column missing a column option

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.

Super User IV
Super User IV

Re: Sort by Column missing a column option

@MatthieuJC 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





MatthieuJC Regular Visitor
Regular Visitor

Re: Sort by Column missing a column option

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.

Super User IV
Super User IV

Re: Sort by Column missing a column option

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors