cyclic reference when using Table.selectRows

[Anonymous]

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.

[Anonymous]

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.

[v-kelly-msft · Accepted solution]

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]

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

[Anonymous]

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]

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