Conditional column - if [Column] = [Column except from same row]

[rkords]

Hello,

i am quite new to Power Query and trying to get so usefull data out of a NetList from a PCB project.

I have a column with designators. In this column one designator can be in multiple rows.

I want to create a custom column with a condition like that:

If [Designator in row x] = [any other designator except from row x]  then ...

Thank you for the help.

[Vijay_A_Verma]

Can you verify the below output for Custom so that I know my understanding is correct

Signal Name	Designator	Custom
DI_BTN_USR_1	C1500	GND
GND	C1500	GND
DI_BTN_USR_2	C1501	ABC
ABC	C1501	ABC

[rkords]

Hello thank you for your response,

I tried your suggestion, but still getting the same row Value.

[Vijay_A_Verma]

Please post your complete query here. 

[rkords]

I figured, that your first suggestions works. It finds the Designators, that appear more than once in the column. How ever, my goal is, to copy the entry in the column Signal_Name from the row that contains the same value for the Designator to the new custom column.

Another Screeshot to explain what i am trying to do:

And here ist the Query (most of it is to format the input File to usefull columns, only the last step really matters):

let
    Source = Csv.Document(File.Contents("C:\Users\user\porject\Auto_Netlist\WireList.NET"),[Delimiter=":", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Find Position" = List.PositionOf(#"Changed Type"[Column1],"<<< Wire List >>>"),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",#"Find Position"+4),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Column1.1", Text.Trim, type text}, {"Column1.2.1", Text.Trim, type text}, {"Column1.2.2", Text.Trim, type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type text}, {"Column1.2.2.2", type text}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Changed Type3",{{"Column1.2.2.2", Text.Trim, type text}, {"Column1.2.2.1", Text.Trim, type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Trimmed Text1", "Column1.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Trimmed Text2" = Table.TransformColumns(#"Changed Type4",{{"Column1.2.2.2.1", Text.Trim, type text}, {"Column1.2.2.2.2", Text.Trim, type text}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Trimmed Text2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Trimmed Text3" = Table.TransformColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", Text.Trim, type text}}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Trimmed Text3", "Column1.2.2.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.2.1", "Column1.2.2.2.2.2.2"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Column1.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2", type text}}),
    #"Trimmed Text4" = Table.TransformColumns(#"Changed Type6",{{"Column1.2.2.2.2.2.1", Text.Trim, type text}, {"Column1.2.2.2.2.2.2", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text4",{{"Column1.1", "Net_Number"}, {"Column1.2.1", "Signal_Name"}, {"Column1.2.2.1", "Designator"}, {"Column1.2.2.2.1", "Pad"}, {"Column1.2.2.2.2.1", "Pin"}, {"Column1.2.2.2.2.2.1", "Pin Type"}, {"Column1.2.2.2.2.2.2", "Component"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"Signal_Name"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filled Down" = Table.FillDown(#"Removed Blank Rows",{"Signal_Name"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Custom", each if List.Count(List.Select(#"Filled Down" [Designator], (i)=>i= [Designator]))>1 then List.Select() else null)
in
    #"Added Conditional Column"

[Vijay_A_Verma · Accepted solution]

Use below kind of formula. replace x and y. Replace Previous Step and Data

= if List.Count(List.Select(#"Previous Step"[Data], (i)=>i= [Data]))>1 then x else y