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

Hi thowa ,

Sorry pressed a wrong button :).

I posted my solution in the thread that you are referring to:

https://community.powerbi.com/t5/Power-Query/Function-to-update-any-table-with-new-values-from-another-table/m-p/1051841/highlight/false#M35392

Kind regards,

JB

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

Thanks to

camargos88and Anonymous for the accepted solutions.

I have summarized both possibilities below - not sure which one performs better.

Camargos' solution coded into a function:

(BottomTable as table, TopTable as table)=>

let
    //Will overlay the Bottom Table with the TopTable.
    //Empty cells in Top table are transparent, i.e. existing data from BottomTable will persist unless the TopTable has data in the same cell.
    //New rows or columns from TopTable will be added
    //There must be a "Date" column as anchor column for the overlay in both tables
    //Columns may come back in a mixed siquence
    
    #"Changed Type1" = Table.TransformColumnTypes(TopTable,{"Date", type date}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"),

    #"Changed Type2" = Table.TransformColumnTypes(BottomTable,{{"Date", type date}}),
    #"Unpivoted Other Columns2" = Table.UnpivotOtherColumns(#"Changed Type2", {"Date"}, "Attribute", "Value"),

    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns1", {"Date", "Attribute"}, #"Unpivoted Other Columns2", {"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"}),
    //Changed Name to keep Query similar to post (as if output from "Anti Join" Query)

    #"Appended Query" = Table.Combine({#"Unpivoted Other Columns1", #"Expanded Unpivoted Other Columns"}),
    Result = Table.Pivot(#"Appended Query", List.Distinct(#"Appended Query"[Attribute]), "Attribute", "Value")
in
    Result

jborro's solution (slighly changed in table names etc.):

(BottomTable as table, TopTable as table, AnchorColumnName as list)=>

let
    //Will overlay the Bottom Table with the TopTable.
    //Empty cells in Top table are transparent, i.e. existing data from BottomTable will persist unless the TopTable has data in the same cell.
    //New rows or columns from TopTable will be added
    //There must be a "Date" column as anchor column for the overlay in both tables
    //Columns may come back in a mixed siquence
    //IMPORTANT: the AnchorColumnNae Parameter must be the name of one of the columns! If the Power Query interface is used, you will be asked to pick a column from one of the tables and that will not work.
    AttributeFields = AnchorColumnName,
    ValueFields = List.RemoveItems(Table.ColumnNames(Source), AttributeFields),
    ValueFieldFunction = List.Accumulate(ValueFields, {}, (a, n) => a & {{n, (x)=>List.First(List.RemoveNulls(Table.Column(x, n)))}}),
    Source = Table.Combine({TopTable, BottomTable}),
    #"Results" = Table.Group(Source, AttributeFields, ValueFieldFunction)

in
    #"Results"

THANKS