Creating a Financial Report Excluding Internal Transactions in Power BI

[Sindre_]

I have a general ledger in Power Query. The general ledger is composed of ledgers from multiple companies. The goal is to create a financial report for the companies collectively. The financial report should exclude all transactions made between the companies (internal transactions).

Internal transactions are posted against account numbers 1501 and 2401. These transactions receive offsets in the range of account numbers 3000-9999.

Similarly, external transactions are posted against account numbers 1500 and 2400. These transactions also receive offsets in the range of account numbers 3000-9999.

I want to tag the transactions that are in the range 3000-9999 and have a match against transactions on account numbers 1501 and 2401.

In my example, there are 2 transactions on account number 1501, and they have refNumber_SelskapID respectively:

AR - 000012 - 103 and

AR - 000013 - 103.

The same ID is found on account number 3600, and they are tagged with 1. The rest receive 0.

How can I achieve this result?

[v-cgao-msft · Accepted solution]

Hi @Sindre_ ,

Please add a custom column like:

if not List.Contains({3000..9999},[account.number]) then null else if List.Contains({3000..9999},[account.number]) and List.Contains(Table.SelectRows( #"Changed Type", each List.Contains({1501,2401}, [account.number]))[refNumber_SelskapID],[refNumber_SelskapID]) then 1 else 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AlienSx · Accepted solution]

let
    Source = your_table,
    internals = Table.SelectRows(Source, (x) => List.Contains({1501, 2401}, x[account.number])),
    rec = Record.FromList(List.Repeat({1}, Table.RowCount(internals)), internals[refNumber_SelskapID]),
    result = Table.AddColumn(
        Source, "Create this column", 
        (x) => 
            if x[account.number] >= 3000 and x[account.number] <= 9999
            then Record.FieldOrDefault(rec, x[refNumber_SelskapID], 0)
            else null
    )
in
    result

[v-cgao-msft · Accepted solution]

Hi @Sindre_ ,

Please add a custom column like:

if not List.Contains({3000..9999},[account.number]) then null else if List.Contains({3000..9999},[account.number]) and List.Contains(Table.SelectRows( #"Changed Type", each List.Contains({1501,2401}, [account.number]))[refNumber_SelskapID],[refNumber_SelskapID]) then 1 else 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group