Forum Discussion

osama_ayoub's avatar
osama_ayoub
Helper III
3 years ago

Creating Index Column

I have a table with 2 column Code and substitute and there are some iterrated codes in the two columns, 

this means maybe row 1 has code a and Substitue b and 

row 2 has Code b and Substitue C and

row 3 has Code F and Substitue D and so on ,

What I want to create a Index Column

and this Index Column will put the same Index for row 1 and row 2 because the a and B and C are alternatives and

put another Index for line 3 and so on 

 

Need Help

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    NewStep=#table(Table.ColumnNames(PreviousStepName)&{"Index"},List.Accumulate(Table.ToRows(PreviousStepName),{{},[],1},(x,y)=>let a=List.Max(List.Transform(y,each Record.FieldOrDefault(x{1},_,null))) in if a=null then {x{0}&{y&{x{2}+1}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each x{2}+1}),2),x{2}+1} else {x{0}&{y&{a}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each a}),2),x{2}}){0})

    • osama_ayoub's avatar
      osama_ayoub
      Helper III
      3 years ago

      Thank you for your support but I still have this errror 

       

       

      This is the code of the query 

       

      let
      Source = Table1,
      #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
      #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 100),
      #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
      #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #"Removed Columns"(Table.ColumnNames(#"Removed Columns")&{"Index"},List.Accumulate(Table.ToRows(#"Removed Columns"),{{},[],1},(x,y)=>let a=List.Max(List.Transform(y,each Record.FieldOrDefault(x{1},_,null))) in if a=null then {x{0}&{y&{x{2}+1}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each x{2}+1}),2),x{2}+1} else {x{0}&{y&{a}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each a}),2),x{2}}){0}))
      in
      #"Added Custom"

       

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        let
        Source = Table1,
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 100),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
        #"Added Custom" = #table(Table.ColumnNames(#"Removed Columns")&{"Index"},List.Accumulate(Table.ToRows(#"Removed Columns"),{{},[],1},(x,y)=>let a=List.Max(List.Transform(y,each Record.FieldOrDefault(x{1},_,null))) in if a=null then {x{0}&{y&{x{2}+1}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each x{2}+1}),2),x{2}+1} else {x{0}&{y&{a}},Record.TransformFields(x{1},List.Transform(y,(z)=>{z,each a}),2),x{2}}){0})
        in
        #"Added Custom"