Forum Discussion

lfrigione's avatar
lfrigione
Helper II
7 years ago
Solved

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

Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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"
      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

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