Production structures - look for latest parent item while calculating usage for each level

[BigMac]

Greetings,

I have a table with given items divided from child to parent with usage per level. The diffiiculty is that child position is used in parent position, which becomes child position in another parent position. What is more the given child item I am looking for may be used in more than one latests parent items.

As in the table below I would like to find a top parent level for ITEM-0017 to be shoed as a result like that:

ITEM-0017 -> ITEM-0001 (USAGE: 0,0006523125)

ITEM-0017 -> ITEM-0002 (USAGE: 0,0006523125)

Here is the table I have.

Parent item	Child item	Usage
ITEM-0001	ITEM-0009	1
ITEM-0001	ITEM-0007	1
ITEM-0001	ITEM-0010	1
ITEM-0001	ITEM-0006	1
ITEM-0001	ITEM-0008	1
ITEM-0001	ITEM-0005	1
ITEM-0001	ITEM-0012	0,001
ITEM-0001	ITEM-0003	1,5
ITEM-0001	ITEM-0017	11
ITEM-0002	ITEM-0011	1
ITEM-0002	ITEM-0007	1
ITEM-0002	ITEM-0010	1
ITEM-0002	ITEM-0006	1
ITEM-0002	ITEM-0008	1
ITEM-0002	ITEM-0005	1
ITEM-0002	ITEM-0012	0,001
ITEM-0002	ITEM-0003	1,5
ITEM-0002	ITEM-0017	11
ITEM-0003	ITEM-0014	0,00427
ITEM-0003	ITEM-0004	0,00245
ITEM-0003	ITEM-0015	0,000035
ITEM-0003	ITEM-0013	0,0005
ITEM-0004	ITEM-0016	0,8226
ITEM-0004	ITEM-0017	0,1775

Is there a way to achive such a result while using POWER QUERY or POWER PIVOT or combined?

[v-kkf-msft]

Hi @BigMac ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @BigMac ,

If you want to find the top parent level, please create the following custom columns.

PATH =
let
mytable=#"Changed Type",p="Parent item",c="Child item" 
  in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")

Top Parent Level = Text.Start([PATH],9)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Where does (USAGE: 0,0006523125) come from? What kind of solution are you looking for? (A query, a measure, or what?)