Forum Discussion
Merging tables with blank fields
Hello,
I am novice in Power BI and DAX function but I want to merge datas from 2 tables into 1.
Data are the same in both tables but are comming from differents sources and are incomplete.
In the screenshot below, I've illustrated the working case :
In this situation we have several problem
- A52 & C53 record in table 2 don't have the B information but the information is in table 1
- A36 record never have the B information and is only present in table 1
- D48 record in table table 1 have 10 in B column wich match with element in table 2 which have this value but don't have A information
- Some element in table 1 and table 2 have only the B information filled
If I create a new table with this formula, I have record in red that I don't want because complete information exist in the upper record :
Result =
DISTINCT(UNION(
SUMMARIZECOLUMNS(
Table 1[A];
Table 1[B]
);
SUMMARIZECOLUMNS(
Table 2[A];
Table 2[B]
)
))If I create a new table with this formula, I lost record wich only have incomplete information :
Result =
DISTINCT(UNION(
SUMMARIZECOLUMNS(
Table 1[A];
Table 1[B];
FILTER(Table 1;AND(Table 1[A]<>BLANK();Table 1[B]<>BLANK()))
);
SUMMARIZECOLUMNS(
Table 2[A];
Table 2[B];
FILTER(Table 2;AND(Table 2[A]<>BLANK();Table 2[B]<>BLANK()))
)
))
My need is obtaining the green table in the center of my screenshot.
Of course my real tables have lot of record (more than 100000).
Thanks in advance for your support.
Aurélien.
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.
1 Reply
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.