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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
legionkauskas
New Member

Merging Cells based on values

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!

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

View solution in original post

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!