Combine data into a single field

[janaselva]

Hi All, 

I have the following table. 

TABLE A

Contract No.   Invoice No.   Invoice amount   Invoice_Date

10001               2341           $100                     01.05.2022

10002               2894           $600                     01.05.2022

10002               2895           $200                     20.04.2022

10003               1799          -$500                    18.05.2022

10003               1800           300                       01.06.2022

I would like to create another table which has information in the below format. Where in i would like to create a table with the contract number, consolidate_invoice_amount and invoice number for each of the contract (which contribute to the consolidated amount), Invoice date of the various invoices. I will be using this table to automatically trigger an reminder email to the team who have oustanding payments. The challenge i am facing here, i am able to create the below table with consolidated invoice amount but unable to pull together the invoice numbers onto a single field relevant to the contract. 

Can some one provide any tips on how to create these 2 fields. Email program will trigger a email every row where the status is "send reminder"

Contract no.        Consolidated_Invoice_Amount  Invoice_number   Invoice_date                                Decision_Status

10001                  $100                                             2341                      01.05.2022                                Send reminder

10002                  $800                                             2894, 2895             01.05.2022, 18.04.2022            Send reminder

10003                  -$200                                            1799,1800              18.05.2022, 01.06.2022            No Reminders.

I appreciate your support on this.

Thanks and regards,

Jana

[ManguilibeKAO · Accepted solution]

Hi janaselva,

Here's a solution:

Create a new table Table B, with rhe following formula:

Table B =
Summarize('Table A',
'Table A'[Contract No],
"Consolidated_Invoice_Amount",SUM('Table A'[Invoice amount]),
"Invoice_number",CONCATENATEX('Table A','Table A'[Invoice no],","),
"Invoice_date",CONCATENATEX('Table A','Table A'[Invoice_Date],",")
)

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

Manguilibe KAO