I think this a strange behaviour of Excel, but maybe it can be solved easily?
I export the weekly information from a bankaccount as a .csv file.
I made a kind of dashboard to see 2017, 2018 and 2019 so far. Because the bankprogram can't export that in one file, I exported it in the files 2017.csv, 2018.csv and 2019.csv. And with PowerQuery I imported the csv information and made my dashboard. All is working fine.
I thought it would be possible to only export the new weeks to a separate .csv file, open the 2019.csv file and that new .csv file and then copy this new lines to the 2019 file, save again as 2019.csv.
BUT when I then open my Excelfile with the dashboard, the PowerQuery says something like 'Can't find' (and then the name of the first column header). Of course it is there, but apparently Excel had changed something in the file when opening and saving it.
I tried to repair this with adding the double quotes it needs, but that doesn't work.
My new method is exporting the whole year from the bankprogram and replace that one as the 2019.csv file. That's working good (of course, because I don't open the .csv and don't save it), but not so nice because I have to adjust things in the bankprogram everytime, which works not so fine.
Any ideas how this issue can happen in Excel and how to solve it?
thanks!
Frans
Hi Frans,
Why don't you use 'Files from Folder' to get the data with Power Query. That way you just add your new 2019 file with the new lines to the folder and Power Query gets all of the files. You don't need to open the csv files in Excel and do anything to them, just grab all of the files in the folder.
Perhaps I've misunderstood?
Mynda
Thanks for answering and this idea Mynda. I didn't thought about that solution, maybe because I don't work often with the Power of Power Query.
New question: I think I must make a folder where I only put the exports from the bankprogram, isn't it? So it can't be mixed up with other files and data it doesn't understand. And do I understand it clearly that the data of every (for instance weekly) file is 'automatically' added to the query?
Frans
Yes, it's ideal if you have a folder that only contains the files you want to get, that way you don't need to worry about filtering the file names upon import and having to update those filters each time new files are added to the folder. Instead you just allow the query to get everything.
If you must put files in the folder that you don't want to import in the query then you should use a consistent naming convention that allows you to filter on 'text that contains' and all csv files you want to import contain that text and the files you don't want to import don't contain it. Hope that makes sense.
OK we are some days further in time now, so I had the change to test everything proparly.
I made this new file and put the data in a folder that's in the same folder as the new file (so the data is not in the same folder as my report file).
This works perfect indeed! New export from the bankprogram is placed in that data folder, you refresh the query and also the pivot table (can that be done automatically?) and everything works. Only thing is to check the bankprogram because the export there sometimes gives a date which I already imported and of course you get 'double values' then.
Problem solved! (ah well to say it correctly: found a better working solution).
Frans
Glad you got it working! You could always apply a filter to remove duplicate rows, but you'd want to be certain that a duplicate could never exist!
