Power Query wont recognise correct column name when trying to identify duplicate values after transformations are applied

Question

Strange issue which has been giving me a bit of trouble for the past few hours. Im trying to identify which values are duplicates in custom.Column1 by creating a new column which returns either duplicate or unique if the value is already present or not within the column.

looking online I found that this can be achieved using:

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

However, this only appears to work if used early on. Atfer I have applied transformations I get an error saying that the column name is not found.

Original Data:

M Code:

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")

in #"Added Custom3"

Hopefully you should see that all of the transformations work up until the final line when trying to identify duplicates. I just cant figure out why. A possible glitch in PowerQuery?

DATA FOR SOURCE:

Custom.Column1 Hydrogenated Glucose

Xanthan Gum
Methyl Paraben



Peppermint Flavour No.2

Sodium Hydroxide
Purified Water

Custom

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

Custom.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

Custom.Column4

25.59685

0.64875
0.37071



0.05561

0.0519
73.12789

Custom.Column5

50
8
-
100
0.3
0.5
0.5
30.00
5.00
-
-

Answer 1

in last step, change

Source[Merged.2.1.1]

to

#"Filtered Rows2"[Merged.2.1.1]

Merged.2.1.1 does not exist in Source table. It exists in the final prior step named #"Filtered Rows2"

Answer 2

The line of code you show that works is:

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

So you are refering to the Step called "Source", which is the first one. This one contains a column called "custom.column1".

In the example that does not work you refer again to the step "Source", but this one doesn't have the column [Merged.2.1.1] which you added later. Thus you get the error.

To refer to the step you have to write:

#"Filtered Rows2"[Merged.2.1.1]

instead of

Source[Merged.2.1.1]