JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
I'm trying to set up the following using Power Query:
An Excel file that reads data from a CSV file, adds to an existing table and outputs all unique dates
Every month a new CSV file with data is generated which needs to be added to existing data
I have created a query that reads data from a CSV file and loads to connection only
I know how to append this table to an 'existing table' and remove duplicate dates
I wish to connect the updated table (with new data) to a worksheet (eventually it'll be to pivot tables)
Final output
Dashboard containing pivot charts and calculated metrics
Data file:
2,031 columns( this will be reduced*)
Max rows: 33 [2 header rows + 31 days in a month; I have a query step to remove row 2 as it is not needed]
For now, yes Excel is being used to hold all data for all months
For the first read of data, I do not have a 'current' table to add data to.
It seems like there are two cases: Initial data read and Subsequent data reads, how do I set this up?
This seems like a common situation, but I've not been able to successfully search for a solution.
Any ideas please?
TIA,
Jack
* Separately I'm using Table.Buffer() but the data refresh from CSV into Excel is very slow when I test with a different CSV file. This is a separate, though, related question if answer can include suggestions to speed up data reads, that would also be appreciated.
I'm trying to set up the following using Power Query:
An Excel file that reads data from a CSV file, adds to an existing table and outputs all unique dates
Every month a new CSV file with data is generated which needs to be added to existing data
I have created a query that reads data from a CSV file and loads to connection only
I know how to append this table to an 'existing table' and remove duplicate dates
I wish to connect the updated table (with new data) to a worksheet (eventually it'll be to pivot tables)
Final output
Dashboard containing pivot charts and calculated metrics
Data file:
2,031 columns( this will be reduced*)
Max rows: 33 [2 header rows + 31 days in a month; I have a query step to remove row 2 as it is not needed]
For now, yes Excel is being used to hold all data for all months
For the first read of data, I do not have a 'current' table to add data to.
It seems like there are two cases: Initial data read and Subsequent data reads, how do I set this up?
This seems like a common situation, but I've not been able to successfully search for a solution.
Any ideas please?
TIA,
Jack
* Separately I'm using Table.Buffer() but the data refresh from CSV into Excel is very slow when I test with a different CSV file. This is a separate, though, related question if answer can include suggestions to speed up data reads, that would also be appreciated.
Still discovering Power Query and how to use it, but it's not fast with data loads and transformations. The previous problem I posted about, I ended up doing it all in VBA, was much faster.
