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

[v-shamiliv]

Hi @j1s 
Hi 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If their posts answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @j1s 
Thank you for reaching out microsoft fabric community forum.
We apologize for any inconvenience caused. Your post may have triggered the Auto Spam detection, which is why you might not have seen the reply. Typically, posts with links, statements, or images that are irrelevant to the community are marked as SPAM.
I wanted to check if you had the opportunity to review the information provided @SundarRaj @dufoq3 @PwerQueryKees . Please feel free to reach us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[SundarRaj]

Hi @j1s , here's a different solution for you check out. Thanks!

[j1s]

@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=​

---

[SundarRaj]

Hi @j1s , is it possible for you to send me a snap of the inputs i.e. the two tables or file? Thanks

[PwerQueryKees]

What the error says is that 

ColNames = List.RemoveFirstN(Table.ColumnNames(Table1),1) & List.RemoveFirstN(Table.ColumnNames(Table2),1),

Produces duplicate column names and the Table.FromRows() does not like that. Can't see the actual data you are using, so I am not sure...

[v-shamiliv]

Hi @j1s 
Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[j1s]

@v-shamiliv 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 <mailer@us.khoros-mail.com>
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

[v-shamiliv]

Hi @j1s 

Could you please let us know if your issue has been resolved? Did any of the solutions meet your expectations?If so, please mark the helpful reply and accept it as the solution. This will help other community members with similar problems to solve them faster.

Thank you.

[j1s]

---

@v-shamiliv wrote:

Hi @j1s 

Could you please let us know if your issue has been resolved? Did any of the solutions meet your expectations?If so, please mark the helpful reply and accept it as the solution. This will help other community members with similar problems to solve them faster.

Thank you.

---

Hello @v-shamiliv , How do I mark several responses as solutions?

[v-shamiliv]

Hi @j1s 
If a response resolves your query, please accept it as the solution. If multiple answers are correct, you can click the "Accept it as solution" button under the appropriate responses.
Thank you.

[j1s]

@v-shamiliv That doesn't seem to be the case.  Selecting a second as a solution seems to unselect the first

[j1s]

Thanks @PwerQueryKees and @dufoq3 - both solutions seem to work.

I'd appreciate any tips on how to manage the updates:

Where

Table 1 is the base data

Table 2 is an update that adds some date columns and some name rows to Table 1

Table 3 is the next update adds to Table 1

I'm drawing in Tables 2 & 3 using Get data from folder, but I want Table 1 to be upated rather than have keep generating a new Table each time 

[PwerQueryKees]

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

[dufoq3]

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

[j1s]

Yes, I was getting stuck with how to reference 2 tables in the tables in the queries.  I think I worked it out. 
I used your query for the Base Data table loaded in an Excel worksheet and set Table1 to Base Data, , then created a query for the Latest Update data and set Table2 to that in your query.   It seems to work, even though it throws an error "Expression.Error: A join operation cannot result in a table with duplicate column names ("01/01/2025").
Details:
[Type]"

[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

[Omid_Motamedise]

Instead of merging, initially unpivot all the tables then append them and again pivot them to reach the desire result, if it is not clear, let me know to give you and example

[PwerQueryKees]

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

[PwerQueryKees]

The trick is to first unpivot, then combine the 3 unpivoted tables and the pivot again.

From this

Using

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"}),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted ALL", List.Distinct(#"Unpivoted ALL"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

to this:

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