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.

 

[severynm]

Welcome!

How do you (or I) know which comment is supposed to go to which transaction (i.e. what is the criteria)? Is there some sort of ID column you can use, or are you looking at whichever row has a date? Based on the table, I initially do not see any kind of repeating pattern that could be used to figure this out.

 

[Bonstan]

Welcome!

How do you (or I) know which comment is supposed to go to which transaction (i.e. what is the criteria)? Is there some sort of ID column you can use, or are you looking at whichever row has a date? Based on the table, I initially do not see any kind of repeating pattern that could be used to figure this out.

Thank you for the quick reply and I am sorry if I was not clear enough.

I am looking at whichever row in Column E (Balance)has data in it. Can I explain my problem by referring to the cells in the table below. Row 2 in this table is as I want it to remain because this row has the Date, Comment, Debit or Credit and Balance data all filled up.

	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

Regarding Rows 3 and 4, Row 3 has no data in Column E. Therefore the Comments “REF ZAF-003” and “INSTRUCTION 007 “ in Rows 3 and 4 of Column B respectively have to be joined in Row 4, ) because Row 4 has data in the Column E, like thus:

4

18/11/2019

REF ZAF-003 INSTRUCTION 007

150.89

11.65

The Date should be copied from the closest Date on top of that row. Incidentally, the date in Cell A5 in my original second table should be 18/11/2019 and not 8/11/2019 (my apologies, my mistake).

All data in cells in Column B that have no corresponding data in the same row in Column E should be joined and form part of the next row in Column E with data in it.

In other words, any row in Column E (Balance) that has data in it has to have the data in Column B of that same row populated with data added from the rows above it which have no data in Column E.

If you need any further clarification, please let me know and thank you again for your patience and advice.

 

[severynm]

Sorry for the long response but I've been busy at work recently. If you are still looking for a solution I should have some time later this week to help out more.

 

[Bonstan]

Thankyou for your interest. I am still looking for a solution and would very much appreciate your help. If you need any information please let me know. I look forward to your advice.

 

[Bonstan]

Sorry for the long response but I've been busy at work recently. If you are still looking for a solution I should have some time later this week to help out more.

Dear severynm,
Did you find some time to look into my problem?
Thanks
Bonstan

 

[shaowu459]

I assume the rules of how to combine rows are still not clear. E7 needs to combine with E6, so why not E3 combine with E2?

 

[Bonstan]

I assume the rules of how to combine rows are still not clear. E7 needs to combine with E6, so why not E3 combine with E2?

View attachment 43515

B7 has to combine with B6 and B4 has to combine with B3. The rows that have dates on them do not need to be combined. I am not sure whether I am making myself clear. Thanks.

 

[shaowu459]

B7 has to combine with B6 and B4 has to combine with B3. The rows that have dates on them do not need to be combined. I am not sure whether I am making myself clear. Thanks.

Each group has 3 rows？

 

[shaowu459]

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