Use 2 columns as 3

Hi Anonymous ,

(1) This is my test data.

(2) Create a new table, click “Advanced Editor” and copy and paste the following code.

let
    Table1 = Table1,
    Table2 = Table2,
    // Extend Table2 to include the combined DIMENSION2 value
    ExpandedTable2 = Table.AddColumn(Table2, "Combined_DIMENSION2", each [#"DIMENSION2-1"] & "_" & [#"DIMENSION2-2"]),
 
    // Creating the Cartesian product of Table1 for concatenation
    CartesianJoin = Table.AddColumn(Table1, "Temp", each ExpandedTable2),
    ExpandedCartesianJoin = Table.ExpandTableColumn(CartesianJoin, "Temp", {"Combined_DIMENSION2"}),
 
    // Filter rows to retain only the combined values of DIMENSION2 for rows containing DIMENSION2 in Table1
    FilteredTable = Table.SelectRows(ExpandedCartesianJoin, each Text.Contains([Combined_DIMENSION2], [DIMENSION2])),
 
    // Creating UNIFIED_DIMENSION columns
    UnifiedDimensionTable = Table.AddColumn(FilteredTable, "UNIFIED_DIMENSION", each [DIMENSION1] & "_" & Text.BeforeDelimiter([Combined_DIMENSION2], "_") & "_" & Text.AfterDelimiter([Combined_DIMENSION2], "_")),
 
    // Select the desired columns and remove the redundant columns
    Result = Table.SelectColumns(UnifiedDimensionTable, {"EMAIL", "UNIFIED_DIMENSION"}),
    #"Removed Duplicates" = Table.Distinct(Result)
in
    #"Removed Duplicates"

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.