用Power Query轻松导入数据

Power Query是微软新推出的软件,为Excel增加了数据发现以及数据导入的功能,结合Power Pivot,使得自助式商务智能轻松无比,以前必须借助重量级的数据库导入导出软件才能完成的工作,现在在Excel里面便能轻松搞定。这里我们先体验一下数据导入功能。

这里有一个Excel文件,包含了上证50每周开盘、最高、最低、收盘等数据:

数据看似整齐,其实有许多地方需要预处理:无用的行、无用的列、列标题有多余的空格、列数据类型未指定等。当然,在Excel中可以手动处理一切,不过如果数据更新以后还需要再来一次,实在不怎么高明。下面让我们看看如果通过Power Query把这一步步操作用计算机语言刻画,然后把操作变成计算的过程吧。

首先打开Excel,选择Power Query、From File、From Excel,选择前面的上证50数据,然后依次删除前三行、把第一行变成抬头(原先第4行)、删除一行(原先第5行)、变更列数据类型(时间列为date,其他都是number)。至此,数据已经是比较整齐有用的了。

由于股市遇到节假日会休市,所以需要把时间列替换成那周的周一,方便之后的数据归并。做法是使用Date.StartOfWeek函数计算出新的列,然后替换掉老的时间列即可。最后可以整理一下列名、修改顺序、更改数据类型等,最终得到如下干净漂亮的数据:

当点击Done之后,清理完的数据便会导入到Excel中。如果上证50的Excel文件更新以后,只要按Refresh即可。

看起来很简单,实际上Power Query的能力远不止这些。你只要告诉Power Query要做什么,具体怎么做与怎么优化,Power Query会自动判断。比如,你可以尝试从SQL Server导入数据并用Profiler监视,可以看到过滤筛选等已经被下推到数据源了,效率绝对有保证。所以,Power Query是一种陈述式数据集成工具。

这一切的背后,都是M语言的功劳。需要指出的是,M曾经是微软代号Oslo项目使用的建模语言,可以让用户集中于领域模型开发,而不必关心如何执行与优化,与Sql Server Integration Service、BizTalk等关心细节工作流的模型迥异,可惜Oslo这个“百万美元项目”从未修成正果。此M并非彼M,只是碰巧采用同样的代号而已,为Power Query提供关系数据建模。以下就是所有步骤背后的源代码:

let
    Source = Excel.Workbook(File.Contents("C:ProjectTrendsAnalytics股市StockData.xlsx")),
    Sheet1 = Source{[Name="Sheet1"]}[Data],
    RemovedFirstRows = Table.Skip(Sheet1,3),
    FirstRowAsHeader = Table.PromoteHeaders(RemovedFirstRows),
    RemovedFirstRows1 = Table.Skip(FirstRowAsHeader,1),
    RemovedColumns = Table.RemoveColumns(RemovedFirstRows1,{"   MA.MA5   ", "   MA.MA10  ", "   MA.MA20  ", "   MA.MA60  ", "  VOL.VOLUME", "  VOL.MA5   ", "  VOL.MA10  ", " MACD.DIF   ", " MACD.DEA   ", " MACD.MACD  ", "    最高", "    最低"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"      时间", type date}, {"    开盘", type number}, {"    收盘", type number}, {"         成交量", type number}}),
    InsertedCustom = Table.AddColumn(ChangedType, "Custom", each Date.StartOfWeek([#"      时间"])),
    RemovedColumns1 = Table.RemoveColumns(InsertedCustom,{"      时间"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns1,{{"Custom", "时间"}, {"         成交量", "成交量"}, {"    收盘", "收盘"}}),
    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"时间", "    开盘", "收盘", "成交量"}),
    ChangedType1 = Table.TransformColumnTypes(ReorderedColumns,{{"时间", type datetime}})
in
    ChangedType1

点击Query Editor中类似脚本的图标便可以看到M源代码,而Query Editor只是M源代码的图新界面而已。如果你的Power Query默认不显示这个图标,需要手动在Power Query的Option中勾选Enabled Advanced Query Editing才行。

管中窥豹,至此我们介绍了一个完整的Power Query数据导入的功能,能够在Excel中轻松完成,真是很享受的。想来微软还是有进取精神的,Power Pivot革了Sql Server Analytic Service的命,而Power Query绝对有让Sql Server Integration Service不寒而栗的资本。摆弄Visual Studio的BI开发者们已经不再是高帅富,在Excel里面拖拖拽拽的反而高端洋气上档次,各位也要与时俱进 🙂

 



张 琪