Forum Discussion
Bank Statement, Description Across Multiple Rows
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.
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.
12 Replies
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:
- In Power Query, select the Date column.
- 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.
- Select the Date column.
- On the Transform tab, click Group By.
- 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).
- 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:
- Click the small expand icon next to the nested table column (e.g., "Combined Description") in the grouped result.
- Uncheck everything except for the Description column.
- Click OK.
Now, you have a new column with the descriptions for each Date grouped together.
- 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.
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.
...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)
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.
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.
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.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.
