How to Extract Data from a Single Column into Multiple Columns in Bold BI

In some scenarios, you may encounter a dataset where multiple pieces of information are stored in a single column, separated by a specific character. For instance, you might have a column like ‘US_Donations_18-19_’, where you want to extract the data into three separate columns - Country, Type, and Date.

Currently, Bold BI does not directly support the extraction of columns from a single column. However, this requirement can be achieved by using expressions to extract the data.

The syntax for this operation is as follows:

SPLIT_PART([Value], '_', 1)

In this syntax:

• [Value] is the name of the column from which you want to extract data.
• '_' is the separator that distinguishes between different pieces of data in the column.
• 1 specifies the part of the column to be included. In this case, 1 would be for ‘Country’, 2 for ‘Type’, and 3 for ‘Date’.

For more information on how to configure these expressions, please refer to the BoldBI Help Document on Configuring Expression Columns.

Note: The split_part function is supported in several data sources in Bold BI, including:

1. PostgreSQL: This function is used to split a string into multiple parts based on a delimiter.

2. MySQL: Although not directly supported, similar functionality can be achieved using the SUBSTRING_INDEX function.

3. SQL Server: Similar functionality can be achieved using the STRING_SPLIT function.