Creating Relationship Within the Same Table? Power Query

[eschultz]

Hello,

I know there must be a formula or something I can use to create this relationship but not sure how. I have two tables that I have a relationship between. (Goal ID is the relationship key). When I filter on Goal 23, Animal ID 46 does not come through because it does not have a Goal ID. However, you can see that the parent ID is 45. So through this hierarchy , the Goal ID should be 23. Can someone help me create this relationship? Is it a formula or some type of table I need to create? Thank you!

Goal ID	Goal
23	Increase Speed

Animal ID	Animal	Animal Tier	Goal ID	Parent Animal
45	Dog	Adult	23
46	Puppy	Baby		45

[serpiva64 · Accepted solution]

Hi, you can obtain this result

by merging on itself you table

and then expanding 

and sorting

here is the example

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFV0lFyyU8Hko4ppTklQNrIGEgoxeoAJc2ArIDSgoJKIO2UmASigAioJzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AnimalID = _t, Animal = _t, AT = _t, GoalId = _t, #"Parent Animal" = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Parent Animal"}, Source, {"AnimalID"}, "Source", JoinKind.LeftOuter),
#"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"GoalId"}, {"GoalId.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Source",{{"AnimalID", Order.Ascending}})
in
#"Sorted Rows"

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[serpiva64 · Accepted solution]

Hi, you can obtain this result

by merging on itself you table

and then expanding 

and sorting

here is the example

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFV0lFyyU8Hko4ppTklQNrIGEgoxeoAJc2ArIDSgoJKIO2UmASigAioJzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AnimalID = _t, Animal = _t, AT = _t, GoalId = _t, #"Parent Animal" = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Parent Animal"}, Source, {"AnimalID"}, "Source", JoinKind.LeftOuter),
#"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"GoalId"}, {"GoalId.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Source",{{"AnimalID", Order.Ascending}})
in
#"Sorted Rows"

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[eschultz · Accepted solution]

Thank you!! So helpful