Adding an conditional row in Power Query and exporting the data as CSV file

Hello everyone!

I hope I have posted this in the correct area and that I am able to work this correctly without rambling. I am currently working on a resolution to simplify adding insurance deductions into our payroll/HR software at work.

Currently, I have to go into the employee profile in our software and enter the insurance deduction (employee amount) and the insurance benefit (employer amount). With over 500 employees, this task can be tedious and time-consuming.

I have recently discovered that I can do a bulk deduction by uploading a CSV file into our software. I have learned that I can download a report from our vendor that shows the insurance roster information, however, it only shows the deduction and not the benefit. For example, if an employee has a medical plan high option (MPLO), I have to enter that into the CSV file as two lines. One line would have the deduction (MPLO) and one line would have the medical plan benefit (MPB). I also have to add the benefit for any dental deduction. The only insurance that does not have a benefit is our vision insurance (VS)


My current process:

1. Download report from vendor
2. Clean up the report in Power Query
3. Copy and paste data into a new workbook, add new rows for benefits, and save as a CSV file.
4. Upload the CSV file into our software.

What I want to do:[*]Download report from the vendor[*]Clean up the report in Power Query-adding conditional column for medical (MPB) and dental benefit (DPB)[*]Create a Macro that would allow me to export a CSV file in the correct format-I'd like to be able to export by month[*]Upload the CSV file into our software.[*]Be happy that I saved myself hours of work.

I've uploaded two worksheets, one that shows what my cleaned-up vendor report looks like, and what the CSV file looks like. The only columns that will ever change in the CSV file are columns A, C, V. The others will always be the exact same.

If anyone could give me any insight into what I need to do/learn to accomplish this or something better, please let me know!

VBA
Create CSV file.

Please Login or Register  to view this content.

Thank you! This has gotten me started!