美文网首页PowerBI专栏PowerQueryPower BI干货
掌握这些技巧,Power Query批量合并Excel再也不会出

掌握这些技巧,Power Query批量合并Excel再也不会出

作者: PowerBI星球 | 来源:发表于2020-09-10 23:09 被阅读0次
    PowerQuery

    ​PowerQuery的一大经典且高频应用,就是批量汇总多个Excel工作簿,很多人刚开始认识Power Query也是通过这个功能,如果你还不熟悉,也可以先翻翻以前的文章。

    虽然比较简单,但遇到不少人会在批量合并的过程中,出现各种问题,不知道怎么解决,其实PowerQuery这个简单的操作步骤中,也有不少技巧,掌握之后可以帮你更灵活高效的汇总数据。

    以批量汇总文件夹的Excel工作簿为例,

    在这个文件夹中,有北京、广州、杭州三个Excel工作簿,其中每个工作簿又包含1月、2月、3月三个sheet。

    先按常规步骤操作一遍,

    ↑ 获取数据>文件夹 ↑ 合并并转换数据

    这是大家最常用的操作方式,来看看这样做的结果是什么。

    虽然完成了一键批量合并,非常快捷,但是左边查询栏多出很多不需要的查询,看着很乱,可是如果你想删除,是不是怎么也删除不掉?

    这些查询PowerQuery执行合并操作时,默认操作过程留下的中间文件,当你点击"合并并转换数据"时,PowerQuery先根据其中一个文件作为示例,生成一个自定义函数,然后调用自定义函数,完成合并。

    自动合并,除了会留下一堆杂乱的查询无法删除,还有个问题是,如果合并结果出错(出错的概率很高),需要修改示例文件或者自定义函数的代码,但是对于初学者是比较困难的,很多人不知道如何修改。

    所以不建议使用默认的合并操作,在导入之后的预览窗口,推荐你使用“转换数据”,如下图所示:

    之后的操作步骤如下:

    ↑ 选中[Content],删除其他列

    你也可以根据需要保留部分列。

    ↑ 新建自定义列

    导入到PowerQuery中的数据默认都是类型为binary类型,需要用函数将它解析出来,对于Excel工作簿文件,输入:

    =Excel.Workbook([Content],true)

    ↑ 展开自定义列 ↑ 展开Data列 ↑ 完成合并

    你再看左边的查询栏,是不是非常干净,只有一个查询。

    这种算是手工合并数据,但相比自动合并,也就是输入一个简短的M函数,多点了几次鼠标而已,熟练操作后,整个过程不会超过一分钟。

    通过上面的描述和操作过程,涉及到两个常用的PowerQuery合并技巧:

    1, 为了避免出现杂乱的查询文件,使用“转换数据”,手动合并;

    2. 新建自定义列时,Excel.Workbook的第二个参数不要省略,当参数为true时,会自动将Excel的第一行用作标题,可以省去一个步骤。

    并且在手动合并的过程中,灵活运用,可以方便的进行各种形式的数据合并:

    3. 合并文件夹中的部分工作簿

    放入文件夹中的工作簿,可以按需要合并,而并不是必须全部合并。

    在导入后的第一步【源】中,可以筛选需要合并的工作簿,假如只需要合并北京和杭州,直接勾选就可以了

    之后的步骤不变。

    4. 合并文件夹中的部分工作表。

    工作簿中的sheet,同样可以按需合并。

    比如只合并每个工作簿中名称为“3月”的sheet,在添加并展开自定义列以后,在Name列筛选3月,

    之后的步骤不变。

    5. 合并文件夹中的某一类型数据。

    如果文件夹中的文件类型,不止一种,还可以选择按文件类型合并。

    假如文件夹中既有Excel格式,还有csv、txt格式的数据文件,如果直接全部合并会报错,那么可以按类型分别单独合并。

    依然在【源】这个步骤中,可以按数据格式来筛选。

    6. csv、txt格式的数据合并技巧。

    上面添加自定义列时用的是Excel.Workbook,是专门用来解析Excel格式的,当数据格式为csv或txt时,需要换个解析函数。

    csv、txt格式本质上属于同一种类型,都可以使用这个函数Csv.Document,为了避免中文出错,一般情况下自定义列可以直接这样写:

    =Csv.Document([Content],[Delimiter=",",  Encoding=936])

    其中Delimiter=","是对逗号分割的数据,如果你的源数据是其他符号分割,这里就改为相应的符号;中文编码一般为936,所以上面代码中用了Encoding=936来避免中文乱码的问题。

    以上就是关于PowerQuery文件夹批量汇总数据时,经常会用到的各种技巧,以及遇到问题时的解决办法,熟练掌握并灵活运用,PQ批量汇总的相关问题,基本都可以处理。

    -精彩推荐-

    PowerBI数据分析和可视化实战案例

    PowerBI时间相关的度量值,都在这里了

    PDF转Excel,这个秘籍推荐给你

    采悟 from PowerBI星球

    相关文章

      网友评论

        本文标题:掌握这些技巧,Power Query批量合并Excel再也不会出

        本文链接:https://www.haomeiwen.com/subject/ecimektx.html