The technique here is, unfortunately, but necessarily, brute force. We use a table constructor to reconstruct our table by picking individual column values out of our base data table, R04_Table, and placing these values into the correct position for transposing the table. We do this by using a combination of the table constructor syntax coupled with the SELECTCOLUMNS and FILTER functions.
Since we begin with a two-row and three-column table in our base data table, R04_Table, the transpose of this table should result in a table with three rows and two columns. Thus, our table constructor for transposing R04_Table consists of three row sets. Each row set includes two columns, which are the values, and a third column, which we use for identification purposes. Technically, we would not need this additional third column (Row) unless we were to use the transposed table in further transformation or mathematical operations.
Each row set includes two SELECTCOLUMNS statements that form our first two columns, plus a hardcoded row identifier, that is, 1, 2, or 3. Within the first row set, for our first value, we use the FILTER function within the SELECTCOLUMNS function to filter down to only the row where the Index column equals 1. Then, we use SELECTCOLUMNS to get the value within the Column1 column. For our second value, we use the FILTER function within the SELECTCOLUMNS function to filter down to only the row where the Index column equals 2. Next, we use SELECTCOLUMNS to again get the value within the Column1 column. Our third value is hardcoded to 1.
The next two row sets repeat the same pattern as the first row set, except that we get Column2 values in the second row set and Column3 values in the third row set. In addition, our hardcoded row identifiers are 2 and X, respectively. Now, we can use a final SELECTCOLUMNS statement to return friendly names for columns from our table constructor.