Power Query Formula

[Bonstan]

Can somebody kindly help me with the following problem in the Excel table below? I am a new Power Query user and am trying to combine the rows in Column B for each transaction into a single cell by creating a Custom Column. What formula should I use? i.e.

Table as is​

A​ B​ C​ D​ E​ 1 Date Comment Debit Credit Balance 2 18/11/2019 Cheque 001 150.89​ 139.24DR 3 REF ZAF-003 4 INSTRUCTION 007 150.89 11.65 6 29/11/2019 Cheque 002 414.93​ 209.9​ 12 GO DIRECT DEBIT 36.45​ 173.45​ 13 TRANSFER REF

Table as I would like it to be​

A​ B​ C​ D​ E​ 1 Date Comment Debit Credit Balance 2 18/11/2019 Cheque 001 150.89​ 139.24DR 5 8/11/2019 REF ZAF-003 INSTRUCTION 007 150.89 11.65 6 29/11/2019 Cheque 002 414.93​ 209.9​ 12 29/11/2019 GO DIRECT DEBIT TRANSFER REF 36.45​ 173.45​ 13

I would appreciate some advice on this. Thank you.

 

[shaowu459]

_[Comment]should change to a[Comment]

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.Combine(Table.Group(Source,"Date",{"n",each let a=Table.Skip(_) in Table.FillDown(Table.FromRecords({_{0},Table.SelectRows(a,(x)=>x[Balance]<>null){0}&[Comment=Text.Combine(a[Comment]," ")]}),{"Date"})},0,(x,y)=>Byte.From(y<>null))[n])
in
    res

 

[Bonstan]

For your reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.Combine(Table.Group(Source,"Date",{"n",each let a=Table.Skip(_) in Table.FillDown(Table.FromRecords({_{0},Table.SelectRows(a,(x)=>x[Balance]<>null){0}&[Comment=Text.Combine(_[Comment]," ")]}),{"Date"})},0,(x,y)=>Byte.From(y<>null))[n])
in
    res

View attachment 43532

 

[Bonstan]

Thankyou for your formula. This seems to be working, but not completely. I refer to the Table A in the attached image:. What I would like is for the rows in Column “Comment” to be joined according to the different background colour scheme, as per the image. Each group does not have to have three rows but as many as needed.

With the formula the result I am getting, although very helpful, is not quite what I would like. Thus, If I press Get Data and load Table B, (in the attached image), what I am getting after I apply the formula and press Load Data is what appears in Table C .

Can the formula be adjusted to what I would like? The attached image contains the comments above. Hopefully the image is clear enough.

Again, Thank you for your help.