How to remove reversed values from 2 columns in Power BI

[Anonymous]

I have a table with values as follows

Actor-1   Actor-2    Value
A             B              2
A             C              1
B             A              2

B             C              2

Since the values of A - B and B - A are same, I want to remove one of those instances, giving me a table

Actor-1 Actor-2  Value
A            B             2
A            C             1
B            C             2

How can I achieve that?

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code on Transform Data -> New Query -> Blank Query -> Advanced Editor:

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
#"Texto Aparado" = Table.TransformColumns(#"Tipo Alterado",{{"Actor-1", Text.Trim, type text}, {"Actor-2", Text.Trim, type text}}),
#"Texto Limpo" = Table.TransformColumns(#"Texto Aparado",{{"Actor-1", Text.Clean, type text}, {"Actor-2", Text.Clean, type text}}),
#"Colunas Mescladas" = Table.CombineColumns(#"Texto Limpo",{"Actor-1", "Actor-2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Actors"),
#"Personalização Adicionada" = Table.AddColumn(#"Colunas Mescladas", "Personalizar", each let _a1 = Text.Split([Actors], ";"){0},
_a2 = Text.Split([Actors], ";"){1} in
Table.ToList(Table.SelectColumns(Table.SelectRows(#"Colunas Mescladas", each
(
(Text.Split([Actors], ";"){0} = _a2 and
Text.Split([Actors], ";"){1} = _a1)
or (Text.Split([Actors], ";"){0} = _a1 and
Text.Split([Actors], ";"){1} = _a2)
)), "Actors")){0}),
#"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada",{"Value", "Personalizar"}),
#"Duplicatas Removidas" = Table.Distinct(#"Outras Colunas Removidas"),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Duplicatas Removidas",{"Personalizar", "Value"}),
#"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Colunas Reordenadas", "Personalizar", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Personalizar.1", "Personalizar.2"}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Dividir Coluna por Delimitador",{{"Personalizar.1", type text}, {"Personalizar.2", type text}}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Tipo Alterado1",{{"Personalizar.1", "Actor-1"}, {"Personalizar.2", "Actor-2"}})
in
#"Colunas Renomeadas"

Did I answer your question? Mark my post as a solution!
Ricardo

[dax · Accepted solution]

Hi @Anonymous , 

You also could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Actor-1", "Actor-1 - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Actor-2", "Actor-2 - Copy"),
    #"Merged Columns1" = Table.CombineColumns(#"Duplicated Column1",{"Actor-1 - Copy", "Actor-2 - Copy"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns1", "Custom", each Text.Combine(List.Sort(Text.Split(Text.From([Merged]), ","),Order.Ascending),",")),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Value", "Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Merged", "Custom"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

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

[mahoneypat · Accepted solution]

Here is one more way to do it:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ListColumn", each List.Sort({[#"Actor-1"],[#"Actor-2"]})),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"ListColumn"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"ListColumn"})
in
#"Removed Columns"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ziying35 · Accepted solution]

let
    Source = Table.FromRows(
               Json.Document(
                   Binary.Decompress(
                       Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)
               ), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]
             ),
    result = Table.FromRows(List.Distinct(Table.ToList(Source,each _),each List.Sort(_)),Table.ColumnNames(Source))
in
    result

[mahoneypat · Accepted solution]

Here is one more way to do it:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ListColumn", each List.Sort({[#"Actor-1"],[#"Actor-2"]})),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"ListColumn"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"ListColumn"})
in
#"Removed Columns"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dax · Accepted solution]

Hi @Anonymous , 

You also could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Actor-1", "Actor-1 - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Actor-2", "Actor-2 - Copy"),
    #"Merged Columns1" = Table.CombineColumns(#"Duplicated Column1",{"Actor-1 - Copy", "Actor-2 - Copy"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns1", "Custom", each Text.Combine(List.Sort(Text.Split(Text.From([Merged]), ","),Order.Ascending),",")),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Value", "Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Merged", "Custom"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code on Transform Data -> New Query -> Blank Query -> Advanced Editor:

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYiOlWB0IzxmIDcE8kLgjXM4JKgfkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Actor-1" = _t, #"Actor-2" = _t, Value = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Actor-1", type text}, {"Actor-2", type text}, {"Value", Int64.Type}}),
#"Texto Aparado" = Table.TransformColumns(#"Tipo Alterado",{{"Actor-1", Text.Trim, type text}, {"Actor-2", Text.Trim, type text}}),
#"Texto Limpo" = Table.TransformColumns(#"Texto Aparado",{{"Actor-1", Text.Clean, type text}, {"Actor-2", Text.Clean, type text}}),
#"Colunas Mescladas" = Table.CombineColumns(#"Texto Limpo",{"Actor-1", "Actor-2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Actors"),
#"Personalização Adicionada" = Table.AddColumn(#"Colunas Mescladas", "Personalizar", each let _a1 = Text.Split([Actors], ";"){0},
_a2 = Text.Split([Actors], ";"){1} in
Table.ToList(Table.SelectColumns(Table.SelectRows(#"Colunas Mescladas", each
(
(Text.Split([Actors], ";"){0} = _a2 and
Text.Split([Actors], ";"){1} = _a1)
or (Text.Split([Actors], ";"){0} = _a1 and
Text.Split([Actors], ";"){1} = _a2)
)), "Actors")){0}),
#"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada",{"Value", "Personalizar"}),
#"Duplicatas Removidas" = Table.Distinct(#"Outras Colunas Removidas"),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Duplicatas Removidas",{"Personalizar", "Value"}),
#"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Colunas Reordenadas", "Personalizar", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Personalizar.1", "Personalizar.2"}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Dividir Coluna por Delimitador",{{"Personalizar.1", type text}, {"Personalizar.2", type text}}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Tipo Alterado1",{{"Personalizar.1", "Actor-1"}, {"Personalizar.2", "Actor-2"}})
in
#"Colunas Renomeadas"

Did I answer your question? Mark my post as a solution!
Ricardo