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 ...
Need Help
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"