Forum Discussion

atasovski's avatar
atasovski
Copper Contributor
Jul 30, 2023

Calculate the sum of turnover on client account by date of loan

Hi all,   Can someone please help, I'm working on a rather big table in PowerQuery(cca 8.000.000 rows) I have a table with 4 columns  Client ID          LoanDate       TurnoverAmount    Turnover...
excel
atasovski's avatar
atasovski
Copper Contributor
Jul 31, 2023

OliverScheurich it worked perfect but with small number of clients. When I turn off the filter on clients so I can see all clients in the file I got this message

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 31, 2023

    atasovski 

    As variant

    let
    getTurnover = Table.Group(
        Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        {"Client ID", "LoanDate"},
        { {"Turnover",
            each [
                tbl = _,
                loanDate = tbl{0}[LoanDate],
                filter = Table.SelectRows( tbl,
                    (q) => q[TurnoverDate] <= loanDate and
                           q[TurnoverDate] >= Date.AddDays(loanDate, -30)),
                t = List.Sum( filter[TurnoverAmount] )
            ][t], Currency.Type
        }  } )
in
    getTurnover
    • atasovski's avatar
      atasovski
      Copper Contributor
      Jul 31, 2023
      I get this error
      Expression.Error: The column 'ClientID' of the table wasn't found.
      Details:
      ClientID
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 31, 2023

        atasovski 

        In the sample it was "Client ID", not "ClientID"

Resources