Data Editing User Guide

On FineBI's data editing page, you can quickly perform operations such as data cleaning, merging multiple tables, adjusting data structures, data analysis, and data validation to meet the data processing needs in actual business scenarios. These operations can help you get organized and reliable data from disorderly information, providing strong support for subsequent analysis and decision-making.

This document introduces how to quickly process data using FineBI’s functions in actual business scenarios.

During the data cleaning stage, you can use FineBI's data editing function to perform the following operations.

Adjusting Fields

You can quickly organize data by preserving or deleting fields, adjusting field positions, and renaming fields.

1. Deleting Unnecessary Columns

After you upload the data, you may find that some fields are unnecessary for the analysis or there are blank columns. Click the field header and choose "Delete Column" from the dropdown menu, as shown in the following figure.

2. Converting Field Type

During the analysis, fields are classified by type and the dimensions are used to measure the indicators. Correct field type as needed.

Dimension fields like User ID and Product Code are often mistakenly recognized as indicators since the field data are numbers. Change them to the Text type, as shown in the following figure.  

3. Renaming and Translating Fields

Rename fields that have non-standard names. For example, you can convert Chinese fields to English fields.

Deleting Duplicate Rows

The deduplication function can help you quickly remove duplicate rows in the dataset to ensure data uniqueness.

You can delete duplicate rows of all fields or selected fields in FineBI.

For example, the Order ID in the following figure is a unique field, and only one record should be kept for an Order ID. But there are many duplicate values for the same ID in the drop-down list of this field.

In this case, you can use the Delete Duplicate Row function to process the data, as shown in the following figure.

Handling Null Values

The null value is an inevitable problem and the handling methods vary with business scenarios.

Meaningless Null Values

When the data volume is huge, few null values will not cause significant fluctuations in calculations of Sum or Average, so you can simply ignore them.

If you want to remove rows with null values as dirty data during processing, you can use the shortcut filter in the header to quickly exclude empty values.

You can also add Filter steps to get Not Null data.

Meaningful Null Values

For example, the student's English score is null in the following figure, which may result from his absence from the exam due to illness. In this context, you cannot ignore it or delete this row of data.

In response, you can add labels for these special cases to facilitate data filtering in subsequent analysis.

You can make it by adding a Formula Column or a Condition Label Column in FineBI

Handling Fields

You can add columns to customize calculations or add auxiliary fields according to business needs.

For example, you can add a Formula Column to process date fields. For details, see Common Date Formula.

You can get the Year and Month fields of value type according to the date, which shall be converted into Text or Time fields for subsequent analysis.

According to specific analysis requirements, you can use the following functions to adjust and transform data structures.

Splitting Fields

You can split a field that contains multiple field values into multiple rows or columns for fine-grained data analysis.

This function applies to scenarios where the original fields contain mixed data, which poses challenges to conducting analysis. After you split the field into rows or columns and convert the field type as needed, the field structure becomes simple and clear.

Row to Column

The Row to Column function helps you convert multiple rows in a dataset into one row, which is suitable for certain data pivoting analyses.

For example, you can convert different subjects in the Subject field into fields, forming new columns. 

You can use the Row to Column function to achieve the conversion in one step. 

Column to Row

The Column to Row function helps you convert multiple columns in a dataset into one column to facilitate specific data operations and analysis.

You can use it to reverse the row-to-column conversion. For example, convert different subject fields into a Subject field, merging them into a column. For details, see Changing Column to Row. 

To facilitate analysis, you may need to merge multiple data tables into one dataset, which can be achieved by the following functions.

Union All

The Union All function can help you merge multiple data tables in a row-wise manner, and the result set contains all the rows of the original tables. The merged table expands vertically, the analyzed fields do not increase, but the number of rows has increased.

For details, see Union All.

For example, here are three Excel files about student scores. You can select the Student Scores (Sheet 1) table and merge it with the Student Scores (Sheet 2) table through Union All. 

Adding a Column from Other Tables

You can use the Column from Other Tables function to obtain data from other data tables and add the data to the current dataset as needed for further analysis and calculation.

This function can aggregate and merge the indicator fields of other tables through the SUMIF function or query corresponding dimensions and add them to the current table through the VLOOKUP function.

Join

You can merge multiple data tables based on specified join conditions through Left Join, Right Join, etc., to form new datasets.

For example, the English Scores table contains the English scores of students A, B, and C and the Chinese Scores table contains the Chinese scores of students A, B, and D. If you want to merge the two tables into one table that includes the scores of all students, select Full Join mode. For details, see Join.

FineBI's data editing page also provides tools and functions for data analysis, as described below.

Group Summary

The Group and Total functions help you divide the dataset into different groups by specified fields (such as grouping records with the same value, customizing grouping, grouping records by year/year-month for date fields, etc.), and perform aggregation calculations, such as Sum, Count, and Average. 

Adding a Column

You can use the Formula Column function to add a column with customized calculations into the dataset as needed, to better meet business requirements and data analysis needs.

During the data editing process, you can use FineBI's data validation function to validate the data based on preset rules and conditions, ensuring data validity and consistency.

After selecting the field, you can quickly obtain data such as average, sum, and record numbers in the lower left corner. You can verify familiar data and judge whether it is correct.

In FineBI, you can insert new steps while processing other steps and also temporarily cancel the application of certain steps.

In this way, you can use some key data after filtering to conduct trials while not involving confusing key steps. It is like the repeated calculations when you learn mathematics. It may seem tedious for experienced players, but it truly puts beginners at ease.

You can use the field header to select some data for the "sampling test" quickly. 

Flexibly use the steps for quick checking, as shown in the following figure. 

You can also perform operations such as copying and pasting steps in the step area on the right. 

The following table shows the analysis steps that can be added.