Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lfrigione
Helper II
Helper II

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
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

View solution in original post

3 REPLIES 3
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

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

Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.