Forum Discussion

thowa's avatar
thowa
Regular Visitor
6 years ago
Solved

Overlaying/merging two tables to add missing data in empty cells and new rows/columns

I have two tables each with a column of unique keys (dates). The other columns have identical headers, albeit there may be rows and columns in one table absent in the other and vice versa. The data...
Need Help
  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi thowa ,

     

    I believe you need to apply the right anti join and append it to the first table.

     

     

     

     

    Check this file: Download PBIX 

     

    Ricardo

    • thowa's avatar
      thowa
      Regular Visitor
      6 years ago

      camargos88 

      Thanks for the quick response!

      I was also thinking that Table.Join with the right Joinkind should be the right track - but I couldn't figure out a solution.

      Your's seems to work judging from the result, but I cannot open the file as I don't actually have Power BI (hence solution for Excel Power Query requested; code should be the same unless it is an unsupported feature).

       

      Happy to accept the solution, if you can post the code.

       

      Thomas

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        thowa ,

         

        Table Result ->

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDBQ0lEyBGIFIDZTitWJVjJCSJhDJUzAEsYICWOwIEgSJGGCkDADYiAyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t, #"2" = _t, #"3" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
        #"Appended Query" = Table.Combine({#"Unpivoted Other Columns", #"Anti Join"}),
        #"Pivoted Column" = Table.Pivot(#"Appended Query", List.Distinct(#"Appended Query"[Attribute]), "Attribute", "Value")
        in
        #"Pivoted Column"

         

        Table (2) ->

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDBQ0lEyAmITIDYD41idaCUjhKQxECsAsTkQg+WMEXIgTQowBUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value")
        in
        #"Unpivoted Other Columns"

         

        Anti join ->

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDBQ0lEyBGIFIDZTitWJVjJCSJhDJUzAEsYICWOwIEgSJGGCkDADYiAyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"1" = _t, #"2" = _t, #"3" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
        #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Date", "Attribute"}, #"Table (2)", {"Date", "Attribute"}, "Unpivoted Other Columns", JoinKind.RightAnti),
        #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Unpivoted Other Columns"}),
        #"Expanded Unpivoted Other Columns" = Table.ExpandTableColumn(#"Removed Other Columns", "Unpivoted Other Columns", {"Date", "Attribute", "Value"}, {"Date", "Attribute", "Value"})
        in
        #"Expanded Unpivoted Other Columns"

         

        Ricardo