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.
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.