Help with transforming data

[donodackal]

Hello BI Gurus!

I receive monthly excel reports from our car hire contractor. The monthly report has any incremental costs and the overall status of the vehicle. The report contains data that differs for the same vehicle depending on the reporting period. See the below table for example. 

I have four different reports that contain the same vehicle. I have appended all four reports to get a consolidated report. This gives me the total cost and Km each vehicle has incurred. I need help in only extracting data from the most recent report or details such as the current owner and when the registration expires. My end data model will be in Power BI. See the below table for the output I am looking for: 

PS: I have data going back to 2017 and have appended 48 spreadsheets to get a consolidated sheet. There are approximately 200 vehicles that I would like to get this information. 

Any help would be appreciated.  Thanks in advance! 🙂 

[v-jingzhang · Accepted solution]

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.

[lbendlin]

You are already doing all the steps you need to do. Hopefully you are storing these files in a network location (or a sharepoint folder).

You could consider pushing the data into a dataflow but it's not really required with this small amount of data.

Once you loaded all the data you can then decide how you want to identify the latest transaction per car.