Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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.
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.
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
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...
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.