Forum Discussion

josectps's avatar
josectps
Regular Visitor
2 years ago
Solved

Compare tables in paralel

Hi everyone,

 

I have two tables like the below.

 

 

 

And I would like to have them next to each other. 

Rows should match each other, but if there is no match then we should leave a blank.

Also if there is a difference in the column area, then it should be highlighted as per below.

 

 

Additionally, I had to include the info in a table embebed the message as below, but I can't attach the file itself, I guess I meant to include a link to the file in the cloud. How do you do that? I wouldn't want to put a link to my local folder on a public space. Apologies for my ignorance.

 

Thank you in advance for your help.

 

Kind regards.

 

nameareaformat  nameareaformat
James1A  James1B
Marcus1A  Marcus3B
Maria2A  Helen2B
Julia6A  Maria2B
     Josh1B
     Steven4B
     Julia7B
        
        
        
        
        
        
nameareaformat nameareaformat 
James1A James1B 
Marcus1A Marcus3B 
    Helen2B 
Maria2A Maria2B 
    Josh1B 
    Steven4B 
Julia6A Julia7B 

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hi, josectps use Table.Join but rename columns in one of tables so column names are different and sort both tables by name columns. And no, you can't highlight anything in PQ - create separate column to check the difference.  

    let
    tbl_a = Table.Sort(your_table_a, {"name_a"}),
    tbl_b = Table.Sort(your_table_b, {"name_b"}),
    tj = Table.Join(tbl_a, "name_a", tbl_b, "name_b", JoinKind.FullOuter, JoinAlgorithm.SortMerge)
in
    tj

3 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi,

     

    1. it is not purpose of Power Query to do such transformation. I recommend to do it directly in Excel.
    2. not possible to highlight cells in PQ
  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    hi, josectps use Table.Join but rename columns in one of tables so column names are different and sort both tables by name columns. And no, you can't highlight anything in PQ - create separate column to check the difference.  

    let
    tbl_a = Table.Sort(your_table_a, {"name_a"}),
    tbl_b = Table.Sort(your_table_b, {"name_b"}),
    tj = Table.Join(tbl_a, "name_a", tbl_b, "name_b", JoinKind.FullOuter, JoinAlgorithm.SortMerge)
in
    tj
    • josectps's avatar
      josectps
      Regular Visitor
      2 years ago

      Thank you,

       

      Using table merge and Full outer did the thing. 

       

      I still struggle with the Advance editor.

       

      Thanks for your help!