Power BI Desktop

There are three kinds of formulas in Power BI desktop. We can create measures, calculated columns or calculated tables. Let's say we want to find all places where one of our columns is mentioned in formulas. For that to happen, we can export all formulas and than we can use Search to find all formulas with our column mentioned.

We'll use Tabular Editor program to extract all formulas from Power BI file. Tabular Editor can be downloaded from here:
https://github.com/TabularEditor/TabularEditor/releases/tag/2.16.1
After installing Tabular Editor, we should open it.

In order to be able to get all formulas 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).   

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

using(var fileWriter = new StreamWriter(file,false, new System.Text.UTF8Encoding (true))  )

We will loop through all of Columns and if column is calculated we will print in our file its type, name of a column and formula that is used to create column. We will remove all line breaks from formula. Semicolon sign ";" is used as delimiter in final CSV file.

foreach(var Col in Model.AllColumns)
    if ( Convert.ToString( Col.Type ) == "Calculated" )
        {
            fileWriter.Write( "CalculatedColumn" + ";" + Col.Name + ";" + (Col as CalculatedColumn).Replace("\n", "") +"\n" );
        }

Next, we will loop through all tables, and through all of their Partitions. In this context, Partition is part of calculated tables that is created by table creating formula. Again, we will write to our file type of table, its name and its expression.

foreach(var Tab in Model.Tables)
foreach(var Part in Tab.Partitions )
        if ( Convert.ToString( Part.SourceType ) == "Calculated" )
             {
                     fileWriter.Write( "CalculatedTable" + ";" + Tab.Name + ";" + Part.Expression.Replace("\n", "") +"\n" );
            }

Last part of puzzle is similar. It is used to extract formulas for measures. When we combine all building blocks we'll get our final and complete code:

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

using(var fileWriter = new StreamWriter(file,false,new System.Text.UTF8Encoding(true)) )
{
    foreach(var Col in Model.AllColumns)
        if ( Convert.ToString( Col.Type ) == "Calculated" )
            {
                fileWriter.Write( "CalculatedColumn" + ";" + Col.Name + ";" + (Col as CalculatedColumn).Expression.Replace("\n", "")  +"\n" );
            }

    foreach(var Tab in Model.Tables)
    foreach(var Part in Tab.Partitions )
        if ( Convert.ToString( Part.SourceType ) == "Calculated" )
            {
                fileWriter.Write( "CalculatedTable" + ";" + Tab.Name + ";" + Part.Expression.Replace("\n", "")  +"\n" );
            }


    foreach(var M in Model.AllMeasures)
            {
                fileWriter.Write( "Measure" + ";" + M.Name + ";" + M.Expression.Replace("\n", "")  +"\n" );
            }
}

After we run our code, we can open our CSV file in Excel. This is how it will looks like:

Here you can download sample PBIX file to test code.

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:
https://github.com/TabularEditor/TabularEditor/releases/tag/2.16.1
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:

Often, we do not want to refresh all the tables in Power Pivot or Power BI Desktop. We can choose subset of tables to refresh, both in Power Pivot and Power BI Desktop, as a permanent settings. It is also possible to select tables to refresh with mouse and only those tables will be refreshed. For Power Pivot we can use VBA to select tables to be refreshed.

Power BI Desktop

In Power BI Desktop, all you have to do is to uncheck the "Include in report refresh" option, for some of the tables, and those tables will not be part of refresh. This option is located in the contextual menu for each query, in Power Query window (1). We can see that only tables SampleTable1 and SampleTable2 will be refreshed (2) if we click on Refresh button in Ribbon. This is because we unchecked this option for SampleTable3 and SampleTable4.

Beside "Include in report refresh" option, which is permanent, we can choose which tables to refresh by selecting them in model view. Holding Ctrl key and clicking on the table headers in model view, we will select subset of tables. On any of those tables we can open contextual menu with right click. Refresh button is in this menu. Now only, tables SampleTable2 and SampleTable4 will be refreshed, because only those tables are selected.

Power Pivot and Power Query

Similar to Power BI Desktop, Power Pivot also has option to permanently disable refreshing of some tables (1). This option is placed in Properties for Queries and Connections (2). Queries are made by Power Query and for them we can control Refresh status for each query.

If the data is retreived using Power Pivot directly, without Power Query, we can only set refresh setting for each connection. This setting is valid for all tables that are loaded by using that connection. If we want to control refreshing of each table separatelly, we have to create separate connection for each of the tables. On the image bellow we can see that we have two connections made to the same Excel file. This is solution for having full control.

In Excel, we can also select tables in Diagram view (1). After this we should click on "Refresh" (not Refresh All), and only selected tables will be refreshed (3).

VBA

If name of query in Power Query is "SampleTable" then by using this line of code we can refresh only that query with VBA.

ThisWorkbook.Connections("Query - SampleTable").OLEDBConnection.Refresh

For refreshing a connection we can use this line of code:

ThisWorkbook.Connections("WorksheetConnection_New Microsoft Excel Worksheet.xlsx!SampleTable").Refresh

In this case it is important whether this query will be executed asynchronously. This article explains how to change that setting.

PQ execute queries asynchronously

Excel and Power BI Desktop sample file:

Connecting to a Power BI desktop is easy if we have credentials available. It's not hard to read credentials. If more than one Power BI desktop file is open, the question is how to connect to the desired file.

Each time a PBIX file is opened, new credentials are written to the default location. That default location is usually:
C:\Users\<Username>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
If we have more files open, more credentials will be written. In the image below we see that three PBIX files are open.

Each of these folders contains a Data subfolder. Within that subfolder are two files that contain the address and name of the database we want to link to. The database name is in the XML file name (looks like "c6fb254f-f146-464d-99cf-b2a6e322eb38.1.db.xml"), and the database address is the number found in the msmdsrv.port.txt file (looks like "58139"). After closing the file, its credentials folder will be deleted.

Each time a PBIX file is opened, its database will have different credentials. It is not easy to make a direct link between the file name and its credentials. The problem is that we don't know which of the credentials to use to link to the desired file. We can solve this problem by creating a table that contains the PBID file name in each PBID file. If we know all the credentials, we can read file names from all tables. When we find the name we want, we will know which credentials to use.

We can now use Power Query to get the necessary data. We will first read the credentials for all three PBIX files.

Next, we will read the names of the open PBIX files and filter only the desired file. That will tell us which credentials to use.