Forum Discussion
Merge Queries: Columns with a true match AND a false match
Hello everyone, I have two tables filled with two different kind of tasks (e.g. table X and Y), and some columns brings the start date and finish date. So, from the subtraction of dates I have th...
Hello - this is how you could accomplish this...
In this example, I have two tables: Table1 and Table2. I would like create a new table (Table3) with records in which the Student ID and Student Name match, but Attribute/Subject do not.
Table1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUBWZGqxUqxOtJIRkO3k7AIkg5MzU/OSU5HkjIFsZxdXIOmbWJJRjCRjAmS7uLphkTEF2eLmjtUuMyDbzd0DVZeCUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Attribute = _t, Exemption = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Attribute", type text}, {"Exemption", type text}}) in #"Changed Type"Table2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrE60khGQ6eTsAiSDkzNT85JTgSwzU7CUMZDp7OIKJH0TSzKKgbQJRMIEyHRxdUOSgBpmCrLBzR3FHnOIlBmQ6ebuASQ9MotL8osqoVKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t, #"Marks " = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Subject", type text}, {"Marks ", Int64.Type}}) in ChangeTypeTable3:
let Source = Table2, Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter), FilterSubtables = Table.SelectRows ( Merge, each [Subject] <> [ExemptionData][Attribute]{0}) in FilterSubtables
jennratten
Super User
Super UserHello - this is how you could accomplish this...
In this example, I have two tables: Table1 and Table2. I would like create a new table (Table3) with records in which the Student ID and Student Name match, but Attribute/Subject do not.
Table1:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUBWZGqxUqxOtJIRkO3k7AIkg5MzU/OSU5HkjIFsZxdXIOmbWJJRjCRjAmS7uLphkTEF2eLmjtUuMyDbzd0DVZeCUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Attribute = _t, Exemption = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Attribute", type text}, {"Exemption", type text}})
in
#"Changed Type"
Table2:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrE60khGQ6eTsAiSDkzNT85JTgSwzU7CUMZDp7OIKJH0TSzKKgbQJRMIEyHRxdUOSgBpmCrLBzR3FHnOIlBmQ6ebuASQ9MotL8osqoVKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t, #"Marks " = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Subject", type text}, {"Marks ", Int64.Type}})
in
ChangeType
Table3:
let
Source = Table2,
Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter),
FilterSubtables = Table.SelectRows ( Merge, each [Subject] <> [ExemptionData][Attribute]{0})
in
FilterSubtables
Hi jennratten,
Thank you for your help, you've done it smoothly, I appreciate it.
I've changed the last part to instead of filtering rows, I added a new column with a if statement to convert the "all-matches" to null values.
let
Source = Table2,
Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Merge, "Custom", each if [Subject] <> [ExemptionData][Attribute]{0} then [ExemptionData] else null),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student ID", "Student Name", "Attribute", "Exemption"}, {"Custom.Student ID", "Custom.Student Name", "Custom.Attribute", "Custom.Exemption"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ExemptionData"})
in
#"Removed Columns"
Thus, I have now exactly what I needed. Thank you again.
- jennratten
You are very welcome!