Merging Cells based on values

[legionkauskas]

Hi, 

I have data currently shapped as follows:

Row              Time        Comments

1                     1             [Description of work]

2                    Null         [Continued Description of Work]

3                    null         [Continued Descrition of Work]

Essentially the only data in row 2 is a continuation of the comments from row 1. 

I know in excel could just add a helper column, and have a formula if time > 0, then look at the values in rows 2 and 3 under time for null and append to the commment in row 1.  

I haven't been able to figure out how to do this in M/PowerQuery though...

Thanks in advance!

[MarcelBeug · Accepted solution]

You can fill down the Time column, group on the Time column, chosing a dummy operation for Comments (e.g. max) and then adjust the code to have the text in Comments combined, e.g. with a line feed character like in this query:

let
    Source = Table1,
    #"Filled Down" = Table.FillDown(Source,{"Time"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Time"}, {{"Comments", each Text.Combine([Comments],"#(lf)"), type text}})
in
    #"Grouped Rows"