Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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?
Solved! Go to Solution.
@CedricMi , replace "gr" step by
gr = Table.Group(conformedtable2, "Date", {{"all", f}}, GroupKind.Local, (x, y) => Number.From(y <> null))
@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
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:
Step z:
What should I change?
My locale is French, could this be the issue?
Thanks very much for your help.