Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
@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?
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
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.:
Hopefully this answers your questions.
Regards,
Daniel
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Thankd dm-p my sort icon is greyed out. I am using a direct query FYI
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
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
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.
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:
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
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Thanks i did exactly that , but now in my other report i have dates and they dont sort lol
what a mess
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |