Merging / appending to keep all

Hi j1s, I recommend Table.Join function for this purpose:

Output

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRgiNDpVgdqLgRTARMGSEkjJE1IIRNoIoNMUwyxWWSGaoWhIQ5disskNQboshYYtdgaIBitVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, #"2024-11-25" = _t, #"2024-12-02" = _t, #"2024-12-09" = _t, #"2024-12-16" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRAiI4FasDlTCCiBhDJY0QMsYQGQwdJkiCQAoubooQRJUwQ7UCyShzhAyq3RaoZiFpscSlxdAApx5DCwOcuiwMMbTFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, #"2025-01-01" = _t, #"2025-01-08" = _t, #"2025-01-15" = _t, #"2025-01-22" = _t]),
    T2_RenamedColumn = Table.RenameColumns(Table2,{{"Full Name", "Full Name2"}}),
    Merged = Table.Join(Table1, "Full Name", T2_RenamedColumn, "Full Name2", JoinKind.FullOuter),
    ReplacedFullName = Table.ReplaceValue(Merged, 
        each [Full Name] is null,
        each [Full Name2],
        (x,y,z)=> if y then z else x,
        {"Full Name"} ),
    RemovedColumns = Table.RemoveColumns(ReplacedFullName,{"Full Name2"})
in
    RemovedColumns

Sorting the columns is not that easy, because your column names are text and we need a date sort.

But this works:

let
    #"Unpivoted Table1" = Table.UnpivotOtherColumns(Table1, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table2" = Table.UnpivotOtherColumns(Table2, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table3" = Table.UnpivotOtherColumns(Table3, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted ALL"= Table.Combine({#"Unpivoted Table1",#"Unpivoted Table2",#"Unpivoted Table3"}),
    #"Added Custom" = Table.AddColumn(#"Unpivoted ALL", "Dates", each [Attribute]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute", "Dates"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Dates", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted ALL", List.Distinct(#"Unpivoted ALL"[Attribute]), "Attribute", "Value", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",#"Sorted Rows"[Attribute])
in
    #"Reordered Columns"

Producing this:

About the missing dates: I suspect this will not be a problem in real life.

But if it is:

• create a table with all possible dates, for exapmple by querying the dat range in the orignal dataset
• Transpose the table
• Use Forst Row as Headers
• And if needed, remove all rows with a Table.SelectRows(#"Promote Headers", each false)
• and then do a Table.Union of this table with the pivoted table

I doubt this will be worth the trouble ....

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel