Forum Discussion

Sindre_'s avatar
Sindre_
New Member
2 years ago
Solved

Creating a Financial Report Excluding Internal Transactions in Power BI

 

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    AlienSx
    2 years ago
    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    AlienSx
    Super User
    2 years ago
    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