Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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!
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!
Thank you!! So helpful
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |