Forum Discussion
Solved
Compare values from previous rows
Hello guys! I have a problem...hope you can help me. We have a process in which every step is inserted in the database. The goal is to know how many days the process stuck in a particular status,...
Solution in Power Query (M):
let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter), #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"}) in #"Removed Columns"
Well, that looks like a job for EARLIER but to be honest, I'm not that very good with EARLIER.
Solution in Power Query (M):
let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
#"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}),
#"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"})
in
#"Removed Columns"MarcelBeug, thank you so much!
This is what I needed, I mean, I'm still trying to really understand what you did, but this is amazing!
Thanks again :)
In general if you add 2 indices, one 0-based and the other 1-based, and use these to merge the table with itself, you get in the nested tables the values:
- from the previous row if you merge the 0-Index (first table) with the 1-index (second table)
- from the next row if you merge the 1-index (first table) with the 0-index (second table)
Notice: if you adjust the generated code from the merge step and adjust "NewColumn" to "Previous" or "Next", then you have clear field names if you expand the required fields from this column and make sure to use the current column name as prefix.
