使用Power Query之前一定要这样设置你的Excel

Power Query堪称神器，以极低的学习成本帮我们在Excel和Power BI中自动化很多数据处理工作。但是，稍微不注意，你制作的自动化工具可能就会埋下地雷，在下次刷新数据时爆炸。这个地雷是什么？如何排除？

1.埋雷过程

假设有以下储存在Excel中的销售数据源，我们将其导入Power Query处理成标准格式。

Excel 2016从“数据”选项卡导入，Excel 2013从"Power Query"选项卡导入，Power BI Desktop在“主页”选项卡的“获取数据”导入。

Excel 2016导入界面

在Power Query后台，只需点击“将第一行用作标题’，表格即变为规范的格式。

"将第一行用作标题”动画

设置完成后，尝试刷新，非常顺畅。时光飞逝，斗转星移，马上来到了2月，数据源变为如下明细：

我们兴冲冲的点下了刷新按钮，结果发生了以下提示：

你设置的自动化数据处理工具以失败而告终。为什么会这样？

2.地雷解析

还记得我们当初只进行了“将第一行用作标题”这一动作，但是在“应用的步骤”这里，可以看到系统自动增加了“更改的类型”这一步骤，这是什么意思呢？

点击任意一个“更改的类型”，可以看到这么一串代码：

Table.TransformColumnTypes(提升的标题,{{"1月销售表", type text}, {"Column2", type any}})

显然，系统自动为我们进行了数据格式调整。问题就出现在调整的过程：系统将“1月销售表”这列调整为文本格式，但是2月的时候，“1月销售表”这一名称并不存在，因此导致了错误。如何解决这一问题？

3.排雷方案

你可以将系统添加的“更改的类型”步骤通通删掉，这样就恢复正常，无论几月数据都不会报错。

但是，这样会非常繁琐，尤其在步骤比较多的情况下，会自动生成很多个“更改的类型”，可不可以直接源头掐断，不生成？可以的。

在Power Query后台，点击“文件-选项和设置-查询选项”，将“类型检测”勾选去掉。如此设置后再开始Power Query操作将会彻底排除此种隐患。

Excel路径，Power BI雷同

所以，最好在任何Power Query操作之前都进行如上设置，避免返工。遗憾的是目前该设置只支持当前工作簿，也就是说你有另外工作任务需要使用Power Query时要重新设置。

最后还有一个问题，更改数据类型有没有必要？有。但不是系统帮我们改，而是我们自己改。在各项步骤设置完成的最后阶段，可以统一将所有字段更改为需要的格式。这样做是为避免后期在求和等运算中出现不必要的麻烦。