Forum Discussion
LurkingDude
1 year agoFrequent Visitor
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 ...
- 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.