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

Resources