cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lfrigione Regular Visitor
Regular Visitor

M language Expression.Error: A cyclic reference

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 

IdGroupIdClientIdIsLeadClient
1590
25101
36111
46120

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: M language Expression.Error: A cyclic reference

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"
3 REPLIES 3
Super User
Super User

Re: M language Expression.Error: A cyclic reference

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 Regular Visitor
Regular Visitor

Re: M language Expression.Error: A cyclic reference

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

Super User
Super User

Re: M language Expression.Error: A cyclic reference

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