Data shaping a synonym list

[Ken317]

 I am creating a synonym list to import into an on premises SharePoint site collection for search purposes. 

My data as I have it now looks like this.

Column1	Column2
term1	term2
term1	term3
term1	term4

For two way searching in SharePoint, I need convert this data to look like this.

Column1	Column2
term1	term2
term1	term3
term1	term4
term2	term1
term2	term3
term2	term4
term3	term1
term3	term2
term3	term4
term4	term1
term4	term2
term4	term3

I know there must be a way to do this through DAX or M Language, but I'm having trouble searching for a solution because I don't know how to describe what I'm trying to do.  Any help would be appreciated.

[v-xjiin-msft · Accepted solution]

Hi @Ken317,

To achieve your requirement, you can try following methods:

DAX:

Table 2 = 
VAR temp =
    UNION ( VALUES ( 'Table'[Column1] ), VALUES ( 'Table'[Column2] ) )
RETURN
    FILTER (
        CROSSJOIN ( temp, SELECTCOLUMNS ( temp, "Column2", [Column1] ) ),
        [Column1] <> [Column2]
    )

Power Query:

let
    Source = List.Distinct(List.Union({Table[Column1],Table[Column2]})),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each #"Converted to Table"[Column1]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Column1] <> [Custom])
in
    #"Filtered Rows"

Sample report: https://1drv.ms/u/s!AlqSnZZUVHmshW6_bTews8VM0izZ 

Thanks,
Xi Jin.

[Ken317]

Xi Jin,

Thank you.  That was exactly what I needed!