Inheritance of Values

[CARL12345]

Hi everyone, I am facing a big problem which I think is very easy to solve for most of you.

I have a list that shows a bom of my machine. The first column shows the hierarchy of the bom, the second the name of the material and the third the current state of the material for processing with the assembly. The current state comes from another table and is linked to the bom list by material name. Now I have the problem that I want to inherit the actual state from hierarchy 2 material name to hierarchy 3 material A1 and A2. Similarly, I want to inherit the actual status from level two for material A to level four for material A2.2

 

At the end the result shoud look like this : I tried it with a nested join over an index column and looking if the value has changed from the value from the last row. Unfortunetaly the bom could have up to six levels and I got confused after joining ang joining and joining. Does somebody have an idea how I could get to my final result. Thanks in adavnce. 

[v-csrikanth]

Hi @CARL12345 
As I wanted to follow up on your response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth

[v-csrikanth]

Hi @CARL12345 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Thanks 
Cheri Srikanth

[v-csrikanth]

Hi @CARL12345 
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.
Cheri Srikanth

[v-csrikanth · Accepted solution]

Hi @CARL12345 
As per my understanding your scenario i can suggest the below steps to that meets your requirement.

Here is step-by-step  as below:

1. Identify Parent-Child Relationship:
   Create a new column that identifies the parent material based on the hierarchy structure.

2. Sort Data by Hierarchy and Material Name:
   Sort the data by the hierarchy level and the material name to ensure correct propagation.

3. Create a Recursive or Self-Join:
   Use a self-join to bring the parent material's status to its child materials.

4. Fill Down the Status:
   Use the "fill down" operation in Power Query to propagate the status from the parent to its children.

5. Conditional Inheritance:
   For each child, if the status is missing, inherit the status from the closest available parent.

PQ code:
// Step 1: Add Parent Column
= Table.AddColumn(#"PreviousStep", "Parent", each Text.BeforeDelimiter([Materialname], "."), type text)

// Step 2: Self Join to Get Parent Status
= Table.Join(#"PreviousStep", "Parent", #"PreviousStep", "Materialname", "ParentTable")

// Step 3: Inherit Status
= Table.AddColumn(#"JoinedTable", "InheritedStatus", each if [Actual S] = null then [ParentTable.Actual S] else [Actual S])

// Step 4: Remove Unnecessary Columns
= Table.RemoveColumns(#"InheritedStatus", {"ParentTable.Actual S", "Parent"})

If you found the above information helpful, we kindly request you to give us a Kudos and mark the response as the Accepted as Solution.

Thank you,
Cheri Srikanth

[lbendlin]

Power BI is a reporting tool. It is not a material management tool.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...