Power Query 合并查询

自动识别路径

在 PQ 中进行合并查询时，如果数据来源是来自工作薄或者来自文件夹时，数据来源是包含路径信息的。此时更换保存路径将导致 PQ 的查询失效，以下是解决方法：

1. 利用Excel公式，读取文件所在路径，区分四种情况

• 同一个工作簿

=left(SUBSTITUTE(CELL("filename"),"[",""),Find("]",SUBSTITUTE(CELL("filename"),"[",""))-1)

• 同一文件夹下不同的工作簿，其中数据.xsx是数据源工作薄的名称

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"数据.xlsx"

• 以工作薄所在的文件夹为数据源

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-2)

• 以工作薄所在文件夹下的特定文件夹，其中数据为文件夹名称

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"数据"

2. 将写公式的单元格通过名称管理器定义为特定名称，例如“路径”
3. 在PQ中将数据源的路径替换为以下函数

Excel.CurrentWorkbook(){[Name="路径"]}[Content]{0}[Column1]

解决字段问题

当使用PQ合并多个工作表时，默认会使用第一个表的标题字段为合并条件，此时其他的表的不同字段会被错误的处理，此时可以利用公式提取全部工作表的标题字段。举例如下

1. 导入数据源之后，需要展开 Binary 类型的数据，可以添加自定义列，使用以下函数

Excel.Workbook([Content],true,true)

2. 接着删除 Binary ，留下刚刚生成的 Table类型的列，再次展开，即可看到全部的工作表，Data 为内容列
3. 此时展开 Data 列，可以看到如下公式

= Table.ExpandTableColumn(删除的其他列1, "Data", {"姓名", "语文", "数学"}, {"Data.姓名", "Data.语文", "Data.数学"})

4. 在删除的其他列1和展开的“Data”之间插入步骤，此时展开的“Data”会变成如下，请将自定义1修改回删除的其他列1

= Table.ExpandTableColumn(自定义1, "Data", {"姓名", "语文", "数学"}, {"Data.姓名", "Data.语文", "Data.数学"})

5. 自定义1中输入以下公式，提取删除的其他列1中全部工作表的字段，并重命名为字段名

= List.Distinct(List.Combine(List.Transform(删除的其他列1[Data],each Table.ColumnNames(_))))

6. 将展开的“Data”，即第3步的公式，字段的部分修改为字段名，即如下

= Table.ExpandTableColumn(删除的其他列1, "Data", 字段名)