Table data link up question

[Ericwhv]

Hi All

First of all, i do really appreciate your time on reading this.

I got a problem on Power Query(power BI) about the data linking,showing.

First of all, i have two Table,
the first are shipment details table

HB_REFERENCE	Weight	Lane	Cargo_Ready_Date	RouteLeg
H13458828	100	BLRHYD	03/05/2023 10:30	FirstLeg
H13458829	50	BLRHYD	03/05/2023 11:00	FirstLeg
H13458908	200	BLRHYD	04/05/2023 22:00	FirstLeg
H13458910	100	BLRHYD	04/05/2023 22:00	FirstLeg
H13458968	300	BLRHYD	04/05/2023 23:30	FirstLeg
H13459055	200	BLRHYD	06/05/2023 22:00	FirstLeg
H13459056	300	BLRHYD	06/05/2023 23:22	FirstLeg
H13459316	500	BLRHYD	12/05/2023 17:45	FirstLeg
H13459317	200	BLRHYD	12/05/2023 17:45	LastLeg

the second are the truck schedule table

Lane	Departure	Arrival
BLRHYD	01-May	02-May
BLRHYD	02-May	03-May
BLRHYD	03-May	04-May
BLRHYD	04-May	05-May
BLRHYD	07-May	08-May
BLRHYD	08-May	09-May
BLRHYD	09-May	10-May
BLRHYD	10-May	11-May
BLRHYD	11-May	12-May
BLRHYD	14-May	15-May

the result i want to achieve as below

Lane	BKD	ETA	Weight	Explaination
BLRHYD	01-May	02-May	0	No cargo is ready
BLRHYD	02-May	03-May	0	No cargo is ready
BLRHYD	03-May	04-May	150	Cargo is ready 100 + 50
BLRHYD	04-May	05-May	600	cargo is ready 200+100+300
BLRHYD	07-May	08-May	500	06/May Cargo is ready roll it in this row 200+300
BLRHYD	08-May	09-May	0	No cargo is ready
BLRHYD	09-May	10-May	0	No cargo is ready
BLRHYD	10-May	11-May	0	No cargo is ready
BLRHYD	11-May	12-May	0	No cargo is ready
BLRHYD	14-May	15-May	700	12/May cargo is ready roll it in this row 500 + 200

not sure if that is possible?

if the cargo ready date is same as BKD date, then, add all of the weight and put it on that row
if the cargo ready date which do not have a schedule, it will push to the next first available schedule.

Thank you everyone!

[Ericwhv]

thank you Stephen!
it could work

but when i create a customs column it come up with below error

[Expression.Error] We cannot convert the value null to type Logical.

i guess it is because of some schedule do not have any cargo ready like below, then, these row become am "Error" could you mind to tell me how get rid of these blank, i prefer to keep it, thx!

at the end, i cannot group it together, thank you.

[Anonymous]

Hi @Ericwhv ,

The error message you are seeing is because the value in the column you are referencing is null, and Power BI is trying to convert it to a logical value To get rid of the blank rows, you can filter them out using the filter feature in Power Query. However, if you prefer to keep the blank rows, you can try using replace error/replace value to return BLANK when a meaningful value cannot be returned. 

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Ericwhv]

Thank you Stephen
Yes, literally, there is some "Truck Schedule" are blank with no cargo can be move, so, it got an "Error"
I just grab these blank schdule out first, anyway, all good now, thank you so much for your help and time!!

[Anonymous · Accepted solution]

Hi @Ericwhv ,

The solution is simple, but it's a bit complicated to tell, and I'll try to make it clear.

1.Create a custom column in your truck schedule table.

2.Use the Merge Queries feature to join the two tables based on the Lane column.

3.Add a custom column in the merged table to filter out the rows that match your needs.

4.Use the Group By feature to group the data by Lane and Departure columns, and use the Sum aggregation function to calculate the total weight.

5.Finally, duplicate the truck schedule table, note that it is not a reference, and then merge it inside based on the Lane column and the Departure column.

6.This is the expected result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.