Power query to convert large number of excels into csv with custom modifications to the columns

[Syndicate_Admin]

Power query to convert large number of excels into csv with custom modifications to the columns

2 hours ago

I have a large set of excels where we need to convert all of them into one csv. While they are being converted to csv we need to modify few columns values to be stored in a way that is accepted for the bacth to pickup. 

1. For few columns we need to extract the first 2 and first 4 characters. after we extract the first 4 characters we need to change their case to upper.

2. For one column where the given name is full name in the format first space last name. this we need to modify and convert to 

space and a comma and end with a period that is a fullstop or a dot.

3. after all these changes are done remove the header row and convert this into csv for all the files present inside a folder.

Can we have a power query written for the same and get the transformed csv available

[Anonymous]

Hi @Syndicate_Admin 

You can put the excel files to a folder, then select 'get data->folder', then you can combine the files. and you can refer to the following link.

Combine all the files in a folder using Power Query | Excel & CSV | Excel Off The Grid - YouTube

06 - Import Data from CSV Files into Excel using Power Query - YouTube

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pbiblog]

OP under impression that power query offered batch file transforms and file format conversions.  Suggested alternative of Power Automate.   Video suggested does not meet OP requirements.

[Syndicate_Admin]

Okay, got it.. I am not seeing option to remove the topic or message that I have posted. I will take care of these things going further. Thanks for guiding on the way.

[foodd]

Following the same question cross-posted on both the Desktop and now the Power Query Forum.

Power BI isn't an automation suite, I'd consider Power Automate, VBA or Python. One option would be to use Power Query in Excel, where the result loads to a table in a worksheet, and then save the workbook as a CSV.  

To which the OP @aswanth replied the intent to cross-post in the Power Query Forum.

Replying to @aswanth :

The point is not to move the post to the Power Query Forum.  It is that you need to build out the business logic on which tools you will use, and how they will be used.   Recommend you explore your options and once you have a sense of a direction, do your best to produce an inventory of Excel files, where stored, and add more information to your transformation requirements.    This is a small project, and as such you and your company will need to scope out how the company data assets are impacted, and any risks identified.   Then, if you need assistance feel free turn back to one or more of the Community Forums, and if possible post in a 'one question' -> 'one answer' approach. 

@aswanth ,  The vast majority of the community are volunteers with day jobs and offer up their time and talents to help others.

Don't cross-post

Cross-posting is considered rude and wasteful and it has been so since the dawn of forums. Don't do it. Again, some answerers use the "All Topics" view of the forums and then we see 5 posts with the same title and question. Which one do we answer? The bottom line, some community members will not answer a cross-posted topic on principle alone. If you are not sure where to post your question, use the "General" forum.