Forum Discussion

Dan_TWE's avatar
Dan_TWE
Copper Contributor
May 14, 2026

A little help on text grouping delimited by nulls in a data set in power query

A have a column in power query:

null
line of text
line of text
line of text
null

 

The number of rows with lines of text can vary, but each "text block" is delimited by column entries at the top and bottom that contain 'null'. 

Ultimately, I would like to take each text block and populate a single cell with it. Probably in a new column in an excel table. Can I get a nudge in the right direction?

excel

4 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    May 17, 2026

     

    Alternatively, a simplified formula…

     

    let

        Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],

        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),

        #"Added GroupID" = Table.AddColumn(#"Added Index", "GroupID", each

            List.Count(List.Select(List.FirstN(Source[Column1], [Index] + 1), each _ = null))),

        #"Filtered Nulls" = Table.SelectRows(#"Added GroupID", each [Column1] <> null),

        #"Grouped Rows" = Table.Group(

            #"Filtered Nulls",

            {"GroupID"},

            {{"CombinedText", each Text.Combine([Column1], "#(lf)"), type text}}),

        #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"GroupID"})

    in

        #"Removed Columns"

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 16, 2026

    OliverScheurich​ 

    Nice, but not sure why you add the complexity of grouping into a column of records with nested tables. Perhaps I misunderstood the OP's problem, though I believe the code below achieves the same result. Just added a filter step at the end, removing the blank rows.

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column] = null then null else "x"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Grouped", each Text.Combine([Column], " "), type nullable text}}, GroupKind.Local),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Grouped"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Grouped] <> "")
in
    #"Filtered Rows"

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 16, 2026
    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column] = null then null else "x"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Anzahl", 
      (r)=> [a = Table.AddColumn(r, "CombinedText",each Text.Combine(r[Column], " ") ) , 
             b = Table.RemoveColumns(a, {"Column" , "Custom"}) , 
             c = Table.Distinct(b) ][c] }}, GroupKind.Local),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Custom"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "Anzahl", {"CombinedText"})
in
    #"Expanded {0}"

    The name of the table in my sample file is "Tabelle2". The M code returns the result in the green table in my sample sheet.