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.

 

numberIndexNext Index
10012 
10023 
1003  
2012 
202  
15012 
15023 
15034 
15045 
1505  
101  
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

3 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    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