BOM Explosion Logic Creation

Hi DemingPDCA ,

I'd recommend looking into the PATH family of DAX functions:

https://learn.microsoft.com/en-us/dax/path-function-dax 

Pete

This M-Code seems to work with your data, although there are likely more efficient methods. It makes use of GroupKind.Local to group together all the lines that descend from each Explosion_Level 1, and then adds a column to each subtable with the required string.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDNbsMgEIRfZeVzDjH/HKtIySlq1PZm5UAVFCM5EBlHVd6+kKbGxrQHWCG+md3ZpqnW1aqCcD5eD+HeGmt8q0+wc+4U3nV1XDXh/v2vESYR28HGXa7OajtAPXIoEYhQFsr77RNevL9PIJx+MeFiCh1U/2NHlqSQCxKN5Kwxi56T8WCr/GB17yO0XgaieSCUB4pEcI9l02lliyxOEKaSx2k7c26H7g5712sYV1sSk6QKSR9ifTH/a2InKrhM5U19wV4NujeqK0pmuVmeG5c5nnOkzImco/keI4GkYM9RS+TMUeaOLHeMBKLk4ehu57bI4gRhKfjfi3pKjt8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Explosion_Level = _t, Parent = _t, Component = _t, Object_Description = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Explosion_Level", Int64.Type}, {"Parent", type text}, {"Component", type text}, 
        {"Object_Description", type text}, {"Qty", Int64.Type}}),
    
    #"Group Explosions" = Table.Group(#"Changed Type","Explosion_Level",{
        {"BOM Explosion", (t)=>[
            #"Added Index"=Table.AddIndexColumn(t,"Index",1,1,Int64.Type),
            x=Table.AddColumn(#"Added Index","BOM_Explosion", (r)=>
                    [a=Table.SelectColumns(#"Added Index",{"Explosion_Level", "Parent","Component","Index"}),
                    b=Table.FirstN(a,r[Index]),
                    c=Table.SelectRows(b, each [Explosion_Level] < r[Explosion_Level] or [Index]=r[Index]),
                    d=Text.Combine(c[Component], "-"),
                    e=if r[Explosion_Level]<>0 then t{0}[Parent] & "-" & d else d][e])][x] }
    },GroupKind.Local,(x,y)=>Number.From(y=1)),
    
    #"Removed Columns" = Table.RemoveColumns(#"Group Explosions",{"Explosion_Level"}),
    #"Expanded BOM Explosion" = Table.ExpandTableColumn(#"Removed Columns", "BOM Explosion", 
        {"Explosion_Level", "Parent", "Component", "Object_Description", "Qty", "BOM_Explosion"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded BOM Explosion",{
        {"Explosion_Level", Int64.Type}, {"Parent", type text}, {"Component", type text}, 
        {"Object_Description", type text}, {"Qty", Int64.Type}, {"BOM_Explosion", type text}})
in
    #"Changed Type1"