Log In
Register
Home
September 26, 2023
Offline
I have a power query in Excel 365 that combined multiple CSV files from my bank (Data > Get Data > From File > From Folder, transform data, load to an Excel table). This has been working well for a couple of years now.
My bank recently changed the format for their downloaded data from CSV to XLSX. Now when I try to get data from folder, although there are about 8 files, power query only brings in data from one of them.
Each XLSX file has the same format in terms of data, columns, etc., just like it was in the CSV, but the data in each file is located on a tab with the same name as the file. I.e. BankFile1.xlsx has a single tab named "BankFile1", BankFile2.xlsx has a single tab "BankFile2", BankFile3.xlsx has a single tab "BankFile3", etc.
My theory is that Power Query is getting caught up because each file has data on a tab with a different name.
Proposed solution 1 is to have an individual import for each of the different files (load as connection only), and then combine those results to load into my table. However, I'm hoping there is an easier way.
Any suggestions on how to combine the data directly without handling each file individually?
Thanks.
September 26, 2023
Offline
Found an answer here - trick was to avoid the helper queries.
October 5, 2010
Offline
Thanks Mark.
Presumably after the bank started providing the data in XLSX format you had to create a new query to load that data - separate to your old CSV query?
If the data in the XLSX files was in a table, and that table had the same name across all files, then using the built in connector would load the data from all workbooks.
But you are right, having different sheet names adds a complication.
Just FYI, the technique in the video is similar to what I show in this post which is how a lot of data can be extracted from 'embedded' data structures like lists, records and tables
Extracting Data from Nested Lists and Records in Power Query • My Online Training Hub
Regards
Phil
1 Guest(s)
