Forum Discussion

craig811's avatar
craig811
Helper III
6 years ago
Solved

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

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 ...
Need Help
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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