cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saivenkat Member
Member

how to get rid of extra space in a table and sort months

Heloo All

I am using table in my report. Can someone please tell me how to get rid of the extra space in the box. Also how can i sort the months?

Thanks

Capture.PNG81a.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
dm-p Established Member
Established Member

Re: how to get rid of extra space in a table and sort months

Hi @saivenkat,

To hide the column, you right-click on it and select Hide in report view, e.g.:

image.png

However, if you absolutely do not want to do the process in my post above (which will work with a DirectQuery table) then your only other option is that if you can modify your query then you can add a CASE statement to create the column in there, e.g.:

SELECT  /* other columns */
            ,   CASE [Month]
                    WHEN 'Jan' THEN 1
                    WHEN 'Feb' THEN 2
                    WHEN 'Mar' THEN 3
                    WHEN 'Apr' THEN 4
                    WHEN 'May' THEN 5
                    WHEN 'Jun' THEN 6
                    WHEN 'Jul' THEN 7
                    WHEN 'Aug' THEN 8
                    WHEN 'Sep' THEN 9
                    WHEN 'Oct' THEN 10
                    WHEN 'Nov' THEN 11
                    WHEN 'Dec' THEN 12
                END as [Month Sort Order]
FROM    /* remaining SQL... */

Power BI will let you provide this as a sort column for Month in your data model, e.g.:

image.png

The above example is built using a DirectQuery table and the CASE statement example above and works fine. I have observed that sometimes the Sort by Column option is greyed out, but I found the following worked:

  • De-select any visual by selecting the canvas
  • Click back on your Month colum in the field list so that it's highlighted again
  • The Sort by Column option should be selectable and you can choose the sort order column.

Hopefully this will match your requirements. Unfortunately I cannot provide any further possibitlies after this as Power BI absolutely needs a column to sort by if the intended column has no natural sort order. ORDER BY clauses in SQL will not work.

Good luck!

Daniel

9 REPLIES 9
EmilyNomura Member
Member

Re: how to get rid of extra space in a table and sort months

@saivenkat 

You can sort the months by adding an index column value to each month in the desired order and then sorting by this column. (https://www.c-sharpcorner.com/article/sort-by-month-name-in-power-bi/)

As for the extra space in the box, do you mean the scroll bar that doesn't seem to do anything? Or the space on the rightmost side of your table?

saivenkat Member
Member

Re: how to get rid of extra space in a table and sort months

Thanks Emily, the space between the actual table and the scroll bar , doesn't look good , why we need a table enclosed in a box like look.
saivenkat Member
Member

Re: how to get rid of extra space in a table and sort months

In my table i added month number, the sort wont work unless you add the month number to the report which is not intended, is there a way we can hide it ?

simple sort cant be done by power bi another reason 

Highlighted
dm-p Established Member
Established Member

Re: how to get rid of extra space in a table and sort months

Hi @saivenkat

The article linked by @EmilyNomura is all you need. To further clarify, please do the following below.

Click on the field in the table (Month) and then click on the Modelling tab in the ribbon and then Sort By Column:

image.png

Choose the column you added for the sort.

The month will now sort by this value in your table; no need to add the sort column, e.g.:

image.png

If you want to close the gap, find the Auto-size column width property in the Column headers menu:

image.png

Set this Off and then you can resize the table column to fill the gap, e.g.:

image.pngMonth column resized to close gap

Hopefully this answers your questions.

Regards,

Daniel

saivenkat Member
Member

Re: how to get rid of extra space in a table and sort months

Thankd dm-p my sort icon is greyed out. I am using a direct query FYI

dm-p Established Member
Established Member

Re: how to get rid of extra space in a table and sort months

Ah, the DirectQuery bit is kind of an important point. If you are querying from SSAS or a Power BI data set, then you'll need to apply these changes in the source model. If you're querying a SQL DB then this method may not be possible.

Another alternative - if your report can work in mixed (import and direct) mode, then a very quick solution might be to add a table using Enter Data like this:

image.png

Do the techique above to apply the column sort.

In the modelling view, relate the Month column to your source table, e.g.:

image.png

Also, hide the column in your original table, as you'll no longer be using that.

In your table, replace the Month column you're using with the one from the table you added, e.g.:

image.png

We're pretty much at the limit of what you can do at this point, but hopefully this gives you some ideas.

Daniel

saivenkat Member
Member

Re: how to get rid of extra space in a table and sort months

Thanks dm_p. I am querying a sql server table (needs to be direct query only) in my query i cannot use ORDER BY as per powerbi. How to hide a column , i do not see a option.

dm-p Established Member
Established Member

Re: how to get rid of extra space in a table and sort months

Hi @saivenkat,

To hide the column, you right-click on it and select Hide in report view, e.g.:

image.png

However, if you absolutely do not want to do the process in my post above (which will work with a DirectQuery table) then your only other option is that if you can modify your query then you can add a CASE statement to create the column in there, e.g.:

SELECT  /* other columns */
            ,   CASE [Month]
                    WHEN 'Jan' THEN 1
                    WHEN 'Feb' THEN 2
                    WHEN 'Mar' THEN 3
                    WHEN 'Apr' THEN 4
                    WHEN 'May' THEN 5
                    WHEN 'Jun' THEN 6
                    WHEN 'Jul' THEN 7
                    WHEN 'Aug' THEN 8
                    WHEN 'Sep' THEN 9
                    WHEN 'Oct' THEN 10
                    WHEN 'Nov' THEN 11
                    WHEN 'Dec' THEN 12
                END as [Month Sort Order]
FROM    /* remaining SQL... */

Power BI will let you provide this as a sort column for Month in your data model, e.g.:

image.png

The above example is built using a DirectQuery table and the CASE statement example above and works fine. I have observed that sometimes the Sort by Column option is greyed out, but I found the following worked:

  • De-select any visual by selecting the canvas
  • Click back on your Month colum in the field list so that it's highlighted again
  • The Sort by Column option should be selectable and you can choose the sort order column.

Hopefully this will match your requirements. Unfortunately I cannot provide any further possibitlies after this as Power BI absolutely needs a column to sort by if the intended column has no natural sort order. ORDER BY clauses in SQL will not work.

Good luck!

Daniel

saivenkat Member
Member

Re: how to get rid of extra space in a table and sort months

Thanks i did exactly that , but now  in my other report i have dates and they dont sort lol

what a messCapture.PNG86.PNG

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 59 members 1,290 guests
Please welcome our newest community members: