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:

 

DateTransaction descriptionValue DateDebitCredit
08.12.2021DIRECT DEBIT SEPA SERVECORP ECH/081221 ID08.12.202126.97 
 SENDER/FR79AAA9876 MDT/989898   
 REF/211368-253-11351865-00825433   
 LIB/HOMESERVE   
 RTXRXT-11118-113-11351865-0081111   
09.12.2021WIRE TRANSFERT SEPA FROM AMAZON09.12.2021 14.99
 /REASON REFUND MOUSE   
 /REF   
09.12.2021WIRE TRANSFERT SEPA FROM ACME09.12.2021 1074.67
 CONSULTING FEE   
 /REASON CONSULTING /REF 11880266   
 FOR LASTNAME 1121   
10.12.2021WIRE TRANSFERTEMENT SEPA  /REASON INVOICE 16106511.12.20218.4 
 DATED NOVEMBER 12 2021 /BEN ACME2 /REFDO   

 

Delivery:

 

The issue is that the description field overflows into multiple lines.

I've tried Fill Down to repeat the data items before grouping by, but it doesn't work: the 26.97 debit of the first record will overflow to the 2 next records, and only stop for the last record because there's an 8.4 debit. So I will get invalid data.

 

Is there anyway to make the Fill conditional? e.g. fill debit until there's a non-null date on a line.

 

If not, how would you go about getting the final table?

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))

5 Replies

  • AlienSx's avatar
    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's avatar
      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:

       

      Step z:

      What should I change?

      My locale is French, could this be the issue?

      Thanks very much for your help.

      Bank Statement import test.xlsx 

  • 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
    • CedricMi's avatar
      CedricMi
      New Member
      3 years ago

      Thanks AlienSx  !

       

      Now I will try to adapt the code to the real imported PDF data.