Forum Discussion
Expandable BOM Tree from flat BOM report
Hi
I have reached a dead end with this challenge I have.
I have a source file that looks like this:
| IDENTIFIER | PART_TITLE | DESCRIPTION | MATURITY |
| 0 | 880000-001 | FINAL PROD TOP LEVEL | IN_WORK |
| 0|1 | 880101-001 | instl 01 | IN_WORK |
| 0|1|1 | 880880-001 | FS ASSy | RELEASED |
| 0|1|1|1 | 883523-001 | FS PRIM | RELEASED |
| 0|1|1|1|1 | 883525-001 | part 91 | RELEASED |
| 0|1|1|1|1|1 | 883526-001 | part 88 | RELEASED |
| 0|1|1|1|1|1|1 | 883539-001 | part 25 | RELEASED |
| 0|1|1|1|1|1|1|1 | 880885-001 | part 46 | RELEASED |
| 0|1|1|1|1|1|1|2 | 880231-001 | part 99 | RELEASED |
| 0|1|1|1|1|1|1|3 | 880232-001 | part 22 | RELEASED |
| 0|1|2 | 880290-001 | assy 01 | RELEASED |
| 0|1|2|1 | 880291-001 | sub 001 | RELEASED |
| 0|1|2|2 | 880292-001 | sub 002 | RELEASED |
| 0|1|2|3 | 880292-002 | sub 003 | RELEASED |
| 0|1|2|4 | C00158 | sub 004 | RELEASED |
| 0|1|2|5 | C00159 | sub 005 | RELEASED |
| 0|1|2|6 | C00160 | sub 006 | RELEASED |
| 0|1|3 | 880270-001 | assy 02 | RELEASED |
Identifier at column A is unique and shows the hierarchy between the BOM leves, which could go up to 18 depth leves so far.
I have managed to create a BOM tree expandable visual that is the goal of this excercise, as you can see below, but even if this is the desire outcome, It is not useful since the user won't know the part description, and so far I haven't been succesfull in adding a column with the description of the part number.
I add screenshots for the visual I have, and also from the transformation data.
I hope you can provide some guidance. Thanks in advanced.
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
8 Replies
AdriOO Hey,
You can try below method to get desire result
Step 1: Duplicate your original query.Step 2: Split the IDENTIFIER column by delimiter | (each part of the path becomes a separate column: Level1, Level2, ...).
Step 3: Create a lookup table: extract the unique IDENTIFIER and corresponding DESCRIPTION (or PART_TITLE).
Step 4: For each level column (which contains numeric indices or part of the path), reconstruct the partial path up to that level, e.g., for level 3: 0|1|1.
Step 5: Merge this partial path with the lookup table to get the description of that node.
Step 6: Add columns with the description per level.
you can also use below dax as per newly created column.
Level1_Label =
LOOKUPVALUE(
Table[DESCRIPTION],
Table[IDENTIFIER],
CONCATENATE("0", "") -- example for level 1
)Thanks
Harish KM
Please accept this as a solution if this solves your problem and give kudos as well.
Thanks for your quick response, if I understand correctly, in this approach I would have to create a description column per level, so If my BOM has 18 depth levels so far, 18 description column would need to be created?
Hi,
The first screenshot is not at all clear. Based on the table that you have shared, show the expected result very clearly in a simple table format.
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
