Use case

You need to split cell values in a column to different rows for further data aggregation. As your cell values are links, you need to preserve the original formatting.

Solution

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SEARCH / AS @a EX(SPLIT_VIEW('Favorite Websites', ",")) /
		RETURN(@a->'Name' AS 'Name', @a-> 'Skill' AS 'Skill', _ AS 'Favorite Websites') FROM T1
  6. Click Next. 
  7. Define the table settings and view options if needed. 

  8. Save the macro and the page.

In this case the underscore in the query (i.e.  _ AS 'Favorite Websites') is utilized as a beacon to return the transformed data in the Favorite Websites column, while @a -> returns the data corresponding to their row values, hence multiplying them as the Favorite Websites column data obtain additional rows after being split. 

 

The resulting table can be further utilized with the Pivot Table macro for distinct value aggregation, counting, and so on.