Read the Properties of all Measures

Power BI Desktop / By / February 27, 2022

Make list of all the properties

We want to see what properties exist for measures in Power BI desktop. We'll use Tabular Editor for this. We already saw how to use tabular editor in this post. This time we will first read all possible properties for Measures. We can use code below to achieve that. Result will be written to CSV file.

using System.Reflection;
using System.IO;
var file = @"C:\Users\Sima\Desktop\MeasureProperties.csv";

using(var fileWriter = new StreamWriter(file))
foreach(var Property in typeof( Measure ).GetTypeInfo().GetProperties() )   
{    
    fileWriter.Write( Property.Name + "\n" );
}

We will get 36 properties.

AnnotationsDataCategoryDataTypeDaxObjectFullNameDaxObjectNameDaxTableName
DependsOnDescriptionDetailRowsExpressionDisplayFolderErrorMessageExpression
ExtendedPropertiesFormatStringInPerspectiveIsHiddenIsRemovedIsSimpleMeasure
IsVisibleKPILineageTagMetadataIndexModelName
NeedsValidationObjectTypeObjectTypeNameParentReferencedBySourceLineageTag
StateSynonymsTableTranslatedDescriptionsTranslatedDisplayFoldersTranslatedNames

I highlighted, with blue color, properties that return more than one value. For example "Synonyms" could have several strings. With green background color I highlighted properties that I don't know what are. We will still try to read their values. Pink color is for properties that will return objects. We already have property DaxTableName that will give us name of a table measure belongs to. For that reason pink color properties (Table, Parent, Model) are not so useful for us.

Get values for all the properties

We have one table "SampleTable" with only one column "Col1" (1). In this table we have four measures that are interdependent (3). We will mostly focus our attention to Measure2 (4). For this measure we will change some of the properties so that we have something to work with (5).

Now we can use code to read values of all of this properties. You can find this code in download files for this post. In the table below we can see result of a code for our sample table. Not all of the properties are shown. Notice the property "DependsOn". This property will list all the objects our measure is depending on. Similar property is "ReferencedBy". This property will show all the objects that reference our measure. This two properties are part of Tabular Editor and you will not find them if you use Visual Studio.

MeasurePropertyValue|MeasurePropertyValue
Measure2DataTypeInt64|Measure2Expression[Measure1] + 1
Measure2DaxObjectFullName[Measure2]|Measure2FormatString#.##0,00
Measure2DaxTableName'SampleTable'|Measure2IsHiddenFALSE
Measure2DependsOn[Measure1]|Measure2IsVisibleTRUE
Measure2DependsOn'SampleTable'|Measure2NameMeasure2
Measure2DependsOn'SampleTable'[Col1]|Measure2ReferencedBy[Measure3]
Measure2DescriptionMeasure2Description|Measure2ReferencedBy[Measure4]
Measure2DisplayFolderMeasure2DisplayFolder|Measure2SynonimsMeasure2Synonym1, Measure2Synonym2

Sample PBIX file and Tabular Editor code can be downloaded here:

Download

Connect to Power BI Desktop from Visual Studio

Power BI Desktop / By / February 22, 2022

We will connect to Power BI Desktop database from Visual Studio and read all of its Table and Column names. When we open Visual Studio, we choose to create a new project (1). As a type of project we will choose "Console App" (2). In new dialog we add a name to our project (3). Here we can choose where our project will be saved (4). Add this folder to your Antivirus program as an exception. My Antivirus program (Avast) blocked execution of a console app.

 

After we declared our project, we have to download two dll libraries. Those are "Microsoft.AnalysisServices.Core.dll" and "Microsoft.AnalysisServices.Tabular.dll". I added them to files for download, at the end of this post. We already have them on our computer if Tabular Editor is installed. If that is true, in "Program Files (x86)/Tabular Editor" folder, we have both libraries (1). Those two libraries have to be referenced in our project. In "Solution Explorer" of Visual Studio, we add both of them (2,3). Now, they are available in our project.

 

Below we can see the code of our project. In purple parts we have to enter credentials of our PBIX file, and fullpath of the file in which we will write the names of tables and columns. Orange part is for creating server object and connecting to database by using Connect method and connection string. Green part is preparation for writing to disk. Blue part is a loop which will get all names of a table and column names.

using System;
using Microsoft.AnalysisServices.Tabular;
using System.IO;

namespace TOMSamples

