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

[Anonymous]

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

[AlB]

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 

[Anonymous]

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.

[AlB]

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 

[Anonymous]

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..

))
in
#"Egendefinert lagt til"

[AlB · Accepted solution]

🤔 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 

[Anonymous]

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.