Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
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
Thank You AlienSX, This was worked.