How to Combine Debit and Credit Ledger Account Data into a Single Line in Microsoft Power Query

[awaheedm-]

Hello Microsoft Community,

I'm currently working with data in Excel and I'm facing a challenge that I hope someone can assist me with.

I have a dataset that displays debit and credit entries on separate lines like this:

Date       | Account     | Debit ($) | Credit ($)

------------------------------------------------

2023-10-01 | Account A   | 100       | 0

2023-10-01 | Account B   | 0         | 100

2023-10-02 | Account A   | 50        | 0

2023-10-02 | Account B   | 0         | 30

2023-10-02 | Account C   | 0         | 20

However, I need to combine the debit and credit entries for each account into a single line to get a ledger-like format, like this:

Date       | Debit Account | Credit Account | Amount ($)

------------------------------------------------

2023-10-01 | Account A   | Account B   | 100

2023-10-02 | Account A   | Account B   | 30

2023-10-02 | Account A   | Account C   | 20

Is there a way to achieve this in Excel? Any guidance or suggestions on how to approach this problem would be greatly appreciated.

Thank you in advance for your help!

[mussaenda]

Hi @awaheedm- ,

[AlienSx]

Hello, @awaheedm- do you always have the same pattern when only one debit and one or more credit entries back to back in line for the same date? Or the following:

Account A DT 60

Account B DT 40

Account C CR 70

Account D CR 30

is also possible for the same date?