Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
Xi Jin,
Thank you. That was exactly what I needed!
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |