Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NJ_hokie
Regular Visitor

Create New Column with Iterating Value

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. 

NameIDID_Needed
person 1123123
person 2 undefined1
person 3234234
person 4345345
person 5 undefined2
person 6 undefined3
person 7456456
1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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

 

ronrsnfld_0-1747689641475.png

 

 

View solution in original post

6 REPLIES 6
NJ_hokie
Regular Visitor

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
Community Support
Community Support

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

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
Super User
Super User

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

 

ronrsnfld_0-1747689641475.png

 

 

SundarRaj
Super User
Super User

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

Sundar Rajagopalan
SundarRaj
Super User
Super User

Hi @NJ_hokie 

SundarRaj_0-1747683547695.png

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

Sundar Rajagopalan

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.