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

[CedricMi]

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.2021	26.97
	SENDER/FR79AAA9876 MDT/989898
	REF/211368-253-11351865-00825433
	LIB/HOMESERVE
	RTXRXT-11118-113-11351865-0081111
09.12.2021	WIRE TRANSFERT SEPA FROM AMAZON	09.12.2021		14.99
	/REASON REFUND MOUSE
	/REF
09.12.2021	WIRE TRANSFERT SEPA FROM ACME	09.12.2021		1074.67
	CONSULTING FEE
	/REASON CONSULTING /REF 11880266
	FOR LASTNAME 1121
10.12.2021	WIRE TRANSFERTEMENT SEPA  /REASON INVOICE 161065	11.12.2021	8.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?

[AlienSx · Accepted solution]

@CedricMi , replace "gr" step by 

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

[CedricMi]

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]

Thanks @AlienSx  !

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

[AlienSx]

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]

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