indx = Table.AddIndexColumn(table0, "Indx", 0, 1),
    id_list = List.Buffer( indx[id] ),
    recs = List.Buffer(Table.ToRecords(indx)),    
fn = (rec, spot, i) => 
    [new_spot = List.PositionOf( id_list, recs{spot}[parentId]),
    new_rec = Record.AddField(rec, "Tree"& Text.From(i), (try recs{new_spot} otherwise rec)[Tree]),
    new_i = List.Accumulate({i}, 1, (state, current) => state + current) ,
    result = if new_spot = -1 then new_rec else @fn(new_rec, new_spot, new_i)]
[result],

recstree = Table.AddColumn( indx, "parentTree", (x) => fn(x, x[Indx], 0 )),

 recstree = List.Transform( recs, (x) => fn(x, x[Indx], 0 ))

let
    Источник = Xml.Tables(File.Contents("C:\...\sampleFeed.txt")){0}[shop],
    category = Источник{0}[categories]{0}[category],
    headrename = Table.RenameColumns(category,{{"Element:Text", "Tree"}, {"Attribute:id", "id"}, {"Attribute:parentId", "parentId"}}),
    listforfilter = List.Buffer(headrename[parentId]),
    tree_last = Table.SelectRows(headrename, each ([id] <> listforfilter)),
    joinL1 = Table.NestedJoin(tree_last,{"parentId"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-1" = Table.ExpandTableColumn(joinL1, "t1", {"Tree", "parentId"}, {"t1.Tree", "t1.parentId"}),
    joinL2 = Table.NestedJoin(#"treelast-1",{"t1.parentId"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-2" = Table.ExpandTableColumn(joinL2, "t1", {"Tree", "parentId"}, {"t1.Tree.1", "t1.parentId.1"}),
    joinL3 = Table.NestedJoin(#"treelast-2",{"t1.parentId.1"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-3" = Table.ExpandTableColumn(joinL3, "t1", {"Tree"}, {"t1.Tree.2"}),
    recs_temp = Table.ToRecords(Table.ReorderColumns(#"treelast-3",{"t1.Tree.2", "t1.Tree.1", "t1.Tree", "Tree", "id", "parentId", "t1.parentId", "t1.parentId.1"})),
    recs_tree = List.Transform(recs_temp, each 
        if _[t1.Tree]=null then _&[t1.Tree.2=_[Tree], Tree = null] 
        else 
            if _[t1.Tree.1]=null then _&[t1.Tree.2=_[t1.Tree], t1.Tree.1=_[Tree], t1.Tree=null, Tree = null] 
            else 
                if _[t1.Tree.2]=null then _&[t1.Tree.2=_[t1.Tree.1], t1.Tree.1=_[t1.Tree], t1.Tree=_[Tree], Tree = null] 
                else _),
    table_tree = Table.FromRecords(recs_tree),
    offer = Источник{0}[offers]{0}[offer],
    join_offer_tree = Table.NestedJoin(offer,{"categoryId"},table_tree,{"id"},"temp",JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(join_offer_tree, "temp", {"t1.Tree.2", "t1.Tree.1", "t1.Tree", "Tree"}, {"temp.t1.Tree.2", "temp.t1.Tree.1", "temp.t1.Tree", "temp.Tree"})
in
    result

Sub TextOnRowsInRange()
  'Автор: webley
'Редактор: JayBhagavan
'Сайт: http://planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=64813&TITLE_...
  
Dim cl As Range, rng As Range, rngTmp As Range
Dim strDelim$, strTmp$
Dim arr() As String
Dim i&, n&, j&, k&

   Application.ScreenUpdating = False 
    'strDelim = InputBox("Введите символ-разделитель")
    'If strDelim = "перенос" Then strDelim = Chr(10)
    ' If strDelim = "" Then End
    strDelim = Chr(10)
    ' strDelim = " "
       
    If TypeName(Selection) = "Range" Then
        Set rng = Selection
        n = rng.Rows.Count
        For i = n To 1 Step -1
            With rng(i, 1)
                strTmp = .Value & strDelim
                arr = Split(strTmp, strDelim)
                j = UBound(arr, 1) - 1
                If j > 0 Then
                    .EntireRow.Copy
                    .Offset(1).Resize(j).EntireRow.Insert shift:=xlDown ', CopyOrigin:=xlFormatFromLeftOrAbove

                    Set rngTmp = .Resize(j + 1)
                    For k = 0 To j
                        rngTmp(k + 1, 1).Value = arr(k)
                    Next k
                End If
            End With
        Next i
    End If
   Application.ScreenUpdating = True
End Sub