merge two table return matched value and unmatched row

[THENNA_41]

 I having two table both  table some values same  and remaining values different . i want return matched rows and unmatched rows  

TABLE 1 :

LOCATION  PRODUCT     M1      M2 

1A04          DA001145     5           6

1B04          DA001145     15        16

1D04          DA001145     35        76

TABLE 2 :

LOCATION  PRODUCT     ACT VALUE      

1C04          CA001145         5 1         

1B04          DA001145        15        

1K04          KA001145        45        

output :

LOCATION  PRODUCT     M1      M2   ACT value

1A04          DA001145     5          6            0

1B04          DA001145     15        16         15

1D04          DA001145     35        76         0

1C04          CA001145       0           0       5 1   

1K04          KA001145        0          0        45   

any idea. tired full outer join but not getting c full values   

[lbendlin · Accepted solution]

here's a slightly less elegant but more readable version

let
    Source = Table.NestedJoin(#"Table 1", {"LOCATION", "PRODUCT"}, #"Table 2", {"LOCATION", "PRODUCT"}, "Table 2", JoinKind.FullOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(Source, "Table 2", {"LOCATION", "PRODUCT", "ACT Value"}, {"LOCATION.1", "PRODUCT.1", "ACT Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table 1",null, each [LOCATION.1],Replacer.ReplaceValue,{"LOCATION"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each [PRODUCT.1],Replacer.ReplaceValue,{"PRODUCT"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"LOCATION", "PRODUCT", "M1", "M2", "ACT Value"})
in
    #"Removed Other Columns"

[v-yalanwu-msft · Accepted solution]

Hi, @THENNA_41 ;

You could use append in power query and then group by .

1.append two tables

2. group by the same rows

The final output is shown below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnQ0MFHSUXJxNDAwNDQxBTJB2EwpVgco52RgooAqaQgmoNIuGNLGIMIcKB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOCATION = _t, PRODUCT = _t, M1 = _t, M2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", type text}, {"PRODUCT", type text}, {"M1", Int64.Type}, {"M2", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Table 2"}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"LOCATION", "PRODUCT"}, {{"M1", each List.Max([M1]), type nullable number}, {"M2", each List.Max([M2]), type nullable number}, {"ACT VALUE", each List.Max([ACT VALUE]), type nullable number}})
in
    #"Grouped Rows"

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.

[lbendlin · Accepted solution]

here's a slightly less elegant but more readable version

let
    Source = Table.NestedJoin(#"Table 1", {"LOCATION", "PRODUCT"}, #"Table 2", {"LOCATION", "PRODUCT"}, "Table 2", JoinKind.FullOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(Source, "Table 2", {"LOCATION", "PRODUCT", "ACT Value"}, {"LOCATION.1", "PRODUCT.1", "ACT Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table 1",null, each [LOCATION.1],Replacer.ReplaceValue,{"LOCATION"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each [PRODUCT.1],Replacer.ReplaceValue,{"PRODUCT"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"LOCATION", "PRODUCT", "M1", "M2", "ACT Value"})
in
    #"Removed Other Columns"

[lbendlin]

In a pinch a simple Table 1 & Table 2 will do.

A more accurate version is 

let
    Source = Table.NestedJoin(#"Table 1", {"LOCATION"}, #"Table 2", {"LOCATION"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"ACT Value"}, {"ACT Value"}),
    Source2 = Table.NestedJoin(#"Table 1", {"LOCATION"}, #"Table 2", {"LOCATION"}, "Table 2", JoinKind.RightAnti),
    #"Removed Other Columns" = Table.SelectColumns(Source2,{"M1", "M2", "Table 2"}),
    #"Expanded Table 3" = Table.ExpandTableColumn(#"Removed Other Columns", "Table 2", {"LOCATION", "PRODUCT", "ACT Value"}, {"LOCATION", "PRODUCT", "ACT Value"})
in
    #"Expanded Table 2" & #"Expanded Table 3"