Forum Discussion

Ken317's avatar
Ken317
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.

 

Column1Column2
term1term2
term1term3
term1term4

 

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

 

Column1Column2
term1term2
term1term3
term1term4
term2term1
term2term3
term2term4
term3term1
term3term2
term3term4
term4term1
term4term2
term4term3

 

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's avatar
    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.

2 Replies

  • v-xjiin-msft's avatar
    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.

    • Ken317's avatar
      Ken317
      Regular Visitor
      8 years ago

      Xi Jin,

       

      Thank you.  That was exactly what I needed!