SUMMARIZECOLUMNS function in DAX

Our Model

We will use this simple model to explain SUMMARIZECOLUMNS function. On the left side we have ProductCategory > ProductSubcategory > Product. On the right side we have "Calendar" table. "Sales", the fact table is in the middle.

Grouping Columns

In its simplest form, this function just groups values from several columns. If the columns do not belong to the same table, the result will be a cross join of their values.

EVALUATE
SUMMARIZECOLUMNS(   
     ProductCategory[ProductCategory]
   , Calendar[Year]
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]

But if the columns are from the same table, then only distinct rows of those columns will be returned.

EVALUATE
SUMMARIZECOLUMNS(
     Product[Manufacturer]
   , Product[BrandName]
)
ORDER BY Product[Manufacturer]
               , Product[BrandName]

If the first table above has 24 rows, and the second has 4 rows, then the formula below will give us a table with 4 * 24 = 96 rows. The formula below combines all four columns.

EVALUATE
SUMMARIZECOLUMNS( ProductCategory[ProductCategory], Calendar[Year]
                                    , Product[Manufacturer]                    , Product[BrandName] )
ORDER BY                      ProductCategory[ProductCategory], Calendar[Year]
                                    , Product[Manufacturer]                    , Product[BrandName]

Filter Table Argument

We don't need to combine all the values from the columns, we can apply filters on them. Any table mentioned after grouping columns will be considered as a filter.


this is filter table created with TREATAS function.
This is why we only have two categories in our table.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , Calendar[Year]
   , Product[Manufacturer]
   , Product[BrandName]
   , TREATAS( { "Audio", "Cameras and camcorders" }
        , ProductCategory[ProductCategory] )
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]
              , Product[Manufacturer]
              , Product[BrandName]

It is possible to use several filter tables. Each table will filter the columns that belong to it.



We now have two filter tables created with
TREATAS function. One will apply a filter to the "ProductCategory" column,
and the other  to the "Year" column.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , Calendar[Year]
   , Product[Manufacturer]
   , Product[BrandName]
   , TREATAS( { "Audio", "Cameras and camcorders" }
        , ProductCategory[ProductCategory] )
   , TREATAS( { 2011, 2012 }, Calendar[Year]   )
)
ORDER BY ProductCategory[ProductCategory]
              , Calendar[Year]
              , Product[Manufacturer]
              , Product[BrandName]

The grouping column must be part of the filter table, for the filter to apply.

Aggregations

Now we can add some aggregations. Aggregations are defined by the the name of the new column, and then we add some expression that returns a scalar value.

The SUMMARIZECOLUMNS function was born for this. This is the fastest and easiest function to group columns from several tables and then add some aggregated values from a fact table. A lot of work can be done with just one function.


*In the source data, we only have Sale for this two product categories.
EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , ProductSubCategory[ProductSubCategory]
   , "SalesQuantity", SUM( Sales[SalesQuantity] )
   , "SalesAmount", SUM( Sales[SalesAmount] )  
)
ORDER BY ProductCategory[ProductCategory]
               , ProductSubcategory[ProductSubcategory]

NONVISUAL

Filter Table argument can do two things. It can affect the number of rows, and it can also affect the measurements. Let's make one measure:
TotalSalesAmount:=SUM( Sales[SalesAmount] )

As you can see below, we just summed the two subcategories with the measure TotalSalesAmount ( 339.112.125 ). The measure is influenced by the filter table argument. We can notice that if we display all the data, then the total amount of sales would be 416.455.001.

EVALUATE
SUMMARIZECOLUMNS(
     ProductSubcategory[ProductSubcategory]
   , TREATAS( { "Recording Pen", "Televisions" }
        , ProductSubcategory[ProductSubcategory] )
   , "SalesQuantity", [TotalSalesAmount]
   , "SalesAmount", CALCULATE( [TotalSalesAmount], ALLSELECTED( Sales )  )
)
ORDER BY  ProductSubcategory[ProductSubcategory]

We can remove the filter table influence on the measure by wrapping it in a NONVISUAL function. This time our TotalSalesAmount is exactly 416.455.001. The image below is from PBID, as this NONVISUAL function was not introduced into Excel.

