Forum Discussion

Regular Visitor

8 years ago

Solved

Data shaping a synonym list

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 t...

v-xjiin-msft

8 years ago

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.

v-xjiin-msft

Solution Sage

8 years ago

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.