Collapsing/manipulating PC hierarchy

[khufu]

This thread is a follow up to "BOM with multi-level parent-child hierarchy" thread found here.

With the previously proposed solution I was able to flatten the PC hierarchy and make a query that displays the data in a following way:

However, as the order goes through the order-delivery process in our ERP, the product structure is in some cases manipulated. This means that in case of some Level1 titles the product structure is "collapsed": the level1 title ceases to exist, the level2 title becomes level1 title, the level3 title becomes level2 title etc. As the product structure in offer stage might look like the one in the picture (title FBA0011381 / key 5053 and its subcomponents), the same structure in production stage consists of titles in Level 2 column and their subcomponents, and the title FBA0011381 which is kind of product name has ceased to exist. If there is a relationship in the data model from order titles to the flattened BOM level1 titles, the right order titles and their subcoponents can't be found (even if in offer stage the data model displays the BOM just right). In the production stage I should find a way to link the order titles to the flattened BOM level2 titles and their subcomponents (in some cases, some of the structures are the same as in offer stage). 

I have no problem to identify the titles/structures/rows that have changed. I can't make a relationship from order titles to flattened BOM Level2 columns, because the same Level2 components can be found on different parts of the table (only the level1 title is different on these rows) and those components would be duplicated. I think I should be able to find a way to add those collapsed product structures to the flattened BOM query table, but I'm not sure how to do this. To my understanding the path functions that are available on DAX are not supported in M. Perhaps I should manipulate the merged table before FlattenPCHierarchy function does its magic, but I haven't been able to succesfully do this.

Any help would once again be greatly appreciated! 

[Anonymous]

@khufu 

Hi! I am having the SAME exact problem with my BOM Hierarchy data. My data was set up like in your example (so I am good on that part) But when I expand and collapse the rows in the matrix table, there are blanks and my values do not match up with each row of data. I then used this DAX function to try to solve it.

Qty Value = if(ISINSCOPE(Data[COMP_2]), MAX(Data[AD_Qty]), CALCULATE (

selectedvalue ( Data[AD_Qty]),

FILTER ( ALL ( Data[LEVEL] ), Data[LEVEL] = "1" )

))

**COMP_2 meaning the second level component.

Problem is, I might be having 50+ levels of components and then this DAX function only works for the first component. I would then have to code again until I get to level 50. Any ideas on how to code this so that it always returns the right row of data?

[v-shex-msft]

Hi @khufu,

>>This means that in case of some Level1 titles the product structure is "collapsed": the level1 title ceases to exist, the level2 title becomes level1 title, the level3 title becomes level2 title etc. As the product structure in offer stage might look like the one in the picture 

Current power bi not contains 'Collapse/ Expand' feature, perhaps you can vote below idea which has the similar requirement:

Matrix +/- Collapse/ Expand Buttons

>>I can't make a relationship from order titles to flattened BOM Level2 columns, because the same Level2 components can be found on different parts of the table (only the level1 title is different on these rows) and those components would be duplicated

I don't think you can direct create relationships between normal column and hierarchy structure. You can create a hierarchy column with these different level and use this hierarchy column as row label.

>>To my understanding the path functions that are available on DAX are not supported in M. Perhaps I should manipulate the merged table before FlattenPCHierarchy function does its magic, but I haven't been able to succesfully do this.

For path function, you can take a look at below article:

Parent-Child Hierarchies

Regards,

Xiaoxin Sheng

[khufu]

Thank you for your reply!

The matrix +/- feature deals with Power BI visualizations. However, I should be able to manipulate the flattened product structure in Power Query M. So I don't think the proposed feature would really solve my problem.

Sorry if my presentation of the problem wasn't too explicit. I was talking about creating a relationship between normal data table and the table created with M query.

The proposed path-functions are DAX-functions, and I'm not sure if I can solve my problem by manipulating the data in DAX. Basicly I should be able to modify some of the table rows and merge them back to the original table, and then create a relationship between the order row/title information and product structure table with flattened PC hierarchies.

But I think my formulation of the problem wasn't too explicit. I try later to post some examples.

[khufu]

Okay, I will try to clarify the problem a bit with examples. All the following tables are data tables/query tables in M.

So we have an offer table in our ERP database, which includes rows of data that specifies what the order consists of. We have an example order that consists of product a. With the help of some really brilliant people from the previous thread I was able to construct the product structure table, that shows the whole flattened product structure. When I make a relationship from offer table title column to product structure level1 column and visualize this, I can show the whole structure of any particular offer. So far so good.

offer table (from ERP)

title

product a

product structure table (created with M function)

level1                level2                                             level3

product a

product a          subassembly a                             component a

product a          subassembly a                             component b

product a          subassembly b

product a          subassembly b                            component d

product a          component c

component e

However, when the offer is transformed to order and proceeds to production stage, the product structure is modified in our ERP (I know this may sound strange). In the order title rows any product of offer title column has been replaced by its subcomponents. So, product a has became subassembly a, subassembly b and component c.

order table (from ERP)

order title rows

subassembly a

subassembly b

component c

To be able to show the whole product structure of any particular production level order, I should be able to construct the order product structure table. In this table any level1 product would be replaced by its subassemblies and subcomponents. In my opinion the preferred option would be to construct this table in M query and append it back to the original product structure table. However, I have been unable to do this.

order product structure table (NEED HELP HERE!)

level1                                             level2

subassembly a

subassembly a                             component a

subassembly a                             component b

subassembly b

subassembly b                             component d

component c

component e

[v-shex-msft]

Hi @khufu,

>>However, when the offer is transformed to order and proceeds to production stage, the product structure is modified in our ERP (I know this may sound strange). In the order title rows any product of offer title column has been replaced by its subcomponents. 

Maybe you can try to get some suggest from the ImkeF.

@ImkeF any suggest about enable the BOM on khufu's scenario?

Regards,

Xiaoxin Sheng

[ImkeF]

Problem here is that "Component e" is missing completely at "production stage". So it's not just about a problem of a missing parent (which might be replacable by the reference to the order no), but one component is missing completely. 

I recommend to check again and correct that error in your ERP-system.

If that isn't possible, one could create a translation table that stores the data from the offer-table and use it as a lookup once you are in production phase. But that would probably require an 1:1-relationship between those.

[khufu · Accepted solution]

Thank you very much for your help!

There was an error in my example, sorry about that. If we look at the same offer/order/product structure in all the example tables, the component e should be present in all the tables. It was missing on the offer and order tables (should be in the first level), sorry! Of course in reality the data on order and offer product structure tables contain every product structure and component that we have, and the relationship with offer table and product table enable us to filter just the right product structure rows for any particular product.

I have tried to solve the problem the following way. I made a duplicate of the table 2, deleted the rows that link the product a (and other products) to its subcomponents and used this table to create a flattened order stage product structure table. This way the subcomponent a and subcomponent b became a level 1 titles. Also I had to delete some duplicated rows and make some minor adjustments. After this the relationship from order table to product structure table seems to work (via intermediary table), at least in some rather straightforward cases. I still have to verify this also with some more complex product structures.

But thank you very much for your help, and if you have any additional comments or smarter ways to achieve the desired result I'm eager to hear them. I will get back to you when I have verified if my current solution actually works!