EVALUATE
SUMMARIZECOLUMNS(
     ProductSubcategory[ProductSubcategory]
   , NONVISUAL( TREATAS( { "Recording Pen", "Televisions" }
        , ProductSubcategory[ProductSubcategory] ) )
   , "SalesQuantity", [TotalSalesAmount]
   , "SalesAmount", CALCULATE( [TotalSalesAmount], ALLSELECTED( Sales )  )
)
ORDER BY  ProductSubcategory[ProductSubcategory]

IGNORE

When we apply aggregations, rows where all measures are blank, will be excluded from the result.
We use VALUES function because there is no row context in the SUMMARIZECOLUMNS function.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , "SalesQuantity", IF( VALUESProductCategory[ProductCategory] ) = "TV and Video"
                                   , BLANK(), SUM( Sales[SalesQuantity] ) )
   , "SalesAmount", IF( VALUES ProductCategory[ProductCategory] ) = "TV and Video"
                                  , BLANK(), SUM( Sales[SalesAmount] ) ) )
ORDER BY ProductCategory[ProductCategory]

We can use the IGNORE function to treat some measures as they were blank. If some of the measures values are blank, and others are IGNORED, then such rows will not be part of a result.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , "SalesQuantity", IF( VALUESProductCategory[ProductCategory] ) = "TV and Video"
                                   , BLANK(), SUM( Sales[SalesQuantity] ) )
   , "SalesAmount", IGNORE( SUM( Sales[SalesAmount] ) )
ORDER BY ProductCategory[ProductCategory]

Using IGNORE on all the rows will not hide all data. Contrary, it will display all of the rows.

EVALUATE
SUMMARIZECOLUMNS(
     ProductCategory[ProductCategory]
   , "SalesQuantity", IGNORE( SUM( Sales[SalesQuantity] ) )
   , "SalesAmount", IGNORE( SUM( Sales[SalesAmount] ) )
ORDER BY ProductCategory[ProductCategory]

ROLLUPADDISSUBTOTAL

ROLLUPADDISSUBTOTAL basics

SUMMARIZECOLUMNS can have subtotals and grandtotal calculated using the ROLLUPADDISSUBTOTAL helper function. This function accepts at least two arguments. First is the column used for grouping. For the items in this column, we would get subtotals. The second argument is the name of the new column which will say TRUE or FALSE depending on whether that row is a detail row or a subtotal row.

In the image bellow we can see three new rows with subtotals. There is, also, one more column that shows whether the row is a subtotal for a particular column ( TRUE or FALSE ).

EVALUATE
SUMMARIZECOLUMNS(
      Calendar[Year]
   ,  ROLLUPADDISSUBTOTAL( ProductCategory[ProductCategory], "CategorySubtotal?" )
   , "SalesAmount", [TotalSalesAmount]
)
ORDER BY  Calendar[Year] ASC
                , ProductSubcategory[ProductSubcategory] DESC

This happens if we wrap each grouping column with ROLLAPADDISSUBTOTAL. We get all possible subtotals.

EVALUATE
SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( Calendar[Year], "YearSubtotal?" )
   ,  ROLLUPADDISSUBTOTAL( ProductCategory[ProductCategory], "CategorySubtotal?" )
   , "SalesAmount", [TotalSalesAmount]
)
ORDER BY  Calendar[Year] ASC
                , ProductCategory[ProductCategory] DESC

It is possible to place all the grouping columns together in a singe ROLLUPADDISSUBTOTAL function. In that case, we would get hierarchical subtotals, from left to right ( like in Excel pivot table ).

EVALUATE
SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( Calendar[Year], "YearSubtotal?"
                                               , ProductCategory[ProductCategory], "CategorySubtotal?" )

   , "SalesAmount", [TotalSalesAmount]
)
ORDER BY  Calendar[Year] DESC, ProductCategory[ProductCategory] DESC

ROLLUPADDISSUBTOTAL filters

We can create a single filter table. We can place this filter table in the ROLLAPADDISSUBTOTAL function and that way we can filter our subtotals and grand total. We can place this argument at the beginning, and at the end of ROLLUPADDISSUBTOTAL function. At the start it would only filter the value of grand total. At the end, it would filter only column items.

In this example below we place it in both places. This would filter both the grand total and the items. The images are from PBID because this argument is not introduced into Excel.

VAR RollupFilter = TREATAS( { "Recording pen"}; OnlyNeeded[ProductSubcategory] )
VAR Result  = SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL( RollupFilter
        ; OnlyNeeded[ProductSubcategory]
        ; "SubcategorySubtotal?"
        ; RollupFilter )
   ; "SalesAmount"; [TotalSalesAmount]
)
RETURN Result

