I have a report from Cloudability for an AWS account. I get report in CSV as shown below. The actual report has more products but this demonstrates the problem.
year_month, service_name, total_adjusted_amortized_cost
2021-01-01, Amazon ElastiCache, 100
2021-01-01, Amazon Elastic Compute Cloud, 120
2021-02-01, Amazon ElastiCache, 140
2021-02-01, Amazon Elastic Compute Cloud, 130
2021-03-01, Amazon ElastiCache, 190
2021-03-01, Amazon Elastic Compute Cloud, 120
What I would like to have is this in an excel table where the values in "service_name" are the rows and there is a column corresponding to each unique entry in "year_month". Something like:
| - | 2021-01-01 | 2021-02-01 | 2021-03-01 |
|---|---|---|---|
| Amazon ElastiCache | 100 | 140 | 190 |
| Amazon Elastic Compute Cloud | 120 | 130 | 120 |
So, the only solution I can think of is to use Perl or a shell script to transform the CSV file and then import it into Excel.
I am wondering if there is a better way to do this, may be using Power Query in excel. This seems to be the only way Cloudability exports the reports and I might not be the only one who has faced this issue yet.
You may want to look at pivot tables. They allow you to take data from a table and rearrange it into a new table. To create a pivot table, select the data you would like to use, then select the "Insert" tab and select "Pivot Table". You can then drag and drop the columns you would like to use into the appropriate boxes.