Forum Discussion
Merging tables with blank fields
- 9 years ago
A solution in Power Query; maybe a similar approach can be done in DAX.
Create a temporary table (UniqueAValues) with the nonblank distinct A values from both tables.
Merge these with Table1 and next with Table2 and take the maximum B value from either table.
Query UniqueAValues:
let Source = Table.FromColumns({List.Distinct(List.Select(Table1[A]&Table2[A],each _ <> null))},type table[A = text]), #"Merged Queries" = Table.NestedJoin(Source,{"A"},Table1,{"A"},"Table1",JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"B"}, {"Table1.B"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Table1",{"A"},Table2,{"A"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"B"}, {"Table2.B"}), #"Inserted Maximum" = Table.AddColumn(#"Expanded Table2", "B", each List.Max({[Table1.B], [Table2.B]}), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Table1.B", "Table2.B"}) in #"Removed Columns"Similarly for nonblank distinct B values.
let Source = Table.FromColumns({List.Distinct(List.Select(Table1[B]&Table2[B],each _ <> null))},type table[B = Int64.Type]), #"Merged Queries" = Table.NestedJoin(Source,{"B"},Table1,{"B"},"Table1",JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"A"}, {"Table1.A"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Table1",{"B"},Table2,{"B"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"A"}, {"Table2.A"}), #"Inserted Maximum" = Table.AddColumn(#"Expanded Table2", "A", each List.Max({[Table1.A], [Table2.A]}), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Table1.A", "Table2.A"}) in #"Removed Columns"Append both temporary tables and remove duplicates.
let Source = Table.Combine({UniqueAValues, UniqueBValues}), #"Removed Duplicates" = Table.Distinct(Source) in #"Removed Duplicates"You won't get the sort order, but that shouldn't matter.
A solution in Power Query; maybe a similar approach can be done in DAX.
Create a temporary table (UniqueAValues) with the nonblank distinct A values from both tables.
Merge these with Table1 and next with Table2 and take the maximum B value from either table.
Query UniqueAValues:
let
Source = Table.FromColumns({List.Distinct(List.Select(Table1[A]&Table2[A],each _ <> null))},type table[A = text]),
#"Merged Queries" = Table.NestedJoin(Source,{"A"},Table1,{"A"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"B"}, {"Table1.B"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Table1",{"A"},Table2,{"A"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"B"}, {"Table2.B"}),
#"Inserted Maximum" = Table.AddColumn(#"Expanded Table2", "B", each List.Max({[Table1.B], [Table2.B]}), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Table1.B", "Table2.B"})
in
#"Removed Columns"
Similarly for nonblank distinct B values.
let
Source = Table.FromColumns({List.Distinct(List.Select(Table1[B]&Table2[B],each _ <> null))},type table[B = Int64.Type]),
#"Merged Queries" = Table.NestedJoin(Source,{"B"},Table1,{"B"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"A"}, {"Table1.A"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Table1",{"B"},Table2,{"B"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"A"}, {"Table2.A"}),
#"Inserted Maximum" = Table.AddColumn(#"Expanded Table2", "A", each List.Max({[Table1.A], [Table2.A]}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Table1.A", "Table2.A"})
in
#"Removed Columns"
Append both temporary tables and remove duplicates.
let
Source = Table.Combine({UniqueAValues, UniqueBValues}),
#"Removed Duplicates" = Table.Distinct(Source)
in
#"Removed Duplicates"
You won't get the sort order, but that shouldn't matter.