This would be the results if we placed this argument only at the beginning, or only at the end of the ROLLUPADDISSUBTOTAL function.

At the beginning, it influence only grand total.
At the end, it influence only subtotals.

ROLLUPGROUP

By placing some columns in a ROLLUPGROUP, we will observe them together. That is why their individual subtotals will not appear in our table. If we want to exclude some subtotals, we use ROLLUPGROUP.

In this example we don't have subtotals for ProductCategory and SubCategory. They are excluded because we placed this two columns in the ROLLUPGROUP function.

EVALUATE
SUMMARIZECOLUMNS(     
     ROLLUPADDISSUBTOTAL(
           Calendar[Year]
         , "YearSubtotal?"
         , ROLLUPGROUP( ProductCategory[ProductCategory]
        
                           , ProductSubcategory[ProductSubcategory] )
         , "(Sub)CategorySubtotal?"
     )
     , "SalesAmount"
     , [TotalSalesAmount] 
)
ORDER BY  Calendar[Year] DESC
                , ProductCategory[ProductCategory] DESC
                , ProductSubcategory[ProductSubcategory] DESC

Sample file can be downloaded from here:

Remember and Reset the Cursor Position

What Problem Are We Solving?

When making video tutorials, it's easiest to make short videos, but sometimes we need more time to explain a topic. Even then, we can break our entire tutorial into smaller videos. Then we have a problem how to connect those small videos into a whole. This problem can be solved by placing a slide between each smaller video. If slides are not a suitable solution then we have to find a way to seamlessly connect our smaller videos. This leads us to our problem, how to record cursor position in the previous video, so that we can start recording new video with the cursor at the same position.

Idea is to save location of our cursor in TXT file, when some shortcut is pressed (1). This is something we have to do at the end of recording. Before we start recording the next video, using another shortcut, we would return the cursor to the last position (2). Note that the TXT file always retains the last 20 recorded positions.

I will show you a solution that doesn't use third-party software and can be use on any computer.

Recording the Last Position of the Cursor

We can record the last cursor position with a Powershell script. This script will read the current cursor position and then it will write that cursor position into TXT file.

Add-Type -AssemblyName System.Windows.Forms

$p = [System.Windows.Forms.Cursor]::Position 
$X = $p.X 
$Y = $p.Y

Add-Content -Path "C:\Users\Sima\Desktop\Resursi\Kursor pozicija\Previous cursor positions.txt" -Value ( $X.ToString() + "`r`n" + $Y.ToString() )

Add-Type -AssemblyName PresentationCore,PresentationFramework
$ButtonType = [System.Windows.MessageBoxButton]::OK
$MessageboxTitle = "Remember cursor position."
$Messageboxbody = "Position $X, $Y is remembered."
$MessageIcon = [System.Windows.MessageBoxImage]::Information
[System.Windows.MessageBox]::Show($Messageboxbody,$MessageboxTitle,$ButtonType,$messageicon)

That TXT file will keep the last 20 positions (1) so we don't have to worry about overwriting the cursor position we saved earlier. In the end we will get a message box (2) with the position of our cursor expressed in pixels.

Resetting of the Cursor Position

Again, we'll use Powershell to reset the cursor position. First we will read the last two numbers from our TXT file. Next, we'll set the cursor position to the new location. Finally, we need to make sure that there are only the last 20 positions saved in our TXT file. We will achieve this by measuring how many lines there are in our file, and if that number is greater than 20 then we will overwrite our file with only the last 20 positions.

$file = "C:\Users\Sima\Desktop\Resursi\Kursor pozicija\Previous cursor positions.txt"
$file_data = Get-Content -tail 2 $file

Add-Type -AssemblyName System.Windows.Forms

$p = [System.Windows.Forms.Cursor]::Position
$p.X = $file_data[0]
$p.Y = $file_data[1]
[System.Windows.Forms.Cursor]::Position = $p 

$content = Get-Content $file
$numberOfLines = $content.Length
if ( $numberOfLines -gt 20 ) 
{
  $content[($numberOfLines-20)..$numberOfLines]|Out-File $file -Force
}

Embedding Powershell Scripts into VBS Scripts

