Power Query

BuiltIn Types

Power Query has built-in record with identifier #shared. This record will give us all identifiers that exist in Power Query in current context, and their types. We will transform this record into table and we will then remove this query itself from the list of identifiers, in order to avoid cyclic reference. We will filter only identifiers that represent some of Power Query types. At the end we will sort by type name.

let
    IdentifiersAndTypes = Record.ToTable( #shared )   
    , RemoveItself = Table.SelectRows(IdentifiersAndTypes, each [Name] <> "Query1" )    
    , FilterOnlyTypes = Table.SelectRows( RemoveItself, each Value.Type( [Value] ) = Type.Type )   
    , SortTypes = Table.Sort( FilterOnlyTypes, { "Name", Order.Ascending })
in
    SortTypes

#Shared (1) returned all identifiers in Power Query, together with their types. We will delete (2) the name of our query from this list in order to avoid cyclic reference. We will filter only identifiers that refer to Power Query types (3).

Result will show us, that there are 63. built in types in Power Query. They are presented below.

Primitive types

Most of those types above are so called "ascribed types". It means that we can declare them, Power Query will remember them, but it will not enforce them. Power Query only enforce primitive types. This is list of primitive types in Power Query:

	If we try to define a function, arguments can only be declared as primitive types. We can see on the image that only primitive types any, binary, text are colored green by intelisense (1). Even the longer syntax like "Any.Type" is not acceptable. All other types (2) are incorrect and can not be used.
	It is the same if we want to use "is" operator. This operator will only work with primitive types ( number is green by intelisense (1) ). Even the longer syntax "Number.Type" will not work. We have to type exactly "number".
	If we select the cell with "Decimal.Type" type in our #shared table, we will see in graphic interface that this type is actually of "number" type (1). It is the same for many other types "Double.Type", "Int16.Type", "Int64.Type", "Percentage.Type" etc., "number" is their real type.

We can create a function that will return primitive type for each of our 63. built in types in Power Query. This is the function:

( TypeToName as type ) as text =>
let
      ListOfTypes = { type any, type anynonnull, type binary, type date, type datetime
        , type datetimezone, type duration, type function, type list, type logical
        , type null, type number, type record, type table, type text, type time, type type }
    , ListOfTypeNames = { "type any", "type anynonnull", "type binary", "type date"
        , "type datetime", "type datetimezone", "type duration", "type function", "type list"
        , "type logical", "type null", "type number", "type record", "type table", "type text"
        , "type time", "type type" }
    , ZipedTypes = List.Zip( { ListOfTypes, ListOfTypeNames } )
    , NameForType = List.Select( ZipedTypes, each  Type.Is( _{0}, TypeToName ) ){0}{1}
in
    NameForType

By using the function above, we will create column "Primitive types". We can see that for each built in type (1), we can get one primitive type (2). This is true for 59/63 built in types. For some built in types (3), we will get Error. In graphic interface we can see that such types are either record or function (4).

The reason for Error in image above is in the fact that table, function and record types are abstract types. There is no value that has type of table, function of record. We can create one table, record and function and compare their types with such abstract types, but the result will never be TRUE.

let
      Table = #table( { "Column" }, { { "Value" } } ) 
    , Record = [Field="Value"]
    , Function = () => let Result = "Value" in Result    
    , IsTableType = Value.Type( Table ) = type table
    , IsRecordType = Value.Type( Record ) = type record 
    , IsFunctionType = Value.Type( Function ) = type function 
    , IsTableTypeCompatible = Type.Is( Value.Type( Table ), type table )
in
    [ IsTableType=IsTableType, IsRecordType=IsRecordType
    , IsFunctionType=IsFunctionType, IsTableTypeCompatible=IsTableTypeCompatible ]

We can see that equality will never be reached (1). But if we use "Type.Is" function to check compatibility with primitive types we will get TRUE. Compatibility means that any table is compatible with the "type table". The same is true for records and functions.

When we declare type for function arguments, that argument will accept any value that is compatible with our declaration. If our argument is of type "number", such argument can receive values of all the compatible types "Double.Type", "Int16.Type", "Int64.Type", "Percentage.Type" etc.

How to check ascribed type

We will create three values of type Int64.Type, Decimal.Type and Text.Type. The question is how to read ascribed types of this values. We can do that from "metadata".

let    
      Int64Type = Value.ReplaceType( 99, Int64.Type )
    , DecimalType = Value.ReplaceType( 99.99, Decimal.Type )
    , TextType = Value.ReplaceType( "hundred", Text.Type )
    , OneTable = #table( { "values" }, { { Int64Type }, { DecimalType }, { TextType } } )
    , AddMetadata = Table.AddColumn( OneTable, "Metadata"
        , each Value.Metadata( Value.Type( [values] ) ) )
    , AddAscribedTypeName = Table.AddColumn( AddMetadata, "AscribedTypeName"
        , each [Metadata][Documentation.Name] )
in
    AddAscribedTypeName 

Value.Metadata function will return record which has Documentation.Name fild which contains ascribed type name. This is where Power Query store information about ascribed types.

Sample file can be downloaded here:

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.

Culture.Current

This is a variable that returns current culture. Current culture is described by symbols like "cs-cz" (for Czech language) or "en-gb" (for UK english). Culture is defined in Options, under Regional Settings, both in PowerPivot and Power BI Desktop.

The code below would return string "sr-Latn-RS". This blog post has, bellow, attachment "Language codes.xlsx" which contains list of all language codes.

let ‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Source = Culture.Current in ‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Source

Comparer.FromCulture()

Imagine that we want to compare two values based on specific culture and case sensitivity. To compare strings "A" and "a" based on "sr-Latn-RS" culture while ignoring case sensitivity, we could use some function that look like this:

SomeFunctionUsedForComparison( "sr-Latn-RS", true, "A", "a" )

Power query can do the same thing but it does it in two steps. First step is to create comparison function:

ComparisonFunction = Comparer.FromCulture("sr-Latn-RS", true)

Second argument is for case sensitivity. Default is false (sensitive). This argument is optional.
After this, we can use our new ComparisonFunction to make comparison when ever we need it.

Second step is to call this function when we need it. We just supply two values to compare. ResultOfComparison = ComparisonFunction( "A", "a") We can see on the right, that this new function receives two arguments to compare. Those two arguments could be of any type. As a result, this new function returns a number. That number is "0" for equality and "-1" or "1" for inequality.

Here are three examples of this function. For brevity we will merge two steps into one step:

We can see that, because of different case sensitivity, we get different results.

Comparer.Ordinal()

This function is used to compare two values. Comparing is based on ordinal rules. Lets asume that we compare:

If we compare this two values with Comparer.FromCulture, for the result we would get equality.

This is because, culture help us to compare values that have similar meaning.

Comparer.Ordinal() is ortodox function. It compares unicode codes, and not the meaning of symbols. This function will compare code "230" with codes "97" and "101". It will find that this two values are not the same. Let's see three examples for this function:

As we see, when comparing numbers, we know which number is bigger. That is way we get results "-1,1" depending on which number is larger or "0" if numbers are equal.
"ae" is consider as two unicode numbers, so for concluding sort order we only need code number for letter "a".

Comparer. OrdinalIgnoreCase()

This function is the same as Comparer.Ordinal(), except it is case insensitive.

Comparer.Equals()

This function can use previous functions as arguments to compare two values. Here are three examples:

As we can see, we are using three arguments. First one is function defining how to compare values in other two arguments. Comparer.Equals() returns "true" if values are equal and "false" if they are different.

List of all culture codes and examples for comparer functions can be found in this file:

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.SplitColumn( 
  table
, sourceColumn 
, splitter function 
, optional Names|Number
, optional default
, optional extraValues
) 

Table.SplitColumn( 
  TableWithColumnToSplit
, "ColumnToSplit"
, Splitter.SplitTextByDelimiter(",")
, {"A","B","C","D"}
, null
, ExtraValues.Ignore
)

• 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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitByNothing(),{"A","B","C","D"},"default",ExtraValues.Error )

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter(","), { "A", "B", "C" }, "value" )

