Data Editing User Guide

  • Last update:January 05, 2024
  • Overview

    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.

    Data Cleaning

    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.

    1703485450320.jpg


    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.

    1703233767008.jpg


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

    1703233585084.jpg


    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.

    1703234470044.jpg

    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 FineBI1703235716374.jpg

    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.1703475619011.jpg

    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.

    Adjusting Data Structures

    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.

    1703238717390.jpg


    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. 

    1703249596536.jpg


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

    企业微信截图_17032392471791.png



    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


    Merging Multiple Tables

    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.

    1703476294306.jpg


    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

    动图1.gif


    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.

    14.png

    Join

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

    动图2.gif

    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.


    Data Analysis

    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

    1703254669831.jpg


    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.

    企业微信截图_17032560234223.png


    Data Validation

    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.

    1703257058993.jpg

    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. 

    1703257676264.jpg

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

    动图-10.gif

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


    Summary

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

    TypeApplication ScenarioDocument

    Multiple Tables 

    Merging

    Vertically merge multiple tables into a long table.Union All
    Horizontally merge the fields of multiple tables into a wide table.Join
    Add fields from other tables to this table based on the match basis.

    Adding Column from 

    Other Tables

    Column Adding

    Add a column through the calculation of existing fields without 

    affecting the existing fields.

    Basic Functions of the 

    Added Column 

    Adding a Formula Column

    Adding a Summary Column

    Adding a Condition Label 

    Column

    Adding a ColumnTime 

    Interval

    Adding a Column–Getting Time

    Filter/SortingFilter the data.Filter
    Delete duplicate rows of all fields/partial fields.Delete Duplicate Row
    Sort the data.Sorting

    Data Structures 

    Adjusting

    Split the string quickly according to requirements.Spliting Field

    The Row to Column function can change a one-dimensional table 

    into a two dimentional table.

    Row to Column
    Convert a two-dimensional table into a one-dimensional table.Column to Row
    Field Settings
    Hide a field.Field Settings
    Rename a field.
    Change the field type.
    Adjust the field type.
    Group SummaryGroup the data and summarize the grouped data for calculations.Group Summary


    附件列表


    主题: Adding and Editing Data
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy