PowerQuery(PowerBI Desktop)查询加载到数据库----以SQL数据库为例

张文洲

我们知道PowerQuery和PowerBI Desktop可以使用M语言创建查询，并将数据加载到Excel工作表或者数据模型。

那么，我们有没有可能将查询直接加载到数据库呢？

答案是肯定的。

下面为大家提供一个使用M语言调用SQL存储过程，将查询数据写入SQL数据库的案例，希望对大家有所帮助。
1.回顾旧版Excel传统的导入外部数据界面，我们可以导入SQL数据库，并使用Select查询语句



除了使用SELECT查询语句，我们还可以调用SQL存储过程，并传递参数


但我们调用以下三种形式的存储过程，为了能顺利得到结果，我们需要按图示在存储过程首尾稍作修改。



为了将PQ查询加载到SQL数据库，我们首先了解一下SQL语句写入多行数据的语法



SQL2008及以后的版本支出VALUES多行语法结果


接下来我们看看PowerQuery中M语言调用SQL查询的主要语法



以上都是单行的示例，那么该如何将PQ中的查询表结果传递给数据库查询语句呢？
比如实现类似于下面的结果



方法1，我们可以使用XML格式，将PQ中的表转为xml二进制文件，传递给存储过程








方法2，自SQL2016起，支持JSON。我们可以将PQ查询表数据转换为JSON文本，传递给SQL查询语句。








最后，为了规避PQ查询的重复刷新，我们稍微进行一些设置。




注意：使用上面的方法，不仅可以将PQ查询表数据写入数据库，也可以对数据库数据进行查询、更新、删除等操作。
如果不是数据库管理员，请谨慎执行以上测试，实在要测试，请新建模拟数据库。




以下是我的PowerQuery部分代码效果，使用的是XML二进制传递方法。




最后是演示视频和测试文件。

PowerQuery查询加载到SQL数据库.part1.rar (1.5 MB, 下载次数: 217)

2017-8-8 23:02 上传

点击文件名下载附件

PowerQuery查询加载到SQL数据库.part2.rar (771.45 KB, 下载次数: 282)

2017-8-8 23:02 上传

点击文件名下载附件

PowerQuery查询加载到数据库.rar (1.05 MB, 下载次数: 311)

2017-8-8 23:03 上传

点击文件名下载附件

changhong8

感谢分享！

lhm667788

感谢分享！

在和弦处等你

Power BI Desktop用R导入；Excel应该2019版本会支持R

张文洲

在和弦处等你 发表于 2017-8-9 08:54
Power BI Desktop用R导入；Excel应该2019版本会支持R

M语言与其他语言的距离越近，我们获益越多。

恋旧ing

厉害，一直在学习

baigu3

实用，厉害

bright_xh

大哥还在吗，遇到这个问题不会操作，怎样将pq查询的数据上传到sqlserver数据库

bobi_wang

专家，按照您的提示做完操作后，提示This native database query isn't currently supported. 请给些建议如何解决，多谢！

代码如下：
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"KeyValue", type text}, {"NumberValue", Int64.Type}, {"DateValue", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"NumberValue", type text}, {"DateValue", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "binary", each Text.Format("<row><KeyValue>#[KeyValue]</KeyValue><NumberValue>#[NumberValue]</NumberValue><DateValue>#[DateValue]</DateValue></row>",[KeyValue=[KeyValue], NumberValue=[NumberValue], DateValue=[DateValue]])),
    XmlBinary = Text.ToBinary("<table>" & Text.Combine(#"Added Custom"[binary]) & "</table>"),
    insertrows=Value.NativeQuery(Sql.Database("tin031app","test1",[CreateNavigationProperties= true]),"InsertRows @xmlBin",[xmlBin=XmlBinary])
in
    insertrows

xiaohulao

厉害，一直在学习