How to merge select rows in a query, where the date field is required to be retained?

[Adoraiswamy]

Following is the sample data formatted from a pdf file using Power Query;

I require the same to look like this. I am stuck and tried 'Group by' and the result is not as expected.

Can someone help me as to what am I missing or what should I do?

If you require I can upload a sample file.

Thanks

AD

[Adoraiswamy]

Thanks got it.

[AlexisOlson · Accepted solution]

This is rather tricky but I think it can be done by generating an index that groups the rows by iterating through the date column counting how many non-blank rows there are and then grouping on that index.

See this sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3NNc3MjA0UtJRcnR0BJIGSrE60UpARmIikDA2A3ORlTk5OSErS0oCEoYGBjB1ljB1zs7OyOqSk0HqLDGUubi4ICtLSQHZaqIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}, {"Amount", Int64.Type}}, "en-US"),
    #"Added Custom" = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Accumulate(#"Changed Type"[Date], {}, (s,c) =>
if c <> null then s & {(List.Last(s) ?? 0) + 1} else s & {List.Last(s)})}, Table.ColumnNames(#"Changed Type") & {"Index"}),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Index"}, {{"Date", each List.Max([Date]), type date}, {"Text", each Table.Max(_, "Date")[Text], type text}, {"Amount", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"

The key bit of logic generating the index is this:

List.Accumulate(
    #"Changed Type"[Date],
    {},
    (s,c) =>
        if c <> null
        then s & {(List.Last(s) ?? 0) + 1}
        else s & {List.Last(s)}
)

This iterates through the date column and increments the index by one for each non-null date.

[Adoraiswamy]

Thanks, Alexis. 

While I am not conversant with Query Editor I will give it a go and get back to you!

[ronrsnfld]

It might be more useful if your sample results could be derived from your sample data. As it is, they don't match.

[Adoraiswamy]

No, It did not solve my requirement as the second cell in the Details does not get included in the first cell. 

AD

[Adoraiswamy]

Hi Fowmy, Thanks for the attempt.

No, It did not solve my problem.

a) The Description column has details in rows (sometimes one row if it is brief), that requires to be combined;

b) Since the description spreads to two rows the transaction amount is reflected in the second row only. 

My trouble is to get the Description combined from two cells (in two adjacent rows) into one cell and accordingly shift the Credit/Debit and the Balance amount to the top row (like the one below). 

Trust this helps.

Thanks for your attempt to assist me.

AD

[ronrsnfld]

• Create a "grouping" column by
  • Add an index column
  • Add a custom column which copies the Index if Column 1 is not null
  • Fill down the custom column
• Group on the custom column
  • This will create multiple two row subtables
  • Aggregate into a new table by using the
    • First row entry for the date
    • Concatenated first and second rows for the description
    • Row 2 of the last three columns for the appropriate value

let
    Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}, 
    {"Column3", Currency.Type}, {"Column4", Currency.Type}, {"Column5", Currency.Type}}),

//create grouping column by adding an Index column
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "GroupOn", each if [Column1] <> null then [Index] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"GroupOn"}),

//Group and combine the first and second rows to bring the required data to the first row
    #"Grouped Rows" = Table.Group(#"Filled Down", {"GroupOn"}, {
        {"Date", each [Column1]{0}, type date},
        {"Description", each Text.Combine([Column2]," "), type text},
        {"Credit", each [Column3]{1}, Currency.Type},
        {"Debit", each [Column4]{1}, Currency.Type},
        {"Balance", each [Column5]{1},Currency.Type}

        }),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"GroupOn"})
in
    #"Removed Columns1"

Source

Results

[Fowmy]

@Adoraiswamy 

Select the last three columns where you see amounts, Fill Down from Transform Tab, now, Filter the Dates column to Not Equal Blank