Adding new column based on another column criteria on the same table

Hi markquisquirin ,

Please have a try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwsDRV0lEKcgYSCkqxOgghRxcMIWd/XwyxYJCQoZGxiSmKcDhY3MzcwtIALm6BaZEFpkUWWCyygFlkamJsZIgiDLHI0sLcTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Link = _t, #"Party ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Link", type text}, {"Party ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(x)=>x[Number]=[Number] and x[Link] = "WSM")[Party ID]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "WSM"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwsDRV0lEKcgYSCkqxOgghRxcMIWd/XwyxYJCQoZGxiSmKcDhY3MzcwtIALm6BaZEFpkUWWCyygFlkamJsZIgiDLHI0sLcTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Link = _t, #"Party ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Link", type text}, {"Party ID", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Link] = "WSM")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Number"}, #"Filtered Rows", {"Number"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Party ID"}, {"Party ID.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Filtered Rows",{{"Party ID.1", "WSM"}})
in
    #"Renamed Columns"

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.