Transpose a table (Beta)

🚩

This documentation describes a public beta feature and is under construction. This page should not be considered part of our published documentation until this notice, and the corresponding Beta flag on the feature in the Sigma service, are removed. As with any beta feature, the feature discussed below is subject to quick, iterative changes. The latest experience in the Sigma service might differ from the contents of this document.

Beta features are subject to the Beta features disclaimer.

Sigma supports transposing a table from rows to columns, or columns to rows. Transposing data can be useful for data analysis, as it allows you to pivot or unpivot your data according to your individual use case.

For example, the table below shows a record of parking lot entries and exits, with each row showing the entry and exit date for each car.

To shape the data such that all the date values are in a single row, a row to column transpose can be performed, resulting in the table transforming into something like this:

This can be especially useful for visualizations, as you may want to reshape your data to suit specific chart types –such as needing date values oriented in a single column for line charts).

This document explains how to transpose your data from rows to columns, or columns to rows, and includes examples of each.

System and user requirements

You must be the workbook owner or be granted Can explore or Can edit access to the workbook.

Limitations

• Transposing input tables is not supported.
• Transposing calculated columns is not supported, and any existing filtering, sorting or grouping is not reflected in the transposed output. This is because transposing is done on the data from your data platform, not the element that is in your workbook.
• A limit of 200 unique row values can be transposed from a selected column. An error message is shown if you select a column with more than 200 unique row values.

Transpose row to columns

1. From your desired table, select More.
2. From Element source, select Transpose.
3. In the Transpose Table modal, Select Row to column.
4. Select your desired Column to tranpose. When transposed, the data in this column is rearranged such that each unique value becomes a new column header.

❗️

If your selected column has more than 200 unique values, an error appears.

5. Select your Value column. This is the column that is used for the calculation that you select in Aggregate.
6. Select your preferred Aggregate method. The aggregation methods shown vary based on the data type of the selected Value column.
7. Select the checkboxes next to your desired Output columns. These are the columns you want included in the transposed output, and do not impact any other calculations in the transpose. A preview of your transposed table is available under Output preview.
   • The order you select the columns in is the order they appear in the table, from left to right.
   • Any remaining source columns from the original source table are used to group the transpose. For an example of these groupings, see Row to column transpose example: Counting games won and lost. For more information on groupings in general, see Group columns in a table in Create and manage tables.
8. Select Submit.

Example: Counting games won and lost

For example, you might have a table of baseball games that your team has played at home, with one row of data for each game played and columns for opponent, result, and division rank after the game.

If you want to calculate the total games that your team has won or lost at each division rank, you can transpose the table by division rank.

To obtain this count:

1. Select Row to column transpose.
2. To set L (Loss) and W (Win) as the column headers for the transposed table, select Win Loss as the Column to transpose. The L (Loss) and W (Win) values appear as new columns in the transposed table.
3. To calculate the number of wins or losses for a given opponent, select Opponent as the Value Column.
4. To calculate the number of non-null and non-empty values, select Count for the Aggregate.
5. Select Division Rank, L and W as Output columns. The table output is automatically grouped by Division Rank, as that is the only remaining source column. This sums the total Count of wins and losses at each division rank.

You might want the output to be additionally grouped by another column, like Start Time. To do this:

1. In Output columns, select Start Time (keeping the existing selections of Division Rank, L and W). For each game start time, the transpose shows the number of wins and losses, at each division rank. Note that not all start times show all four division ranks, as no wins and losses were recorded at those times.
2. [optional] To make the output easier to understand, you can also sort the Start Time column to ascending, so that the data is sorted from earliest to latest game in the day. In the Start Time column, right click or click the caret () to open the column menu, then select Sort ascending. Once sorted, the output will look something like:

You might also want to find out the number of unique opponents that your team has won or lost against at each division rank. To obtain this:

• Change the Aggregate method to CountDistinct. CountDistinct provides the number of unique values within the Opponent column. The values represent the number of different opponents the team has beaten at each division rank. Your output will look something like:

Transpose columns to rows

1. From your desired table, select More.
2. From Element source, select Transpose.
3. In the Transpose Table modal, select Column to row.
4. Select your Columns to merge.
5. Enter a Column label for merged columns. This label is the column header of the new column that contains the previous header values of the merged columns.
6. Enter a Column label for values. This is your column headers for the new column containing the values of your merged columns.
7. Select the checkboxes next to your desired Output columns. These are the columns you want included in the transposed output, and do not impact any other calculations in the transpose. A preview of your transposed table is available under Output preview.
8. Select Submit.

Example: Merging audit log event times

For example, you might have a table of audit log events. Each row details one unique table sync, with information like connector ID, table name, write to table start/end, sync start/end, and unique table sync key.

If you want a table with rows for every table event and its timestamp, you can transpose the table and merge these columns. To obtain this new table:

1. Select Column to row transpose.
2. For Columns to merge, select Write to Table Start, Write to Table End, Sync Start and Sync End. These are all merged into one column.
3. For Column label for merged columns, enter Event Type as the name for the merged column.
4. For Column label for values, enter Timestamp.
5. Select the rows you want represented in the table: Unique Table Sync Key (so it is clear which table the event is linked to), and the new columns, Event type and Timestamp.
6. [optional] To make the output easier to understand, you can also sort the Unique Table Sync Key column to ascending, so that the events for each table are in ordered rows. To sort a table by one column ascending, right-click or click the caret () to open the column menu, then select Sort ascending.