Forum Discussion
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.
| 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 |
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
6 Replies
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
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
Custom1Hi 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=104752674875039603034&rtpof=true&sd=trueHi - 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.
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.
