Forum Discussion
jasemilly
Helper III
5 years agouse condition to refer to the previous Row
Hi am I have created a simple table with some test data. I would like to get the value of the next rows startdate only if the name is the same else I would like it to be blank.
I am getting the following error
I am able to reference the next row perfectly fine when I don't have the condition here is my code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlTSUfLKzwOSQCGYmIKBmZWBAUgMIg4TNjQHCcfq4NIP124B0W5MnnZDA4h2EzK1G0G0GxGlPa0oNQXN9SR5Hot+c4h+QzL1w7xPXOhh0W+ML/hiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkDate = _t, Name = _t, StartDate = _t, Worktype = _t, ShiftEnd = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkDate", type date}, {"Name", type text}, {"StartDate", type text}, {"Worktype", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
//works but Picks up next person start date not what I want
//#"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each try #"Added Index"{[Index]}[StartDate] otherwise null)
//errors
#"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each if [Name]{[Index]} = [Name]{[Index] - 1} then #"Added Index"{[Index]}[StartDate] else null )
in
#"Added Custom"
Hi jasemilly ,
Try with this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlTSUfLKzwOSQCGYmIKBmZWBAUgMIg4TNjQHCcfq4NIP124B0W5MnnZDA4h2EzK1G0G0GxGlPa0oNQXN9SR5Hot+c4h+QzL1w7xPXOhh0W+ML/hiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkDate = _t, Name = _t, StartDate = _t, Worktype = _t, ShiftEnd = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkDate", type date}, {"Name", type text}, {"StartDate", type text}, {"Worktype", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each try if #"Added Index"{[Index]}[Name] = #"Added Index"{[Index] - 1}[Name] then #"Added Index"{[Index]}[StartDate] else null otherwise null) in #"Added Custom"
1 Reply
- Payeras_BI
Solution Sage
Hi jasemilly ,
Try with this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDXNzIwMlTSUfLKzwOSQCGYmIKBmZWBAUgMIg4TNjQHCcfq4NIP124B0W5MnnZDA4h2EzK1G0G0GxGlPa0oNQXN9SR5Hot+c4h+QzL1w7xPXOhh0W+ML/hiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkDate = _t, Name = _t, StartDate = _t, Worktype = _t, ShiftEnd = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkDate", type date}, {"Name", type text}, {"StartDate", type text}, {"Worktype", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "End Task", each try if #"Added Index"{[Index]}[Name] = #"Added Index"{[Index] - 1}[Name] then #"Added Index"{[Index]}[StartDate] else null otherwise null) in #"Added Custom"