Forum Discussion

sravani9920's avatar
sravani9920
Frequent Visitor
2 years ago
Solved

at what Index the value occurred again?

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...
Show and Tell
  • AlienSx's avatar
    AlienSx
    2 years ago

    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
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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