Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
CedricMi
New Member

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:

CedricMi_0-1684826100822.png

 

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?

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

@CedricMi , replace "gr" step by 

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

View solution in original post

5 REPLIES 5
AlienSx
Super User
Super User

@CedricMi , replace "gr" step by 

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

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

Thanks @AlienSx  !

 

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

AlienSx
Super User
Super User

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

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:

CedricMi_0-1684930607989.png

 

Step z:

CedricMi_1-1684930639391.png

What should I change?

My locale is French, could this be the issue?

Thanks very much for your help.

Bank Statement import test.xlsx 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors