How to sum cost in a day with counting duplicate entry once only?

[PBI0311]

Hi guys,

I'd like to get some enlightenment on how to solve the following question.

I need to sum my daily delivery to different countries as shown below, however, for example like UK got more than once shipment in a day but I only count once if it happened in a same day. Hope to get some advices here

 

[alansidman]

Can be solved using Power Query. 3 Queriies as follows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Date", "Country"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Date"}, {{"Total Cost", each List.Sum([Del Cost]), type number}})
in
    #"Grouped Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Date", "Country"}),
    #"Extracted Month Name" = Table.TransformColumns(#"Removed Duplicates", {{"Date", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Date"}, {{"Total Cost By Month", each List.Sum([Del Cost]), type number}})
in
    #"Grouped Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Date", "Country"})
in
    #"Removed Duplicates"

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Country	Del Cost		Date	Total Cost		Date	Total Cost By Month		Date	Country	Del Cost
2	1-Jan	UK	100		1/1/2021	180		January	280		1/1/2021	UK	100
3	1-Jan	UK	100		1/2/2021	100					1/1/2021	Germany	50
4	1-Jan	Germany	50								1/1/2021	Japan	30
5	1-Jan	Japan	30								1/2/2021	UK	100
6	2-Jan	UK	100
Sheet1