Custom format all columns in Power BI desktop

We can do this manually, but the problem is that the number of columns can be too large. We need an automatic solution. This automated solution should allow us to specify custom formatting for each column. We will use the Tabular Editor for this.

Tabular Editor can be downloaded from this page:
After installing Tabular Editor, open it.

In order to be able to change the formatting of the columns we need to check option (1). This option is located in File > Preferences > Features in Tabular Editor. Next step is to attach to the PBID from the Tabular Editor. Go to File> Open> From DB (2). In the “local instance” we select our PBID file (3).  PBID file has to be already opened.

Tabular Editor will now look like this. In the left pane we will see the tables and columns from our Power BI file (1). Advanced Scripting (2) is the area where we will enter the code. After entering the code in (3), we will start the code by clicking on the green arrow (4).   

This is the code by which we will extract the names of all the columns. We will skip columns that are String typed because we do not need to adjust the format for them.

using System.IO;
var file = @"C:\Users\Sima\Desktop\Columns.csv";      //file where to export column names

using(var fileWriter = new StreamWriter(file))  
foreach(var Tbl in Model.Tables )                  
foreach(var Col in Tbl.Columns)
if ( Convert.ToString(Col.DataType) != "String" )     //exclude String columns
    fileWriter.Write( Tbl.Name + ";" + Col.Name + ";" + Col.DataType +  "\n" );

The result will be a three-column CSV file. In each row we will see the name of the table, the name of the column and the type of the column (1). We will use this information to decide the desired format for each column (2). Using the Excel formula (3), we will create a code by which we will assign the desired format to each column in the PBID file (4).

After running this code, you still need to save these changes to the BPID file itself.

Model.Tables["SampleTable"].Columns["OrderDate"].FormatString = "dd/mm/yyyy";
Model.Tables["SampleTable"].Columns["Units"].FormatString = "#,##0";
Model.Tables["SampleTable"].Columns["Unit Cost"].FormatString = "#,##0.00";
Model.Tables["SampleTable"].Columns["Total"].FormatString = "#,##0.0";
Model.Tables["SampleTable"].Columns["% of Total"].FormatString = "0.00%";

This is done by clicking on Save icon or by typing Ctrl+S.

Now all columns will have the format we specified.

PBID sample file and Tabular Editor scripts:

Leave a Comment

Your email address will not be published. Required fields are marked *