M Query Resource on doing conditional transforms on rows.

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"