join 2 different tables

[jppuam]

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                      

[Anonymous]

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]

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 · Accepted solution]

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.

[VahidDM]

Hi @jppuam 

Please see this link:

https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/

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

Appreciate your Kudos✌️!!