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
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
Solved! Go to 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"
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.
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.
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"
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |