Help with transforming data

Hi donodackal 

I think Group By feature can help achieve your expected output. Since you have appended all data to get a consolidated table, you can transform the consolidated table with the following steps to get the total cost & total KM Run as well as latest owner & expiry date from the most recent report row per car.

Steps are:

1. Add a custom column "Report Date" based on "Report Period" column to get a report date for every row and change this column to Date type. 

Date.FromText("1"&[Report Period])

2. On the Transform tab, select Group By and set up the operations like below under Advanced. 

Cost & KM Run: Sum;

Last Report Date: Max;

All Date: All Rows.

3. Add a custom column to get the lastest report row for every car from "All Data" column.

4. Remove "All Data" column. Expand "Latest Report Data" column from previous step and select "Registration Expiry" and "Owner" columns to be expanded. 

Result below. You can then apply the data to report and select visuals to display the info. 

https://docs.microsoft.com/en-us/power-query/group-by 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.