Forum Discussion

jppuam's avatar
jppuam
Helper V
4 years ago
Solved

join 2 different tables

Hello, i'm trying to join 2 tables like this :

 

table A                                                                                                     table B

ID       Date     Category      Product        Code      value                         Date         reference       sucess      Type

 

I want to create a unique table that has :

ID       Date     Category      Product        Code      value   (all from table A and the non match fields from b)   reference  sucess type (from b)

 

is this possible ?

 

thanks,

JP                      

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jppuam 

    Full outer will merge rows from both TableA and Table B (matching or not matching).

    For example: 

    Table A has dates 2021/01/01 - 2021/01/03.

    Table B has date 2021/01/04.

    If you use Full outer to merge two tables, result is as below.

    Here is a blog about join kind in Merge in Power Query.

    For reference: Choose the Right Merge Join Type in Power BI

    If you want to expand all values in Table B for each Row in Table A, you can try to add custom column.

    Expand rows you need from Table B.


    Best Regards,

    Rico Zhou

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jppuam 

    I see Table A and Table B only has one same column Date. Do you want to combine to tables by Date column? Here I suggest you try Merge function in Power Query.

    For reference: Combine queries

    My Sample:

    Table A

    Table B

    Merge Table A and Table B by Date column by Left Outer Join Kind. Then you can expand Table B by Reference  Sucess and Type columns. If you have multiple key columns in two tables, use Ctrl + left click to select them in Merge function.

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

    • jppuam's avatar
      jppuam
      Helper V
      4 years ago

      Thanks RicoZhou,

      just one question, if i've in table A (some dates - not every days) and on table B other dates (for example like you put - Table B with record on day 4) than i should use full outer join right ?

       

      thanks,

      JP

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi jppuam 

        Full outer will merge rows from both TableA and Table B (matching or not matching).

        For example: 

        Table A has dates 2021/01/01 - 2021/01/03.

        Table B has date 2021/01/04.

        If you use Full outer to merge two tables, result is as below.

        Here is a blog about join kind in Merge in Power Query.

        For reference: Choose the Right Merge Join Type in Power BI

        If you want to expand all values in Table B for each Row in Table A, you can try to add custom column.

        Expand rows you need from Table B.


        Best Regards,

        Rico Zhou

         

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