Text contains from a different table and returning the latest field

[Racquel]

Hi,

My aim is to produce a visual that will show customer payment days (ie how long it took them to pay the full invoice)

I have 2 tables. Invoices and Payments.

The invoices have a unique audit number.

The payments have an allocation column which may contain multiple invoice audit numbers.

The payments allocation column may also have one invoice audit number against multiple payments (if there has been part payments). 

Payments:

Invoices:

Formula:

let InvAuditNo=[AutoIdx]
in

Text.Combine(
Table.SelectRows(CustomerPayments,
each Text.Contains(InvAuditNo,[cAllocs]))
[TxDate]
,
",")

As you can see, I am trying to bring in the TxDate from the payments table into the invoices table.

The PaymentDate column gives me an error - is this because there may be multiple payment dates or the column I am brinbging in is a date?

How can I change this formula to bring in the last date that was allocated to the invoice and no date if there has been no payment yet?

Kind regards

R

[Racquel]

Perfect, Thank you SO much 🙂

[wdx223_Daniel · Accepted solution]

let InvAuditNo=[AutoIdx]
in

List.Max(
Table.SelectRows(CustomerPayments,
each Text.Contains([cAllocs],InvAuditNo))
[TxDate]
)