Forum Discussion

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

3 years ago

CedricMi , replace "gr" step by

gr = Table.Group(conformedtable2, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> null))

AlienSx

Super User

3 years ago

Hi, CedricMi

let
    Source = Excel.CurrentWorkbook(){[Name="statement"]}[Content],
    f = (t as table) => 
        [rec = Table.ToRecords(t){0},
        descr = Text.Combine(t[Transaction description], " "),
        new_rec = Record.TransformFields(rec, {"Transaction description", each descr})][new_rec],
    gr = Table.Group(Source, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> Character.FromNumber(32))),
    z = Table.FromRecords(gr[all])
in
    z

CedricMi

New Member

3 years ago

Hi AlienSx ,

Thank you very much for your help.

I had to adapt your code.

However, it doesn't work as expected:

let
    Source = Excel.CurrentWorkbook(){[Name="statement"]}[Content],
    conformedtable = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    conformedtable2 = Table.TransformColumnTypes(conformedtable,{{"Date", type date}, {"Value Date", type date}, {"Transaction description", type text}}),
    f = (t as table) => 
        [rec = Table.ToRecords(t){0},
        descr = Text.Combine(t[Transaction description], " "),
        new_rec = Record.TransformFields(rec, {"Transaction description", each descr})][new_rec],
    gr = Table.Group(conformedtable2, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> Character.FromNumber(32))),
    z = Table.FromRecords(gr[all])
in
    z

Step gr: