PowerQuery清洗过的数据回写到SQLServer（1）—全量更新

本文链接：https://blog.youkuaiyun.com/thinkersky123/article/details/140724391

PowerQuery数据回写到SQLServer(1) 一键全量更新

专业的工具做专业的事，Power BI只负责数据分析和可视化交互报表的制作，数据清洗交给PowerQuery并加载到数据库，便于Power BI获取到干净的数据。

在Power BI中进行数据ETL存在一些问题：

报表刷新慢，有的需要等待将十几分钟；
编辑效率低，每次修改要等几分钟刷新结果，后期运维会让人崩溃；
无法增量更新，每次都会重新计算所有数据，特别到年末会把前面的数据源重新进行ETL和计算；
过多的重复数据ETL；
清洗后的干净数据只存在于Power BI文件中，只能在数据模型上消费，没法重复利用；
多表的关系建模，既影响性能又浪费精力在表和表的关系上面；

随着项目越来越多，数据量越来越大，不建议直接在Power BI中进行数据清洗。

Power BI使用SqlServer作为数据源的好处：

避免多表的关系建模，使用SQL拿到一张干净的表，再做数据分析；
避免重复的ETL过程，一个数据源只要清洗一次，后面所有报表都可以直接使用；
刷新效率高，可以从几分钟缩减到几秒（除了非常复杂的数据建模会慢一些）；
Power BI可以直连数据库，实现实时刷新数据，比如一些实时报表可以直连实时数据库；
极大提高报表开发效率，方便后续的运维；
使用SQL查询可以减少度量值，有的报表几十上百个度量值，脑子会乱，不方便管理；
数据仓库方便保留历史数据， Power BI不需要每年都更改数据源路径；
报表的数据定时刷新只要输入一次数据库用户密码，不需要更换电脑登录密码后，所有数据源都要单独再设置用户密码；

以下介绍PowerQuery数据全量更新到SQLServer数据库的方法

let
    Source=(valueList as list) => let
        convertNull = List.Accumulate(valueList,"",(x,y)=>x&(if y is null or y="" then "null" else "'"&y&"'")&","),
        removeLastText = Text.Range(convertNull,0,Text.Length(convertNull)-1)
    in
        removeLastText
in
    Source


let
    Source = (Source as table , tableName) => let
        Data = Table.AddIndexColumn(Source, "RowIndex", 0, 1, type text),
        columnNameList = Table.ColumnNames(Source),
        sqlPrefix = "INSERT INTO "&tableName&"(["&Text.Combine(columnNameList,"],[")&"]) VALUES",
        records=Table.ToRecords(Data),
        result=  List.Accumulate(records,"",(x,y)=>x&"("&nullValue(List.RemoveLastN(Record.ToTable(y)[Value],1)) & "),"&(if Text.End(Text.From(List.LastN(Record.ToTable(y)[Value],1){0}),3)="999" then "|" else "")),
        sqlList= if Value.NativeQuery(Sql.Database("SQL", "powerbi"),"delete from "&tableName){0}[Records Affected]>-1 then Text.Split(result,"|") else "",
        insert=  List.Transform(sqlList,(x)=>Sql.Database("SQL", "powerbi",[Query=sqlPrefix & Text.Range(x,0,Text.Length(x)-1)]))
        in
        insert
in
    Source