duplicates - 应用转换后尝试识别重复值时，Power Query 无法识别正确的列名

Question

奇怪的问题在过去的几个小时里给我带来了一些麻烦。我试图custom.Column1通过创建一个新列来确定哪些值是重复的，如果该值已经存在或不在列中，则该列返回重复或唯一。

在网上看我发现这可以通过以下方式实现：

if List.Count(List.FindText(Source[custom.Column1],[custom.Column1]))>1 then "duplicate" else "unique")

但是，这似乎只有在早期使用时才有效。在我应用了转换之后，我收到一条错误消息，指出找不到列名。

原始数据：

码：

let
Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom.Column1", type text}, {"Custom", type text}, {"Custom.Column3", type text}, {"Custom.Column4", type text}, {"Custom.Column5", type text}}),


#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Custom.Column1", "Custom.Column4"},Combiner.CombineTextByDelimiter("@", QuoteStyle.None),"Merged"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Merged", "Custom", "Custom.Column3", "Custom.Column5"}),
#"Merged Columns1" = Table.CombineColumns(#"Reordered Columns1",{"Custom", "Custom.Column5", "Custom.Column3"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged.1"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter("?", QuoteStyle.None),"Merged.2"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter("?", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "Merged.2", Splitter.SplitTextByDelimiter("@", QuoteStyle.None), {"Merged.2.1", "Merged.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", type number}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type3", "Merged.2.1", Splitter.SplitTextByDelimiter("#", QuoteStyle.None), {"Merged.2.1.1", "Merged.2.1.2", "Merged.2.1.3"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter2","-","",Replacer.ReplaceText,{"Merged.2.1.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value",{{"Merged.2.1.2", type number}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type4", each ([Merged.2.1.1] <> "")),

//PROBLEMATIC LINE
#"Added Custom3" = Table.AddColumn(#"Filtered Rows2", "Custom.1", each if List.Count(List.FindText(Source[Merged.2.1.1],[Merged.2.1.1]))>1 then "duplicate" else "unique")

在 #"添加了 Custom3"

希望在尝试识别重复项时，您应该看到所有转换都一直有效到最后一行。我只是想不通为什么。PowerQuery 中可能出现的故障？

资料来源：

Custom.Column1 氢化葡萄糖

Xanthan Gum
Methyl Paraben



Peppermint Flavour No.2

Sodium Hydroxide
Purified Water

风俗

Sorbitol
Maltitol
Xanthan Gum
Methyl Paraben
Methanol
Purified Water
Amorphous Silica Gel
Menthol
2-Isopropyl-5-Methylcyclohexanone
Sodium Hydroxide
Purified Water

自定义.Column3

68425-17-2
585-88-6
11138-66-2
99-76-3
67-56-1
7732-18-5
7631-86-9
89-78-1
89-80-5
1310-73-2
7732-18-5

自定义.Column4

25.59685

0.64875
0.37071



0.05561

0.0519
73.12789

自定义.Column5

50
8
-
100
0.3
0.5
0.5
30.00
5.00
-
-

标签： duplicatesoffice365powerquery

Answer 1

在最后一步，改变

Source[Merged.2.1.1]

至

#"Filtered Rows2"[Merged.2.1.1]

源表中不存在 Merged.2.1.1。它存在于名为 #"Filtered Rows2" 的最后一步中