Forum Discussion

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago
Solved

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 ...
  • ronrsnfld's avatar
    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.