Merging cargo journey data with intermediate stops

Hi hartleybovine84 ,

Here's the explanation of how to group and transform your cargo movement data in Power BI using Power Query Editor, with the M code provided at the end.

Start by loading your data into the Power Query Editor. Once your tables are loaded, the next step is to handle intermediate stops and duplicated rows. Begin by identifying unique trips based on the following columns: Date, ID, Time, Point A, Point B, and Carrier. Use the Group By feature to consolidate rows with the same trip details.

In the Group By window, select the columns Date, ID, Time, Point A, Point B, and Carrier to group your data. For the Distance and Mass columns, select the Sum function to add up the values. For the Cargo column, select All Rows. This will create a nested table in the Cargo column.

After grouping, the nested table in the Cargo column needs to be expanded and concatenated into a single string. To achieve this, use the Expand icon next to the Cargo column and select Aggregate > Text.Combine. In the Custom Column dialog box, use the formula Text.Combine(List.Transform([Cargo], Text.From), ", ") to concatenate the cargo types into a comma-separated list.

To handle intermediate stops, follow a similar process. Group the intermediate stops table by Date, ID, Time, and Carrier. Sum the Distance and Mass columns, and concatenate the Cargo column. Once both tables are grouped and transformed, use the Merge Queries option to join the main table and the intermediate stops table based on Date, ID, Time, and Carrier. After merging, expand the merged table to adjust the total distance and mass columns.

Below is a sample M code that performs these transformations. The code groups the rows, sums the distances and masses, and concatenates the cargo types into a single string.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Date", "ID", "Time", "Point A", "Point B", "Carrier"}, {
        {"Total Distance", each List.Sum([Distance]), type number},
        {"Total Mass", each List.Sum([Mass]), type number},
        {"Cargo Types", each Text.Combine(List.Transform([Cargo], Text.From), ", ")}
    })
in
    GroupedRows

This M code transforms your dataset to a format where each trip is summarized into a single row, showing the total distance traveled, the total mass of cargo, and a concatenated list of all cargo types carried during that trip. For trips with intermediate stops, the process ensures the total distance is aggregated across all segments of the journey. After applying these steps, the final dataset will be clean, consolidated, and ready for analysis in Power BI.

Best regards,