If we create table in Power BI desktop that has images URL-s in one column (1), this will allow us to present those images in Report View (4). For this to work, we have to mark this column as "Image URL" column (2). First we select our column (1) and then we go to Column tools > Properties > Data category. There, we choose option "Image URL" (2). Now, that our column is of "Image URL" type, we can add this column as a Field to a table visual (3). Final result is table, in Report View (4), that has image in every cell of table presented.
If we don't have URL-s for our images, but the images are available in some folder on our computer or network, we have to raise our local image server. Here, I will explain two ways how to do that easily.
Python http.server modul
We will open windows CMD environment. This CMD environment has to stay open for the time we are using our images in Power BI Desktop. When we close this environment, images will not be available any more. Then, first we have to change current folder to folder with images (1). Second, we have to open python "http.server" module (2). This module asks for port number. We can type anything, but for this example I used port number 21.000.
(1)
>cd "c:\Folder with images"
(2)
>pyhton.exe -m http.server 21000
All images in folder are now available as URL links. On our computer we can approach those links with "localhost" and our port number (1). On our computer, and all other computers on local network, we can open page with links by using IP address of our computer and our port number (2). Access from local network can be limited by firewall, so we need to have rights to access computer where python server works.
Links to images will be generated as "http://localhost:21000/" + "Name of image.jpg". If we have names of all our images in Excel, we just have to prefix them with "http://localhost:21000/" and then we will have the links to all our images. Those links can be imported in Power BI Desktop and used in Report View.
There is a very simple server that can be used for the same purpose. Its name is "Mongoose server" and can be downloaded from https://mongoose.ws/desktop-app/. This is small portable executable file. Just click on it to open it.
New icon will appear in system tray. Right click this icon to access main menu. With option (1) we can change folder with images that are publish online. With (2) we can open browser and jump directly on page where all our images are listed. Link for this page and for each of images will be similar to one that python web server created.
We can see bellow how page with image links looks like (1). On the right side (2) we can see that Mongoose server can share our images globally. This option has to be enabled by clicking on (3) on the image above. In this case sharing is done through Mongoose server so the link is something like "https://32cf61d37e7299.share.mongoose.ws". Global sharing of our images is not something that we want in this case and could present security risk because now anyone on world can access our images. It is best to turn this option off.
Here is PBIX sample file and one folder with collection of images.
SPSS / By Bizkapish
/ October 17, 2021 October 17, 2021
We have three columns with the scores of a judges (1). We want to create new column that will present an average score (2). For this, we are using option Transform > Compute Variable (3). This will open form where we can define new variable.
In new dialog, first thing is to give name to new variable (1). Button (2) can open small dialog where we can define label (3) for our variable. That label is just description of a variable. We can also define our variable as Numeric or String type (4). In this case, we will choose "Numeric". Pane (6) is where we create expression for new variable. This expression is comprised of column names, mathematical operators, builtin functions and constants. We can type the whole expression from our keyboard, but instead of typing we can just select some building blocks for our expression from other areas in this dialog, and they will appear in pane (6).
Pane (5) has names of all the columns. Pane (9) has names of functions. Virtual keyboard (7) allows us to type different signs and numbers by using mouse. We can use all those elements to create desired expression in pane (6). By selecting some of functions groups in pane (8), we can limit what functions will be presented in pane (9). When some of functions in pane (9) is selected, we will be able to see its syntax and description in pane (10). We created formula (6). The formula "MEAN (judge1, judge2, judge3)" would give us the same result.
When we click on column name, that column name will appear in our expression:
By using virtual keyboard we can add different operators and numbers to our expression.
Clicking on some of functions will add syntax for that function in expression pane. Then we'll have to replace question marks with functions arguments. Those arguments can be column names, constants or subexpressions.
In virtual keyboard we have signs "**", "&", "|". Sign "&" is for logical AND. Sign "|" is for logical OR. When we write "2**3", it is the same as "23".
Conditional variables
If some of values, in columns that we used to calculate our new variable, are missing, then the value in the same row will not be calculated for our new variable (only for our original expression, it seems that builtin MEAN function is resistant to this). Missing value will be presented with a dot. If we have all the values in columns judge1, judge2, judge3, it is still possible to make variable with missing values. Example bellow shows how to do it.
Now we have one column more (1). This column shows, whether scores belong to junior or senior competitor. We want to create new variable that will present maximal value, but only for junior competitors (2). We can achieve this with conditional variables. First we create formula for our variable. We are going to use MAX function (3). Then we have to define condition. Place for that is opened by clicking on "If" button on the bottom of the main dialog (4).
MAX(judge1,judge2,judge3) (3)
We will be greeted with a dialog where on the top we have to select option "Include If case satisfies condition" (1).
In pane (2) we have to enter condition. In every row where this condition is satisfied, new variable will have a value, and vice versa. Way of creating condition is similar as way of creating expression for new variable. We have columns (3), functions (5) and buttons (4) to help us create this boolean expression. Our junior competitors are coded with number "0" (Seniors are "1"). This makes our conditional expression as simple as:
Seniority = 0
This is all if we want only "Junior" rows filled with values for new variable.
After this, we can create new variable with the same name (1). This variable will calculate maximal score for judge2 and judge3 (2), but only for Senior competitors (3). For some reason, we will consider scores of judge1 invalid for Senior competitors. SPSS will ask us whether we want to change existing variable (4). We'll click on YES. SPSS will not overwrite old column with new column. It will combine them. We can see in (5) that all rows are populated. Rows 1, 2 and 5 are for Junior competitors and they show maximum for "judge1,judge2,judge3". Rows 3 and 4 are for Senior competitors and they show maximum for "judge2,judge3". In row 3 we can see that the value in "Maximal" column is 8.8, which is maximum for columns "judge2,judge3". Total maximum is 8.9, but that maximum is in column judge1 that does not count.
We can continue to add more conditional variables with the same name. Each new variable will overwrite only results which satisfy its condition. If we add third variable with name "Maximal" and condition "judge2>8", it will only overwrite values in rows where this condition is fullfilled.
Two caveats to consider
When we click on some function, its syntax will be added to expression pane. If we click on MAX function, syntax like this will be added "MAX(?,?)". This doesn't mean that this function can have only two arguments. It can have infinite number of arguments "MAX(a,b,c,d,e…)", but we have to type other arguments by hand. Same for many other functions.
When we create new variable, we can give it the same name as one of original columns. For example, we have original column "judge1", and we create new variable with the same name "judge1". SPSS will ask us whether to change existing variable. If we click on YES, we are going to lose original column. New variable will overwrite original variable.
While typing some formula, we can click on spreadsheet cell to get its reference. If the cell is inside declared table, our reference will be structured reference.
Structured reference works by cutting horizontal and vertical slices of a table, and then it returns intersection of those slices.
There is limited number of ways how we can slice table horizontally. Let's ignore vertical slicing for now, and we will cut the whole table only horizontally. We can use 5 specifiers to get what we want. We type the reference as name of table + specifier (like in cell H1 bellow).
-[#Headers] returns table header (A). -[@] returns current row (B). -[#Data] returns table body (C). -[#Totals] returns Total row (D). -[#All] returns the whole table (E). ㅤSo, when we combine name of a Table with some of specifiers, we can get horizontal slices of a table. ㅤOne special specifier is "[@]". This specifier means that we'll get data from a row where our formula is placed. If our formula is in cell H5, then we are going to get data from cells C5:F5 in the table.
Vertical slicing can be done in two ways. We can slice only one column, and we can slice several consecutive columns.
(1) is how to reference only one column. (2) is how to reference several consecutive columns. We can notice (3) that we don't get whole columns. We only get [#Data] part of columns. This is because [#Data] horizontal slicing is the default. If we want some other horizontal part of table, then we have to combine horizontal and vertical specifiers.
Combining specifiers
Now we have to use two specifiers. First we write horizontal specifier, and then the vertical one. Yellow example bellow, returns whole "Units" column because horizontal specifier is [#All]. Blue example returns headers for two consecutive columns because horizontal specifier is [#Headers]. Red example is different. It uses two horizontal specifiers. This way we can only use combinations ( [#Data],[#Headers] ) and ( [#Data],[#Totals] ). Red example returns "Total" column without its header. Green example assumes that formula is in the sixth row. That is why it returns values for columns "Region" and "Rep" in sixth row.
It is not possible to combine [#All] specifier, or [@] specifier with some other. They only goes alone. Specifier for "current row" is not separated with coma, there is no coma between "@" and name of column. If we want current value for only one column we type "=SampleTable[@ColumnName]".
Choose function
Choose function can help us to solve two problems. First is, that we want to reference several not consecutive columns from the table. Let's say that we want to reference columns "Region" and "Total", but we don't want their [#Totals]. We can use this formula:
If we want to wrap two columns into one aggregate function, we don't need Choose function, we can directly type columns references as arguments into function. Function bellow will return 2339,36 ( 264 + 2075,36 ).
=SUM(SampleTable[Units],SampleTable[Total])
Other problem is that we can not combine [#Headers] and [#Totals]. Solution is again to use Choose function. Notice that this time "{1;2}" argument is using semicolon, and not coma.
If we copy formula with structured reference to some other cell (1), it will not adapt. We can see in cells A10:D10, in image bellow, that all cells have the same value. Sometimes this behavior is desired, but sometimes is not.
Solution is to drag this formula, instead of copying it. We can see in cells A11:D11 that now every cell has value which corresponds to its position.
This is valid only for individual columns. Any structured reference that has range of columns in it, will not adapt, it will always be absolute. If we type our formula like a range of onecolumn, that formula will not change its result even if we drag it:
=SampleTable[[#Headers],[Region]:[Region]]
Things to consider about structured references
Structured references are easy to read, but not to type. Usually we just click on some table cell and Excel creates structured reference for us. If this is not what we want, it is possible to disable automatic creation of structured references. This is done by unchecking option in Excel Options > Formulas > Working with formulas > Use table names in formulas. VBA version of this would be:
Application.GenerateTableRefs = xlGenerateTableRefA1 'to turn automatic creation OFF
Application.GenerateTableRefs = xlGenerateTableRefStruct 'to turn automatic creation ON
Structured references can be used inside VBA. Here is one example:
Worksheets(1).Range("Table1[#All]")
Declared table doesn't need to have headers and total row. They can be disabled in Table Design > Table Style Options, in Excel ribbon. After this, formulas which refer to [#Headers] or [#Totals] will not work.
Column names can be qualified and unqualified. Unqualified names can only be used inside tables. This means that instead of writing SampleTable[Region] we can just type [Region]. This will not work outside of table which has "Region" column.
In ribbon, there is option to convert declared table into ordinary range. That option is in Table Design > Tools > Convert to range. After this, all structured references for that table will be transformed into regular A1 references.
For special signs []#' we have to include escape sign – single quotation mark (').
Splitter functions are used as arguments in Table.SplitColumn function. Table.SplitColumn function is used to split column vertically into more columns (1 => 2). Here is the syntax and example of this function.
table – table where is the column which we are going to split.
sourceColumn – column that will be splitted.
splitter function – function that explains how to split the column.
Names | Number – names of new columns.
default – value to place in cells which would be empty because we have more columns then values.
extraColumns – if we have the opposite situation, number of values is bigger than the number of columns, then this argument decide what to do with extra values.
This is how we give names to new columns. If we have too many column names, extra columns will be filled with default value.
We can name columns by setting some number as fourth argument. In that case, names of columns will be generated as name of initial column + index number ( ColumnToSplit + 1,2,3 ). In the example bellow we are using number three, so there are going to be only 3 columns as a result.
If we don't have enough column names for all the columns, those columns will be missing, but only if we use "ExtraValues.Ignore" (1) as the last argument. There are two more possibilities. We can use "ExtraValues.List" (2), so last column will held all surplus values as a list. Last possibility is to raise an error. This will happen when we use "ExtraValues.Error" (3). "ExtraValues.Ignore" is default value.
Third argument is what interests us the most. It contains function that explains how to vertically slice origin column.
Splitter.SplitByNothing
Splitter.SplitTextByAnyDelimiter
Splitter.SplitTextByDelimiter
Splitter.SplitTextByEachDelimiter
Splitter.SplitTextByLengths
Splitter.SplitTextByPositions
Splitter.SplitTextByRanges
Splitter.SplitTextByRepeatedLengths
Splitter.SplitTextByWhiteSpace
Splitter.SplitTextByCharacterTransition
Splitter.SplitByNothing
"SplitByNothing" will not split origin column. It will only add new columns filled with default values.
If names of new columns and default value are not defined (because they are optional), then two things would still change. Name of column would change, and type of column would become "ABC 123".
Splitter.SplitTextByDelimiter
"Splitter.SplitTextByDelimiter" takes one argument. That argument is delimiter by which text will be splitted.
"SplitTextByDelimiter" accepts second optional argument. Default value for this argument is QuoteStyle.None. Difference is made when we use QuoteStyle.Csv. In that case all individual quotes will be removed ("), and double quotes will be transformed into individual quotes ("" => "). So, instead "O""A","B" we will have O"A.
Anytime we find any of the delimiters from the list in the text, we will split text. In example bellow, anytime we meet coma or semicolon, we will split the text. In table we can, also, see what will happen if there are two delimiters side by side. Two consecutive delimiters will define one empty cell. "SplitTextByAnyDelimiter" also accepts "QuoteStyle" argument.
"SplitTextByEachDelimiter" is different from "SplitTextByAnyDelimiter" because now, we will split text consecutively in the same order delimiters have in their list. In our example, first we look for coma, then for colon, and then for coma again. So, if we have three delimiters in our list, we can make maximally three splits, and only if all three delimiters exist in text in proper order.
In second row, in table above, we can see that we only made one split. We split on the first coma, but we couldn't split on semicolon because there were no semicolons after first coma.
Splitter.SplitTextByLengths
This time, splitting is not done by delimiters but with consecutive lengths. The last number in the list is three, that is why our "D" column has value "DDD", and not "DDDD" with 4 D's. If this number was 4 (or 5 or 6…) then we would see all four D's.
If we had longer list, for example { 2, 3, 2, 3, 8, 4, 2 }, nothing would change. But, if we had smaller list, for example { 2, 3 }, then last two columns would be filled with default values.
ColumnToSplit
A
B
C
D
Row1
AABBBCCDDDD
=>
AA
BBB
default
default
Splitter.SplitTextByPositions
Positions are positive numbers and every position can not be smaller than previous position. This positions define places where text would be splitted. In our examples we can see positions 0–11 as "0A1A2B3B4B5C6C7D8D9D10D11".
In first row we have position "9" which limits value in column "D" to only two D's. In second row, we only have three positions, so the text is splitted to only three columns. In third row, we have two same consecutive positions. This will create empty cell in column "B".
Splitter.SplitTextByRanges
In Excel, when we use function "=MID( A1; 5; 3)", we first jump to fifth character and then we take that and next two characters. This is how "SplitTextByRanges" work. Function takes argument that looks like { { 0, 2 } ,{ 3, 1 } } . Each pair of values defines position and length, the same way as MID function. In table bellow, we will see how to split text AABBBCCDDDD.
Each pair defines one substring from initial column. Substrings can overlap. We can see that black "B" in ColumnToSplit belongs both to "B" and "C" column.
Splitter.SplitTextByRepeatedLengths
This function is similar to "SplitTextByLengths", except all lenths are of the same lenght. Here, we want to split text into groups of three characters. We can notice that last column has only two characters.
This function accepts two arguments. Both arguments can be lists of characters. If character from first list stands before some character from the second list, then text will split between those two characters.
In the table above, we can see that we made a split anywhere between A and B, B and B and B and C.
We can also use functions which decide, are two consecutive characters adequate to be split between. In example bellow we have two functions. First function returns true if character is before or on "B", second function decides whether character is after "B". So this logic says that if first character is A or B, and if second character is any other letter, then split text between them (we will observe only upper letters).
Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByCharacterTransition( each if _ <= "B" then true else false, each if _ > "B" then true else false ), { "A", "B", "C" }, "default" )
ColumnToSplit
Where to split
A
B
C
AABBBCCDD
AABBB▲CCDD
=>
AABBB
CCDD
default
BBAFFF
BBA▲FFF
=>
BBA
FFF
default
AAAGGGBBFF
AAA▲GGGBB▲FF
=>
AAA
GGGBB
FF
In table above, we can see that we made a split anywhere between B and C, A and F,A and G and B and F.
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.
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).
These are the building blocks of code we are going to use. First we need to declare file where our formulas will be saved. Part "new System.Text.UTF8Encoding (true))" is important. It adds BOM mark to our file. This mark helps programs understand what encoding the file is using. We will afterwards open this file in Excel. If our file contains unusual characters, like Č, Ž, Á, Ж; Excel will properly read and present such characters.
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.
