bizkapish

Outlook, reminder before sending email

Outlook / By Bizkapish / April 3, 2022 April 3, 2022

Reminder problem

This post will explain how to use VBA to get a reminder each time you try to send email with specific words in its subject. Let's say you have a mail with a subject "New products". You send this email every month. Someone informs you that, the next time when you send this email, you have to add "newmanager@company.com" to a list of recipients. Usually you would just hit "Replay All" button on the old email, make some changes, and then you would send newly made email. But, it is very hard to recall, in that specific moment, to add the new manager to recipient list. It would be great to get message like this that would remind us of a needed change.

VBA procedure

We can use VBA procedure to reminds us to make a change. In the sample files, at the end of this blog post, you will find such VBA procedure. This procedure has to be placed in the Outlook VBA environment in ThisOutlookSession. This procedure has hardcoded array that looks like this:

Array(Array("New", "products"), "Add newmanager@company.com to recipients. Click Cancel button to cancel email.")

What this array says is to look for an email that has both "New" and "products" words in its subject. Array could have as many searched words as we want. We will change this procedure by placing our searched terms in it. This is how we say to VBA procedure what are we looking for. After this first step, don't close your Outlook, we have to solve certification problem.

Certification problem

Outlook should always have macro security turned on. This, unfortunately means that our macro will be either disabled or we will get notifications each time we open Outlook. The only way out is to acquire a digital certificate. You can buy such certificate or you can create your own with "SelfCert.exe" program. It is not common to buy your own digital certificate so most people will use the one made with "SelfCert.exe". This is small program which is included in every Windows. Certificate created with it will be only valid for computer on which that certificate is created. This is enough to make our reminder VBA procedure fully operational.

We can find "SelfCert.exe" (1) in the same folder where Excel and Outlook are placed (2,3). When we find this program, we click on it and the window (3) is opened. Here, we have to give our certificate a name. After this, confirmation message is shown (4). This is how we create certificate.

Now we have to implement our certificate in Outlook. We go to VBA environment, and in Tools menu we choose "Digital Signature" option (1). New dialog will open. In it, we click on "Choose" button (2). Now, we will be offered with our previously created certificate and we will accept it (3). We return to dialog (4) again where we click on OK button.

Before closing Outlook, make sure that your macro security level is set to "Notifications for digitally signed macros, all other macros disabled".

After this, our VBA procedure is placed in correct place and it is certified. We can close Outlook now. We will get a confirmation dialog to save VBA project which we will confirm.

Sending an email

Open Outlook. Before fully open, Outlook will show us a message (1). On this message we have to click on "Trust all documents from this publisher". We have to do this only once.

Only now, can we try our VBA procedure. When we try to send email (1) with subject that has words "New" and "products", instead we will get a message box (2) with notification "Add newmanager@company.com to recipients. Click Cancel button to cancel email.".

If you click on Cancel (3), message box will close but email will not be send. Now, we can change recipient list. We can add "newmanager@company.com" to our mail (1).

After our mail is corrected, we will delete our Array from VBA procedure. Then we can send our email normally.

We can have several such arrays. Each array is a trap waiting for your email to be send. First trap to catch your email will decide what message to show to the user. If no traps catch something, that email will be send without notification.

Array( Array("New", "products"), "Add newmanager@company.com to recipients. Click Cancel button to cancel email.") _
, Array( Array("New", "and", "old"), "Add newemployee@company.com to recipients. Click Cancel button to cancel email.")

Problem with creating certificate

Sometimes we will have problem to create our certificate with "SelfCert.exe" program. We can try to delete our old certificates. Go to Run > mmc (1). In new dialog, click on File > Add/Remove Snap-in (2).

Sub dialog will then open. Select "Certificates" (3) and add them (4,5) into pane (6). Now, close sub dialog and return to the main dialog. There, click on "Certificates – Current User" (7), then on "Personal" (8), and finally on "Certificates" (9). Here, you will see your certificate and you can delete it (10).

Also, go to location "C:\Users\<yourUsername>\AppData\Roaming\Microsoft\Crypto\RSA" and there delete all of the subfolders.

Finally, reset your computer. You should now be able to create new certificate with SelfCert.exe.

You can download VBA procedure here:

Download

"Show items with no data" and DISTINCT

Power BI Desktop / By Bizkapish / March 26, 2022 March 26, 2022

Description of data

