Hi,
I'm receiving daily excel files with a table in which a field shows the extraction date of the data.
I want to keep track of all data and store all historical values into a data model to make some basic performance comparisons.
Excel file 1 would look like this
Test Value ExtractionDate
| A | 3 | 13/02/2023 |
| B | 8 | 13/02/2023 |
| C | 6 | 13/02/2023 |
| D | 9 | 13/02/2023 |
Excel file 2 would look like this (Values & extraction date will change the following day)
Test Value ExtractionDate
| A | 3 | 14/02/2023 |
| B | 9 | 14/02/2023 |
| C | 10 | 14/02/2023 |
| D | 5 | 14/02/2023 |
I want to write a basic script to append Excel file 1 & 2 and 3 and 4 and...... N (each time a new file is posted on the Shared folder into a table so that I can have all data into one sole table and that the import process would be fluid.
Is there a simple solution to do this? Did try several methods including Table.InsertRows but I could not figure that out.
My script is (didn't work)
let
// Import Excel data from the source table
Source = Excel.Workbook(File.Contents("MyFile.xlsx"), null, true),
Table1 = Source{[Item="Table1",Kind="Table"]}[Data],
// Determine the maximum extraction date in the destination table
MaxExtractionDate = List.Max(Base[ExtractionDate]),
// Filter the source table based on the extraction date
FilteredTable1 = Table.SelectRows(Table1, each [ExtractionDate] > MaxExtractionDate),
// Append the filtered rows to the destination table
Import = Table.InsertRows(Base, 1, FilteredTable1)
in
Import
Can you please give me a hand?
