This is how I would build the flow to get what you're after.
Below is the CSV file that I want to transform.
Below is the full flow. I'll go into each of the actions.
Get file content using path retrieves the content from the CSV I want to transform. I'm not sure how you are getting this content into your flow.
Compose converts the content to a string using the following expression.
base64ToString(outputs('Get_file_content_using_path')?['body']?['$content'])
Filter array splits the data by carriage return and new line then removes any empty rows (normal get an empty row at the end after splitting the data). The expressions used are:
//From
split(outputs('Compose'), decodeUriComponent('%0D%0A'))
//Condition
item()
Select uses the body of the Filter array and the following expressions. Note that the column names should be called col0, col1, col2, etc. as shown.
split(item(), ',')?[0]
split(item(), ',')?[1]
split(item(), ',')?[2]
split(item(), ',')?[3]
split(item(), ',')?[4]
split(item(), ',')?[5]
split(item(), ',')?[6]
split(item(), ',')?[7]
split(item(), ',')?[8]
split(item(), ',')?[9]
split(item(), ',')?[10]
split(item(), ',')?[11]
split(item(), ',')?[12]
Create CSV table uses the following expressions.
//From
range(1, 12)
//Month (note this is index 1 to get the month)
body('Select')[1][concat('col', item())]
//Year (note this is index 0 to get the year)
body('Select')[0][concat('col', item())]
//Residential
body('Select')[2][concat('col', item())]
//Multifamily
body('Select')[3][concat('col', item())]
//Commercial
body('Select')[4][concat('col', item())]
//Grand total
body('Select')[5][concat('col', item())]
Create file uses the output from Create CSV table to create a new CSV file with the transformed data. Note that I've just added an expression to build up a dynamic date part for the file name - you can do whatever you want here.
formatDateTime(utcNow(), 'yyyyMMdd-hhmmss')
After running the flow I will get a new CSV file that looks like the example below.