We have three tables below. Table (1) shows all products we sell. Table (2) shows our sale in first two days of a month. Note that we still didn't have sale for Product 3 in that month. In third table (3) we have month plan defined for each Salesman/Product combination.

In our model we will connect the first (1) and the second (2) table with a relation. Plan (3) will be detached table and we will use measure to get our plan values.

"Show items with no data on rows" pivot table option

If we add column Products[Product] into pivot table (1), all three products will be shown. After we add column Sales[Sale] to the same pivot table, only two products will remain (2). This is consequence of the fact that we didn't have any Product 3 sales. This behavior could be changed by enabling option "Show items with no data on rows" (3) in the pivot table options. Now, we can see that all the three products are presented (4).

Problem description

We will start with a pivot table (1) below. Option "Show items with no data on rows" is enabled. This pivot table has no Grand Total in order to make things simpler. Then, we will create measure [Target] (2) which will add Plans[Plan] values into our pivot table. This measure will use DISTINCT function. As we can see, rows 7-10 in image (3) will have no plan values, although we have them defined. The reason for this is in the expression DISTINCT( Sale[Name] ). This expression will return BLANK() for each row where [Sum of Sale] is also BLANK(). That is consequence of the fact that in Sales table there are no rows with Product/Name combinations like in rows 7-10. DISTINCT function is trying to read directly from table Sales, but there are no rows to read from.

Solution

Instead of DISTINCT function, we should have used FILTERS function. This function accepts only one column argument. It returns all of values that are filtered from that column. Let's make two simple measures "DistinctReturn:=DISTINCT( Sales[Name] )" and "FiltersReturn:=FILTERS( Sales[Name] )" to see difference between them.

DISTINCT (1) will not return anything for rows 7-10 as we saw earlier. FILTERS (2) function will return "Yuri" in cell "D7" because column Sale[Name] is filtered by only that value in that specific row. That is why column [FiltersReturn] has result in each row, because it is reading filter context for column Sales[Name], and is not reading from Sales[Name] column itself. Column Sales[Name] is reduced to nothing in cell "C7" because there are no rows with "Pr2 & Yuri" combination in the Sales table.

Now, that we know, what is the problem, we can create correct expression for Target measure. We only need one small change. We have to replace DISTINCT with FILTERS function to get correct result.

Target:=CALCULATE( SUM( Plans[Plan] ); Plans[Name] = DISTINCT( Sales[Name] )
    ; Plans[Product] = DISTINCT( Products[Product] ) )
TargetCorrected:=CALCULATE( SUM( Plans[Plan] ); Plans[Name] = FILTERS( Sales[Name] )
    ; Plans[Product] = DISTINCT( Products[Product] ) )

And finally, we have a required result:

Sample file can be downloaded here:

Download

Read the Properties of all Measures

Power BI Desktop / By Bizkapish / February 27, 2022 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.

Annotations	DataCategory	DataType	DaxObjectFullName	DaxObjectName	DaxTableName
DependsOn	Description	DetailRowsExpression	DisplayFolder	ErrorMessage	Expression
ExtendedProperties	FormatString	InPerspective	IsHidden	IsRemoved	IsSimpleMeasure
IsVisible	KPI	LineageTag	MetadataIndex	Model	Name
NeedsValidation	ObjectType	ObjectTypeName	Parent	ReferencedBy	SourceLineageTag
State	Synonyms	Table	TranslatedDescriptions	TranslatedDisplayFolders	TranslatedNames

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

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.

Measure	Property	Value	\|	Measure	Property	Value
Measure2	DataType	Int64	\|	Measure2	Expression	[Measure1] + 1
Measure2	DaxObjectFullName	[Measure2]	\|	Measure2	FormatString	#.##0,00
Measure2	DaxTableName	'SampleTable'	\|	Measure2	IsHidden	FALSE
Measure2	DependsOn	[Measure1]	\|	Measure2	IsVisible	TRUE
Measure2	DependsOn	'SampleTable'	\|	Measure2	Name	Measure2
Measure2	DependsOn	'SampleTable'[Col1]	\|	Measure2	ReferencedBy	[Measure3]
Measure2	Description	Measure2Description	\|	Measure2	ReferencedBy	[Measure4]
Measure2	DisplayFolder	Measure2DisplayFolder	\|	Measure2	Synonims	Measure2Synonym1, 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 Bizkapish / February 20, 2022 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 Bizkapish / February 13, 2022 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