BOM Explosion Logic Creation

[DemingPDCA]

I need help creating the "BOM Explosion" Column based on the other Parent and Component field. I can't figure out if Power Query or DAX is a better way to get there. I'm open to options. 

Explosion_Level	Parent	Component	Object_Description	Qty	BOM Explosion
0		TOP	Finished Good	1	TOP
1	TOP	1234	FG Component 1	1	TOP-1234
2	1234	2456	Sub Assy 1	1	TOP-1234-2456
3	2456	3478	Sub Assy Part 1	4	TOP-1234-2456-3478
3	2456	3489	Sub Assy Part 2	4	TOP-1234-2456-3489
2	1234	2468	Component 1 Fastners	10	TOP-1234-2468
1	TOP	1235	FG Component 2	1	TOP-1235
2	1235	2345	Clean Component 2	1	TOP-1235-2345
3	2345	3597	Slightly More Finished Component 2	1	TOP-1235-2345-3597
4	3597	4897	Semi Finished Component 2	1	TOP-1235-2345-3597-4897
5	5879	5879	Raw Material Component 2	1	TOP-1235-2345-3597-4897-5879
1	TOP	1236	FG Component 3	1	TOP-1236
1	TOP	1237	FG Component 4	1	TOP-1237
1	TOP	1238	FG Component 5	1	TOP-1238
2	1238	2986	Raw Component 5	1	TOP-1238-2986
1	TOP	1239	FG Component 6	1	TOP-1239
2	1239	2546	Rough Component 6	1	TOP-1239-2546
3	2546	3987	Raw Material Component 6	1	TOP-1239-2546-3987

[BA_Pete · Accepted solution]

Hi @DemingPDCA ,

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

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

Pete

[Shubham_rai955]

Power Query Solution

1. Load your table.

2. Sort by Explosion_Level ascending.

3. Add a custom column:

    

   = Table.AddColumn(PreviousStep, "BOM_Explosion", each if [Explosion_Level] = 0 then [Component] else let parentPath = PreviousStep{[Index]-1}[BOM_Explosion] in parentPath & "-" & [Component] )

   Notes

   • Add an Index column before this step.

   • Explosion_Level must already be in correct order.

   • This builds the path step by step.

     If you want a simple DAX version (calculated column):

      

     BOM_Explosion = VAR ParentNode = CALCULATE( MAX('BOM'[BOM_Explosion]), FILTER('BOM', 'BOM'[Component] = EARLIER('BOM'[Parent])) ) RETURN IF( 'BOM'[Explosion_Level] = 0, 'BOM'[Component], ParentNode & "-" & 'BOM'[Component] )

     This gives exactly the output shown in your screenshot.

[v-prasare]

Hi @DemingPDCA,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @DemingPDCA ,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@ronrsnfld & @BA_Pete  ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[ronrsnfld · Accepted solution]

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"

[BA_Pete · Accepted solution]

Hi @DemingPDCA ,

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

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

Pete