Running Powershell PS1 scripts is restricted due to security. Instead of calling our scripts directly, we'll wrap them in VBS scripts. Our code for the first and second scripts will now look like this. At the beginning and end, we need to create and destroy the shell object. We use that object to run our scripts using powershell.exe. The quotes and double quotes in the original Powershell scripts have been modified so that the script can be embedded inside a VBS script.

RECORD:
Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -Command ""Add-Type -AssemblyName System.Windows.Forms;$p = [System.Windows.Forms.Cursor]::Position;$X = $p.X;$Y = $p.Y;Add-Content -Path 'C:\Users\Sima\Desktop\Pamcenje kursor pozicije\Previous cursor positions.txt' -Value ( $X.ToString() + """"""`r`n"""""" + $Y.ToString()  );Add-Type -AssemblyName PresentationCore,PresentationFramework;$ButtonType = [System.Windows.MessageBoxButton]::OK;$MessageboxTitle = 'Remember cursor position.';$Messageboxbody = """"""Position $X, $Y is remembered."""""";$MessageIcon = [System.Windows.MessageBoxImage]::Information;[System.Windows.MessageBox]::Show($Messageboxbody,$MessageboxTitle,$ButtonType,$messageicon)"" ", 0
Set WshShell = Nothing

RESET:
Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -Command ""$file = 'C:\Users\Sima\Desktop\Pamcenje kursor pozicije\Previous cursor positions.txt'; $file_data = Get-Content -tail 2 $file; Add-Type -AssemblyName System.Windows.Forms;$p = [System.Windows.Forms.Cursor]::Position; $p.X = $file_data[0]; $p.Y = $file_data[1]; [System.Windows.Forms.Cursor]::Position = $p; $content = Get-Content $file; $numberOfLines = $content.Length; if ( $numberOfLines -gt 20 ) { $content[($numberOfLines-20)..$numberOfLines]|Out-File $file -Force }"" ", 0
Set WshShell = Nothing

Notice that WshShell.Run has a second argument. That argument is zero. This is used to prevent opening of a terminal window while scripts are executing.

Calling our Scripts

We need to call our scripts with keyboard shortcuts. We have to use shortcuts because we can't use the mouse for that. Our cursor must be stationary.

We can create global shortcuts by creating two shortcut files that target our VBS scripts (1). These two shortcut files must be placed on the desktop, otherwise the keyboard shortcuts will not work.

Next we need to go to the properties of our shortcut files and there on the "Shortcut" tab we can set the keys (2) that will be used to call our VBS scripts.

Sample files can be downloaded below. Remember to change fullpath of "Previous cursor positions.txt" file inside of each VBS script. Also, change the target of each shortcut file. Change fullpath of powershell executive file, too.

XML flattening (expanding) in Power Query

I have two XML files in folder (1). Their content is in the tables in column (2). Their content is extracted with "Xml.Document" function. If I click the Expand button (3) I could expand second column (2). After that I would have to expand the next column (4), and so on. I would end up flattening all my XML files. There are two problems with this approach. The first is that I don't want to click the expand button, I want to automatically expand all the columns (5). Another problem is that some columns may have a combination of tables and values in one column (6). In that case the expand button will not work. Let's solve this two problems.

funcIsOnlyTables

I need several helper functions to achieve my goal. funcIsOnlyTables is function which will tell me whether the only content of some column are nested tables. This function accepts two arguments – a table and a column position. This function will return TRUE if all the cells in that column have nested tables. Here, we are using function "Table.MatchesAnyRows" to see is there any cell in our column that has content that is not of type table.  If that is FALSE, then our function should return TRUE, because that mean that we only have nested tables in our column.

( TableToExpand as table, ColumnNumber as number) =>
let
    ColumnName = Table.ColumnNames( TableToExpand ){ColumnNumber}    
    , TemporaryRenaming = Table.RenameColumns( TableToExpand, { { ColumnName, "ŽŽŽ" } } )
    , HasSomethingElse = Table.MatchesAnyRows( TemporaryRenaming, each not Value.Is( [ŽŽŽ], type table ) )
    , HasOnlyTables = not HasSomethingElse
in
    HasOnlyTables

I had to rename my column because I had to reference it somehow in "Value.Is" function.

funcIsOnlyNonTables

funcIsOnlyNonTables is similar function. It works in the same way. The only difference is that we are here looking for cells that are of type table. If there are such cells, then our function will return FALSE, because that means that our column has some cells with nested tables.

