Insert blank columns to table in #PowerBI Desktop

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:Power BI Trick - Insert Blank Column

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…

Power BI Trick - Insert Blank Column1

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.

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

Increasae Font Size in Formula Bar3

Dangerous way… DO NOT DO THIS!

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()

Power BI Trick - Insert Blank Column5

Wherever we want a blank column in a table, we put that dummy measure.  Like screenshot below:

Power BI Trick - Insert Blank Column6

 

Then changing the displayed name from “Dummy” to “.” (dot).

Power BI Trick - Insert Blank Column7
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.

Power BI Trick - Insert Blank Column8

 

Final touch.  Turn Off horizontal grid.

Power BI Trick - Insert Blank Column9

Now, we have a table with blank columns (as visual divider) that you can sort by any column within the table with no worries. 🙂

Increasae Font Size in Formula Bar4

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.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power BI Desktop and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.