How to Merge Queries by Column?

[Anonymous]

TABLE 1
StoreName	ProcessName	ManagerName	GoodProductCount
Fish n Chips	1	Bill	100
Fish n Chips	2	Steve	125
Dave's Fish	21	Shannon	100
Chum Bucket	88	Plankton	32

TABLE 2
StoreName	ProcessName	ManagerName	BadProductCount
Fish n Chips	1	Bill	12
Fish n Chips	1	Tom	200
Dave's Fish	20	Shane	150
Happy Sushi	11	Shao	85

DESIRED OUTPUT
StoreName	ProcessName	ManagerName	GoodProductCount	BadProductCount
Fish n Chips	1	Bill	100	12
Fish n Chips	1	Tom	0	200
Fish n Chips	2	Steve	125	0
Dave's Fish	20	Shane	0	150
Dave's Fish	21	Shannon	100	0
Chum Bucket	88	Plankton	32	0
Happy Sushi	11	Shao	0	85

I have tried using "Full Outer Join" to merge the two tables but when Table 2 data doesn't match ANY of the Table 1 data it leaves NULL values in the Output table for, "StoreName, ProcessName, MangerName". Thus only leaving the BadProductCount and NULL for everything else.

How can I merge Table 1 and Table 2 regardless if either table contains the same "StoreName, ProcessName, ManagerName" data?

[v-yalanwu-msft]

Hi @Anonymous  ,

According to you description, You could Append the two tables in power query, and then GroupBy the column. The steps are as follows:
Step1: Append two tables

Step2: Group By column

Step3: Replace values

The final output is shown below::

In addition, M language is as follows:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssszlDIU3DOyCwoVtJRMgRip8ycHBDTwEApVgdDhREQB5eklqWClBiZgpW4JJalxpQaGBiZFyuAlIOUgUwKzkjMy8vPQzLMOaM0V8GpNDk7tQQoamEBJAJyEvOyS8CqjI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreName = _t, ProcessName = _t, ManagerName = _t, GoodProductCount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreName", type text}, {"ProcessName", Int64.Type}, {"ManagerName", type text}, {"GoodProductCount", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", Table2}),
#"Grouped Rows" = Table.Group(#"Appended Query", {"StoreName", "ProcessName", "ManagerName"}, {{"GoodProductCount", each List.Sum([GoodProductCount]), type nullable number}, {"BadProductCount", each List.Sum([BadProductCount]), type nullable number}}),
#"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"GoodProductCount", "BadProductCount"})
in
#"Replaced Value"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak  The Append Queries Functionality just creates Duplicate data as shown below.

How can i resolve this?

Append Output
StoreName	ProcessName	ManagerName	GoodProductCount	BadProductCount
Fish n Chips	1	Bill	100	Null
Fish n Chips	1	Bill	Null	12
Fish n Chips	1	Tom	Null	200
Fish n Chips	2	Steve	125	Null
Dave's Fish	20	Shane	Null	150
Dave's Fish	21	Shannon	100	Null
Chum Bucket	88	Plankton	32	Null
Happy Sushi	11	Shao	Null	85

[amitchandak]

@Anonymous , that you can deal with by creating the aggregate table

refer: https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

[amitchandak]

@Anonymous , Append in power query should do. It will append the same column and add a new column where the column name does not match

https://radacad.com/append-vs-merge-in-power-bi-and-power-query