( TableToExpand as table, ColumnNumber as number) =>
let
    ColumnName = Table.ColumnNames( TableToExpand ){ColumnNumber}    
    , PrivremenoPreimenovanje = Table.RenameColumns( TableToExpand, { { ColumnName, "ŽŽŽ" } } )
    , HasTables = Table.MatchesAnyRows( PrivremenoPreimenovanje, each Value.is( [ŽŽŽ], type table ) )    
    , IsOnlyNonTables = not HasTables
in
    IsOnlyNonTables

Residual logic

If both functions, "funcIsOnlyTables" and "funcIsOnlyNonTables" return FALSE then our column is of mixed type. It has both nested tables and values.

Now, we can discover nature of each column, so we can process each column differently based on its content.

funcExpandColumn

"funcExpandColumn" will expand only those columns that only have nested tables. First we have to create a list of all the names of columns in nested tables. On image, such list would be { "Quarter", "Quantity", "Revenue" } (1,2,3). We would prefix each subcolumn name with the name of original column ( "Quarters" + "." +  "Quantity" (4) ), so that each newly created column has unique name. Then, it is easy to expand our column with Table.ExpandTableColumn function.

( TableToExpand as table, ColumnNumber as number) =>
let
    ColumnName = Table.ColumnNames( TableToExpand ){ColumnNumber}    
    , TakeNamesOfSubColumns = List.Union( List.Transform( Table.Column( TableToExpand, ColumnName )
         , each Table.ColumnNames( _ ) ) ) 
    , NewNames = List.Transform( TakeNamesOfSubColumns, each ColumnName & "." & _ )
    , Expanding = Table.ExpandTableColumn( TableToExpand, ColumnName, TakeNamesOfSubColumns, NewNames )
in
    Expanding

funcTabelizeExpand

This function will solve the problem of columns with mixed content. We will transform each mixed column (A) into column where all the cells are tables (B). We will achieve that by wrapping each non table value with table that has one column and one row (1). Name of that new column (1) will be the same as the name of major column (B). Now that all the cells are filled with nested tables, we will just call function "funcExpandColumn" that we saw in the earlier step.

( TableToExpand as table, ColumnNumber as number) =>
let
    ColumnName = Table.ColumnNames( TableToExpand ){ColumnNumber}    
    , Tabelize = Table.TransformColumns( TableToExpand, { {   ColumnName, each if Value.Is( _, type table ) then _ 
          else #table( { ColumnName }, { { _ } } )    } } )
    , Expanding = funcExpandColumn( Tabelize, ColumnNumber )
in
    Expanding

Final logic

Now we have all the ingredients to flat our XML file. First we will prepare our input. Input is a table which have names of XML files, from some folder, in its first column "Name". Second column "Tabelix" has content of those files that is fetched with "Xml.Document" function.

"funcExpandAll" is the last function. This function will use all of the previous functions. This function is based on all powerfull "List.Generate" function. We will not use recursion, because "List.Generate" is faster. Logic of "List.Generate" function is similar to recursion. We have three steps:
1) First step is to establish initial conditions. TableToExpand is the table from the image above. ColumnNumber is a column of that table that will be first process, so it will be number zero.

2) Second step is condition. We will end processing of columns when we reach the last column. Note that number of columns in our table will increase with each "Table.ExpandTableColumn". This means that we don't have only two columns from the table from the image above ("Name" and "Tabelix"). As we expand columns, number of columns will increase (like on the first image in this blog post).

3) Third step is processing.  In first cycle, processing is done on initial arguments TableToExpand and ColumnNumber. After first cycle, new values will be created and those new values will be input arguments for the nest cycle. Just like in recursion.
a) Here we use IF logic to decide how to process current column. If we have normal column, we want change anything. If we have column with nested tables, we will apply expansion with "funcExpandColumn". In all other cases we have mixed column. For mixed columns we will call function "funcTabelizeExpand" which will first make sure that all the values are tables and then it will call "funcExpandColumn" to finish the job. Result of the first cycle will be initial table unchanged, or it will be initial table with one column expanded.
b) We also have to change the column that will be processed in the next cycle. If we didn't work on column that had only "non table" values, then that column is just expanded and newly created column on that position could also contain nested tables. We will have to cycle through column on that position again. If our column had only normal values (numbers, strings, dates) then we can jump to the next column.

( TableToExpand as table ) =>
let
    ExpandAll = List.Generate( 
        ()=>[ TemporaryResult = TableToExpand, ColumnNumber = 0 ]
        , each [ColumnNumber] < Table.ColumnCount( [TemporaryResult] )
        , each [ TemporaryResult = if funcIsOnlyNonTables( [TemporaryResult], [ColumnNumber] ) then [TemporaryResult] 
             else if funcIsOnlyTables( [TemporaryResult], [ColumnNumber] ) 
                 then funcExpandColumn( [TemporaryResult], [ColumnNumber] )
             else funcTabelizeExpand( [TemporaryResult], [ColumnNumber] )
        , ColumnNumber = Iif not funcIsOnlyNonTables( [TemporaryResult], [ColumnNumber] ) then [ColumnNumber] 
             else [ColumnNumber]  + 1  ]               
        , each [TemporaryResult]
    )
    , OnlyLastElement = List.LastN( ExpandAll, 1 ){0}
in
    OnlyLastElement

"List.Generate" will return all the interim results as a list. That is why, we want to fetch only the last one.

Sample file can be downloaded from here. Just go to "XMLsInput" query to change the folder where XML files are placed on your hard disk.

Three interesting cases in Power Query

Code for all three examples can be found in sample file that can be downloaded below.

Uncomplete Pivot

After cleaning some data from XML file, I got all the values presented vertically (1). My goal was to transform this table (1) into normal horizontal table (2). This can be done with Table.Pivot function.

Problem is that, above, in the left table we only have 2 columns. That is enough to write down our Table.Pivot function, but our result will not be correct. We will get an error.

Table.Pivot( FillDownID, { "ID", "OrderDate", "Item", "Units" }, "Name", "Value" )

The problem stems from the fact that there are no other columns to make up the rows of our pivot tables. So, we have to generate one more column. That column should uniquely define each row of a new table. We have to use ID values to create our new column.

First, we will use IF expression to create new column (1). After this, we can fill down values in that column, to fill empty spaces (2).

That is all. Now, we can use our Table.Pivot function to create final result, because our source table now has three needed columns.

Concatenated Values

I got an Excel file with values concatenated, as in (1). As always, we want our data presented as a regular table (2).

First we need to untangle concatented values that are tied to the names of contintents. Because we have two columns with such data (Stock and Sale), our first step is to unpivot table (1). Only then can we fix concatenated values.

We will then remove prefix "Continent-" from "Attribute" column (1->3). After that, we will split column "Value" into to two columns (2->4) by using function Table.SplitColumn. Next, we can have our final result by applying Table.Pivot function on the table with corrected columns.

Table.Pivot( SplitValueColumn, { "Sale", "Stock" }, "Attribute", "Value" )

Direct Join

We have two tables (1,2). We want to join them into one table (3). First row should be joined with the first row, second row with the second row, etc. Problem is that the first table contains only dimensions, and second table contains only values, there is no column to base our join on. We can not use Table.Join function.

Solution is to flip our columns and rows with Table.Transpose. We'll get transposed tables (1). Each table will have exactly 7 columns because the original tables had 7 rows. Now we can create their union (2) to get table (3). Table (3) has two problems. First problem is that columns and rows are switched, second problem is that we have lost column names. By transposing table (3) we can solve first problem.

Second problem should be solved by taking original column names from tables (1,2). Then, those column names must  be applied to the final table (4).

Sample file can be downloaded from here:

Literals in Power Query (M language)

Table

We can create empty table by specifying number of columns to create. For values we just have to provide empty list.
#table(4,{})
This is how we create regular table. All the columns will have unspecified type.
#table( {"A", "B"}, { {1, 2}, {3, 4} } )
We can assign type to table columns.
#table(type table [Digit = number, Name = text], {{1,"one"}, {2,"two"}, {3,"three"}} )

Some information about Tables

It is possible to make an union of several tables with "&" operator. Orphan columns will be filled with nulls.
#table({"A","B"}, {{1,2}}) & #table({"B","C"}, {{3,4}})

Tables are considered the same if for each column in one table there is equivalent column in another table. Order of columns is not important.

#table({"A","B"},{{1,2}}) = #table({"A","B"},{{1,2}}) // true
#table({"A","B"},{{1,2}}) = #table({"X","Y"},{{1,2}}) // false
#table({"A","B"},{{1,2}}) = #table({"B","A"},{{2,1}}) // true

Number

