Power Query Appending new CSV files to an EXISTING Excel table

[meg_a_bytes]

I have researched but can't find specific instructions on how to append new monthly CSV files to an existing Excel Table. The table is a collection of all of the previous month's CSV's. All of the example i have found explain how to import CSV's to a new table. I have been doing this in the past but now the list of existing CSV's (old and new) is getting long and i don't see a need to re-import previous month CSV's. My current workflow is to extract, transform the CSV's to make them all consistent then load them to a new worksheet and table. Is there a way of preserving the previous month's table and just adding the new month's CSV's? I'm not interested in in doing any VBA code if that is the only solution.

 

[jdellasala]

Power Query was made for a job like this. Hopefully the CSV files are all in the same directory, in which case you can use Get Data -> From File -> From Folder. You'll be able to get this set up, and with a little work all you'll have to do is click Refresh All every month! I recommend YouTube playlists here and here. The playlists are long, but there's a good chance only a few are needed. I prefer the latter playlist because before and after example files are provided. And, of course, there's plenty of talent here. Post some data, I'm pretty sure it will be worth it!

 

[meg_a_bytes]

It looks like the solution that is most appropriate was from the "Bark01" thread. From what i can understand, the new month's CSV's would be Power Queried into a stand alone workbook then you would open the workbook that contains all of the previous month's data and execute a second PQ to append the current month data into it. Sounds reasonable. I was assuming this could be done with one PQ in one workbook.
Thanks for responding.

 

[jdellasala]

It looks like the solution that is most appropriate was from the "Bark01" thread. From what i can understand, the new month's CSV's would be Power Queried into a stand alone workbook then you would open the workbook that contains all of the previous month's data and execute a second PQ to append the current month data into it. Sounds reasonable. I was assuming this could be done with one PQ in one workbook.
Thanks for responding.

No, as I indicated, if all the CSV files are in a given folder, and if there are other CSV files in that folder can be easily filtered by name - such as starts with Sales, one time with PQ could pull all of them in and append them into a single table. Once set up, new files could be appended by just refreshing the table. Haven't seen Bark01's thread, so don't know what he's talking about.

 

[meg_a_bytes]

I understand, but can it pull all of the filtered files into an existing table (with previous month data) in a different workbook?

Bark01:

Power Query - import from folder and add to static table

I have a daily import report that i save to a folder and then use power query to append all the daily files and transform them. However I'm importing and transforming all the tables every time i need an update. The onyl change would be the newer files. Is there any way to have power query only...

www.mrexcel.com

 

[jdellasala]

I understand, but can it pull all of the filtered files into an existing table (with previous month data) in a different workbook?

Bark01:

Power Query - import from folder and add to static table

I have a daily import report that i save to a folder and then use power query to append all the daily files and transform them. However I'm importing and transforming all the tables every time i need an update. The onyl change would be the newer files. Is there any way to have power query only...

www.mrexcel.com

Yes. In fact a different file makes it easier. Attempting to pull in data from the existing workbook is slightly trickier because you have to be sure to filter out the final Worksheet/Table, or else you end up with a very big mess!
This is the code I put together with nothing but the user interface to import and Append (put one CSV file on top of the other) in a few minutes:

let
    Source = Folder.Files("C:\Users\XXXXX\Excel\Sales\CSV2011-2018"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SalesRepID", Int64.Type}, {"ProductID", Int64.Type}, {"ChannelID", Int64.Type}, {"SegmentID", Int64.Type}, {"RegionID", Int64.Type}, {"Date", type text}, {"Quantity", Int64.Type}, {"Discount", Percentage.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

There is NO hand coding. The only tricky thing was that the Date column was in UK format (dd-mm-yy) so had to use the Locale option to convert it to a US date. There are over NINE MILLION rows of data there from 10 CSV files - one per year. This literally took minutes - LESS time than it took to post this!
That much data had to be loaded as a Connection Only and added to the Data Model so that a Power Pivot report could be created. With less than a million rows of data, it could be loaded to a table in a New Worksheet. Then when subsequent years (or months) are added to the Folder, open the file with the full table, and click Data -> Refresh All to add the new year's/month's data.
Note that when expanding the data, several queries as well as most of the steps above are created. One of those is a Transform Sample File query. Some changes can be made to that and will be done to all files transformed, but one thing that doesn't come from it is the Data Types which is why they're in the final query.
The CSV files were never opened for this.

 

[jdellasala]

Yes. In fact a different file makes it easier. Attempting to pull in data from the existing workbook is slightly trickier because you have to be sure to filter out the final Worksheet/Table, or else you end up with a very big mess!
This is the code I put together with nothing but the user interface to import and Append (put one CSV file on top of the other) in a few minutes:

let
    Source = Folder.Files("C:\Users\XXXXX\Excel\Sales\CSV2011-2018"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SalesRepID", Int64.Type}, {"ProductID", Int64.Type}, {"ChannelID", Int64.Type}, {"SegmentID", Int64.Type}, {"RegionID", Int64.Type}, {"Date", type text}, {"Quantity", Int64.Type}, {"Discount", Percentage.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

There is NO hand coding. The only tricky thing was that the Date column was in UK format (dd-mm-yy) so had to use the Locale option to convert it to a US date. There are over NINE MILLION rows of data there from 10 CSV files - one per year. This literally took minutes - LESS time than it took to post this!
That much data had to be loaded as a Connection Only and added to the Data Model so that a Power Pivot report could be created. With less than a million rows of data, it could be loaded to a table in a New Worksheet. Then when subsequent years (or months) are added to the Folder, open the file with the full table, and click Data -> Refresh All to add the new year's/month's data.
Note that when expanding the data, several queries as well as most of the steps above are created. One of those is a Transform Sample File query. Some changes can be made to that and will be done to all files transformed, but one thing that doesn't come from it is the Data Types which is why they're in the final query.
The CSV files were never opened for this.

This is the Pivot Table that Data Model was able to generate:

Book1big.xlsx
	A	B
1	Year	Quantity
2	2011	3,824,840
3	2012	4,053,304
4	2013	3,863,882
5	2014	4,462,822
6	2015	4,316,000
7	2016	4,460,736
8	2017	4,399,717
9	2018	4,435,346
10	2019	4,601,320
11	2020	4,587,494
12	Grand Total	43,005,461
CSV2011-2018

One nice thing about using the Data Model is that the format of the Date and Quantity can be set in the Data Model, not in the Pivot Table report!

 

[meg_a_bytes]

I'm not following what your trying to do so let me take one last stab at explaining this. I receive 8 csv's from different financial institutions every month. The csv's have different layouts so my first step is to ETL each csv and put them all into a common column format. The 8 etl's are in 1 workbook (lets call it wkbETL) where I save and load all of the 8 etl's to a new table (tblNewMonth).
I then open and existing workbook (wkbReporting). It contains a one table (tblTransactions) with all of the previous months data that was captured and ETL'd as described above. It also contains dashboards and pivot tables that refer to the tblTransactions name, so changing that table name (or creating a new table with the new and old data) is not really an option.
I simply want to append tblNewMonth from wkbETL to the bottom of tblTransactions in wkbReporting.

Is the solution to simply copy/paste tblNewMonth to the bottom of tblTransactions? Sounds pretty "unsophisticated".

Thanks for your patience.

 

[jdellasala]

OMG! Where did I ever indicate, say, or imply to COPY? Watch this tutorial. Maybe then you'll get it.

 

[meg_a_bytes]

i was not implying that YOUR solution was to copy anything. I was coming to the conclusion that if i created tblNewMonth, i could simply cut and paste it into tblTransactions!
relax