{
   class Program
   {
     static void Main(string[] args)
     {
       string ConnectionString = "DataSource=localhost:58064";
       using (Server server = new Server())
       {         
        server.Connect(ConnectionString);
        var OurModel = server.Databases["a02591ad-8656-4e1d-9a75-1672b223c8e6"].Model;
        string file = @"C:\Users\Sima\Desktop\TablesColumns.csv";
        using (StreamWriter fileWriter = new StreamWriter(file))
         foreach(var Tbl in OurModel.Tables)
         {
           foreach (Column Col in Tbl.Columns)
           {
             fileWriter.Write(Tbl.Name + ";" + Col.Name + "\n");
           }
         } 
       }    
     }     
   }     
}

You can find more samples of code to interact with tabular model on this address. This code is placed in Visual studio (1) and executed by clicking on the Start button (2).

Final result is CSV file with all table and column names. Interesting is that each table has column with the name "Row Number-2662979B-1795-4F74-8F37-6A1BA8059B61". This is probably primary key column used internally by Power BI Desktop.

 

PBIX sample file, dll libraries and C# code can be downloaded below.

Download

Python CSV external tool for Power BI desktop 2

Power BI Desktop / By / February 13, 2022

Old CSV tool

This is an improvement on a previously created external tool.

https://bizkapish.com/power-bi-desktop/python-csv-external-tool-for-power-bi-desktop/

 The old solution could only create a CSV file from the DAX we entered. I wanted to improve this tool.

It would be great if we could store some DAX in a PBID table, similar to saved queries. It would also be great to be able to change that stored DAX just before execution. This would give us an opportunity to parameterize our query.

New solution

So I made a new tool presented below. We open this tool with a button from ribbon tab (0). As before, we can type our DAX query (1), but this time we can also choose the name of a file (2), and CSV delimiter (3). If we don't enter name of file and CSV delimiter, default values will be used ("CSV exported.csv" and ";" delimiter). What is new is that we can select some of the stored queries from the listbox (9). This listbox will read its content from a PBID table.

One row in that PBID table has a name of a query (8) and its definition (7). Definition has DAX string (A), CSV delimiter (B) and the name of a file (C). They are all separated with three dashes (5,6). Then we can add three signs of equality (4), and then a new query (D). We can have multiple queries in one row of table (7 = A+D).
Each row from the table will become one row in Listbox (9). If we select one query from the Listbox (9), and we type some query in (1), we can then click on "Make CSV" (11) button. This will create several CSV files. One for our typed (1) DAX and several more for our stored query (7).

We can do one more thing. Before executing queries, we can change something in our stored query. This will not be permanent change, nothing will be changed in a table (7,8). First we have to right click query we want to change (9,12). New dialog will open. This new dialog will show our query (13). We have to change manually something in our query and then we click on "Remember Modified DAX" (14). This temporary change will be only saved in the listbox control. This is how we can parameterize our query.

Implementation

Implementation is almost the same as for the old tool. We just have to replace old python script with the new one. The only real difference is that we have to create a table with the name "CSVqueries" and columns "query" (name of query) and "DAX" (definition of a query). This table can be empty, but without her this external tool will not work at all.

In stored queries delimiter can be any character, but only single character. Tab sign is the only special sign that can be used. If you want to use tab sign, type "tab" as delimiter.

Sample files can be downloaded here. Your have to change fullpaths in JSON file.

Download

How to Transform (Recode) Variables in SPSS

SPSS / By / January 30, 2022

Recoding is process of transforming variable values by using some rule. Simple example would be that we want to replace quarter of the year values (Q1,Q2,Q3,Q4) with half of the year values (H1,H2).

As a result we can create new variable (as on the image above) or we can overwrite existing variable. This depends on whether we open dialog Transform > "Recode into Same Variables" or "Recode into Different Variables".

Both options will open similar dialog. The difference is that "Recode into Same Variables" will not have textboxes (3,4) and button (5) because there will be no new variable. This is the only difference so we will explain only "Recode into Different Variables" case.

First, we choose column to recode (1) and we add it to pane (2). Next, we give name and label to the new column (3,4). After that, we click on the "Change" button (5) and name of the new column will be added to pane (6). Now we have to define how new column will be created. For that we use dialogs "Old and new Values" (7) and "If…" (8).

System Missing and User Missing Values

This terms are used in dialog "Old and New Values". If we have some values missing in our data source, SPSS will present such values with a dot (1). Those are called System Missing Values.

