Combine data into a single field

Question

Hi All,&nbsp;I have the following table.&nbsp;TABLE AContract No.&nbsp; &nbsp;Invoice No.&nbsp; &nbsp;Invoice amount&nbsp; &nbsp;Invoice_Date10001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2341&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01.05.202210002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2894&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$600&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01.05.202210002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2895&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20.04.202210003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1799&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -$500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18.05.202210003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;300&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01.06.2022&nbsp;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.&nbsp;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"&nbsp;Contract no.&nbsp; &nbsp; &nbsp; &nbsp; Consolidated_Invoice_Amount&nbsp; Invoice_number&nbsp; &nbsp;Invoice_date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Decision_Status10001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2341&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01.05.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Send reminder10002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2894, 2895&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01.05.2022, 18.04.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Send reminder10003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -$200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1799,1800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18.05.2022, 01.06.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No Reminders.&nbsp;I appreciate your support on this.&nbsp;Thanks and regards,Jana

manguilibekao · Accepted Answer

Hi janaselva,Here's a solution:&nbsp;Create a new table Table B, with rhe following formula:&nbsp;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],",")) &nbsp;&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;Manguilibe KAO

Forum Discussion

Combine data into a single field

1 Reply

Related content

Tables combine

remove the date hierarchy for a single date field

Field Parameter - Single selection

Combining two visuals in single visual

Error in Power Query combining overlapping start end datetime fields