數(shù)據(jù)源：

“姓名”“基數(shù)”“個(gè)人比例”“個(gè)人繳納”“公司比例”“公司繳納”“總計(jì)”，共7列7行數(shù)據(jù)，其中姓名列，第1、2行與第6、7行內(nèi)容重復(fù)

目標(biāo)：

留下第1、2、6、7姓名列中內(nèi)容重復(fù)的行

操作過程：

選取指定列》【主頁】》【保留行】》【保留重復(fù)項(xiàng)】

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)

• 給指定的列名一個(gè)說法，叫作“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)

• 【分組依據(jù)】計(jì)算指定列里各個(gè)值出現(xiàn)次數(shù)，并將這結(jié)果命名為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)

• 將原表和只保留重復(fù)值的表進(jìn)行【合并查詢】，連接種類使用“內(nèi)部”

將所有步驟拆分如圖所示。

其中核心部分：

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)

最終效果：

數(shù)據(jù)只剩下姓名列中重復(fù)的四行數(shù)據(jù)

多說一句：

好吧，我承認(rèn)，我被這個(gè)公式驚到了！這其實(shí)已經(jīng)不是一個(gè)簡(jiǎn)單的公式，而是一段M代碼，這不是有let開頭，in結(jié)尾么……