Delimiter can have more than one character. Also, we can see what will happen when delimiter is at start of the text.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter("A,"), { "A", "B", "C" }, "value" )

"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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter( ",", QuoteStyle.Csv), { "A", "B", "C" }, "value" )

Splitter.SplitTextByAnyDelimiter

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByAnyDelimiter( { ",", ";"},QuoteStyle.None), { "A", "B", "C", "D" }, "value" )

Splitter.SplitTextByEachDelimiter

"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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByEachDelimiter( { ",",":", ","},QuoteStyle.None), { "A", "B", "C", "D" }, "value" )

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByLengths( { 2, 3, 2, 3 } ), { "A", "B", "C", "D" }, "default" )

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.

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 "₀A₁A₂B₃B₄B₅C₆C₇D₈D₉D₁₀D₁₁".

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByPositions( { 0, 2, 5, 7, 9 } ), { "A", "B", "C", "D" }, "default" )

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByRanges( { { 0, 2 }, { 2, 3 }, { 4, 4 } } ), { "A", "B", "C", "D" }, "default" )

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByRepeatedLengths( 3 ), { "A", "B", "C", "D" }, "default" )

Splitter.SplitTextByWhiteSpace

SplitTextByWhiteSpace is the same as SplitTextByDelimiter when delimiter is space.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByWhitespace(), { "A", "B", "C", "D" }, "default" )

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByDelimiter(" "), { "A", "B", "C" }, "value" )

Splitter.SplitTextByCharacterTransition

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.

Table.SplitColumn( TableWithColumnToSplit, "ColumnToSplit", Splitter.SplitTextByCharacterTransition( { "A", "B" }, { "B", "C" } ), { "A", "B", "C", "D", "E" }, "default" )

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" )

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.

