巧用PowerQuery实现不规则数据整理

◆吴涛／丹江口市审计局

笔者在工作中经常会碰到如企业股东信息、不动产登记信息等，将同一公司或不动产的所有权利人的姓名、身份证号信息集中保存在一个单元格中（如图1所示），不便于后期的数据分析。无论用常规的哪种方法来进行数据标准化，往往难度较大且费时费力。这里以企业股东信息为例，介绍运用Excel 2016中的Power Query三分钟完成数据的整理。

图1

一、Power Query简介

Power Query是Excel 2016及Power BI Desktop中内置的一个免费工具，主要用于数据获取、数据转换、数据处理，入手极快，不需要太多的学习时间，仅使用Power Query图形界面就足以完成80%的日常任务。

二、数据整理方法

1.如图2所示，将光标定位到表格中，然后选择“数据—从表格”，将表格引入Power Query中。

图2

2.如图3所示，选中“姓名（股东）、证件号码（股东）”两列，选择“逆透视列”,将二维表转为一维表。

图3

3.如图4所示，选中“值”列，选择“拆分列—按分隔符”，将每个人的姓名、身份证号拆分出来。其中：分隔符为“逗号”，拆分为“行”。

图4

4.由于每条记录除“值”不同外，存在重复，在进行升维操作时会造成取值混乱。为了唯一区分每条记录，需要给姓名和身份证号分别进行编号加以区分。如图5所示，选择“证件号码（法定代表人）”“属性”执行“转换—分组依据”，对“所有行”进行计数分组。

图5

5.添加自定义列，录入公式=Table.AddIndexColumn（[计数],"分组编号",1），作用是对每个子表添加从1开始的索引编号，并将该列命名为“分组编号”。删除“计数”列，然后展开“自定义”列，完成分类索引编号,如图6所示。

图6

6.选中“属性”列，执行“转换—任意列—透视列”，即可得到所需标准表，如图7所示。■

图7