如何开启您的Excel大数据ETL应用

Excel技巧 2021-11-25 09:14:44

Power Query作为一个数据预处理工具，相当于大数据分析系统中的ETL（Extract Transform Load）部分，负责完成数据的多数据源获取、清洗、转换、合并、加载等处理工作，帮助后期的数据模型分析获得规范、完整的数据源。

Excel Power Query应用

在Excel 2016专业增强版或更高版本中自带了Power Query数据查询组件。下图显示的是Office 2016和Office 365版本中Excel的Power Query功能。

在Excel中与Power Query有关的功能都集中在功能区“数据”选项卡中，不同版本功能菜单名称略有差别：

利用上面的工具我们可以开始数据加载，在进行查询编辑时，Excel会自动打开“Power Query编辑器”。

这里请注意，在“编辑器”操作的时候不能操作Excel窗口，包括打开新的电子表格文件，也会因为正在打开着Power Query编辑器，而看不到新打开的文件窗口。

Excel Power Query数据查询结果加载选项

-Power Query上载数据选项

Excel中数据查询编辑完成后，利用并加载这些数据的方法与Power BI中有很多区别。可以选择将数据返回到工作表、直接生成数据透视表、仅创建连接、加载到数据模型等加载方式。

· 表：将数据查询结果返回到Excel 工作表中。Excel每张工作表有1048576行，如果数据记录行高于这个值，数据会显示不完整、表格操作速度也会受到影响。

· 数据透视表、数据透视图：将查询数据直接用于生成数据透视图表，获得统计结果，数据不会在工作表中出现。

· 仅创建连接：将数据源路径、数据查询整理步骤保留，不会将数据返回到Excel。数据连接配置信息保留在当前的Excel文件中，也可以将连接配置保持为文件。

在做以上3类选择的同时，我们还以选择对话窗中的“将此数据添加到数据模型”选项，就是将数据保存到Excel中的Power Pivot数据模型中。这个选项的经典应用场景是：

· 数据源的信息量非常大，超过Excel工作表承受范围，数据模型可以帮助Excel 存储“大数据”信息，打破工作表1048576行记录限制，并对数据进行压缩。

· 数据分析需要的信息不是在一张表中，而是像数据库中的规范结构——分布在几个不同主题的表中。表与表需要建立链接关系，完成数据查询，获取更完整的基础数据。

Power Query与Power Pivot应用流程如下图所示。通过这两个组件Excel 可以突破工作表中记录行的限制，在模型中以列式存储引擎，完成大数据统计分析应用。