Inverse perspective

1. Overview

1.1 Expected effect

Column to row conversion is a conversion of a two-dimensional table to a one-dimensional table.

• One-dimensional tables are also often called pipeline tables. Generally, there are fixed column names, and normally you only need to add one row of data to enter.

• A two-dimensional table is a relational table. Usually, the values of data areas need to be determined simultaneously by rows and columns.

In terms of data relationships, one obvious feature of a two-dimensional table is that a portion of the column fields is fields of the same nature. Whereas a one-dimensional table does not have any two fields of the same nature.

For example, the Excel table "Regional consumption expenditure" is a two-dimensional table, as shown in the following figure.

1）After converting to a one-dimensional table, each column is an independent dimension, which is more suitable for data analysis. For example, after the "Regional consumption expenditure" table is processed with the Self-Service dataset, the original Expenses amount and Consumption amount are displayed by each row of data, as shown in the figure below.

2）The effect of dragging and dropping processed data in the grouping table of the dashboard is shown in the following figure.

1.2 Implementation idea

The original data is processed by the Self-Service dataset, and the data indicators to be converted are transferred to the new columns, and the conversion of rows and columns is completed by merging the new columns up and down.

Note: Please refer to Row to Column Conversion for the row to row conversion operation.

2. Example

2.1 Load data

Data download: Regional consumption expenditure.rar

Login to FineBI Data Decision System, enter "Data Preparation>Package", click "Add Table>EXCEL Data Set," select "Regional consumption expenditure" table and conduct it, as shown below.

Click on the "Regional consumption expenditure" table to preview the data, the result is shown in the following figure.

2.2 Column to row operation

2.2.1 Add new column

1）Click "Add Table" to create a new Self-Service dataset, as shown in the following figure.

2）Select the "Regional consumption expenditure" table, click on the field for data processing, add the field Region and consumption amount, and the preview effect will be displayed automatically on the right side, as shown in the following figure.

3）Click to add a new column, as shown in the following figure.

4）Add a new column to the text type consumption amount field and name the new column as type and click "OK", as shown in the following figure.

5）Click "Save" to save dataset 1.

Note: Update the data after adding the Self-Service dataset, otherwise "merges up and down" cannot select the newly created Self-Service datasetA.

6）Use the same way to add dataset 2, do the same "New column" and "Select field" processing for the table, but use the fields "Region" and "Expenses amount", add a new column of text type "expense amount", and named as "Type". The effect is shown in the following figure.

2.2.2 Merges up and down

1）In the edit screen of dataset 2, add a new column and click "add" and select "merges up and down", as shown in the following figure.

2）Add the merge table as Self-Service dataset 1 , as shown below.

3）Edit the merge table as needed, merge the expense type and consumption type, adjust the column order, and the data preview will pop up automatically below. Click "Save" after configuration to complete the column-to-row operation. The following figure shows.

2.3 Effect view

See Section 1.1 of this document for the implementation.