Updating a central file using power query question/issue

Hi - excel on a mac. ( I know but i do a lot of video nowadays )

I am trying to redo my sales reporting

To save repetition I have set up power queries that read from a folder where i dump xlsx outputs from NAV

I have found this easy to set up and is great

However i have a large set of invoice sales by item line going back to 2019 - I need to measure these as they are trends due to covid that just looking at 20 onwards wont pick up.

what I want to do is update a sheet on my master xlsx with sales invoices by line - I want to ensure there are no duplicates- I also have 2 new coloms to add

Its really basic - im trying to use append but its only overwriting

I thought the process would be just ti drag a new file call sales figures NEW into the data folder and then refresh the master but I cant get it to add and not duplicate )

Help please

Microsoft excel 16.77

just updated this morning - I assume i would have the cleaned and added file within the master workbook before I start creating pivot tables etc - the data is 35Mb

so an update - Iv etreid YT and ChatGPT for advice

Chat suggested this - which blew my mind Im afraid

If your folder path is /Users/stuartbrown/Library/CloudStorage/OneDrive-Personal/12 New customer details model 2023/New data for updates, and you want to create a Power Query in the advanced editor to read data from the "Sales Figures.xlsx" file within this folder, here's how the advanced editor screen might look:

excel
Copy code
let
FolderPath = "/Users/stuartbrown/Library/CloudStorage/OneDrive-Personal/12 New customer details model 2023/New data for updates",
Source = Folder.Files(FolderPath),
FilteredFiles = Table.SelectRows(Source, each [Name] = "Sales Figures.xlsx"),
Data = Table.FromList(FilteredFiles[Content], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(Data, "Column1", {"Column1", "Column2", "Column3", ...}, {"Column1", "Column2", "Column3", ...}),
#"Remove Duplicates" = Table.Distinct(#"Expanded Column1")
in
#"Remove Duplicates"
Replace "Column1", "Column2", "Column3", and so on with your actual column names and adjust the code accordingly to match your data structure. This code specifies the folder path you provided and looks for the file "Sales Figures.xlsx" within that folder. It then loads and processes the data from that file.

however when I follow the basic stages to update via data query and then load a new exel and then append it simply overwrites and doesn't add (excluding duplicates is what i need just in case there's some date overlaps in my reports)

the instructions look simple - and in case my writing is poor I want just to update a table with the latest invoices - automatically - surely it can do this

apologies for going on but I know i am doing something stupid!!

Microsoft excel 16.77

i didn't add it to the other one as it was full of yukky google sheets stuff