如何在 powerBi 的高级编辑器中编写 Snowflake SELECT 语句查询

问题描述

我正在尝试将 power Bi 仪表板的数据源从 SQL 重新指向 Snowflake.

I am trying to repoint the data Source of a power Bi dashboard from SQL to Snowflake.

到目前为止，大多数表格都有效.但是，我得到一个表，在更改数据源时出现以下错误:

So far, for most of the tables worked. However, I got one table on which I got the following error when changing the data source:

Expression.Error: 'Query' 不是有效的 Snowflake 选项.有效选项为:'ConnectionTimeout、CommandTimeout、CreateNavigationProperties、Role'

此特定查询(来自 PowerBi 中的高级编辑器)包含一个简单的选择，如下所示:

This specific query ( from Advance Editor in PowerBi) contains a simple select and it looks as follows:

let
    Source =Snowflake.Databases("serverabc", "abc", [Query="SELECT DateLabel, SnapshotDate, Future, Latest#(lf)FROM Xtable#(lf)WHERE DateLabel IS NOT NULL#(lf)GROUP BY DateLabel, SnapshotDate, Future, Latest", CreateNavigationProperties=false]),
    #"Filtered Rows" = Table.SelectRows(Source, each true)
in
    #"Filtered Rows"

select 语句在 SQL 和 Snowflake 中都有效，但我在如何在 Power BI 中翻译它时也遇到了困难.

The select statement works in both SQL and Snowflake but I am having difficulties on how to translate this in Power BI as well.

提前谢谢你

推荐答案

PowerBI 六月

Snowflake(更新的连接器)

我们正在为 Snowflake 连接器添加高要求的自定义 SQL 支持.与 SQL 连接器一样，这将允许您输入 Snowflake 原生查询，然后在其之上构建报告.这适用于导入和直接查询模式.

We are adding the highly demanded Custom SQL support for the Snowflake connector. Like the SQL connector, this will let you input a Snowflake native query and then build reports on top of it. This will work with both Import and Direct Query mode.

https://powerbiblogscdn.azureedge.net/wp-content/uploads/2021/06/snowflake_update.png

Expression.Error:查询"不是有效的雪花选项.有效选项为:'ConnectionTimeout、CommandTimeout、CreateNavigationProperties、Role'

似乎之前的来源支持自定义查询".

It seems that the previous source was supporting "custom query".

Sql.Database

function (server as text, database as text, optional options as nullable record) as table

Query :用于检索数据的本机 SQL 查询.如果查询产生多个结果集，则只返回第一个.

Query : A native SQL query used to retrieve data. If the query produces multiple result sets, only the first will be returned.

---

Snowflake 的 PowerBI 连接器不支持此类选项:

---

PowerBI connector to Snowflake does not support such option:

Snowflake.Databases

function (server as text, warehouse as text, optional options as nullable record) as table

options，可以指定控制以下选项:

options, may be specified to control the following options:

• ConnectionTimeout:等待来自 Snowflake 的网络响应的秒数.
• CommandTimeout:等待查询执行的秒数.

有有效票证:雪花连接器 ->将 SQL 语句添加为可选.

可能的解决方法:

1. 在 Snowflake 中创建一个包装查询并使用它的视图

1. Create a view in Snowflake that wraps the query and use it instead

访问表格内容并执行过滤/聚合电源查询

Access the table content and perform the filtering/aggregation inPowerQuery

这篇关于如何在 powerBi 的高级编辑器中编写 Snowflake SELECT 语句查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！