Expandable BOM Tree from flat BOM report

Thanks Ashish_Mathur

, I think that if you click on the screenshot it will zoom in.

I tried to add attachments but couldn't find where.

maybe this screenshot will be more clear.

and this is what I did to create that matrix, the matrix behavior as expandable tree is the desired outcome, but is missing to show to the user the description of the part number so they are able to navigate the visual knowing what installation or assembly they are clicking in.

this is the code from my advanced editor:

let
Source = Excel.Workbook(File.Contents("C:\Users\a.a\Desktop\test 2.0\Public_A_EBOM.xlsm"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Level", each Text.Length([IDENTIFIER])- Text.Length(Text.Replace([IDENTIFIER], "|", ""))+1),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Path", each let
// This creates a list of all the parent titles for the current row
ParentTitles = List.Select(
List.Transform(
{1..[Level]-1},
(parentLevel) =>
try
Table.Last(
Table.SelectRows(
#"Added Index", // <-- IMPORTANT: This name must match your previous step
(searchRow) => searchRow[Index] < [Index] and searchRow[Level] = parentLevel
)
)[PART_TITLE]
otherwise
null
),
each _ <> null
),

// This joins the parent titles and the current part's title together with a separator
FullPath = Text.Combine(ParentTitles & {[PART_TITLE]}, " | ")
in
FullPath),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Path", "Path - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Path - Copy", "Level-"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Level-", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level-.1", "Level-.2", "Level-.3", "Level-.4", "Level-.5", "Level-.6", "Level-.7", "Level-.8", "Level-.9", "Level-.10"})
in
#"Split Column by Delimiter"

Hi AdriOO ,

You want each part number (PART_TITLE) in your expandable tree to also show its DESCRIPTION  ideally right next to each part name.

To preserve the tree behavior and include DESCRIPTION per level is to modify your Path to include both PART_TITLE and DESCRIPTION. Right now, your Path column only includes PART_TITLE, like "880000-001 | 880101-001 | 880270-001" Instead, you want to concatenate the part title and description at each level like.

"880000-001 - FINAL PROD TOP LEVEL | 880101-001 - instl 01 | 880270-001 - assy 02".

Please check the below updated  M code.

let
Source = Excel.Workbook(File.Contents("C:\Users\a.a\Desktop\test 2.0\Public_A_EBOM.xlsm"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Level", each Text.Length([IDENTIFIER])- Text.Length(Text.Replace([IDENTIFIER], "|", ""))+1),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Path", each
let
CurrentIndex = [Index],
CurrentLevel = [Level],
ParentTitles = List.Select(
List.Transform(
{1..CurrentLevel - 1},
(parentLevel) =>
try
let
parentRow = Table.Last(
Table.SelectRows(#"Added Index", each [Index] < CurrentIndex and [Level] = parentLevel)
)
in
parentRow[PART_TITLE] & " - " & parentRow[DESCRIPTION]
otherwise null
),
each _ <> null
),
FullPath = Text.Combine(ParentTitles & {[PART_TITLE] & " - " & [DESCRIPTION]}, " | ")
in
FullPath)
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Path", "Path - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Path - Copy", "Level-"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Level-", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Level-.1", "Level-.2", ..., "Level-.18"}) 

in
#"Split Column by Delimiter"

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh