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 t...
excel
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.