rolling up data without a parent / child reference table

AndrewPF,

 

Try this in Power Query. Here's the concept:

 

1. Add a column that determines if "group" is in the Name column.

2. Add a column that returns the group name if the row is a header row.

3. Use the Fill Down feature to copy the group name to each row.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Tc7NCsIwDAfwVxk9+xbCBEdh4EGk9BA0tqFdUtIN0acX2h12++XrT5wzFog/oMRhCCpbMf7kzJ1yFW68CmNtGhV+qH0OOe28yOut+B1aMVPChilLKYfAG2SITRa4r4ykC7H0JmXe884Rtifq4XZiCnFtfAgu0P+albD2rhVOxvs/",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Name = _t]
  ),
  ChangeType = Table.TransformColumnTypes(Source, {{"Name", type text}}),
  AddIsGroup = Table.AddColumn(
    ChangeType,
    "Is Group",
    each if Text.PositionOf([Name], "group") = - 1 then 0 else 1
  ),
  AddGroupName = Table.AddColumn(
    AddIsGroup,
    "Group Name",
    each if [Is Group] = 0 then null else [Name]
  ),
  ReplaceValue = Table.ReplaceValue(
    AddGroupName,
    " group",
    "",
    Replacer.ReplaceText,
    {"Group Name"}
  ),
  FillDown = Table.FillDown(ReplaceValue, {"Group Name"}),
  RemoveColumns = Table.RemoveColumns(FillDown, {"Is Group"})
in
  RemoveColumns