从Excel到PQ：动态提取文件夹下工作簿中的指定数据

文件夹内有三个文件夹，每个文件夹下面有不同的工作簿，每个工作簿中都有一张销售工作表，存放每个分公司的销售每个季度的销售数据。如下图所示：

要求：将上面的每个工作表中的每个季度的销售数量与销售合计的数据提取出来。结果如下图所示。

针对上面的问题，基本的公式与函数基本上处理不了，那么Power Query来解决，是最好的选择。具体思路如下：Step-01：单点【数据】选项卡，单击【获取数据】-【来自文件】-【从文件夹】。如下图所示：

Step-02：在弹出的对话框中选择文件夹的路径，然后单击【确定】，在弹出的界面中选择【转换数据】。如下图所示：

Step-03：然后单击Power Query编辑栏，将公式修改为：（向右划动即可查看）

= Table.SelectColumns(Table.AddColumn(Folder.Files("C:\Users\81532\Desktop\合并素材"),"a",each Excel.Workbook([Content],true)),{"Name","a"})

从二进制文件中将数据解析出来并删除掉无用的列。得到如下图所示的数据：

Step-04：然后使用多层深化的方法将“合计”的“销售数量”与“销售金额”深化出来，构建一个Record，添加一个步骤，命名为“深化”，然后写入公式：（向右划动即可查看）

= Table.TransformColumns(源,{"a",each[数量=_{0}[Data]{[分公司="合计"]}[销售数量],金额=_{0}[Data]{[分公司="合计"]}[销售金额]]})

得到的数据如下图所示：

Step-05：然后将Record展开，重命名标题名称，再将“Name”字段里的“.xlsx”替换成空白，使用公式：（向右划动即可查看）

= Table.ReplaceValue(Table.RenameColumns(Table.ExpandRecordColumn(深化,"a",{"数量","金额"}),{"Name","季度"}),".xlsx","",Replacer.ReplaceText,{"季度"})

得到数据如下：

最后上载至工作表中即可。完成的步骤与M公式如下图所示：（向右划动即可查看）

let源 = Table.SelectColumns(Table.AddColumn(Folder.Files("C:\Users\81532\Desktop\合并素材"),"a",eachExcel.Workbook([Content],true)),{"Name","a"}),深化 = Table.TransformColumns(源,{"a",each [数量=_{0}[Data]{[分公司="合计"]}[销售数量],金额=_{0}[Data]{[分公司="合计"]}[销售金额]]}),转化 = Table.ReplaceValue(Table.RenameColumns(Table.ExpandRecordColumn(深化,"a",{"数量","金额"}),{"Name","季度"}),".xlsx","",Replacer.ReplaceText,{"季度"})in转化