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

Cyclic reference error

I found, so I though, a solution to a timesheet problem (to display timesheets with no data), but it uses "M code", something I'm totally unfamiliar with.  I duly imported the code and, after several attempts, managed to get it syntax error free . . . . but, when I tried to save it I received the cyclic reference error message.

 

The code is as follows :

let

   Source = OData.Feed("https://mitiegrp.sharepoint.com/sites/pwa/_api/ProjectData/", null, [Implementation="2.0"]),

   Resources_table = Source{[Name="Resources",Signature="table"]}[Data] ,

   Filter_for_Work_Resources_who_are_Users = Table.SelectRows(Resources_table, each ([ResourceType] = 2) and ([ResourceIsActive] = 1) and ([ResourceNTAccount] <> null) and ([ResourceEmailAddress] <> null)),

   Get_core_fields = Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,({Resources[ResourceId], Resources[ResourceCreatedDate], Resources[ResourceEmailAddress], Resources[ResourceName], Resources[TeamName], Resources[ResourceDepartments]}))

in

Get_core_fields

 

Please can someone point out the error of my ways, and, if you have time, explain the how, what and why it's doing what it's doing.  Also, is there some good reference material out there that will help me better understand how it works ?

 

Thanks in advance

Fred

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Cyclic reference error

Hi @MitieFred,

 

AFAIK, the second parameter of 'Table.SelectColumns' function is a list of string values.  But the parameter which you provide seems like inputted a list with multiple list values in it.

 

Maybe you can try to use following formula to replace original one or add list index to return specific row from that list.

Get_core_fields = Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,{"ResourceId", "ResourceCreatedDate", "ResourceEmailAddress", "ResourceName", "TeamName", "ResourceDepartments"})
Get_core_fields = Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,{Resources[ResourceId]{0}, Resources[ResourceCreatedDate]{0}, Resources[ResourceEmailAddress]{0}, Resources[ResourceName]{0}, Resources[TeamName]{0}, Resources[ResourceDepartments]{0}})

BTW, can you please provide more info about 'resource'? I'm not so clear for it.


If it is reference from current query table, you will faced cycle reference issue when you use the reference query table to calculate with current query table.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
6 REPLIES 6
NickNg278 Senior Member
Senior Member

Re: Cyclic reference error

@MitieFred,

Have you tried to clear your cache in Options? Also, do you know at which step the error was produced?

Highlighted
Community Support Team
Community Support Team

Re: Cyclic reference error

Hi @MitieFred,

 

AFAIK, the second parameter of 'Table.SelectColumns' function is a list of string values.  But the parameter which you provide seems like inputted a list with multiple list values in it.

 

Maybe you can try to use following formula to replace original one or add list index to return specific row from that list.

Get_core_fields = Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,{"ResourceId", "ResourceCreatedDate", "ResourceEmailAddress", "ResourceName", "TeamName", "ResourceDepartments"})
Get_core_fields = Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,{Resources[ResourceId]{0}, Resources[ResourceCreatedDate]{0}, Resources[ResourceEmailAddress]{0}, Resources[ResourceName]{0}, Resources[TeamName]{0}, Resources[ResourceDepartments]{0}})

BTW, can you please provide more info about 'resource'? I'm not so clear for it.


If it is reference from current query table, you will faced cycle reference issue when you use the reference query table to calculate with current query table.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
MitieFred Regular Visitor
Regular Visitor

Re: Cyclic reference error

Hi @NickNg278,

 

Ummmm, I've been using Power BI for a whole 2 months, starting from nothing with no training apart from what I can find on message boards and the Community site.

 

How would I clear the cache ?

 

As to where, the error occurs, I've entered the formula, it says there are no syntax errors, so I click Done and it then tells me that there's a cyclic reference error, and displays the following code

 

= Table.SelectColumns(Filter_for_Work_Resources_who_are_Users,({Resources[ResourceId], Resources[ResourceCreatedDate], Resources[ResourceEmailAddress], Resources[ResourceName], Resources[TeamName], Resources[ResourceDepartments]}))

 

Thanks though for the cache hint, and, if I can find what to do, I'll try it.

 

Rgds

Fred

SJim Frequent Visitor
Frequent Visitor

Re: Cyclic reference error

To clear the cache in Power BI Desktop:  File > Options and Settings > Options > Data Load > Clear Cache

 

Regards, 

Jim

MitieFred Regular Visitor
Regular Visitor

Re: Cyclic reference error

Hi @v-shex-msft

 

Thankyou, the first option worked and gave me a tabe with the named columns in it.  I also tried the second option, but that returned the cyclic reference error.

 

As to Resources, it is a table in MS Project, containing rows for each resource name in the enterprise resource table.

 

Thanks again for your help.

 

One final (for now) question, is there any good reference material that will assist my coding ?

 

Rgds

Fred

MitieFred Regular Visitor
Regular Visitor

Re: Cyclic reference error

Hi @SJim

 

Thanks for that, got it and done it.  Useful for future reference

 

Regards

Fred