Other possibility is for user to declare some values as invalid or impossible. This is done in "Variable view" (2). In new dialog user will enter three discrete values (3), or combination of one range and one discrete value (4). Invalid values will not be specially labeled in "Data view", but rows with such values will be excluded from any further analysis by the SPSS. This is explained in the blog post https://bizkapish.com/spss/spss-data-entry/.

Old and New Values

We want to transform quarter of year values (coded as 1,2,3,4) to half of year values (coded as 1,2). We enter code of a quarter in (1), and code of a half of year in (2). Then we click on button "Add" (3) and new transformation will appear in pane (4). We have to do this for each combination of quarter and half of year. We also have option to change the type of a result value. We can change it from number to string (7), or from string to number (8), if possible.

"Old and new values" dialog has left (5) and right (6) side. On the left side we can choose to transform specific value, missing values, range of vales, or all other values. On the right side we can choose to transform those values into specific value, missing value or just to copy old values. So, anything selected on the left side can be transformed to anything selected on the right side.

Conditional selection

We can make transformations conditional. For that we use "If…" dialog. In this dialog we use columns (1) to create expression (2). Transformation will occur only in those rows where this expression returns true. We can type expression manually, but we can also use any of the controls (3) to add building blocks in our expression. This is better explained in the post https://bizkapish.com/spss/how-to-create-new-variables-in-spss/.

In image above we typed expression "year = 1". This means that only quarters in the year 2007. will be transformed to half years. Rows for 2008. will be missing (presented by dot). If we want to fill cells for 2008. we have to create new transformation, but this transformation has to use the same name of new variable .

Let's say that we want to perform three conditional transformations. Each next transformation will overwrite cells where its condition is fullfiled. If there is overlap in some rows for two transformations, the last transformation will prevail. See the image. In the last step we change our transformation to "9", just to make a difference.

Value Labels

Last step is to associate new code values with their text labels. This is done in usual way, in "Variable view".

Sample data can be downloaded here:

Download

Discover relative path to Power BI Desktop file

Power Query / By / July 18, 2022

Power BI Desktop file is in folder with some Excel files. Those Excel files are used as a data source. If we move folder with all of this files to some other location, refreshing of PBID file will not work any more. PBID will not be able to connect to Excel files because fullpath of those Excel files will be changed. Solution is to find address of a folder where PBID file reside. That way, we will always know where our Excel files are.

When we run a PBID, a process with the name PBIDesktop.exe is created. We can find it in Details tab in Task Manager:

There is a console program named Handle64. That program can list all the files that are locked by specific process. In CMD environment we can type:

C:\Users\Sima\Desktop\handle64.exe -p PBID

This will return all files locked by PBIDesktop process:

Because our PBIX file is locked by PBIDDesktop.exe, its fullpath will be listed in there. We just have to filter this list by using name of our PBIX file and we will find location of our PBIX file. That way we will find folders where our Excel files are located, so we can create relative path.

This console program can be downloaded from the address below, but it is also available at the end of this blog post.
https://docs.microsoft.com/en-us/sysinternals/downloads/handle

All we have to do now is to call this Handle64 program from PBID, and for this we are going to use python script. For python script to work we have to tell Power BI desktop where is pythons home folder. We can do this in Options > Python scripting. If we have python installed we can select its home folder from drop down menu (1). I am using portable version of python so I have to tell PBID explicitly where my python is. I can do that with (2) Browse button.

This is our python script. Blue part will read result of CMD command and encode it in UTF-8. Green part will remove all other text, so that at the end, we only have our fullpath to return.

import pandas
import subprocess
FilesListBytes = subprocess.check_output(r'"D:\Programi\handle64.exe" -p PBID', shell = True)
FilesListString = FilesListBytes.decode('utf-8')
FirstSplit = FilesListString.split('Name of the file.pbix', 1)
SecondSplit = (FirstSplit[0].rsplit('File     ',1))[1]
ReplaceSlashes = SecondSplit.strip().replace("//","/")
df = pandas.DataFrame([[SecondSplit.strip()]],columns=['Files'])
print (df)

Name of our PBID file will not be hardcoded. We will read it from table that we prepare in advance in our PBID file.

Sample PBID file, whole Power Query script that contains python code, can be found in this attachment. For this script to work on your computer you have to change fullpath of Handle64.exe. That fullpath is in the first line of the script:
Handle64location = """C:\Users\Sima\Desktop\handle64.exe""", change it to where you placed Handle64.exe program.

Download