Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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"
It doesn't work.., every records have null value.
does it work as an independent query on your machine?
how have you adjusted syntax to match your solution?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |