Merging / appending to keep all

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  

Omid_Motamedise , this is excatly the solution I gave yesterday....

Hi, I did not generate new table. I've updated Table1.

You are extending your initial question....

BUT: Keep Table1 in your Excell sheet in unpivoted form.

Get Table2 (or any number of tables from a folder) and unpivot them with a custom function.

Table.Join all tables and load the result back into Table1 (need some trickery, but doable)

The do your Pivot and other reporting stuff on Table1. I would use Power Pivot or PBI to do the pivotting.

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

Anonymous there are apparently more than one solution, and I can't make any of them as the solution until I have seen all of them.  Apparently there was another posted, which I was notified about by email but for some reason (any idea?) is not visible (to me) here.

This is the email 

From: Fabric Community Administrator <[email protected]>
Date: Sun, 23 Feb 2025 at 12:03
Subject: SundarRaj mentioned you in Microsoft Fabric Community
To: j1s


Hi j1s,

SundarRaj (Frequent Visitor) mentioned you in a post! Join the conversation below:


Re: Merging / appending to keep all
Hi @j1s . Here's another way to approach your query. I'll share two images, one of the ouput and the other of the M code used. In this approach, 1. Remove the first column in each table (to make it dynamic you can use a List.Transform to remove the text columns) and use Table.ToColumns 2. Combine the lists of list created in each table and create one single list containing the contents of both the table 3. Use Table.ColumnNames on both the table and combine them again in a list format. 4. Last step is the to simply use Table.FromColumns and convert them into a Table using ColumnNames to generate the headers as well.

Untill I can see the pictures referred to, I'm not sure how to understand if it is the solution

SundarRaj 

Thanks for proposing this solution.

Since my sources references are slightly different, I took your code:

let
Table1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
ColNames = List.RemoveFirstN(Table.ColumnNames(Table1),1) & List.RemoveFirstN(Table.ColumnNames(Table2),1),
TranspTable1 = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]))),
TranspTable2 = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Table2"]}[Content]))),
Table = Table.FromRows(Table.ToColumns(TranspTable1 & TranspTable2),ColNames),
#"Added Index" = Table.TransformColumns(Table.AddIndexColumn(Table, "Name", 0, 1, Int64.Type),{"Name", each List.Distinct(Table1[Column1] & Table2[Column1]){_}}),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Name", "01-01-2025", "01-02-2025", "01-03-2025", "01-04-2025", "01-05-2025", "01-06-2025", "01-07-2025", "01-08-2025", "01-09-2025"})
in
#"Reordered Columns"

...and tried to modify it like this:

let
    Source = Excel.CurrentWorkbook(){[Name="BaseData"]}[Content], 
    Table1 = Source,
    Table2 = #"Latest Update", //refers to an additional query that gets the latest data from another file and puts it in the same format as "Table1" 
    ColNames = List.RemoveFirstN(Table.ColumnNames(Table1),1) & List.RemoveFirstN(Table.ColumnNames(Table2),1),

    // modified these 2 lines:
    TranspTable1 = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table1))),
    TranspTable2 = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table2))),
    
    // from here it's the same as @SundarRaj  orginal, but there's an error from the next step

    Table = Table.FromRows(Table.ToColumns(TranspTable1 & TranspTable2),ColNames),

    #"Added Index" = Table.TransformColumns(Table.AddIndexColumn(Table, "Name", 0, 1, Int64.Type),{"Name", each List.Distinct(Table1[Column1] & Table2[Column1]){_}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Name", "01-01-2025", "01-02-2025", "01-03-2025", "01-04-2025", "01-05-2025", "01-06-2025", "01-07-2025", "01-08-2025", "01-09-2025"})
in
#"Reordered Columns"

The error I get at  

Table = Table.FromRows(Table.ToColumns(TranspTable1 & TranspTable2),ColNames)

 is:

Expression.Error: The field '01/01/2025' already exists in the record.
Details:
    Name=01/01/2025
    Value=​