In Excel, it is a common (but not necessarily a good) practice for people to add a thin blank column in a table to give a visual effect of divider. Like the screenshot below:
To insert a blank column in Excel is super easy… probably the reason of making such practice a common one, i guess. But when you try to do that in Power BI Desktop, you will find it super hard. After a little of Googling, you still have no clue how to do that.
Indeed I did not and do not recommend my colleague to do so when he asked me to replicate a report from Excel to Power BI. I said “NO” many many times. And then one day, he sent me a Power BI page that shows blank columns as divider (see below) and kindly asked me to replicate that for him…
Tricky but Dangerous
Honestly when I first saw it i was so impressed. “How could he do that?” was the question on my mind because I really didn’t know how to do that by that time. Then I opened the Power BI file that comes with that page he made.
Ohhhhhh my… another example how users can be innovative in breaking Excel’s rules.
What he did was to split one table into three… with different measures put in the tables. Followed by hiding the first column on the second and third tables by changing the field name to a dot, then adjust the column width to “hide” it. Last step was to align the three tables perfectly with a tiny gap in between. Nicely done BUT DANGEROUS.
Unlike Excel, Power BI has a cool feature to sort a table by any column by simply clicking on the header. When we split a table into three, the sorting on one table will not propagate to the other two tables. Since the three tables were aligned perfectly to give a visual effect of one table… and since the first column of the other two tables was “hidden”… if a report user unintentionally clicks on a column header, you guess what happen?
Again, a GIF tells thousand words:
See?! What a dangerous act just because we are so persistent for a blank column as divider. Worth it? NO.
Having said that, my colleague still insisted on the blank columns. Just like many other people I encountered in workplace, they don’t care if there are better alternatives for the same piece of information because (they think) their bosses are so getting used to the old “existing” report. And they don’t even try to ask most of the time because bosses are always busy. Don’t show them new things, don’t ask them to learn, just keep the same way as it is… Not his fault… just organizational behavior.
Well… thanks for listening.
Let’s go back to the topic of inserting blank column to table in Power BI Desktop. Although the innovative way by my colleague was not a proper one, his act together with his persistence, inspired me to think of a (not so perfect) solution.
Tricky and safe
I was inspired by the trick of having dot as column header.
The trick to insert blank column to table in Power BI Desktop is simple. What we need is a Dummy measure that returns blank result. Here’s the DAX formula:
Dummy = BLANK()
Wherever we want a blank column in a table, we put that dummy measure. Like screenshot below:
Then changing the displayed name from “Dummy” to “.” (dot).
Next step is to set the font and background color of the dummy field (now being renamed as “.”) to white (or the same background color of the table). Make sure you have turned On setting 5 to 7 too.
Final touch. Turn Off horizontal grid.
Now, we have a table with blank columns (as visual divider) that you can sort by any column within the table with no worries. 🙂
As simple as that.
How to you do that in Power BI Desktop? If you know an alternative way, please share by leaving comment below.
Excellent piece of work !!!!!!!!!!
LikeLiked by 1 person
Note, step 4 of Field Formatting, one still has to select white colour even though that is what is already shown to be selected for this to work 🙂
You are right. Thanks for highlighting this. 😀
This is an excellent solution to my problem. I needed to insert thick vertical lines at several but not all columns. Did it with your help, tnx a lot!
You are welcome. Glad it helps！😀