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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Add columns with list of keywords - a cyclic reference was encountered during evaluation

 

I have two tables:

1) Anamnesis with a long free text column (Diagnosis)  for each patient.

2) Conditions of interest(Named ListOfDiagnosis): A table consisting of only those conditions I am interested in.

 

Having looked at an other solution on this forum   I tried the proposed solution on my example

 

Table.AddColumn(

                 #"Anamnesis","NewColumn",
                     each let CurrentText =
                            [Diagnosis] in Table.SelectRows
                                 (
                                      ListOfDiagnosis, each
                                      Text.Contains(CurrentText, [condition]
                                                            )
                                 )
          )

 

However when I execute this code I get:  a cyclic reference was encountered during evaluation.  I am unable to see this cyclic reference and it is driving me mad(der). 🙂

Hope for some help from the community.

Thanks

 

 

         Anamnesis

anamnese-ut.jpg

 

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

🤔 hmmm, it is working on my side. I created mock Anamnesis and ListOfDiagnosis tables (with teh relevant info and structure, although simpler than yours) and it works fine. I do not get any error. Plus looking at the code I really do not see any circular reference problem. 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
AlB
Community Champion
Community Champion

Hi @Anonymous 

Can you show the full M code for the query? I don't really see any cyclic reference in what you've written

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

Thanks for taking time to look into my problem.

But I believe it was the entire code I put in. Please see attached a picture of the code.

NB: The original example listed wrong name for one column. Correct name is in this code [Diagnose].

 

Thank you so much.

M-Code.JPG

AlB
Community Champion
Community Champion

That is the code for that step only (or so it looks). I mean the code for the full query. Open the advanced editor to copy the whole thing and paste it here. I just ran a quick test and your for that step throws no error. So the error must be somewhere else

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

let
Kilde = Excel.Workbook(File.Contents("C:\Users\bhengse\Documents\Anamnesis.xlsx"), null, true),
Anamnesis_Sheet = Kilde{[Item="Anamnesis",Kind="Sheet"]}[Data],
#"Endret type" = Table.TransformColumnTypes(Anamnesis_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Kolonner med nye navn" = Table.RenameColumns(#"Endret type",{{"Column1", "PatientID"}, {"Column2", "SSNNbr"}, {"Column3", "State"}, {"Column4", "Hospital"}, {"Column5", "Physician"}, {"Column6", "Diagnose"}}),
#"Egendefinert lagt til" = Table.AddColumn(#"Kolonner med nye navn", "MyColumn", each Table.AddColumn(#"Anamnesis","NewColumn",
each let CurrentText =
[Diagnose] in Table.SelectRows
   (
       ListOfDiagnosis, each
       Text.Contains(CurrentText, [condition]
                              )
   )

 These are the error screens and messges I get when I run the code.

 

Thanks again.

Appologize for having Norwegian settings on my Power Bi..

cyclic.JPGerror.JPG
))
in
#"Egendefinert lagt til"

AlB
Community Champion
Community Champion

🤔 hmmm, it is working on my side. I created mock Anamnesis and ListOfDiagnosis tables (with teh relevant info and structure, although simpler than yours) and it works fine. I do not get any error. Plus looking at the code I really do not see any circular reference problem. 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

 

SU18_powerbi_badge

Anonymous
Not applicable

Thank you for your time.

I am somewhat pleased that it wasn´t a stupid fault on my part.

I think I create a new example from scratch to see if that will work?

 

Thanks again.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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