Forum Discussion

BigMac's avatar
BigMac
Regular Visitor
4 years ago

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

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 itemChild itemUsage
ITEM-0001ITEM-00091
ITEM-0001ITEM-00071
ITEM-0001ITEM-00101
ITEM-0001ITEM-00061
ITEM-0001ITEM-00081
ITEM-0001ITEM-00051
ITEM-0001ITEM-00120,001
ITEM-0001ITEM-00031,5
ITEM-0001ITEM-001711
ITEM-0002ITEM-00111
ITEM-0002ITEM-00071
ITEM-0002ITEM-00101
ITEM-0002ITEM-00061
ITEM-0002ITEM-00081
ITEM-0002ITEM-00051
ITEM-0002ITEM-00120,001
ITEM-0002ITEM-00031,5
ITEM-0002ITEM-001711
ITEM-0003ITEM-00140,00427
ITEM-0003ITEM-00040,00245
ITEM-0003ITEM-00150,000035
ITEM-0003ITEM-00130,0005
ITEM-0004ITEM-00160,8226
ITEM-0004ITEM-00170,1775

 

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

Need Help
Tutorial Requests

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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