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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors