Hi Catalin / Mynda
Currently I got master table, each month we need to add the latest data in the master table (See attached AppendTables_tobePermanent.xlsx consist of Oct19 + Nov19)
As the Nov19 is always reference to MasterTable (Started from Oct 19), and when times go by, there will be a lot of queries, Dec19, Jan20, Feb20......
i was thinking whether there is a way to break the reference whenever a current month data append to the master, so I can only maintain a current query that point to a file call current.xlsx (Source), I just need to put in the data that need to copy/append to the master.
It's not right Chris.
Use a query from File> From Folder, not a query from excel file for each file in a folder.
whenever a new file is added to that folder, data will be extracted, you will always have a single query that you don't need to update.
Hi Catalin,
ooh, have not tried from folder yet. so I put Oct 19, Nov 19 inside the folder, Append them and whenever there is a new file Dec 19, it will automatically append to the single query ?
Hi Catalin,
I was not familiar with query from File> From Folder
manage to put Oct 19, Nov 19 to a folder
In the query
ContentNameExtensionDate accessedDate modifiedDate createdAttributesFolder Path
| Binary.FromText(...) | Nov19.xlsx | .xlsx | 12/12/2019 2:03:15 PM | 12/12/2019 2:03:15 PM | 12/12/2019 2:02:19 PM | [Record] | C:UsersUserDocumentsPowerBI & PowerQueryMyonlineTrainingListing |
| Binary.FromText(...) | Oct19.xlsx | .xlsx | 12/12/2019 2:08:51 PM | 12/12/2019 2:08:51 PM | 12/12/2019 2:00:39 PM | [Record] | C:UsersUserDocumentsPowerBI & PowerQueryMyonlineTrainingListing |
how to proceed from here ?
Hi Catalin,
I did combine with edit
Encounter error for the second excel Oct 19
An error occurred in the ‘Transform Binary from Listing’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table
after you get the list of files in that folder, add a new column with the formula:
=Excel.Workbook([Content])
Then you will expand this new column.
You have a tutorial here: https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
Hi Catalin,
got it thank you very much
