什么是POWER QUERY？WhatPower Query是一款搭载在Excel（或PowerBI）上的数据组件，可以帮你导入、清洗、上载数据。当我们在进行数据分析时，可能有80%的时间都是用来清洗数据，如果使用power query去处理数据则会非常高效。为什么要用POWER QUERY？Why这么多数据处理工具，为什么要用Power Query？1.海纳百川就像大海可以容纳上千条江河之水一样，Power Query也可以连接数百个数据源，包括但不仅限于：文
件：Excel/TXT/CSV/PDF/JASON/XML/文件夹 数据库：Access/MySQL/Oracle网络云：Azure/Salesforce其
他：Web/Python script2.一劳永逸 所有数据处理步骤都会记录，编辑好的查询只需要点击刷新，就可以自动更新数据，而且每个步骤都可以追溯再修改，这样极大的地方便了数据工作人员。3.显而易见图形化界面，易看易学。深入学习完Power Query的时间，你VBA可能才刚刚入门。这是网络上流传的一张工具掌握时间与收益的关系图。总结来说学习Power Query就是低付出高回报，多出来的时间就用来提升自己吧！POWER QUERY怎么用？How假设你是一名数据分析师，领导给你下达了一个紧急任务，让你汇总2018-2021年的销售数据；分析每年利润变化； 如果你不会powerquery，你会怎么做？手动复制粘贴2018-2021年的数据？汇总之后再清理数据 函数提取公司名和ID→成本列文本转数字→计算利润现在有4年的数据，我就要重复操作4次（实际工作中可能只会更多），可能你会说可以用函数做模板，确实数据少可以用函数，但是数据多了函数真的不要太卡，而且复制原表的数据也要手工完成，真的就是钱少事多。话不多说，来看powerquery怎么做吧。案例：快速合并工作簿Action1.导入数据数据选项卡→获取数据→来自文件→从文件夹→选择文件夹的路径，点击打开（注意，2010和2013版本EXCEL需要单独下载PowerQuery的插件才能使用）选择合并并转换数据点击下图右上角的文件夹图标或者表格图标都可以，然后确定。这样就进入到了powerquery编辑器，是不是很熟悉，和Excel很像。左边查询目录右边查询设置上方命令栏中间数据预览区（一次1000行）2.编辑数据筛选除去ID为XXXXX的赠品数据，这个就是和excel操作一样，点击筛选器勾选掉就行。拆分拆分客户ID和客户名，如果用excel处理，可以直接用分列功能，powerquery也有类似功能，主页选项卡→拆分列，然后选择需要分隔的方式，案例中客户ID和公司名都是用 -连接的，所以选择按分隔符，然后分隔符自定义为 - 就可以了。转换格式点击列名左边的小图标就可以选择相应的格式。不同列值加减选中销售额列再选中成本列（注意顺序不能错）→添加列选项卡→标准→减→自动生成新的一列，值是两列的差值。双击列名就可以重命名。操作完后，发现右边的查询设置记录了我们所有的操作，操作步骤可以点击红×删除，也可以在任意步骤之间再插入新的步骤，是不是灵活性很大。3.保存并上载数据处理好之后就可以选择关闭并上载至，选择表（按照自己的需求选择，这次需求是合并数据，所以选择表）筛选数据后，发现四个表的内容全部都合并在一起了，也按照要求完成了数据清洗，Perfect！4.数据更新任意表区域内，右键点击刷新，所有文件夹内的表格就会自动合并过来，不管你放了几年的数据，都可以按照你的要求清洗再合并。如果学会了话，示例文件下方自取，动手试一试吧！https://pan.baidu.com/s/1eioyQKG1nuzkrOQllhUBuA?pwd=cz6x 提取码：cz6x简妮的Office指南/职场提升/持续精进Excel/PowerQuery/VBA/PowerBI关注我，一起成长吧！

**附件下载地址：**https://jiaopengzi.com/2602.html一、背景在我们使用 Power BI 或者 Power Pivot 做数据分析模型时，使用 Power Query 做数导入，经常会遇到如下场景：同一文件夹下多个表格的合并。同一个 Workbook 中有多个相同的字段的 WorkSheet 。Xlsx, Xls, Csv 并存。字段的类型更改每次都要打开 Power Query 去修改。字段增加和删除每次都要打开 Power Query 去修改。字段重命名后要修改类型等多处操作。合并的表格表头有多行不需要的信息，影响数据的读取。有多个项目文件夹都是合并表格，要重复写 M 语句或者重复面板操作。把文件名称按照规则添加列到对应的合并的表格里。Excel 文件筛选后，会出现数据变多的情况。基于上述的场景，我们写好了一个能应对的自定义函数：TableXlsxCsv ，让我们来看一下函数的介绍。这个函数的提示和 Power Query 内置函数的提示是一样的，基本函数的使用和说明都清晰了。二、TableXlsxCsv 的使用方法1、参数介绍Ⅰ、必填参数1参:FolderNamedata 文件夹下需要合并表的文件夹名称，比如demo中的文件夹：01_订单2参:RemoveFirstRows数据从顶部开始需要移除的行数，一般情况需要把标题及以前的数据都要移除，在名称管理中已经管理好了对应的标题。3参:RemoveLastRows数据从底部开始需要移除的行数，比如底部有类似汇总行的数据。4参:IsAddColumn是否需要把文件名称按照规则添加列，只能填写 true 或者 false 两个参数，true 表示需要添加列，false 表示不需要添加列。**注意：**当 4 参 IsAddColumn 为 false，后面四个参数不用填写，为 true 时，后面四个参数必须填写。Ⅱ、可选参数5参:ColunmName当 4 参 IsAddColumn 为 true 表示需要把文件名称按照规则添加列，ColunmName 即为新增列的名称，注意不要与合并表中的字段名称重复。6参:ColumnType5 参 ColunmName 添加列的数据类型，只能填写如下类型：type texttype numberInt64.Typetype datetype datetimetype time7参:NameStartNumber文件名称字符开始的索引，比如文件名称：订单信息-2022-05-16；索引是从 0 开始的，需要取年与日的话索引的开始就是 5 。8参:Length文件名称在 6 参 NameStartNumber 后的长度，比如文件名称：订单信息-2022-05-16；索引是从 0 开始的，需要取年与日的话索引的开始就是 5 ，Length 就是 2022-05-16 的长度 10 个字符。2、必要配置Ⅰ、项目文件夹项目新建一个文件夹，名称随意，我们当前的案例项目文件夹：demo152Ⅱ、数据文件夹 data项目文件夹建立好后，需要把项目文件和数据文件区分开，我们建立 data 文件夹来管理数据，当然这个文件夹的名称也是随意，根据自己的需求来即可。Ⅲ、数据字段的名称管理在 数据文件夹 data 下有一个 00_辅助表 的文件夹，这个文件名称不能更改，是和函数绑定的名称。Ⅳ、需合并表格的名称管理在辅助表文件夹中，有一个 Excel 文件： 01_名称管理，这个文件就是把需要合并的文件夹中的表格的重命名、数据类型、是否显示等都解耦出来，在这个 Excel 文件中操作即可。具体配置如下图：1、文件夹：表示需要合并的文件夹，比如当前的：01_订单 文件夹。2、ID：表示需要合并表格从左开始的字段序号，序号从1开始依次递增，新增加的文件夹需要再次从 1 开始。3、原始名称：表示需要合并表格从左开始的字段名称。4、统一名称：表示在原始名称的基础下重命名后的字段名称，比如当前的配置就把所有字段都重命名了。5、字段类型ID：表示表格合并后最后统一数据类型标识，具体对应参照左边表格。6、是否显示：表示该字段是否在最终上载到模型中，只能填写：**是 ** 或者 否，填写其它会报错。Ⅴ、Power Query 路径配置打开项目文件把路径参数：Path 配置好，注意这个参数就是 前面 data 文件夹的路径。参数 Path 名称不能更改,，注意大小写。Ⅵ、配置自定义函数 TableXlsxCsv在 Path 下面 新建一个空白查询，点击高级编辑器，把 TableXlsxCsv 的 M 代码复制到编辑框里面保存，重命名查询为 TableXlsxCsv即可。TableXlsxCsv 的 M 代码：let
fx0 = (
FolderName as text,
RemoveFirstRows as number,
RemoveLastRows as number,
IsAddColumn as logical,
optional ColunmName as text,
optional ColumnType as text,
optional NameStartNumber as number,
optional Length as number
) as table =>
let
Types0 = List.Buffer({type text, type number, Int64.Type, type date, type datetime, type time}),
Types1 = List.Buffer({"文本", "小数", "整数", "日期", "日期时间", "时间"}),
ColumnNameTable0 = Table.Buffer( Excel.Workbook(File.Contents(Path & "\00_辅助表\01_名称管理.xlsx"), true, true){[Item = "ColumnNameTable", Kind = "Table"]}[Data]),
ColumnNameTable0Fileds = List.Buffer(Table.ColumnNames(ColumnNameTable0)),
TypeListNewjpz = List.Buffer(List.Zip({ColumnNameTable0Fileds, List.Transform({0, 2, 0, 0, 2, 0}, each Types0{_})})),
ColumnNameTable1 = Table.Buffer(Table.TransformColumnTypes(ColumnNameTable0, TypeListNewjpz)),
ColumnNameTable2 = Table.Buffer(Table.SelectRows(ColumnNameTable1, each ([文件夹] = FolderName))),
ColumnCount = Table.RowCount(ColumnNameTable2),
ColumnNameTableX = Table.Buffer(Table.SelectRows(ColumnNameTable2, each [是否显示] = "是")),
ColumnNameListOld = List.Buffer(List.Transform(ColumnNameTableX[ID], each "Column" & Text.From(_))),
ColumnNameListNew = List.Buffer(List.Zip({ColumnNameListOld, ColumnNameTableX[统一名称]})),
TypeListNew = List.Buffer(List.Zip({ColumnNameTableX[统一名称], List.Transform(ColumnNameTableX[字段类型ID], each Types0{_})})),
FileList0 = Table.Buffer(Folder.Files(Path & "\" & FolderName)),
BinaryList0 = List.Buffer(FileList0[Content]),
List0 = List.Buffer({0 .. List.Count(BinaryList0) - 1}),
Extension0 = List.Buffer(FileList0[Extension]),
NameList0 = List.Buffer(FileList0[Name]),
NameList1 = List.Transform(NameList0, each Text.Middle(_, NameStartNumber, Length)),
TableList0 = List.Buffer(
List.Transform(
List0,
(n) =>
let
wb = Table.SelectColumns(
if Text.Contains(Extension0{n}, "Csv", Comparer.OrdinalIgnoreCase) then
Table.RemoveLastN(
Table.RemoveFirstN(
Csv.Document(BinaryList0{n}, [Delimiter = ",", Columns = ColumnCount]),
RemoveFirstRows
),
RemoveLastRows
)
else
Table.Combine(
List.Transform(
Table.SelectRows(
Excel.Workbook(BinaryList0{n}, null, true),
each not Text.Contains([Name], "Filter", Comparer.OrdinalIgnoreCase)
)[Data],
each Table.RemoveLastN(Table.RemoveFirstN(_, RemoveFirstRows), RemoveLastRows)
)
),
ColumnNameListOld
)
in
if IsAddColumn then Table.AddColumn(wb, ColunmName, each NameList1{n}) else wb
)
),
Rename0 = Table.Buffer(Table.RenameColumns(Table.Combine(TableList0), ColumnNameListNew)),
Result =
if IsAddColumn then
Table.TransformColumnTypes(
Rename0,
TypeListNew & {{ColunmName, Types0{List.PositionOf(Types1, ColumnType)}}}
)
else
Table.TransformColumnTypes(Rename0, TypeListNew)
in
Result,
TypeMeta0 = type function (
FolderName as (
type text
meta [
Documentation.FieldCaption = "1参:FolderName, Path 路径下需要合并表的文件夹名称.",
Documentation.SampleValues = {"订单信息"},
Documentation.AllowedValues = List.RemoveItems(
List.Transform(
List.Distinct(Folder.Files(Path)[Folder Path]),
each Replacer.ReplaceText(Replacer.ReplaceText(_, Path, ""), "\", "")
),
{"00_辅助表"}
)
]
),
RemoveFirstRows as (
type number
meta [
Documentation.FieldCaption
= "2参:RemoveFirstRows,从顶部开始移除的行数.",
Documentation.SampleValues
= {1},
Documentation.AllowedValues = {0 .. 50}
]
),
RemoveLastRows as (
type number
meta [
Documentation.FieldCaption
= "3参:RemoveLastRows,从底部开始移除的行数.",
Documentation.SampleValues
= {0},
Documentation.AllowedValues = {0 .. 50}
]
),
IsAddColumn as (
type logical
meta [
Documentation.FieldCaption
= "4参:IsAddColumn,是否需要把文件名称按照规则添加列.",
Documentation.SampleValues
= {false},
Documentation.AllowedValues = {false, true}
]
),
optional ColunmName as (
type text
meta [
Documentation.FieldCaption = "5参:ColunmName,添加列的名称.",
Documentation.SampleValues = {"NewName"}
]
),
optional ColumnType as (
type text
meta [
Documentation.FieldCaption
= "6参:ColumnType,添加列的类型.",
Documentation.SampleValues
= {"文本"},
Documentation.AllowedValues = {"文本", "小数", "整数", "日期", "日期时间", "时间"}
]
),
optional NameStartNumber as (
type number
meta [
Documentation.FieldCaption
= "7参:NameStartNumber,文件名称字符开始的索引.",
Documentation.SampleValues
= {0},
Documentation.AllowedValues = {1 .. 50}
]
),
optional Length as (
type number
meta [
Documentation.FieldCaption
= "8参:Length,文件名称在 NameStartNumber 后的长度.",
Documentation.SampleValues
= {1},
Documentation.AllowedValues = {1 .. 50}
]
)
) as table
meta [
Documentation.Name = "TableXlsxCsv",
Documentation.LongDescription
= "返回同一文件夹下相同字段表格合并后的表格,兼容 XLSX, XLS, CSV 三种常见的格式, 同时兼容一个 WorkBook 多个相同字段的 WorkSheet 的 Excel 文件.【注意】:当参数 IsAddColumn 为 true 时,后四个参数 ColunmName, TypeNumber, NameStartNumber, Length 必填; 【00_辅助表】文件夹下的名称管理 Excel 文件需要在使用 TableXlsxCsv 提前配置好.",
Documentation.WhoAskedTheRightQuestion = "www.jiaopengzi.com",
Documentation.Author = "焦棚子",
Documentation.Examples = {
[
Description = "1、从文件夹【订单信息】中合并表格,且不用文件名称添加列.",
Code = "TableXlsxCsv( " & """订单信息""" & ", 1, 0, false )",
Result = "当参数 IsAddColumn 为 false 时,后四个参数可以不写;从文件夹【订单信息】中每个表格从顶部移除 1 行后合并表,且不用文件名称添加列."
],
[
Description = "2、从文件夹【订单信息】中合并表格,使用文件名称按照规则添加列.",
Code = "TableXlsxCsv( " & """订单信息""" & ", 1, 3, true," & """数据日期""" & ", "&"""日期"""&", 12, 10 )",
Result
= "当参数 IsAddColumn 为 true 时,后四个参数必填;从文件夹【订单信息】中每个表格从顶部移除 1 行并且从底部移除 3 行后合并表,添加列的名称:数据日期,数据类型为:日期格式,按照文件名称从 12 个字符开始取 10 个字符."
]
}
],
fx1 = Value.ReplaceType(fx0, TypeMeta0)
in
fx1
M 代码复制到高级编辑器后保存后，必要配置就算完毕了。3、TableXlsxCsv 调用Ⅰ、数据源首先来看一下数据源，在函数说明中介绍到，我们的自定义函数 TableXlsxCsv ，可以兼容 Xlsx、Xls、Csv 三种常见的本地文件，我们直接通过一个函数就可以解决了，不用去考虑表格是否筛选，表头有多行，同一个 Workbook 有多个相同的 WorkSheet等问题了。在 data 文件夹下新建文件夹来管理同一字段表格，案例中文件夹是：01_订单，在前面配置名称管理时也可以看。细心的朋友发现了，我们这个文件夹下是有三种数据类型。我再看下具体的每个文件下的内容：订单信息-2022-03-15 文件类型是 Xlsx，里面有 2 个Sheet 分别对应 15 号的数据和 16 号的数据，为了方便演示 15 号的 Sheet 中有 5 行数据， 16 号的 Sheet 中有 6 行数据。订单信息-2022-03-17 文件类型是 Xls，里面只有 1 个Sheet 为了方便演示 17 号的文件中有 7 行数据。订单信息-2022-03-15 文件类型是 Csv，为了方便演示 18 号的文件中有 8 行数据。Ⅱ、函数调用A、不添加列按照图示输入信息后点击调用函数，即可得到新的查询，更改查询名称为自己需要的名称即可。我们看到查询语句非常简单了，而且可以复用。let
源 = TableXlsxCsv("01_订单", 1, 0, false )
in
源
B、添加列按照图示输入信息后点击调用函数，即可得到新的查询，更改查询名称为自己需要的名称即可。**注意：**在需要添加列的情况下，后四个参数都是必填。M 语句如下：let
源 = TableXlsxCsv("01_订单", 1, 0, true, "数据日期", type date, 5, 10)
in
源
C、结果对比结合前面的数据源，可以看到 Xlsx, Xls, Csv 在同一个文件夹中是可以通过一个我们写的自定义函数合并的。虽然我们做了三个中格式兼容，但从效率来讲我们更希望看到的是 Csv 格式。导出的平面文件 PQ 读取的效率高低顺序： Csv > Xlsx > Xls ，尽量不使用Xls， Xls 这种格式读取是比较慢。看到下单日期，3 月 15 号和 3 月 16 号的数据在同一个 Workbook 的不同的 Sheet 里面，也是合并到了一起。筛选的零时表也是剔除掉的。每个字段的名称按照辅助表里名称管理的 Excel 配置的来重命名的。每个字段的格式按照辅助表里名称管理的 Excel 配置的数据类型来设置的。每个字段的显示按照辅助表里名称管理的 Excel 配置字段是否显示来显示的，如自动编号不显示，其它都显示出来了。下图第一张是没有后四个参数的，也就没有添加列，第二张图片有后四个参数，在数据后添加了一列。需要注意的我的文件名称的格式：订单信息-2022-03-15 这个数据名称也是有将就的，年是四位数占位，月是和日都必须是两位数占位，比如这里的 3 月，就是 03 ;这里的规则其实使用的函数 Text.Middle 这个规则可以自己去修改。最后的添加列，是对文件名称使用规则，而不是 Sheet 名称，比如这里的订单信息-2022-03-15 ，其中有两个 Sheet，最后添加列也是 3 月 15 号。第二张第 4 参数为 2，每个表在合并前都移除了最后两行，可以和上述数据源作对比。三、总结1、我们自定义的这个函数，其实就是封装了 Excel.Workbook 和 Csv.Document 两个主要的函数。2、当然如果自己写一个这样的函数的成本是很高的，我们写好的函数可以直接拿来用即可。3、有了这函数以后，我们在做项目的时候就提高数据导入的效率，同时一张 Excel 来管理我们的数据字段。4、有朋友可能会说直接使用数据库不更方便嘛，使用数据库也是完全可行的，但是更多的业务场景给的就是 Excel 、Csv 等文件，使用这个 PQ 自定义函数会更方便快捷。5、这个函数的复用还体现在，我们的项目会多张，这样就有多个文件夹，我们通过名称管理的 Excel 文件，把需要的表格名称管理起来，同时建立好对应的文件夹，这样我们就可以直接快速的合并文件夹里面的内容了。比如如这样：6、最后需要说明的一点，使用自定义函数合并的表格一定要自己校验。7、附件中有详细的分解步骤，有兴趣的可以研究下。by 焦棚子