Setting snowflake session variable with powerbi

Question

I have parameterized views created in snowflake. I need to set parameters to query the parameterized views. Can you please help to set snowflake session variable through powerbi before running select query on view.

Set dat_parameter='2022-01-01'
Select * from parameterized_view

Power bi is not accepting set statement

Answer 1

By setting enablefolding=false as shown below in power bi and setting multi+statement_count = 0 in snowflake. I am able to extract the data from parameterized view in snowflake.

power bi:

= Value.NativeQuery(Snowflake.Databases("XXXXXXXXX.snowflakecomputing.com","compute_wh"){[Name="TESTDB"]}[Data], 
"set abc=60001;select * from testdb.public.param_view", null, [EnableFolding=false])

Snowflake:

alter account set MULTI_STATEMENT_COUNT = 0;

parameterized view creation:

create or replace view param_view AS
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER where C_CUSTKEY = $abc;