Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Adoraiswamy
Regular Visitor

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

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

Adoraiswamy_3-1642912667507.png

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

Adoraiswamy_2-1642912641813.png

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

8 REPLIES 8
Adoraiswamy
Regular Visitor

Thanks got it.

AlexisOlson
Super User
Super User

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.

Thanks, Alexis. 

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

ronrsnfld
Super User
Super User

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

Adoraiswamy
Regular Visitor

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

AD

Adoraiswamy
Regular Visitor

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

 

Adoraiswamy_2-1642929580189.png

Trust this helps.

 

Thanks for your attempt to assist me.

AD

  • 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

ronrsnfld_0-1643071007150.png

 

Results

ronrsnfld_1-1643071057155.png

 

 

 

 

Fowmy
Super User
Super User

@Adoraiswamy 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.