Sum of multiple rows into one row

[Bleucherie]

Hello all, I have set up an import template to upload journal entries into our accounting system. I used Power Query and have it close to what is in the How it should look tab but need some help in summarizing specific rows using Column F in my PQ Table. For example, for June 17th I would like one row to show the sum of all the rows that have 630 and one row that shows the sum of all the rows that have 375 but keep the rows that have 523 listed individually as I need to show the different journalItemLine_memo for each. Also, since each day would be its own entry, I may also need help on how I can add the tranId column (Column A) in the How it should look tab to do sequential numbering for each date but since the transactions per day vary not sure how I can make it work. Thank you!

Sample.xlsx

[AliGW]

I had a look and don't understand the logic of what you want at all or where the extra columns are coming from.

It would help if you could fully explain, for example, the data for 17 June and the logic of its transformation from Table1 to how you want it to look.

[Bleucherie]

I had a look and don't understand the logic of what you want at all or where the extra columns are coming from.

It would help if you could fully explain, for example, the data for 17 June and the logic of its transformation from Table1 to how you want it to look.

Hello AliGW,

I have updated my post and attached an updated file to be clearer on my ask. Hopefully it makes sense. Thank you.

[JeteMc]

This was as close as I could get to what you wanted.
Added two columns to Table1
1. Transaction Id: =IF(A2<>A1,"062124JE RS Upload"&TEXT(NETWORKDAYS(A$2,A2),"000"),K1)
2. Batch: ="organization"
Added to the M-Code:

Please Login or Register  to view this content.

Produced a pivot table in which I collapsed the account references for 630 and 375 while leaving those for 523 expanded.
Let us know if you have any questions.