Sample file can be downloaded here:

In Power Query there are no loops. There are no "For each", "For next", "Do Until" blocks. In Power Query we can accomplish the same thing with recursive functions. Let's say that we want to sum elements of a list, from the start, until our sum reach 10 or more. Question is, after how many elements, our goal will be reached. We can see on the chart that our goal will be reached after 4-th element 2 + 0 + 3 + 5 = 10.

List = { 2,0,3,5,0 }

First we have to establish our initial state. We know that our list is { 2,0,3,5,0 } and that initial sum is zero. We also know that first element in the list has index zero. We can write that into query. This query will call recursive function which will supply the final result.

let 
	List = { 2,0,3,5,0 }
	, Sum = 0
	, ElementIndex = 0
	, NoOfElements = RecursiveFunction ( List, Sum, ElementIndex )
in
	NoOfElements

Second, what is our logic? Our logic will go like this:
0) Does our initial state (Sum=0) meet the condition? It doesn't, so we'll go to next step.
1) We'll add one element to sum. We got 2, this doesn't meet the condition so we go to next step.
2) We'll add another element to sum. We got 2 (2+0), this doesn't meet the condition so we go to next step.
3) We'll add another element to sum. We got 5 (2+3), this doesn't meet the condition so we go to next step.
4) We'll add another element to sum. We got 10 (5+5), this does meet the condition so our answer is 4 elements.

Step 0) is already described in our query. Steps 1-4 are pretty similar. They have the same logic, but initial state for each step is different. All we have to do is to wrap this logic into function and then to call that function with different arguments each time.  Our recursive function is bellow. If condition is satisfied, we will return "Sum", otherwise we will call function again, this time with different arguments. That will repeat until condition is met.

( List, Sum, ElementIndex ) => 
let
  SubTotal = if Sum >= 10 then Sum else
    RecursiveFunction( List, Sum + List{ ElementIndex }, ElementIndex + 1)
in
    SubTotal

Every recursive function has the same logic. First we establish initial state and then we repeat this two steps until condition is met:
– Does current state satisfied the condition? If does, then we have final result.
– If it doesn't, we'll change the state and new state will give as arguments for another call of function itself.

Let's do one more complex example. Now we have a bunch of nested lists. Our goal is to sum all scalar values in those lists. Our condition is that we use all scalar values, so we are looking for the sum of 2 + 7 + 8 + 9 + 3 + 5 + 5 + 4 + 4 + 11 = 58.

NestedLists = {   { { 2, 7 }, { 8, 9 } }
                , { 3 }
                , { { 5, 5 }, { 4, 4 } } 
                , 11     
}

First, we will establish initial state. We know that initial sum is zero, and we have our list. This query will call recursive function which will supply final result.

let 
          NestedLists = {  { { 2, 7 }, { 8, 9 }  }
                         , { 3 }
                         , { { 5, 5 }, { 4, 4 } }   	
                         , 11
          }
        , Sum = 0
        , ElementIndex = 0
	, Total = NestedListsRecursiveFunction ( NestedLists, Sum, ElementIndex )
in
	Total

All we need more is a recursive function. This function is complex. Let's try to make it easier to understand by thinking about simplier NestedLists = { 1, { 2 }, 2, { 3 } }. We can present this list by picture:

First we will count how many toothbrushes are in each package. After that is easy to sum the whole list.

Here is the trick. Addition of all toothbrushes in one package is similar to addition of all toothbrushes in the whole list. This mean that we can use same logic to individual package and to whole list. That means we can use recursion. Here is recursion function.

(NestedLists, Sum, ElementIndex) =>
  let
    NewSum = 
      if ElementIndex < List.Count(NestedLists) then
        if Value.Is(NestedLists{ElementIndex}, List.Type) then
          NestedListsRecursiveFunction(
              NestedLists 
            , Sum + NestedListsRecursiveFunction(NestedLists{ElementIndex}, 0, 0) 
            , ElementIndex + 1
          )
        else
          NestedListsRecursiveFunction(
              NestedLists 
            , Sum + NestedLists{ElementIndex} 
            , ElementIndex + 1
          )
      else
        Sum
  in
    NewSum

With purple code we are passing through all elements of a List. If we reach the final element, function will return sum of that List as final result. For each element we are using red code to determin if element is List or not.  If element is scalar, we will use green line above to add such element. Problem is that beside scalar elements, we can also have subList elements.

Orange code is used to add such elements. It is similar to green code. Green code is adding scalar values, and orange code is adding sums of subLists. Blue code above is used to sum every subList. Blue code is using recursion.

So this is our goal, we want to replace every subList with its sum. When we reach subList element, we will dive in it with recursive call to the function. If that subList element has only scalars, green line of code will give us sum of that subList. If that is not true, we will dive deeper until we find subList that contains only scalars. When we get sums of lowest subLists, we can use those to calculate sums for subLists that are higher in hierarchy.

Excel file with sample in Power Query: