[Merge Queries] How to show data of both table no matter the first one has no value?

[Yunyunh]

Hi, 

I have two tables, the first one shows "sell out qty" and the second one shows "ready to ship qty" and I merged two tables based on the item code and created a visual table and filter by month. The problem is, for example, when a item has no "sell out qty" in January but does have "ready to ship qty," the table will show blank. Does anyone know how to show the data of the second table while doing merge queries? 

[Anonymous]

Hi @Yunyunh 

@Idrissshatila Good share!

Here are a few ways that might solve your problem:

One option is to use the Full Outer join kind when merging the tables. This will keep all the rows from both tables, and fill in any missing values with nulls.

Another option is to use the Left Outer join kind when merging the tables. This will keep all the rows from the first table, and match them with the corresponding rows from the second table. If there is no match, the values from the second table will be null.

A third option is to use the Right Outer join kind when merging the tables. This will keep all the rows from the second table, and match them with the corresponding rows from the first table. If there is no match, the values from the first table will be null.

You can then replace the nulls with zeros, by using the Replace Values option in the Transform tab.

You can view the link below for more details:

Merge queries overview - Power Query | Microsoft Learn

Regards,

Nono Chen

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

[Idrissshatila]

Hello @Yunyunh ,

replace blank with 0 in power query, use the replace option.

https://www.youtube.com/watch?v=f4oqBE_wjiE

[Yunyunh]

I tried to replace blank with 0 in power query, but it does not change show 0. 

Is this setting correct?