Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jasemilly
Helper II
Helper II

use 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

jasemilly_0-1625233346772.png

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"

 

1 ACCEPTED SOLUTION
Payeras_BI
Super User
Super User

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"

Payeras_BI_0-1625235005252.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

1 REPLY 1
Payeras_BI
Super User
Super User

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"

Payeras_BI_0-1625235005252.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors