Forum Discussion
Bank Statement, Description Across Multiple Rows
- 1 year ago
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.
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.
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.
- ronrsnfld1 year agoSuper User
Glad to help. Take a look here for an explanation of the fifth argument of the GroupBy function.
- LurkingDude1 year agoFrequent Visitor
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.- ronrsnfld1 year agoSuper User
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.