at what Index the value occurred again?

[sravani9920]

Hi Guys,

Please help me out,

I am providing sample data for you, Here the output should be as Next Index column  using PQE.

Thanks in Advacne.

number	Index	Next Index
100	1	2
100	2	3
100	3
20	1	2
20	2
150	1	2
150	2	3
150	3	4
150	4	5
150	5
10	1

[dufoq3]

Hi @sravani9920, different approach.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMlSK1YGxjZDYxmC2EUKJEZIKUySdpujixkhsEyS2KdR0iNZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, Index = _t]),
    Ad_NewIndex = Table.Combine(Table.Group(Source, {"number"}, {{"t", each Table.AddColumn(_, "New Index", (x)=> if Number.From(x[Index]) <> Table.RowCount(_) then Number.From(x[Index]) +1 else null, Int64.Type), type table}})[t])
in
    Ad_NewIndex

[AlienSx · Accepted solution]

hello, @sravani9920 

let
    Source = your_table,
    f = (tbl) => 
        [new_col = List.Skip(tbl[Index]) & {null},
        out = Table.FromColumns(
            Table.ToColumns(tbl) & {new_col}, 
            Table.ColumnNames(tbl) & {"Next Index"}
        )][out],
    g = Table.Group(Source, "number", {"data", f}),
    z = Table.Combine(g[data])
in
    z

[sravani9920]

Thank You AlienSX, This was worked.