none
Merge - Changing join type RRS feed

  • Question

  • Hi

    How can you change the type of join (inner, left, right, full outer) when using the merge option?

    I am experimenting with the Merge option and see that it performs a left join via Table.AddJoinColumn by default.  However, the documentation also says:

    Table.AddJoinColumn performs the following joins:
    o Left Outer (default)
    o Inner
    o Right Outer
    o Full Outer

    but the syntax of this function does not show what argument controls the type of join.

    Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as table
    Thanks in advance for your help.
     

    Help Others! Don't forget to mark your thread as answered

    Rafael Salas - SSIS and more

    Monday, August 5, 2013 1:58 AM
    Moderator

Answers

  • Hi Rafael,

    This is an issue in the Library specification that will be addressed shortly. In order to modify the type of join, you need to use the function Table.Join instead of Table.AddJoinColumn. This function (Table.Join) is described in page 111 of the Library Specification. Note that the output of this function is a flattened table rather than a table with a nested column with the result of the operation (like Table.AddJoinColumn).

    We are currently working on better support in the UI for inner vs. outer join, so that users can make this decision within the Merge UI.

    Thanks,
    M.

    Monday, August 5, 2013 3:57 PM
  • Hi Rafael,

    Correct, Table.AddJoinColumn only supports Left join. We will address the misleading information on the Library Specification shortly.

    Thanks,
    M.

    Monday, August 5, 2013 5:47 PM

All replies

  • Hi Rafael,

    This is an issue in the Library specification that will be addressed shortly. In order to modify the type of join, you need to use the function Table.Join instead of Table.AddJoinColumn. This function (Table.Join) is described in page 111 of the Library Specification. Note that the output of this function is a flattened table rather than a table with a nested column with the result of the operation (like Table.AddJoinColumn).

    We are currently working on better support in the UI for inner vs. outer join, so that users can make this decision within the Merge UI.

    Thanks,
    M.

    Monday, August 5, 2013 3:57 PM
  • Thanks for replying Miguel.

    So this means Table.AddJoinColumns only supports Left join? If not, what is the argument and syntax to use?

    It is great to know that you guys are considering that enhancement. I did read about Table.Join but did not try it. I will look into it.

    Rafa


    Help Others! Don't forget to mark your thread as answered

    Rafael Salas - SSIS and more

    Monday, August 5, 2013 5:42 PM
    Moderator
  • Hi Rafael,

    Correct, Table.AddJoinColumn only supports Left join. We will address the misleading information on the Library Specification shortly.

    Thanks,
    M.

    Monday, August 5, 2013 5:47 PM