Create New Column with Iterating Value

[NJ_hokie]

Hello - With existing data in the Name and ID columns, how can I create the ID_Needed column? Basically every time there is a blank in ID, I want to note it as 'undefined' but also append an iterating variable, so that ID_Needed can be used as a primary key column. Thanks in advance. 

Name	ID	ID_Needed
person 1	123	123
person 2		undefined1
person 3	234	234
person 4	345	345
person 5		undefined2
person 6		undefined3
person 7	456	456

[NJ_hokie]

Thanks to both for your solutions. 
SundarRaj - my actual data will not have an iterating number at the end of each name, so your solution would work on the data I listed but not in my data set, I should have explained that better. Thanks though. 
ronrsnfld - just what was needed, and a good push for me to learn m code better, thanks. 

[v-karpurapud]

Hi @NJ_hokie 

Could you please confirm if your query have been resolved the solution provided by @SundarRaj and @ronrsnfld ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[NJ_hokie]

Hi - yes, I was getting there, but had to learn the advanced editor a bit before actually getting things working. 

Thanks to both SundarRaj and ronrsnfld for the quick responses. SundarRaj - your response almost got me there and would have worked with that particular data, but the name field data will not actually have a unique number at the end. Ronrsnfld - great solution, thanks.  

[ronrsnfld · Accepted solution]

Examine the steps in the #"Add ID_Needed" step to best understand the algorithm. But we Zip into a two element array the position of the null and it's replacement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtKs7PUzBU0lEyNDJWitWBCxkBhZD5xkC+kbEJspAJUMjYxBRZyBRNlxka3xzINzE1U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}}),
    
    #"Add ID_Needed" = [a=#"Changed Type"[ID],
                        b=List.PositionOf(a,null,Occurrence.All),
                        c={1..List.Count(b)},
                        d=List.Transform(c, each "undefined" & Text.From(_)),
                        e=List.Zip({b,d}),
                        f=List.Accumulate(
                            e,
                            a,
                            (s,cur)=>List.ReplaceRange(s,cur{0},1,{cur{1}})),
                        g=Table.FromColumns(
                            Table.ToColumns(#"Changed Type")
                            & {f},type table[Name=text, ID=Int64.Type, ID_Needed=any]
                        )][g]
in
    #"Add ID_Needed"

Results from Your Data

[SundarRaj]

Hi @NJ_hokie , a quick correction to the above post. I'll leave the file link for you see. Thanks
https://docs.google.com/spreadsheets/d/1HNnLlJSroVaO2ciISbtq3ztUSKoQDy1y/edit?usp=sharing&ouid=10475...

[SundarRaj]

Hi @NJ_hokie 

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", Int64.Type}}),
Custom1 = Table.AddColumn ( #"Changed Type" , "ID_Needed" , each if Text.From ( _[ID] ) = null then "undefined " & Text.End ( _[Name] , 1 ) else Text.From ( _[ID] ))
in
Custom1