M Query Resource on doing conditional transforms on rows.

[daleos]

I have a bunch of bank statements in pdf form that I want to extract and transform the data. Unfortunately Power Query only goes so far at importing the data neatly and some fields end up split over two rows. Are there any resources about that focus on manipulting data in rows, not just columns?

Here's a mocked up version of one of these statement that has been imported into Power Query. As you can see, some Payment details are split over two rows and when that happens the values end up on the wrong row too. I need to be able to conditionally shift certain values up one row. There's also a small issue of concatenating the details text but I think I can manage that part once I get the gist of how to move data between rows. 

I see there's two aspects to M Query I need help on
1. How to set up conditional actions.
2. How to deal with transforming rows 

Any ideas where to look for tutorials at this skill level?

[smpa01 · Accepted solution]

@daleos  try this

let
Source = Excel.Workbook(File.Contents("C:\Users\user1\Desktop\dummystatement.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", type date}, {"Code", type text}, {"Payment type and details", type text}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type number}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Date", "Code"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Date", "Code"}, {{"ad", each _, type table [Date=nullable date, Code=nullable text, Payment type and details=nullable text, Paid out=nullable number, Paid in=nullable number, Balance=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Payment type and details", each let x = [ad],
_list=x[Payment type and details],
Loop1 = List.Generate(
    ()=> [i=0,j=_list{i},k=" ",l=j&k],
        each [i]<List.Count(_list),
        each [i=[i]+1, j=_list{i},k="",l=[l]&k&j],
        each [l]
) in Loop1{List.Count(_list)-1}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Paid out", each let x = [ad],
_list=x[Paid out]

in try List.Distinct(List.RemoveNulls(_list)){0} otherwise null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Paid in", each let x = [ad],
_list=x[Paid in]

in try List.Distinct(List.RemoveNulls(_list)){0} otherwise null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Balance", each let x = [ad],
_list=x[Balance]

in try List.Distinct(List.RemoveNulls(_list)){0} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"ad"})
in
#"Removed Columns"

[daleos]

Perfect. Thank you so much. Time to study it!

[smpa01]

@daleos  this is not how you provide sample data. Please share the excel either through gdrive/1drive or provide the sample data here in a way that can be easily copy as a table and pasted in PBI as a table.

[smpa01]

@daleos  it is doable. Provide sample data here.

[daleos]

Here you go...

Date,Column2,Payment type and details,Column4,Paid out,Paid in,Balance
13 Dec 20,,BALANCE BROUGHT FORWARD,,,,,500
19 Dec 20,BP,GOLD CARD 999999,,,,
,,99999******9999,,74.1,,425.9
21 Dec 20,DD,FIBRE PYMTS,,54.5,,
,SO,SMITH D *OBS,,,,
,,MONTHLY SAVINGS,,400,,
,TFR,909090 099999999,,,,
,,INTERNET TRANSFER,,,200.82,
,CR,TOMMYS TOYS,,,,
,,IT WORKS,,,112,284.22
23 Dec 20,CR,RICKYS SPORTS,,,48,332.22
29 Dec 20,BP,CINDYS CAKES,,,,
,,CINDYS CAKES LTD,,,480,812.22
31 Dec 20,CR,MEGACORP,,,,
,,MEGACOPR LTD,,,1000,1812.22
03 Jan 21,BP,D SMITH,,,,
,,MYBANK DD,,1312.22,,500
08 Jan 21,CR,MR.MONEY ACCS,,,,
,,908 MR.MONEY,,,500,1000
13 Jan 21,,BALANCE CARRIED FORWARD,,,,1000

[daleos]

Cool. Thanks for the tip.

Here you go

https://1drv.ms/x/s!AtUEgNH-XqdngcAtnRDlnx5Y3_U9KQ?e=h7V5fV