Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 t...
  • MarcelBeug's avatar
    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.