Extract Data from Snowflake using User Defined Function

[sumesh2429]

Hi,

I have a User Defined Function created in Snowflake Cloud DB which accepts 4 Date Parameters .

When i pass the 4 Parameters - Basically Start date, End Date, Process Start Date & Process End date, this Function returns me data in Tabular Format . The function looks like this as below .

Select * from table(MYTESTFUNCTION(202112,202111,202203,202202))

In above, MYTESTFUNCTION is the Function name & the 4 values in Brackets are the parameters . 

As of now, the users needs to login into Snowflake Database & Run the query to extract the data in CSV File . 

They are requesting to have this data pulled in Power BI & Where they have the option to select these 4 parameters in the Report itself . 

I tried to get the function into Power BI Desktop using Snowflake connector, however i dont see the Function under Objects when i try to import it . I can only see the Views & Tables in Snowflake .

Can someone please suggest some ideas ?

[v-jingzhang · Accepted solution]

Hi @sumesh2429 

Currently Snowflake connector doesn't support to bring User Defined Functions into Power BI. I found a similar idea about this requirement as below. You can vote it up. 

Microsoft Idea - Power BI Support to call Snowflake Functions with Parameters that return Table

As User Defined Function is not supported, if your purpose is to filter data that will be imported into Power BI, you can use Power Query parameters as an alternative. In steps that filter rows with specific date columns, use date parameters to replace the fixed date values. 

You can also create a custom function in Power Query. You can define query parameters for this custom function and let other users invoke this function to query data and generate a table result. 

Using custom functions in Power Query - Power Query | Microsoft Docs

Power Query Custom Functions • My Online Training Hub

If the users are not report authors but report viewers, the Dynamic M query parameters in Power BI Desktop may be an option. It can let report viewers change parameter values at the report level. But this feature is only available for queries in DirectQuery mode and it has some limitations & considerations need to consider in advance. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[akhilduvvuru]

@v-jingzhang - Thanks for your response. We do have similar requirement.

"Currently Snowflake connector doesn't support to bring User Defined Functions into Power BI" - Is this still the same case? Snowflake connector doesn't support to bring User Defined Functions into Power BI?

Thanks!

[DonIncognito]

let
Source = Value.NativeQuery(Snowflake.Databases("<company id>.snowflakecomputing.com",#"SFWarehouse",[Role=#"SFRole"]){[Name=#"SFAnalyticsEnv"]}[Data], "select * from table(<schema>.<udf name>(<udf parameters>)", null, [EnableFolding=true])
in
Source

Thats what I use to call a snowflake UDF with parametised Snowflake connection details.