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.
I will try to explane my problem with an example.
I have a table that contains people.
Only one person in a group is the lead.
I wont to get the id of lead for every person.
My Table
Id | GroupId | ClientId | IsLeadClient |
1 | 5 | 9 | 0 |
2 | 5 | 10 | 1 |
3 | 6 | 11 | 1 |
4 | 6 | 12 | 0 |
Something like that:
Table.AddColumn(#"My Table", "LeadClientId", each getLeadClientId([Groupid]))
getLeadClientId : (x) =>List.First(Table.Column(Table.SelectRows(#"My Table", each [GroupId] = x and [IsLeadClient] = 1),"ClientId"))
But it doesn't run: Expression.Error: A cyclic reference
Solved! Go to Solution.
would join with the table filtered for LeadClients only work?
as in below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYksgNlCK1YlWMoKKGBqACLCQMZBlBuIZwoVMYEJGEI2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, ClientId = _t, IsLeadClient = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"GroupId", Int64.Type}, {"ClientId", Int64.Type}, {"IsLeadClient", Int64.Type}}), LeadClients = Table.SelectRows(#"Changed Type", each ([IsLeadClient] = 1)), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"GroupId"},LeadClients,{"GroupId"},"Changed Type",JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ClientId"}, {"LeadClient"}) in #"Expanded Changed Type"
would join with the table filtered for LeadClients only work?
as in below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYksgNlCK1YlWMoKKGBqACLCQMZBlBuIZwoVMYEJGEI2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, ClientId = _t, IsLeadClient = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"GroupId", Int64.Type}, {"ClientId", Int64.Type}, {"IsLeadClient", Int64.Type}}), LeadClients = Table.SelectRows(#"Changed Type", each ([IsLeadClient] = 1)), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"GroupId"},LeadClients,{"GroupId"},"Changed Type",JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"ClientId"}, {"LeadClient"}) in #"Expanded Changed Type"
It doesn't work.., every records have null value.
does it work as an independent query on your machine?
how have you adjusted syntax to match your solution?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |