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

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

目標(biāo)：

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

操作過程：

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

?

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)

• 【分組依據(jù)】計算指定列里各個值出現(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ù)值的表進行【合并查詢】，連接種類使用“內(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)，我被這個公式驚到了！這其實已經(jīng)不是一個簡單的公式，而是一段M代碼，這不是有l(wèi)et開頭，in結(jié)尾么……