Forum Discussion
Collapsing/manipulating PC hierarchy
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!
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:
Regards,
Xiaoxin Sheng
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.
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
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
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.
