Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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.
Where does (USAGE: 0,0006523125) come from? What kind of solution are you looking for? (A query, a measure, or what?)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |