Merging / appending to keep all

[j1s]

I have 2 tables containing the number of times people attended per week that look like this:

Table 1:

Full Name	2024-11-25	2024-12-02	2024-12-09	2024-12-16
Name 1				1
Name 2		1		2
Name 3
Name 4	1	1		1
Name 5		1		2
Name 6	1	1
Name 7
Name 8	1	1	1
Name 9
Name 10		1

Table 2:

Full Name	2025-01-01	2025-01-08	2025-01-15	2025-01-22
Name 1		1		1
Name 2		3	1	2
Name 3				1
Name 6			1
Name 7		1	1
Name 8		3	1	1
Name 9		3		2
Name 10		1	1	1
Name 180		3		2
Name 181		1	1	1

I’m trying merge them so they look like this:

Full Name	25/11/2024	02/12/2024	09/12/2024	16/12/2024	01/01/2025	08/01/2025	15/01/2025	22/01/2025
Name 1				1		1		1
Name 2		1		2		3	1	2
Name 3								1
Name 4	1	1		1			1
Name 5		1		2		1	1
Name 6	1	1				3	1	1
Name 7						3		2
Name 8	1	1	1			1	1	1
Name 9						3		2
Name 10		1				1	1	1
Name 180						3		2
Name 181						1	1	1

But in Power Query Merge as New > Full Outer (all rows from both), I get a second Full Name a whole lot of blank rows at the bottom for those not present in the Table 1, a bit like this:

Full Name	25/11/2024	02/12/2024	09/12/2024	16/12/2024	01/01/2025	Table2,Full Name	08/01/2025	15/01/2025	22/01/2025
Name 1				1		Name 1	1		1
Name 2		1		2		Name 2	3	1	2
Name 3						Name 3			1
Name 4	1	1		1				1
Name 5		1		2			1	1
Name 6	1	1				Name 6	3	1	1
Name 7						Name 7	3		2
Name 8	1	1	1			Name 8	1	1	1
Name 9						Name 9	3		2
Name 10		1				Name 10	1	1	1
						Name 180	3		2
						Name 181	1	1	1

What am I doing wrong?

[dufoq3 · Accepted solution]

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

[j1s]

Thanks, @PwerQueryKees , @Omid_Motamedise . 

That's helpful. Using this method, how do I fix a couple of problems?:
1. it seems to ditch columns with no entries

2.  the dates columns are not in sequence across the top (in the headers)

There seems to be an althernative (less efficient) 2 query method that does preserve the date sequence:
Query A (a helper query) that merges Table1 and Table 2 using right anti join 

let
    Source = Table.NestedJoin(Table1, {"Full Name"}, Table2, {"Full Name"}, "Table2", JoinKind.RightAnti),
    Table3 = Source{0}[Table2]
in
    Table3

Query B (a final query) that first merges Table1 and Table 2 using left outer join, then appends the data from Query A 

    Source = Table.NestedJoin(Table1, {"Full Name"}, Table2, {"Full Name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"01/01/2025", "08/01/2025", "15/01/2025", "22/01/2025"}, {"01/01/2025", "08/01/2025", "15/01/2025", "22/01/2025"}),
    #"Appended Query" = Table.Combine({#"Expanded Table2", #"Query A (helper)"})
in
    #"Appended Query"

This produces the correct output:

I would prefer to do it all in one query using the unpivot method if I can understand how to make the dates columns appear in sequence  

[PwerQueryKees · Accepted solution]

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

[j1s]

Could you explain the adding of a custom column and then grouping, rather than say changing the Attributes column to type date, then sorting and then chnaging it back to text again?  

[PwerQueryKees]

The pivot does not work with a type date column, so I needed an additional column...