This is the standard dialog for changing the pivot table style. Below we will list the elements of this style that can be changed, with the corresponding images.
Some of these changes require that the appropriate Pivot Style Options are enabled.
"Whole Table" will affect all parts of the pivot table, including filters.
"Report Filter Labels" and "Report Filter Values" will only affect the filter section.
"First and Second Column Stripe", "First and Second Row Stripe", will alternately color columns and rows. If we specify both rows and columns, they will overlap, but the rows will have priority. It is possible for the same color to repeat multiple columns/rows. For example, we can have two green and then two orange columns and so on alternately.
The "First Column" and "Header Row" can also overlap, in the corner cell. Here too, the color given to the row will take precedence.
"The First Header Cell" will color the top left cell in the pivot body. The name of the measure used is usually written there.
"Subtotal Column" and "Subtotal Row" will color the subtotals by columns and rows. There are "Subtotal Column 1,2,3" and "Subtotal Row 1,2,3", so the first three levels of subtotals can have their own colors.
If we turn on the "Blank Row" option then we will have an empty line after each subtotal. The "Blank Row" part of pivot style will color all rows below the first blank row.
"Column Subheading" and "Row Subheading" will color the row and column headers. There are "Column Subheading 1,2,3" as well as "Row Subheading 1,2,3" so we can have up to three colors for different levels of headers.
For totals we have the opportunity to design "Grand Total Column" and "Grand Total Row".
We can do this manually, but the problem is that the number of columns can be too large. We need an automatic solution. This automated solution should allow us to specify custom formatting for each column. We will use the Tabular Editor for this.
In order to be able to change the formatting of the columns 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).
This is the code by which we will extract the names of all the columns. We will skip columns that are String typed because we do not need to adjust the format for them.
using System.IO;
var file = @"C:\Users\Sima\Desktop\Columns.csv"; //file where to export column names
using(var fileWriter = new StreamWriter(file))
foreach(var Tbl in Model.Tables )
foreach(var Col in Tbl.Columns)
if ( Convert.ToString(Col.DataType) != "String" ) //exclude String columns
{
fileWriter.Write( Tbl.Name + ";" + Col.Name + ";" + Col.DataType + "\n" );
}
The result will be a three-column CSV file. In each row we will see the name of the table, the name of the column and the type of the column (1). We will use this information to decide the desired format for each column (2). Using the Excel formula (3), we will create a code by which we will assign the desired format to each column in the PBID file (4).
After running this code, you still need to save these changes to the BPID file itself.
In Power Query "Table.CombineColumns" function is used to combine values from several columns (1) into one column (2). This function takes 4 arguments. First argument is source table. Second argument is list of columns to combine. Last argument is name of newly created column.
Third argument is what interests us. That argument explains how to combine column values. That argument is given by using one of this five functions:
Combiner.CombineTextByDelimiter
Combiner.CombineTextByEachDelimiter
Combiner.CombineTextByLengths
Combiner.CombineTextByPositions
Combiner.CombineTextByRanges
Some of these functions are simple, but others are not so obvious.
Combiner.CombineTextByDelimiter
This version is simple. It concatenate all values by placing delimiter between them. Combiner. CombineTextByDelimiter function is used just to give that delimiter. In thise example, delimiter is "–".
Combiner.CombineTextByDelimiter can have one more argument. That argument could take value QuoteStyle.None or QuoteStyle.Csv. Quote.Csv will change every column which has quotes, so that column content will be delimited with quotes and all original quotes will be doubled.
A
BB"BB
C
DDDD
=>
A–BB"BB–C–DDDD
QuoteStyle.None
A
BB"BB
C
DDDD
=>
A–"BB""BB"–C–DDDD
QuoteStyle.Csv
Combiner.CombineTextByDelimiter( "-", QuoteStyle.Csv or QuoteStyle.None)
Combiner.CombineTextByEachDelimiter
This version is similar to previous. This time we give a list of delimiters. Those delimiters are used sequentially to separate column values.
The question is what if have too many or too less of delimiters. If we have five delimiters, but four columns, the result will be unchanged "A_1_BBBB 2_C_3_DDDD". Surplus delimiters will not be used. If we have only two delimiters, but four columns, the result be "A_1_BBBB_2_CDDDD". There will be no delimiters between "C" and "DDDD".
This function also accepts QuoteStyle second argument.
Combiner.CombineTextByLengths
We define list of numbers. From each column only so many characters will be presented.
From first column we will take only two characters, from second column only three characters and so on. If we don't have enough characters in our column, column content will be padded with spaces to reach reserved space. If we have too many characters in column, rest of characters will be trimed. If our argument is { 2, 3, 3 } then the last column will not be presented.
C1
C2
C3
C4
CombinedByLenghts
A
BBBB
C
DDDD
=>
A␣BBBC␣␣DDD
This function has another argument called "template". Template is "background" text that we can use to create mask for our text.
So, content of first column will start on position (2), of second column on position (4), third column on position (6), last column on position (9). If our list contains only numbers { 2, 4, 6 } then there will be no last column values in final result.
0
1
2
3
4
5
6
7
8
9
10
11
12
A…
B…
C…
D…
Then, we'll add rest of letters to see what is happening. Columns will fill as much space as possible. If column content is not enough, reserved space will be filled with spaces. If our list only has values { 2, 4, 6, 9 }, last column would not be trimed to three "DDD".
0
1
2
3
4
5
6
7
8
9
10
11
12
A
B
B
C
D
D
D
This is final result.
C1
C2
C3
C4
CombinedByPositions
A
BBBB
C
DDDD
=>
␣␣A␣BBC␣␣DDD
This function also accepts Template second argument.
Combiner.CombineTextByRanges
This version is combination of Positions and Lengths versions. Our argument is sequence of pairs. First value in pair is position. The second is length.
We can see bellow that content of every column starts on positions 2, 4, 5, 7 respectively. Each columns content has maximal length 2, 4, 2, 3 characters respectively. Latter columns will overwrite former columns. For example, column 3 will overwrite column 2 on position 5 ("C" instead of "B").
0
1
2
3
4
5
6
7
8
9
C1
A
␣
C2
B
B
B
B
C3
C
␣
C4
D
D
D
result
A
B
C
B
D
D
D
If we only use pairs { { 2,2 },{ 4, 4},{ 5,2 } }, then last column will not be presented. Length of range can not be zero.
This function also accepts Template second argument.
Often, we do not want to refresh all the tables in Power Pivot or Power BI Desktop. We can choose subset of tables to refresh, both in Power Pivot and Power BI Desktop, as a permanent settings. It is also possible to select tables to refresh with mouse and only those tables will be refreshed. For Power Pivot we can use VBA to select tables to be refreshed.
Power BI Desktop
In Power BI Desktop, all you have to do is to uncheck the "Include in report refresh" option, for some of the tables, and those tables will not be part of refresh. This option is located in the contextual menu for each query, in Power Query window (1). We can see that only tables SampleTable1 and SampleTable2 will be refreshed (2) if we click on Refresh button in Ribbon. This is because we unchecked this option for SampleTable3 and SampleTable4.
Beside "Include in report refresh" option, which is permanent, we can choose which tables to refresh by selecting them in model view. Holding Ctrl key and clicking on the table headers in model view, we will select subset of tables. On any of those tables we can open contextual menu with right click. Refresh button is in this menu. Now only, tables SampleTable2 and SampleTable4 will be refreshed, because only those tables are selected.
Power Pivot and Power Query
Similar to Power BI Desktop, Power Pivot also has option to permanently disable refreshing of some tables (1). This option is placed in Properties for Queries and Connections (2). Queries are made by Power Query and for them we can control Refresh status for each query.
If the data is retreived using Power Pivot directly, without Power Query, we can only set refresh setting for each connection. This setting is valid for all tables that are loaded by using that connection. If we want to control refreshing of each table separatelly, we have to create separate connection for each of the tables. On the image bellow we can see that we have two connections made to the same Excel file. This is solution for having full control.
In Excel, we can also select tables in Diagram view (1). After this we should click on "Refresh" (not Refresh All), and only selected tables will be refreshed (3).
VBA
If name of query in Power Query is "SampleTable" then by using this line of code we can refresh only that query with VBA.
Volatile functions are recalculated each time any cell in the spreadsheet is changed. Some other actions can also cause recalculation, such as renaming sheets, inserting columns, deleting rows, etc. Any formula that contains a volatile function will also become volatile. Any cell that depends on a cell that contains volatile function will also become volatile. Too many cells that contain a volatile function in a spreadsheet will significantly slow down that spreadsheet.
Volatile functions are RAND, RANDBETWEEN, RANDARRAY, NOW, TODAY, OFFSET, INDIRECT. INFO and CELL functions can be volatile depending on which arguments they use.
UDF functions can become volatile if the statement "Application.Volatile" is placed in front of them. The function below is nonvolatile.
Function UnVolatileFunction()
UnVolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function
In contrast, this function is volatile because it contains an "Application.Volatile" statement..
Function VolatileFunction()
Application.Volatile
VolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function
Excel Functions That Return References
Excel functions that return references are OFFSET, INDEX, XLOOKUP, CHOOSE, SWITCH, IF, IFS, INDIRECT. This means that the result of the function is neither a scalar nor an array. The result is the address of the range in the table.
Although the reference refers to multiple cells, Excel 365 or 2021 can directly display the result of these functions as a spill (1). In older versions of Excel, it was necessary to convert these functions into array functions with Ctrl + Shift + Enter in order to display their result directly (2). If a given range contains only one cell, all versions of Excel will return the contents of that cell (3). If a range contains more than one cell, it has always been possible to wrap that range in an aggregate function that will result in a scalar that can be displayed in a spreadsheet (4).
Case Sensitive Excel Functions
In Excel, there are three functions that are case sensitive. Those are EXACT, FIND and SUBSTITUTE. There are also XLOOKUP and XMATCH functions that have arguments that can be used to control whether the functions are case sensitive. Those functions are available in Excel 365 and 2021.
Direct comparison of A1 and B1 cells will return TRUE (1), but if we use EXACT function, result will be FALSE (2).
