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 PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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