Inheritance of Values

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