Merging cargo journey data with intermediate stops

[hartleybovine84]

Hi PowerBI community,

I'm fairly new to PowerBI, using it for data analysis purposes. I am struggling to group data together in the edit query that is duplicated in nearly every, but not every column.

I have data I receive about cargo movements. The data has been abstracted for confidentiality purposes. Please see the attached images to show how this data arrives to me.

For a given shipment on a given day, the data is separated by cargo type and by mass. Therefore each row shows a shipment of an individual cargo item, where I instead want one row to show a total shipment for the day. Again, this is hopefully explained in image 1.

For example, "Bob2" left at 05:50 from Loc1 ("location 1") to Loc6, using a yellow lorry, and travelled 80 miles. It carried bags, tubs, boxes and barrels, for a total mass of 120. Currently, this is made up of four rows, with one for each cargo item. I'd like it to be one row with the cargo grouped together, as shown in the bottom table of image 1. 

The thing that complicates this is that some journeys have intermediate stops. This is shown in image 2, where Harry1 goes from Loc2 to Loc7 via Loc4. The departure time remains the same as it is the departure from Point A, not the time it leaves each location. 

However, the distance does change, so would need to be added together. I am not interested in intermediate stops, and would like the data to be condensed to the same outcome as if the intermediate steps were not there. 

I've tried using "group by" in the editor and grouping by both Date and ID, and then I get a nested table with the remaining data. From there, I cannot figure out how to merge the columns that remain constant (Point A, point B, carrier, and distance), sum the masses, and string the cargo types together all in one go. Additionally, I'm completely stumped as to what to do with the intermediate stops.

I've put the data into two tables below if you want to play around with the sample data yourself. Any support you could offer would be really helpful. Thank you!

Date	ID	Time	Point A	Point B	Carrier	Distance	Mass	Cargo
17/04/2024	Bob1	04:30	Loc1	Loc2	Red Lorry	55	20	Boxes
17/04/2024	Bob1	04:30	Loc1	Loc2	Red Lorry	55	10	Bags
17/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	10	Bags
17/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	40	Tubs
17/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	20	Boxes
17/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	50	Barrels
17/04/2024	Harry1	06:00	Loc2	Loc7	Blue Lorry	35	20	Boxes
17/04/2024	Harry1	06:00	Loc2	Loc7	Blue Lorry	35	10	Bags
17/04/2024	Harry2	06:21	Loc6	Loc1	Blue Lorry	110	50	Barrels
17/04/2024	Harry2	06:21	Loc6	Loc1	Blue Lorry	110	40	Tubs
18/04/2024	Bob1	04:30	Loc1	Loc2	Red Lorry	55	20	Boxes
18/04/2024	Bob1	04:30	Loc1	Loc2	Red Lorry	55	10	Bags
18/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	10	Bags
18/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	40	Tubs
18/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	20	Boxes
18/04/2024	Bob2	05:50	Loc1	Loc6	Yellow Lorry	80	50	Barrels
18/04/2024	Harry1	06:00	Loc2	Loc7	Blue Lorry	35	20	Boxes
18/04/2024	Harry1	06:00	Loc2	Loc7	Blue Lorry	35	50	Barrels
18/04/2024	Harry2	06:21	Loc6	Loc1	Blue Lorry	110	20	Boxes
18/04/2024	Harry2	06:21	Loc6	Loc1	Blue Lorry	110	40	Tubs

Table 2: intermediate stop data (note the different distances)

Date	ID	Time	Point A	Point B	Carrier	Distance	Mass	Cargo
17/04/2024	Harry1	06:00	Loc2	Loc4	Blue Lorry	10	20	Boxes
17/04/2024	Harry1	06:00	Loc2	Loc4	Blue Lorry	10	10	Bags
17/04/2024	Harry1	06:00	Loc4	Loc7	Blue Lorry	25	20	Boxes
17/04/2024	Harry1	06:00	Loc4	Loc7	Blue Lorry	25	10	Bags
18/04/2024	Harry1	06:00	Loc2	Loc4	Blue Lorry	10	20	Boxes
18/04/2024	Harry1	06:00	Loc2	Loc4	Blue Lorry	10	10	Bags
…	…	…	…	…	…	…	…	…

Image 1

Image 1, showing how the bulk of the data appears, and how I'd like to have it.

Image 2

Image 2, showing the exceptional data with intermediate stops.

[DataNinja777 · Accepted solution]

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,

[hartleybovine84]

Hi DataNinja777,

Thank you for your quick and thorough response- this seems to have fixed the problem. I appreciate the help, thank you so much!

All the best,