excel 保留重复行(Power Query 之 M 语言)
时间:2023-08-10 17:10:52
数据源:
“姓名”“基数”“个人比例”“个人缴纳”“公司比例”“公司缴纳”“总计”,共7列7行数据,其中姓名列,第1、2行与第6、7行内容重复
目标:
留下第1、2、6、7姓名列中内容重复的行
操作过程:
选取指定列》【主页】》【保留行】》【保留重复项】
M公式:
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
说明:
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
给指定的列名一个说法,叫作“columnNames”
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
【分组依据】计算指定列里各个值出现次数,并将这结果命名为addCount
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
【保留行】保留“Count”列中值大于1的行
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
【删除列】将“Count”列删除
= let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
将原表和只保留重复值的表进行【合并查询】,连接种类使用“内部”
将所有步骤拆分如图所示。
其中核心部分:
addCount = Table.Group( 更改的类型,"姓名", {{"Count", Table.RowCount, type number}}),
selectDuplicates = Table.SelectRows(addCount, each [Count] > 1),
removeCount = Table.RemoveColumns(selectDuplicates, "Count"),
最后一步 = Table.Join(更改的类型, "姓名", removeCount, "姓名", JoinKind.Inner)
最终效果:
数据只剩下姓名列中重复的四行数据
多说一句:
好吧,我承认,我被这个公式惊到了!这其实已经不是一个简单的公式,而是一段M代码,这不是有let开头,in结尾么……
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
win7与ubuntu双系统完美卸载ubuntu的方法
![](https://img.aspxhome.com/file/2023/28/a246763_0s.png)
Win10系统默认web浏览器设置找不到Edge选项的解决方案
![](https://img.aspxhome.com/file/2023/4/a311178_0s.jpg)
win8开机变慢怎么办 启动Hybrid Boot与选择无GUI引导让win8开机更快速
![](https://img.aspxhome.com/file/2023/29/a258056_0s.jpg)
逍遥安卓模拟器怎么设置分辨率?
![](https://img.aspxhome.com/file/2023/7/a330801_0s.jpg)
win10 Edge浏览器周年更新版提升了多少?
![](https://img.aspxhome.com/file/2023/28/a243971_0s.jpg)
Ultraiso注册码是什么?Ultraiso软碟通如何注册?
![](https://img.aspxhome.com/file/2023/8/a339368_0s.jpg)
excel2017表格怎么插入数值调节按钮?
![](https://img.aspxhome.com/file/2023/2/40082_0s.png)
win7系统语言栏不见了怎么调出来?
![](https://img.aspxhome.com/file/2023/30/a271161_0s.jpg)
Win7系统中打开U盘显示I/0设备错误无法进行此项请求
![](https://img.aspxhome.com/file/2023/29/a252805_0s.png)
wps文字怎么自由裁剪图片
excel折线图怎么设置实线虚线混排显示?
![](https://img.aspxhome.com/file/2023/8/41248_0s.jpg)
网易云音乐怎么分享单曲?网易云音乐分享单曲的方法
![](https://img.aspxhome.com/file/2023/8/a333080_0s.jpg)
Win7系统cad不能复制粘贴怎么办?
![](https://img.aspxhome.com/file/2023/5/a316718_0s.png)
ppt音乐相册怎么做?PPT的制作技巧介绍
![](https://img.aspxhome.com/file/2023/10/a347352_0s.png)
如何使用HomePod和Apple TV 4K设置家庭影院音频?
![](https://img.aspxhome.com/file/2023/8/a206438_0s.jpeg)
系统损坏进不去怎么重装系统?
![](https://img.aspxhome.com/file/2023/6/a321480_0s.png)
云橙PDF编辑器怎么去水印?云橙PDF编辑器去水印的方法
![](https://img.aspxhome.com/file/2023/8/a333931_0s.jpg)
excel 模板路径的获取方法
![](https://img.aspxhome.com/file/2023/9/a155129_0s.gif)
AutoCAD2016快速入门:偏移
![](https://img.aspxhome.com/file/2023/7/a330834_0s.jpg)
Win10 2004系统显卡驱动报错怎么解决?
![](https://img.aspxhome.com/file/2023/26/a222309_0s.jpg)