Forum Discussion
How to refer previous cell for this case ?
Here's how to do it. Please insert this piece of code into the Editor in PQ and inspect each step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDLCQAwCAPQXTz30n93ke6/RoMUApJD4UHViO52rODd4ragFWpQC21ohyY0kwY0Ul2FavqlmMYMVdehnsRclcHJFHvVfmqeSuNd1H7qQn/efQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [B = _t, C = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"B", Int64.Type}, {"C", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Index", "B", "C"}), Buferred = Table.Buffer(#"Reordered Columns"), #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Previous Row", (r) => Table.SelectRows(Buferred, (tbl) => tbl[Index] = r[Index] - 1) ), #"Expanded Previous Row" = Table.ExpandTableColumn(#"Added Custom", "Previous Row", {"B"}, {"Previous Row.B"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded Previous Row", "Output", each if [B] = [Previous Row.B] then [C] else "First"), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Previous Row.B"}) in #"Removed Columns"Once you know what it does, use it to your advantage.
Best
D
Here's how to do it. Please insert this piece of code into the Editor in PQ and inspect each step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDLCQAwCAPQXTz30n93ke6/RoMUApJD4UHViO52rODd4ragFWpQC21ohyY0kwY0Ul2FavqlmMYMVdehnsRclcHJFHvVfmqeSuNd1H7qQn/efQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"B", Int64.Type}, {"C", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Index", "B", "C"}),
Buferred = Table.Buffer(#"Reordered Columns"),
#"Added Custom" =
Table.AddColumn(#"Reordered Columns", "Previous Row",
(r) => Table.SelectRows(Buferred, (tbl) => tbl[Index] = r[Index] - 1)
),
#"Expanded Previous Row" = Table.ExpandTableColumn(#"Added Custom", "Previous Row", {"B"}, {"Previous Row.B"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Previous Row", "Output", each if [B] = [Previous Row.B] then [C] else "First"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Previous Row.B"})
in
#"Removed Columns"Once you know what it does, use it to your advantage.
Best
D
Thanks a lot for sharing the solution, Anonymous !
With what you shared, I have created as table below on Power query editor!
(for Next J date, I used your code, and used +1 instead -1)
Code J code J Creation Date Previous J date Next J date AAAAD AAAAD_2 25-Apr-16 First 26-Apr-16 AAAAD AAAAD_3 26-Apr-16 25-Apr-16 Last CCCB CCCB_4_2 28-May-16 First 3-Jun-16 CCCB CCCB_4_3 3-Jun-16 28-May-16 Last RRREE RRREE_6 29-Aug-16 First 30-Oct-16 RRREE RRREE_7 30-Oct-16 29-Aug-16 Last AAAAD AAAAD_4 25-Nov-16 First 26-Dec-16 AAAAD AAAAD_3_2 26-Dec-16 25-Nov-16 Last Now, I have faced another chellenge here.
Please see the code, AAAAD underlined. With IF formula and the code you wrote, this si correct result, but actually AAAAD's first job is already existed on the first row.
I am wondering that there is ways to make this as below?
Code J code J Creation Date Previous J date Next J date AAAAD AAAAD_2 25-Apr-16 First 26-Apr-16 AAAAD AAAAD_3 26-Apr-16 25-Apr-16 25-Nov-16 CCCB CCCB_4_2 28-May-16 First 3-Jun-16 CCCB CCCB_4_3 3-Jun-16 28-May-16 Last RRREE RRREE_6 29-Aug-16 First 30-Oct-16 RRREE RRREE_7 30-Oct-16 29-Aug-16 Last AAAAD AAAAD_4 25-Nov-16 26-Apr-16 26-Dec-16 AAAAD AAAAD_3_2 26-Dec-16 25-Nov-16 Last I am really appreciated your help with the first question, I am hoping you could look into this as well.
Please let me know if you need more clearification on this.
Thanks in advance!
Regards,
Yumi
- I sent a reply to this long time ago but can't see it... Well, I said in the message that you should first sort your data correctly before you start doing any transformations on it...
Best
D