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
Stachu Super Contributor
Super Contributor

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

3 REPLIES 3
Stachu Super Contributor
Super Contributor

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
lfrigione Regular Visitor
Regular Visitor

Re: M language Expression.Error: A cyclic reference

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

Stachu Super Contributor
Super Contributor

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 146 members 2,402 guests
Please welcome our newest community members: