Forum Discussion
Bank Statement, Description Across Multiple Rows
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.
Super UserProbably 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.- ronrsnfld
Glad to help. Take a look here for an explanation of the fifth argument of the GroupBy function.
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.
- dufoq3
Community Champion
- 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.
