Convert existing query to an append query?

[Mr_Phil]

Hi. I have a functioning query. Now I would like to be able to do a daily .xls file (same name, same format obviously) and append to the data already there. I'm new to this so any advice or tutorial link or even the best keyword searches would be gratefully accepted.

Here is the M code if that helps.

let
    Source = Excel.Workbook(File.Contents("C:\Users\phillip.gwinn\My Drive\TRANSACTIONS.xlsx"), null, true),
    #"Export Data_Sheet" = Source{[Item="Export Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Export Data_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Batch #", Int64.Type}, {"From", type text}, {"To", type text}, {"Type", type text}, {"Created On", type datetime}, {"Created By", type text}, {"Item", type text}, {"Serial #", type text}, {"Rec #", type text}, {"SC #", type text}, {"Qty", Int64.Type}, {"Cost", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"From", "Item", "Serial #", "SC #", "Qty", "Cost"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([To] = "Holding Acct, East Texas" or [To] = "Holding Acct, WestTX") and ([Type] = "From Office To Technician" or [Type] = "From Technician To Technician"))
in
    #"Filtered Rows"

I am not sure if I need to make a new query or if this can be "upgraded" for lack of a better term. Thanks for looking.

[JEC.]

Two options:

1. After refresh, copy the query to a combined sheet with VBA. You can append it to the existing table everytime.
2. If you don't want to use VBA, you need a workaround with the help of the datamodel. To achieve this, you need to add a table to your data model, retrieve it in a worksheet (you can hide this sheet), and then reference this table in Power Query. It sounds more complicated than it actually is.

Attached an example with the datamodel (option 2). Eventually, you can hide the datamodel sheet.
For testing, try adding values to the blue table. Then hit "Refresh All" and see what happens. The green table next to the blue table will grow. Already loaded records stay in the table as well

[Mr_Phil]

Oh wow. Thanks. I am closer to visualizing how it will work. The table my M-code referenced is a connection only so that I could marry the key fields with other tables. The final product is a Merge between the current inventory, the current transactions, and any current comments. So I should create a table in excel that matches what I currently have stored in the datamodel. Then reference THAT new table in the query that is in the data model. change the connection only to a table in excel proper. Did I get that right? I really appreciate the help. Thanks again.

[JeteMc]

To answer the question posed in post #3, it would be helpful to see a sample file.
Let us know if you have any questions.