Help required with uploading new file from a folder - Refreshing queries

Yes.

Follow my instructions above. The "Combine Files" step will effectivly be clicking on the binary as you will have one file. As long as the tab names in the replacement files are the same, it will be 100% seamless as the file names change.

By clicking on the binary like you are, you are hard-coding the file name in the query, which is causing the manual maintenance issues.

Hi craig811 ,

most likely currently your individual queries look similar to this

let
    Source = Excel.Workbook(File.Contents("..\Colours.xlsx"), null, true),
    // Above this point the binary file is been loaded
    
    // Below this point the actual data from ta is extracted
    Range_Sheet = Source{[Item="Range",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Range_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Colour", type text}, {"Intensity", Int64.Type}})
in
    #"Changed Type"

Where above the comment - your binary data being extracted from the file in one or another form, below the data from a tab being extracted to the query. And this repeats for every tab as far as I understood.

You can separate the top part into a separate query which will not load (right-click on the query name and untick "Enable Load" in the editor), all other queries will refer to this single source.

Assuming this is the source query (tExcelSource😞

let
    Source = Excel.Workbook(File.Contents("...\Colours.xlsx"), null, true)
in
    Source

This is how it is referred to in the tab-specific queries:

let
    Source = tExcelSource,
    Range_Sheet = Source{[Item="Range",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Range_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Colour", type text}, {"Intensity", Int64.Type}})
in
    #"Changed Type"

let
    Source = tExcelSource,
    Range_Sheet = Source{[Item="DataTab",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Range_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Colour", type text}, {"Intensity", Int64.Type}})
in
    #"Changed Type"

etc...

As long as the structure of the file does not change - same tab/table names, same columns, etc. - this should work if you change the name in the tExcelSource code/query.

Kind regards,

JB