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

[Anonymous]

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

[Anonymous]

@Anonymous 

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?

[Anonymous]

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 

[dm-p]

Hi @Anonymous

The article linked by @Anonymous 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:

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.:

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

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

Month column resized to close gap

Hopefully this answers your questions.

Regards,

Daniel

[Anonymous]

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

[dm-p]

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:

Do the techique above to apply the column sort.

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

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.:

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

Daniel

[Anonymous]

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 · Accepted solution]

Hi @Anonymous,

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

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.:

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

[Anonymous]

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

what a mess

[Anonymous]

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.