使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

JaryYuan
JaryYuan
JaryYuan
40
文章
0
评论
2020-09-1313:09:00 评论 2,721 801字
摘要

在前面我们已经介绍过 Excel.Workbook() 函数,该函数能将 Excel 中的智能表格、命名区域和打印区域识别加载到 Power Query 编辑器。可惜的是无法将 Excel 的 sheet(工作表)识别进来。因此通常需要使用 「 Ctrl + T 」或者设置成打印区域。

在前面我们已经介绍过 Excel.Workbook() 函数,该函数能将 Excel 中的智能表格、命名区域和打印区域识别加载到 Power Query 编辑器。可惜的是无法将 Excel 的 sheet(工作表)识别进来。因此通常需要使用 「 Ctrl + T 」或者设置成打印区域。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

因此合并 Excel 文件时,一般选择从外部合并,并且需要同时使用「从文件夹」及 Excel.Workbook() 函数

一、从文件夹导入

在 Source Files 文件夹下面有以下文件,我们需要合并其中 Excel 格式的文件

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

从新建空白 Excel 文件开始合并

  • 新建查询 > 获取数据 > 来自文件 > 从文件夹
  • 导航到目标文件夹,点击确定 > 转换数据

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

Power Query 将文件夹中的文件都加载进来了。其中也包含了 html、txt 等格式的文件。我们需要通过筛选去掉这部分文件。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

这时我们可以点击 Content 列的合并文件按钮进行合并,也可使用 Excel.Workbook 函数。

二、Excel.Workbook()函数

在 Power Query 中添加列,并使用函数提取 Excel文件 。

  • 点击添加列 > 自定义列
  • 在弹出的对话框中输入公式:= Excel.Workbook([Content])

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

自定义列中包含了 Excel 所有的对象,包括智能表格、命名区域和打印区域,还有就是每一个工作表本身。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

点击自定义右上角双箭头图标的展开按钮,展开以后就能看到这些对象的明细信息。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

筛选去除 Named Range 工作簿的内容。同时为了避免数据重复导入,还需要将非工作表类型的数据通过筛选去除。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

最后展开 Data 列,并提升标题,更改数据类型,完成数据合并。

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

使用 M 函数 Excel.Workbook 更高效的合并 Excel 文件

End.爱数据网专栏作者:JaryYuan专栏名称:Power BI 自动化与可视化专栏简介:Excel、Power BI、Python等学习交流园地。Excel黑科技挖掘,Python小技巧。致力于办公自动化、职场效率提升、数据分析及可视化设计个人公众号:JaryYuan

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: