Power Query 系列 (13) - 自定义函数
原创Power Query 系列 (13) - 自定义函数
原创
本篇博客介绍 Power Query 自定义函数的技巧，在 PQ 中计算个税。
以工资类所得应交个税为例，最新的个税起征点为 5000 并按下表的级次进行缴税（假设没有其它扣除项）。
对照税率表，我们先看看手工如何计算。比如 xxx 的薪资为 8900，没有其它扣除，那么应缴纳的个税为：
1: 应纳税所得额 = 8900 - 5000 = 3900
2： 3900 对应级数为 1，应纳税额 = 3900 * 10% - 210 = 180接下来介绍在 PQ 中如何通过自定义函数来计算。有下面两个表：税率表（TaxRate）he员工薪资表（salaries），要计算张三和李四应交个税金额。假设我们用自定义函数的方式。本文将给出三种方法。
将 Excel 工作表中的两个数据源通过 Ctrl + T 转换成表，加载到 PQ 中。
函数的语法
PQ 函数的语法示例：
(x, y) => x + y方法1：在高级编辑器中手写代码。新建一个空查询，改名为 GetIncomeTax，进入高级查询，在高级查询中输入下面的代码：
(taxable) =>
if taxable <=0 then 0
else if taxable <= 3000 then taxable * 0.03
else if taxable <= 12000 then taxable * 0.1 - 210
else if taxable <= 25000 then taxable * 0.2 - 1410
else if taxable <= 35000 then taxable * 0.25 - 2660
else if taxable <= 55000 then taxable * 0.3 - 4410
else if taxable <= 80000 then taxable *0.35 - 7160
else taxable * 0.45 - 15160点击完成按钮，回到查询编辑器界面，这个函数就完成了。以上代码跟其他编程语言差不多，思路比较直观。可以通过上面代码熟悉 M语言 if then else 的语法。
函数调用
选中 salaries 查询，右键，点击【复制】，将 salaries 查询表复制一个名为 IncomeTaxMethod1 的新查询，添加一个自定义列， 计算应纳税所得额 (用 Taxable 表示）：
再增加一个计算列，调用自定义函数 GetIncomeTax 计算个税 ：
点击完成按钮回到查询编辑器界面，第一种方法定义函数和调用函数完成。
对应的 M 语言脚本：
let
源 = Excel.CurrentWorkbook(){[Name="salaries"]}[Content],
ChangedTypes = Table.TransformColumnTypes(源,{{"Name", type text}, {"Salary", Int64.Type}}),
AddedTaxableCol = Table.AddColumn(
ChangedTypes,
"Taxable",
each [Salary] - 5000),
Result = Table.AddColumn(
AddedTaxableCol, "IncomeTax", each GetIncomeTax([Taxable]))
in
Result查询转函数
方法2：方法 2 的个税计算方法来自于我见过的 Excel 中最简洁的计算公式：
=ROUND(MAX((B4-5000)*{3;10;20;25;30;35;45}%-{0;210;1410;2660;4410;7160;15160},0),2)公式中只有一个参数，但这个公式有点晦涩，先解释一下。这个公式用到了 Excel 的数组。第一个数组是税率， 为方便表述称为 taxrate_array，第二个数组是速算扣除数，为表述方便称为 deduction_array:
taxrate_array: {3;10;20;25;30;35;45}
deduction_array: {0;210;1410;2660;4410;7160;15160}所以，
(D2-5000)*{3;10;20;25;30;35;45}%-{0;210;1410;2660;4410;7160;15160}表示是将 (salary-5000）后的值 （taxable）与 taxrate_array 数组的每一个元素进行计算，再减去 deduction_array 数组的对应值，结果组成一个新数组。然后再从这个结果数组中取最大值，四舍五入保留两位小数。用伪代码表述更加清晰：
taxable = salary - 5000
incomeTax = {taxable*0.03-0; taxable*0.1-210; taxable*0.2-1410; ...}
incomeTax = Max({incomeTax})
IncomeTax = Round(IncomeTax, 2)这个算法是怎么想出来的我们不去管它，主要讲解在 PQ 中用同样算法实现的步骤。
选中 TaxrateTable 查询，右键，点击【引用】菜单，得到一个新的查询，将查询改名为 GetTax。后面基于这个查询编写计算个税的函数。
根据刚才的 Excel 公式，上图中 Level 字段、TaxableFrom 字段和 TaxableTo 字段是无关字段，可以删除。可以用 Table.RemoveColumn 函数删除，也可以使用 Table.SelectColumns 函数保留需要的列。本次使用Table.SelectColumns 函数。在高级编辑器或公式栏中操作都可以：
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
这样选择了相关的两列，查询编辑器界面如下：
函数需要参数，比如计算个税需要应纳税所得额。为了方便，我先用一个特定值，后面再替换。进入高级编辑器，目前的代码如下：
let
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
in
SelectedCols比如用刚才的应纳税所得额 3900，将代码变更为：
let
taxable = 3900,
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"})
in
SelectedCols注意下图我的变更是插入了一个变量：
然后增加一个自定义列 Trial (表示试算）：
此时查询编辑器界面如下：
对应的 M 语言代码如下：
let
taxable = 3900,
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"}),
Trials = Table.AddColumn(
SelectedCols,
"Trial",
each taxable * [Rate] - [Deduction])
in
Trials
接下来取出 Trial 列的最大值。我们知道 Table 每列都是 List 类型的数据，所以可以增加一个步骤，命名为 MaxOfTrial，在公式栏输入：
= List.Max(Trials[Trial])再套用 Number.Round 函数:
= Number.Round(List.Max(Trials[Trial]),2)此时查询编辑器的界面如下：
对应的 M 语言代码如下：
let
taxable = 3900,
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"}),
Trials = Table.AddColumn(
SelectedCols,
"Trial",
each taxable * [Rate] - [Deduction]),
MaxOfTrial = Number.Round(List.Max(Trials[Trial]),2)
in
MaxOfTrial接下来是见证奇迹的时刻，在高级编辑器中将代码变更为：
(taxable as number ) as number =>
let
Source = TaxRatesTable,
SelectedCols = Table.SelectColumns(Source, {"Rate", "Deduction"}),
Trials = Table.AddColumn(
SelectedCols,
"Trial",
each taxable * [Rate] - [Deduction]),
MaxOfTrial = Number.Round(List.Max(Trials[Trial]),2)
in
MaxOfTrial回到查询编辑器界面，PQ 将查询变成了函数。经过本步骤，第二种方法定义函数完成。请体会和掌握如何将查询转换成函数，这种方式可以将编写函数的步骤分解，并且能看到每一个步骤的计算结果，从而降低了手写代码的难度。
函数的调用比较简单，略去不提。
个税计算方法 3： 对于应纳税所得额来说，需要在 TaxRatesTable 中找到对应行，从而确定税率和速算扣除数。仍以 3900 的应纳税所得额为例，对应下图的级次确定税率和速算扣除数：
在 PQ 中，从另外一个 Table 中找出一行，使用构造结构化列的方法。这种方法具有普遍意义，能够处理两个表没有相等值，从而不能用合并查询的场景。
选中 salaries 查询表，右键菜单【引用】，新建一个名为 IncomeTaxMethod3 的查询。添加一个自定义列。这一列每个单元格都包含完整的 TaxRatesTable 查询表数据。
回到查询编辑器界面，我们看到，TaxInfo 每一个单元格都包含 TaxRateTable 的所有数据，我们需要对 sub-table 的数据进行筛选。
这种操作技巧在本系列第 11 篇有详细介绍，本篇就不再赘述。在高级编辑器中，将代码变更。变更前代码：
let
Source = salaries,
AddedTaxData = Table.AddColumn(Source, "TaxInfo", each TaxRatesTable)
in
AddedTaxData变更后代码后：
Source = salaries,
AddedTaxData = Table.AddColumn(
Source,
"TaxInfo",
each Table.SelectRows(
TaxRatesTable,
(row)=>row[TaxableFrom]<=[Salary] - 5000
)
)
in
AddedTaxData通过 Table.SelectRows 将税率级次高的数据排除掉。比如 8900 - 5000 = 3900，则只需先下面两行：
接下来调用 Table.Last 函数获取最后一行，得到一个 record 类型的数据：
let
Source = salaries,
AddedTaxData = Table.AddColumn(
Source,
"TaxInfo",
each Table.Last(
Table.SelectRows(
TaxRatesTable,
(row)=>row[TaxableFrom]<=[Salary] - 5000
))
)
in
AddedTaxData这时 TaxInfo 变成了 record，对 TaxInfo 列进行展开操作，保留 Rate 和 Deduction:
查询编辑器的界面如下：
最后添加一个计算列，计算出各自的个税：
这种结构化列的方法，同样可以先做出查询，然后转换成函数。操作过程类似，就不重复说明了。给出获取 TaxRateTable 相应行的函数代码：
(taxable as number) =>
let
Source = Table.SelectColumns(TaxRatesTable, {"TaxableFrom", "Rate", "Deduction"}),
SelectedRow = Table.Last(
Table.SelectRows(
Source, (row)=>row[TaxableFrom] <= taxable
))
in
SelectedRow示例数据已经放到 github - Income Tax Calculation 上，方便大家学习。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。