There are three ways to write literal numbers.
Regular = -1.5
Scientific = 2.3e-5
Hexadecimal = 0xff
There are also two special symbols for infinity and for "not a number". Infinity is something we get when we try to divide one and zero. If we try to divide zero and zero, we will get "NaN". "NaN" is the only value that is not equal to itself.
Infinity = #infinity
NaN = #nan

Type

With "type" keyword we can create literal types.
ListOfNumbers = type { number }
TwoNumberFieldsRecordType = type [ X = number, Y = number ]
DateTimeType = type datetime
TextType = type text

Logic, text and null

Logic literals are TRUE and FALSE. Text literals are wraped with quotes. Null is a special literal which symbolizes missing data.
TRUESymbol = true
FALSESymbol = false
TextLiteral = "word"
NullSymbol = null

Record

This is how we can write record literal.
Record = [ A = 1, B = 2 ]

Some information about records

Records are equal if for each field in one record ther is an equal field in another record. Order of fields is not important.

[ A = 1, B = 2 ] = [ A = 1, B = 2 ]        // true 
[ B = 2, A = 1 ] = [ A = 1, B = 2 ]        // true

We can make a union of several records. If we have one field in more than one record, then the value from the last record with that field will be the final one. In our example x will be assigned the value 3, and not the value 1.

[ x = 1, y = 2 ] & [ x = 3, z = 4 ]  // [ x = 3, y = 2, z = 4 ] 

List

List is created by placing comma separated list inside of curly brackets.
List = {1, 2, 3}

Some information about lists

Two lists are equal if the have the same elements and position of those elements is the same.

{1, 2} = {1, 2} // true 
{2, 1} = {1, 2} // false

We can concatenate several lists with & operators.

{1, 4} & {2, 3} // list concatenation: {1, 4, 2, 3} 

Special Signs

Special signs, that are invisible, can be written by their symbolic presentation.
TAB = #(cr)
LineFeed =  #(lf)
CariageReturn = #(tab)

I used some unusual unicode brackets in the "Expression" column to prevent typed text to be identify as a special sign. In the "Result" column, we can see that both "cr" and "lf" signs are causing line break.

If we want to type two characters "#(", we have to write them like "#(#)(", because those characters has to be escaped. Two signs "#(cr)#(lf)", when consecutive, can be typed as "#(cr,lf)", the result would be the same.

Unicode Signs

We can enter any Unicode symbol in Power Query by using their codes.
YenSymbol = "#(00A5)"
CopyRightSymbol = "#(00A9)"
CapitalTheta =  "#(0398)"

Binary – list of bytes

Binary data is actually list of bytes. We can present those bytes with letters, hexadecimal numbers or ordinary numbers.
BinaryByLetters = #binary("AQID")
BinaryByHexidecimalNumbers = #binary( {0x00, 0x01, 0x02, 0x03} )
BinaryByRegularNumbers = #binary({65, 66, 67})

Date and time

Date and time can be expressed with these literals.

#time(hour, minute, second)
#date(year, month, day)
#datetime(year, month, day, hour, minute, second) 
#datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes) 
#duration(days as number, hours as number, minutes as number, seconds as number)
For each argument there is a limit on what values that argument could have.
1 ≤ year ≤ 9999
1 ≤ month ≤ 12
1 ≤ day ≤ 31
0 ≤ hour ≤ 23
0 ≤ minute ≤ 59
0 ≤ second ≤ 59
-14 ≤ offset-hours ≤ 14
-59 ≤ offset-minutes ≤ 59

Some information about dates and times

We can concatenate date and time.#date(2013,02,26) & #time(09,17,00) // #datetime(2013,02,26,09,17,00)
We can add duration to date or to time.#datetime(2010,05,20,0,0,0) + #duration( 8, 0, 0 ) //#datetime(2010,5,20,8,0,0)
#time(8,0,0)+#duration(30,5,0,0)     //#time(13,0,0)
Duration can be multiplied.#duration(2,1,0,15.1) * 2      // #duration(4, 2, 0, 30.2)
Durations can be divided.#duration(2,0,0,0) / #duration(0,2,0,0)        //24
Dates and times can be converted to numbers and from.Number.From(#datetime(2020, 3, 20, 6, 0, 0)) // 43910.25
Date.From(43910) // #date(2020,3,20)
Time.From(0.7575) // #time(18,10,48)
Duration.From(2.525) // #duration(2,12,36,0)

Sample file can be downloaded from here: