Power Query中数据的定位

数据定位

(一) 列(字段)定位

1. 定位本查询：

步骤名[字段名]=源[成绩]

2. 定位其他查询：

查询名[字段名]=成绩表[成绩]

(二) 行(记录)定位

1. 定位本查询：

步骤名{行号}——0代表第一行

=源{0}

=源{[姓名="张三"]}

解释：定位时可以使用字段条件来进行。

2. 定位其他查询：

查询名{行号}——0代表第一行

=成绩表{0}

=成绩表{[姓名="张三"]}

(三) 值定位

1. 定位本查询：

步骤名[字段名]{行号}

如果行号为数字，[字段名]和{行号}位置可以互换

=源[姓名]{0}

=源{[姓名="张三"]}[姓名]

请注意这里的前后顺序，必须条件行号在前，[字段名]在后。

指定字段当前行直接使用[字段名]，例如在添加列里面直接使用[字段名]代表的就是当前指定的字段名的当前行的值。

2. 定位其他查询：

查询名[字段名]{行号}

如果行号为数字，[字段名]和{行号}位置可以互换

=成绩表[姓名]{0}

=成绩表{[姓名="张三"]}[姓名]

请注意这里的前后顺序，必须条件行号在前，[字段名]在后。

3. 列表值定位

{列表}{项目号}=

{1..10}{2}=3

{{1,2},{2,3}}{1}={2,3}

(四) 文本内定位

例：

Text.PositionOf(text as text, substring as text, optional occurrence as nullable Occurrence.Type, optional occurrence as nullable function)as any

第1个参数是需要操作的文本；第2个参数是需要查找的文本值；第3个参数是出现的次数的位置（0代表第一次出现的位置，1代表最后一次出现的文职，2代表所有出现的位置）默认第一次出现的位置；第4参数是大小写比较（Comparer.Ordinal代表完全一致，Comparer.OrdinalIgnoreCase代表不区分大小写，Comparer.FromCulture根据地区设置来识别）；返回的如果是第一或者最后一次的位置则是数值格式，如果是所有的次数则是列表格式。注意第一位是0，未找到的话返回-1。

例：

Text.PositionOf("abcAbcabcABC","a")=0

解释：找到第1个a出现的位置。

Text.PositionOf("abcAbcabcABC","a",1,Comparer.OrdinalIgnoreCase)

=9

解释：找到最后一个"a"出现的位置，不区分大小写。

Text.PositionOf("abcABCabcABC", "a",2, Comparer.OrdinalIgnoreCase)

={0,3,6,9}

解释：寻找所有"a"的位置，不区分大小写。返回的是一个列表，如果只找到1个，返回的也是单个项目的列表格式。

我们已经知道返回第一次，最后一次以及所有的，那如果是返回第2次出现的需要如何操作呢？原理是一样的，我们首先通过查找所有的，然后再找第2个项即可。

Text.PositionOf("abcAaABCaA","a",2){1}=4

解释：正常返回全部"a"所在的位置，是一个列表{0,4,8}，我们需要第2次则直接定位列表中的第2项，也就是{1}。

返回倒数第3次出现的位置。

Text.PositionOf("abcaaaBCaa","a",2){List.Count(Text.PositionOf("abcaaaBCaa","a",2))-3}

=5

解释：先求出出现"a"的所有位置，返回{0,3,4,5,8,9}。如果我们知道要显示的位置则直接可以定位第几项，但是如果我们不知道，则可以使用List.Count函数先计算出总的列表里面的项目数，然后再计算位置并指定。

Text.PositionOfAny(text as text, characters as list, optional occurrence as nullable Occurrence.Type) as any

第1参数是需要操作的文本；第2参数是一个查找的列表；第3参数是出现的位置；返回的是any。这里需要注意的是，字符只做完全匹配，未找到返回的是-1。

例：

Text.PositionOfAny("abcABCabcABC",{"A","b"})=1

解释：返回列表中任意字符第一次出现的位置，"b"先出现，位置在1，所以返回1。

