Bank Statement, Description Across Multiple Rows

[LurkingDude]

When importing a bank statement into PQ, all is well except for the fact that the Description column on some entries is spread across two or more (variable number) rows. I would like to combine them into one row.

I have a pattern to work with, however, in that the Date column only displays the date in the first row of each statement entry. The rest are null. For example:

Date
2024-12-02
null
2024-12-02
null
null
2024-12-02
null
null
null
etc...

So I can see what needs to be done, just no idea on the implementation. Can this be done exclusively with the user interface, or is some M code manipulation required?

Thanks.

[ronrsnfld · Accepted solution]

Probably most efficient with M-Code.

You can Group by the dates, then concatenate the Description column.

Given your date column, you can use the fourth and fifth arguments of the GroupBy function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1NNI1MFLSUUpMUorViVbKK83JAfKSU8A8FBWpacgq0jOQeZlZmOqzc5BV5OYh8/ILkHmFRUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Description", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {
        {"Description", each Text.Combine([Description]," "), type text}
        }, GroupKind.Local,(x,y)=>Number.From(x[Date] <> null and y[Date]<>null))
in
    #"Grouped Rows"

Source Data example

Results:

You will need to consider how to treat the other columns, depending on how they are laid out.

[dufoq3]

Hi @ronrsnfld, in this case where you have only 1 grouping column you can write the code this way:

[ronrsnfld]

That is interesting. I actually tried that first but had the grouping column within a List "..{"Date"}...", where it doesn't work (requires y[Date]).

Doesn't seem to be much of a performance difference though.

Some times there is (with the y only version being slower) and sometimes not.

[dufoq3]

Hi @ronrsnfld , that's interesting, but I think the speed is the same. It's not that easy to measure the exact query calculation duration because you have to maximize CPU usage and turn off all other unnecessary processes

[LurkingDude]

Update:

I've copied your M code and placed it in the appropriate spot and it is working perfectly to solve the problem. I don't understand WHY it's working yet, but I'll work on that. 🙂

Thank you for your help in providing the solution.

[ronrsnfld]

Glad to help. Take a look here for an explanation of the fifth argument of the GroupBy function.

[LurkingDude]

This solution is working great, but I always like to understand why it's working.

I've been reviewing the 5th argument in the Table.Group function. It appears you have created basically a Lambda function as I would see in Excel. I believe x refers to the current row, and y refers to the following row.

How is the logic working there? The way I've been reading it, it should NOT work! I am clearly missing comething. Can you assist my understanding?

Thank you.

[ronrsnfld]

It is mentioned in the article I linked above. It is counterintuitive (because 0 = false), but if fifth argument returns a zero (0) then the rows are in the same group. 

[LurkingDude]

Thanks for the response. I am not at all proficient with M coding as yet to make any sense of this. I will need to look it over carefully and see how I can incorporate it into my own problem.

[AlienSx]

...some M code hand writing would be easiest in your case. Show a sample of your data - it's not clear what to do with any other columns (other than Description)

[Shravan133]

Step 1: Fill Down the Date Column

Since the Date column contains the correct date in the first row of each statement entry and null values in subsequent rows for the same entry, you can fill down the date to align the entries:

1. In Power Query, select the Date column.
2. On the Transform tab, click on Fill > Down. This will propagate the date value downward for all rows that follow.

Now, your table will look like this:

Date	Description
2024-12-02	Transaction 1
2024-12-02	Description part 2
2024-12-02	Description part 3
2024-12-02	...
2024-12-02	...

Step 2: Group the Rows Based on the Date

After filling down the Date column, you can group the rows by the Date column and concatenate the descriptions for each group.

1. Select the Date column.
2. On the Transform tab, click Group By.
3. In the Group By dialog:
• Choose Date for the grouping column.
• Set the New column name to something like "Combined Description."
• Under Operation, select All Rows (this will create a table of rows for each date).
4. Click OK.

This step will group all the rows by the Date column and create a nested table for each Date group.

Step 3: Combine the Descriptions

Now, you'll combine the descriptions from each group (for each Date) into one cell:

1. Click the small expand icon next to the nested table column (e.g., "Combined Description") in the grouped result.
2. Uncheck everything except for the Description column.
3. Click OK.

Now, you have a new column with the descriptions for each Date grouped together.

1. To combine the descriptions into a single text string, you'll need to use a custom column. Click Add Column > Custom Column and use the following formula:

Text.Combine([Combined Description][Description], " ")

This will combine all descriptions within each group into a single string, separated by a space (you can choose a different separator if desired).

Step 4: Final Cleanup

• If you don't need the nested table anymore, you can remove it.
• You can also remove any columns you no longer need, like the old Description column or the intermediate "Combined Description" column.

[LurkingDude]

Thanks for the response. I think I was not specific enough in my first post, however.

Your solution would work IF date were a unique identifier. In other words, if there were only one statement entry for each date. But there are many entires for each date (the description for each entry spans a variable number of rows). If I fill down and then group by date, ALL the description fields matching that date will simply be mashed together, no?

I was thinking that the solution might have something to with creating an Index column, transforming that index column somehow to reflect the correct number of rows per entry, and then using your method to group by that index column and concatenate the description. But I don't know how to implement it.

Thanks again.