0

I have a source folder with CSV files in an identical format and data structure. The files contain monthly data from our internal system with columns named "service name", "date from", "date to", "monthly trafic". Every files are named as YYYYMM.csv. I inicially imported monthly data from these files as a new query from a folder. This created an Excel table in the same structure as in the CSV files. After that, I created one more column "Customer" and wrote a formula into the first cell in the column under the table header and pushed Enter. It automatically fill in all cells in column in the range of the table with the formula. This works well.

But!

If I insert a new CSV file with new data into the folder and Update all in excel spreadsheet, calculated column named "customer" contains a gap of cells without a formula, while new data is correctly imported into the range of the table. And this is the problem. I don't want to have the gap in the data and I don't want to manually copy the formula into cells in the gap.

Any help appreciated. Thank you very much.

Improve this question
2
  • Is a database an alternative? Then you only write formula once & can script/code everything. I started with dBase when spreadsheets were only two greenbar pages wide & long. Then converted to MS Access and now python & SQLite. Many other database alternatives.
    – oldfred
    Aug 28, 2019 at 15:27
  • Hi @oldfred, thank you for your answer. I need to work with the excel spreadsheet due to another reporting functions and because the report should be easily created in Excel with no other system using for a data preparation. This issue is only thing I need to resolve to have a fully automatic report in excel. Installing, implementing, configuring, programming, etc. of any other system is nonsense for this easy purpose.
    – Muji
    Aug 29, 2019 at 10:50

1 Answer 1

Reset to default
0

I found a solution in adding a custom column in the Query.

Example: https://support.office.com/en-us/article/add-a-conditional-column-power-query-f2422ed1-f565-4e64-ba3f-4f8d4616254e

This allows me to add a column with a formula (in Power Query M language) directly into the source table in Excel. So, after adding a new source CSV file Excel updates all columns in the table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.