Advanced Query Parameter Expression.Error

[Chas2002]

Hi Everyone,

I have a large db so I need to limit the initial load.  I researched and found paramaters and advanced queries, which I'm testing with a simple Start Date and End Date input.

My setup:

Source Database is callrecord, table is callrecord, sys_date is Date Type

1. Have a start_date paramater, Date Type

2. Have an end-date paramater, Date Type,

3. Have the below Advanced query in place, it passes with no syntax errors, but when I select done I get: "Expression.Error: A cyclic reference was encountered during evaluation."

What am I doing wrong?

The query:

let
    Source = Odbc.DataSource("database=Z:\IntelDriven\db\callrecord.db;dsn=SQLite3 Datasource", "SELECT * FROM CallRecord WHERE sys_date => ('" & Start_Date & "') AND sys_date =< ('" & End_Date & "')")
    
in
    CallRecord

The image:

Thanks,

Chas

[ianca42]

Hi Chas,

this look like a simply fix.

Try:

let
    Source = Odbc.DataSource("database=Z:\IntelDriven\db\callrecord.db;dsn=SQLite3 Datasource", "SELECT * FROM CallRecord WHERE sys_date => ('" & Start_Date & "') AND sys_date =< ('" & End_Date & "')")
    
in
    Source

[Chas2002]

ianca42 - Thanks for your help, but still getting error: "Expression.Error: A cyclic reference was encountered during evaluation."

Chas

[ianca42]

Let me explain further.

Looking at the Image I see that the Query in question is named CallRecord. When you write CallRecord in any other Query inside the PBIX it will call it. If you change the reference from "in CallRecord" to "in Source", it should work.

[Chas2002]

Yes, I removed CallRecord and replaced it with Source.  Still getting the cyclic error...

[v-jiascu-msft]

Hi @Chas2002,

Seems the parameters from Power BI can't be added in the SQL statements. Please vote up this idea.

Best Regards,

Dale

[Chas2002]

Voted.