Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
THENNA_41
Post Partisan
Post Partisan

merge two table return matched value and unmatched row

 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   

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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"

View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi, @THENNA_41 ;

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

1.append two tables

vyalanwumsft_0-1627628998090.png

2. group by the same rows

vyalanwumsft_1-1627629043156.png

The final output is shown below:

vyalanwumsft_2-1627629066576.png

vyalanwumsft_4-1627629850670.png

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @THENNA_41 ;

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

1.append two tables

vyalanwumsft_0-1627628998090.png

2. group by the same rows

vyalanwumsft_1-1627629043156.png

The final output is shown below:

vyalanwumsft_2-1627629066576.png

vyalanwumsft_4-1627629850670.png

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
Super User
Super User

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
Super User
Super User

lbendlin_0-1627510726958.png

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"

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.