Count difference between dates

[KGPBI]

I would like to count the number of days an invoice is paid in full. So in the same column the difference between the min and max date per invoice number.

As a table I use the general ledger mutation table for this. So I have multiple rows per invoice. This looks like this:

Invoice number	date
101	1-1-2019
101	1-2-2019
101	2-2-2019
101	3-2-2019
101	4-2-2019
101	5-2-2019
101	6-2-2019
101	7-2-2019
102	1-1-2019
102	2-1-2019
102	3-1-2019
102	4-1-2019

[KGPBI]

@CNENFRNL thanks i should learn more Power query!

[CNENFRNL · Accepted solution]

@KGPBI , a most straightforward way is to calculate the duration in Power Query,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BDQAQEATAXu5NYtchahH9tyHxIOx3XjOGIcGCISIyodsMl/gTlbKSKxWlqtReor64E0JZyQ/NBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice number" = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice number", Int64.Type}, {"date", type date}}, "en-GB"),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice number"}, {{"Diff", each Duration.Days(List.Max([date])-List.Min([date]))+1}})
in
    #"Grouped Rows"