You can view our full solution workflow below and
download it here!
 |
| Our full solution workflow. |
1. Pivot the products and associated sales.
The first step in our workflow is to convert the [Product 1] & [Product 2] fields and the [Sales] & [Sales 1] fields into two fields, [Product] & [Sales], so we have one product and one sales value per row.
To do this, we can use a column to rows pivot. First add the two product fields to Pivot1 Values. Then, add a second set of pivot values using the ‘+’ icon underneath the pivot type drop down.
Finally, add the two sales fields to the Pivot2 Values area. Take care to align [Sales] with [Product 1] and [Sales 1] with [Product 2] as this ensures they are on the correct rows together
 |
| Pivoting the Products & Sales into their correct rows. |
2. Remove the dashes from the product names.
Either in a clean step or in the pivot step we just used, update the new [Product] field by creating a new calculated field with the same name as follows:
[Product]
|
REPLACE([Product], '-',
' ')
|
This replaces all the dashes in our product names with spaces as desired.
3. Extract the address parts.
We used two separate techniques to pull our address apart: REGEXP_EXTRACT() & SPLIT().
a. Use RegEx to extract the property number.
We can’t simply split up the [Address] field to get the property number as some addresses don’t begin with the number whilst others do. A great way around this is using REGEXP_EXTRACT() to pull out the first string of digits in the field. The following RegEx command does just this; click on the RegEx to see the command broken down on RegEx101 in more detail.
[Property Number]
|
REGEXP_EXTRACT(
[Address]
)
|
b. Use a custom split to pull out the remaining address parts.
The rest of the address parts are evenly spaced out, all separated by a comma and a space. As we just want the town, postal code, and country we can use a custom split to split off the last 3 fields using the separator ", ".
 |
| Using a custom split on the last 3 fields with ", " as a separator. |
This creates the 3 fields we need. After this, we can open up the fields in the changes pane and rename them as appropriate.
 |
| Renaming custom split fields in the changes pane. |
After this, all that’s left is to remove the original [Address] field and admire the results.
