Forum Discussion

NJ_hokie's avatar
NJ_hokie
Regular Visitor
1 year ago
Solved

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 iteratin...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    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