# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Excel.Workbook]] [[Table.Sort]] [[Table.Group]] [[Table.AddColumn]] [[Table.RemoveLastN]] [[Date.AddDays]] [[Table.ToColumns]] [[Table.ColumnNames ]] [[Table.FromColumns]] [[Table.Combine]] [[Table.TransformColumnTypes]] [[Duration.Days]] [[DateTime.Date]] [[Excel.CurrentWorkbook]] **Date** : 21-March-2025 # Description - There are many ways to refer to the previous row in dataset in power query. in this tutorial i demonstrate a specific use case where you can kind of previous row hack using lists. # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/1mJaN1t9mwk?si=W9177WA2bvmcqPug" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe> [GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_PreviousRowsEmployeeTimeLine_Video.xlsx) # M-Query ```mquery let Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Event", type text}, {"StartDate", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Employee", Order.Ascending}, {"StartDate", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Employee"}, {{"All", each _, type table [Employee=nullable text, Event=nullable text, StartDate=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let LastDate = List.Transform( {null} & Table.RemoveLastN([All], 1)[StartDate], each Date.AddDays(_, -1)), AllColumList = Table.ToColumns([All]) & {LastDate}, Headers = Table.ColumnNames([All]) & {"EndDate"} in Table.FromColumns(AllColumList, Headers)), Custom1 = Table.Combine(#"Added Custom"[Custom]), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"EndDate", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NrOfDays", each if [EndDate] <> null then Duration.Days([EndDate] - [StartDate]) else Duration.Days(DateTime.Date( DateTime.FixedLocalNow()) - [StartDate])) in #"Added Custom1" ``` ## Optional Tags **Tags** : **Technique** : [[Nested Let]] [[Working with lists]] [[Custom Function]]