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

     

     

     

v-karpurapud's avatar
v-karpurapud
Icon for Community Support rankCommunity Support
1 year ago

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

NJ_hokie's avatar
NJ_hokie
Regular Visitor
1 year ago

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.