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

[craig811]

Hi,

I can upload the lastest file in Power Query with no issue, however how can I get the quries to refesh using the data from the new file?

My Power Query currently loads the new file which is fine,  I then create the table queries on each tab from that spreadsheet, however when I re-fresh the query the new file is loaded ok but my table queries are still linked to the old file. How can I get them to look at the new file intsead of manually going into each query and updating the source?

[edhans · Accepted solution]

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.

[Anonymous]

Hi @craig811 

Change something like this  (I guess this is what you have currently):

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\data (18).xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data]

in Table1_Table

 To something like this:

let
    Source = Folder.Files("C:\Downloads"),
    #"Sorted Rows" = Table.Sort(Source,{{"Date modified", Order.Descending}}),
    NewFile = #"Sorted Rows"{[#"Folder Path"=#"Sorted Rows"[Folder Path]{0},Name=#"Sorted Rows"[Name]{0}]}[Content],
    ImportFile = Pdf.Tables(NewFile)
in
    ImportFile

Kind regards,

JB

[craig811]

Thanks all,

I can pick up the lastest file from the folder and update without an issue. 

The problem I have is that the excel file has many tabs and I have to create a query for each tab. I do this by clicking on the Binary of the file and then create a query for each tab by right clicking and selecting 'add a new query'. This is done without an issue, however when I update the main file the queries won't update unless I manually go into each one and change the source. Is there no way of automating this ? 

[edhans · Accepted solution]

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.

[Anonymous · Accepted solution]

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

[edhans]

Rather than linking to a file, you need to open all files in a folder. Then before you expand the contents of a file, use basic Power Query filters to only show the most recent file.

To do this:

• Get Data
• More
• From Folder
• Put in Folder Path
• Tell it to Transform Data when you see a list of files.
• CLick on the "Combine Files" button in the Contents column, even if you only have one file. DO NOT click on the "binary" link. That will hard-code that file name into the query that expands the data.
• You'll now see your list of files in that folder. Filter as necessary to always show the most recent file.