M language Expression.Error: A cyclic reference

[lfrigione]

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

[Stachu · Accepted 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"

[lfrigione]

It doesn't work.., every records have null value.

[Stachu]

does it work as an independent query on your machine?
how have you adjusted syntax to match your solution?