Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

cyclic reference when using Table.selectRows

Hello Everyone,

 

I have been trying to solve this problem for a while but still have no clue why there is alway an error message about cyclic reference.

My data source is  a CSV file named "Testing" and I want to filter the column "Country" in this file with a list of country code I entered seperately by "Enter Data" in Power BI. The list of country code I entered is already converted into a list, which is called CountryCode.

I use "= Table.SelectRows(Testing, each List.Contains(CountryCode,[Country]))"

I really don't understand why there is a cyclic reference during the evaluation since the data source is independent.

If somebody has any clue about this issue, please tell me. Thank you in advanced.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    You should use the table name which is created by"Enter Date" after the function of "Table.SelectRows",rather than the one from CSV.

    See my sample below:

    For the related sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sample:

    let
    Source = Csv.Document(File.Contents("C:\Users\NCC7FE\Desktop\Sample file.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Export Country", type text}, {"Import Country", type text}, {"Product", type text}, {"Price", Int64.Type}, {"Supplier", type text}}),
    Custom1 = Table.SelectRows(Sample, each List.Contains(CountryCode,[Export Country]))
    in
    Custom1

     

    It seems to that you are referencing the query called Sample from inside the query.

     

    This is for sure one problem.

    I don't know if there are other.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    you probably used some name in the query that matches the query name. Is your query called "testing" by any chance?

    but if you put the entire script and the names given to the queries, maybe you can get more precise answers.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello, thank you very much for your reply.

      I do not use any same name.

       

      please see the script of my sample file and the list I created by Enter Data

      Sample:

      let
      Source = Csv.Document(File.Contents("C:\Users\NCC7FE\Desktop\Sample file.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Export Country", type text}, {"Import Country", type text}, {"Product", type text}, {"Price", Int64.Type}, {"Supplier", type text}}),
      Custom1 = Table.SelectRows(Sample, each List.Contains(CountryCode,[Export Country]))
      in
      Custom1

       

      List:

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gpQitWJVvIOAlO+EWDKI1QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryCode = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryCode", type text}}),
      CountryCode1 = #"Changed Type"[CountryCode]
      in
      CountryCode1

       

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You should use the table name which is created by"Enter Date" after the function of "Table.SelectRows",rather than the one from CSV.

    See my sample below:

    For the related sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Kelly,

      Thanks for your hint! I did not notice that before.

      Sorry that I am not able to download your file.

      I feel confusing about one thing.

      I want to use the list to filter in my csv file, and I need another table to be a reference.

      So I copied the country column in my csv file and created a table seperately by Enter Data as reference.

      Now it is not cyclic reference anymore, but there is a error message

      Expression.Error: The field 'ExportCountry' of the record wasn't found.
      Details:
      Country=MX

       

      My csv file: Sample

      List created by enter data: CountryCode (only contains JP,KR,MX,HU)

      Table created by enter data: Export_Country (contains the copied value of export country column from sample)

      let
    Source = Csv.Document(File.Contents("C:\Users\NCC7FE\Desktop\Sample file.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Export Country", type text}, {"Import Country", type text}, {"Product", type text}, {"Price", Int64.Type}, {"Supplier", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Export Country", "ExportCountry"}}),
    Custom1 = Table.SelectRows(Export_Country, each List.Contains(CountryCode,[ExportCountry]))
in
    Custom1

       Best Regards,

      ChiaoC