Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Only Show Duplicates between two tables

Hi,

 

Please, i have two tables, and i need to show only the lines that just exists in a only one. 

 

Example:

 

Table 1: Name, ID, Function

Table 2: Name, ID, Function

 

Then i have to show just the lines that exists in one of the tables.

 

Thank you so much!

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Mariusz,

     

    Thank you so much for the answer, i got it using the merge columns. There is an option that can show only the lines that exists in one of the columns.

     

    Best regards,

     

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    You can use DAX expression to create a new table like below.

     

    yourTable = 
VAR a = TableA
VAR b = TableB
VAR ab = EXCEPT(a, b)
VAR ba = EXCEPT(b, a)
RETURN 
UNION(ab, ba)

     

    Regards,
    Mariusz

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

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    You can use Query Editor to merge the tables like below.

     

    // TableA
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ValueA = _t])
in
    Source

// TableB
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclKK1YlWcgaTLkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ValueB = _t])
in
    Source

// Merge1
let
    Source = Table.NestedJoin(TableA, {"ValueA"}, TableB, {"ValueB"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"ValueB"}, {"ValueB"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded TableB", each ([ValueB] = null) or ([ValueA] = null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Value", each if [ValueA] = null then [ValueB] else [ValueA], type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Value"})
in
    #"Removed Other Columns"


    Regards,
    Mariusz

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

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    The below DAX will return only maching rows from both tables.

    yourTable = NTERSECT(TableA, TableB)

    Regards,
    Mariusz

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mariusz,

       

      Thank you so much for the answer, i got it using the merge columns. There is an option that can show only the lines that exists in one of the columns.

       

      Best regards,

       

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous ,

        Have you solved your problem?

        If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

        If you still need help, please share your data sample and your desired output so that we could help further on it.

        Best Regards,

        Cherry