Forum Discussion

CedricMi's avatar
CedricMi
New Member
3 years ago
Solved

Help needed: conditional combining fields on multiple rows into a single row

I need to transform PDF bank statements into an Excel file. Source:   Date Transaction description Value Date Debit Credit 08.12.2021 DIRECT DEBIT SEPA SERVECORP ECH/081221 ID 08.12.20...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    CedricMi , replace "gr" step by 

    gr = Table.Group(conformedtable2, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> null))
AlienSx's avatar
AlienSx
Super User
3 years ago

CedricMi , replace "gr" step by 

gr = Table.Group(conformedtable2, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> null))
CedricMi's avatar
CedricMi
New Member
3 years ago

Thank you so much AlienSx !

If've finally collated 13 months of data.

 

I moved TransformColumnType to the end, so that dates appeared correctly as dates in the new table:

let
    Source = #"2022",
    f = (t as table) => 
        [rec = Table.ToRecords(t){0},
        descr = Text.Combine(t[Nature des opérations], " "),
        new_rec = Record.TransformFields(rec, {"Nature des opérations", each descr})][new_rec],
    gr = Table.Group(Source, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> null)),
    z = Table.FromRecords(gr[all]),
    z2 = Table.TransformColumnTypes(z,{{"Date", type date}, {"Valeur", type date}, {"Nature des opérations", type text}, {"Débit", type number}, {"Crédit", type number}})
in
    z2