Text.PositionOfAny("abcABCabcABC",{"A","b"},2)={1,3,7,9}

解释：返回列表中所有字符出现的位置。

(五) 列表内定位

List.PositionOf(list as list, value as any, optional occurrence as nullable Occurrence.Type, optional equationCriteria as any) as any

第1参数是需要操作的列表；第2参数是需要查找的值；第3参数是查找出现的位置（0代表第一次，1代表最后一次，2代表全部）默认第一次；第4参数是比较器；返回的是any，如果返回的是全部次数则是list格式。

例：

List.PositionOf({1,2,1,4,1},1,2)={0,2,4}

解释：找到所有1出现的位置。

List.PositionOf({1,2,1,4,1},{1,2},1)=-1

解释：因为是完全匹配，也就是要查找列表{1,2}，但是查找的列表中没有这个列表值，所以返回-1。

List.PositionOf({{1,2,1,4,1},{1,2},{1,2}},{1,2},1)=2

解释：查找列表{1,2}所在的最后显示的列表位置，因为{1,2}出现过2次，所以返回最后一次所在的位置。

List.PositionOfAny(list as list, value as list, optional occurrence as nullable Occurrence.Type, optional equationCriteria as any) as any

第1参数是需要操作的列表；第2参数是需要查找值的列表；第3参数是查找出现的位置（0代表第一次，1代表最后一次，2代表全部）默认第一次）；第4参数是比较器；返回的是any，如果返回的是全部次数则是list格式，未找到的话返回-1。

例：

List.PositionOfAny({1,2,1,4,1},{1,2})=0

解释：查找1或者2任意出现第一次的位置。也就是第一项，返回的是列表项位置是0。请注意这个和上面个List.PositionOf的差异，一个是绝对格式匹配，一个是任意字符匹配。

(六) 表格内定位

Table.PositionOf(table as table, row as record, optional occurrence as any,optional equationCriteria as any) as any

第1参数是需要操作的表；第2参数是需要查找的记录；第3参数是出现的位置；第4参数是相等值比较；返回的是any格式，未找到返回-1。

例：

Table.PositionOf(源,[姓名="张三",成绩=100,学科="数学"])=0

解释：返回第一条记录值所在的表格位置，需要查找一个完全匹配的记录。

Table.PositionOfAny用法和之前的雷同。

(七) 错行定位

1. 列表错行定位

根据列表的偏移位置加减计算来定位。

例：

在列表{1,2,3,4}中找到3之前的数字。

{1,2,3,4}{List.PositionOf({1,2,3,4},3)-1}=2

解释：通过List.PositionOf先找到3的位置，然后往前推1位并返回结果。

在列表{1,2,3,1,2,1,2}中找到第2次出现1之前1位的数字。

{1,2,3,1,2}{List.PositionOf({1,2,3,1,2},1,2){1}-1}=3

解释：我们来分解下。

1. 先找到第2个1出现的位置。使用List.PositionOf({1,2,3,1,2},1,2){1}，返回3，也就是列表的第4项。
2. 之前的1位则在找到的基础上-1。
3. 因为我们要从列表中挑选数据，格式是{列表}{项目的位置}，所以2个都是列表格式，我们在之前计算出结果的基础上套上一层{}代表列表。{3-1}这个就是我们要找的项目的位置。
4. 最后通过{列表}{3-1}也就是{1,2,3,1,2,1,2}{2}找到我们需要的值。

2. 表格错行定位

a. 通过指定记录位置偏移

例：

源{Table.PositionOf(源,[姓名="张三",成绩=100,学科="数学"])+1}

=[姓名="李四",成绩=90,学科="语文"]

解释：表格的错行定位也就是记录的偏移定位。

先找到目标位置

加减偏移位置

最终返回的位置

b. 通过指定列表位置偏移

源{List.PositionOf(源[姓名],"张三")+1}

=[姓名="李四",成绩=90,学科="语文"]

c. 通过索引来辅助偏移

源{List.PositionOf(已添加索引[索引],0)+1}

性质实际上和列表定位一样，只不过通过索引进行辅助而已。