Load results of SNOWFLAKE 'show' query into Power BI

[HumbeHero]

I need to load as power bi table result of Snowflake command:

show tasks

Power BI Native queries don't seem to work with 'show' queries. If I use plain select query, all works. Anyone has solution to load tasks metadata?
Power Query:

= Value.NativeQuery(Snowflake.Databases("XXXXX.XXXXX-east.privatelink.snowflakecomputing.com","QUERY_WH",[ Role="MYROLE"]){[Name="DBNAME"]}[Data], "show tasks", null, [EnableFolding=true])

 Error:

DataSource.Error: ODBC: ERROR [42601] SQL compilation error:
duplicate alias '"_"'
Details:
    DataSourceKind=Snowflake
    DataSourcePath=XXXXXX-east.privatelink.snowflakecomputing.com;QUERY_WH
    OdbcErrors=[Table]

[HumbeHero · Accepted solution]

I finally manged to get this to work. The simplest solution seems to be to create a Snowflake store procedre that returns `show tasks` result and then running native query in power bi as below:

select * from table(show_tasks_procedure_name());

[v-mdharahman]

Hi @HumbeHero,

Thanks for reaching out to the Microsoft fabric community forum.

If you're aiming to avoid any scheduled jobs or external orchestration, the manual SHOW TASKS + RESULT_SCAN(LAST_QUERY_ID()) approach is really the closest you can get to a self-contained solution within Snowflake. However, there is one important limitation "RESULT_SCAN(LAST_QUERY_ID())" only works within the same session that ran the SHOW command and Power BI does not support running multiple SQL statements or maintaining session state across steps in a native query.

This limitation is there because PowerBI runs each native query in a separate, stateless session. So even if you tried to do, it won't work, because Power BI executes only the final statement and doesn’t preserve the session where "SHOW TASKS" was run.

If you’re okay with running "SHOW TASKS" manually once a day or week, you could store the result in a table, and Power BI could refresh that on schedule. No Snowflake jobs needed just a one liner insert when you want updated data.

I would also take a moment to thank @HarishKM and @TomOs, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @HumbeHero,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[HumbeHero]

@v-mdharahman contributors on Reddit suggested a solution that might work, but I need to test it and will post the results here.

[v-mdharahman]

Hi @HumbeHero,

I wanted to check if you had the opportunity to review the information provided by the mentioned contributors on reddit. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @HumbeHero,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[TomOs]

To work around this, I recommend querying the relevant metadata via Snowflake's INFORMATION_SCHEMA views instead of using SHOW. For example, instead of SHOW TASKS, you can run a query like SELECT * FROM INFORMATION_SCHEMA.TASKS; to get the same information.

Once you’ve written your custom query, you can use Power BI’s Advanced Editor to enter it manually and pull the metadata into your reports. Make sure to set up automatic data refreshes in Power BI so the data remains up to date.

[HumbeHero]

@TomOs there is no TASKS view in INFORMATION_SCHEMA.

[TomOs]

The workaround I’ve used is wrapping the SHOW TASKS command with TABLE(RESULT_SCAN(LAST_QUERY_ID())) inside a stored procedure or using a scriptable tool.

[HumbeHero]

Thank you,

I am aware of that, but I am trying to avoid running any jobs for this if possible.

[HumbeHero]

@HarishKM Sorry, I don't understand how will this solve my issue? I don't have problem connecting to Snowlake or running query.

I have a problem running a very specific query type.

[HarishKM]

@HumbeHero Hey,
I will recommend to follow this blog post - Connect to Snowflake with Power BI - Power BI | Microsoft Learn

Kindly refer above and hopefully this will solve your issue.

BR

HarishM