Time series data in Excel power query. I wish to return a value from the previous day

Question

I have time series data that is being imported to an excel table via power query. The data is daily data for several processes, that are identified as running or not running. So, my data tells me if the process is running - however I wish to identify if the process was running yesterday.

Actually. specifically i wish to identify the processes that shut down each day (ie, yesterday were running but today are not) and the processes that restarted each day (ie, yesterday were not running but today are running). With this in the excel table (that is returned form powerquery), I can pivot in excel to show the processes that shutdown today (or this week, or this month) and the processes that restarted today (or this week or this month).

I hope that makes some sense. below is example data. I'm australian so my dates are d/m/yyyy. To be clear, I've only shown two processes where, but my data has many hundreds, so im hoping for a generic solution.

You can see below that aaa went offline on 3/1/2020, and came back online 6/1/2020. bbb went offline 4/1/2020 and came back 5/1/2020. I'd like to create a column that identifies that fact. In excel it would be easy. I would create a dummy column called isOnlineYesterday to concatenate the process with the date, and then index(isonline,match(procecss+(date-1),isOnlineYesterday,0)). however I wish to do this within powerquery.

I hope my question is clear. Thank you for you cleverness.

Date         Process        IsOnline
1/1/2020     aaa            true
1/1/2020     bbb            true 
2/1/2020     aaa            true
2/1/2020     bbb            true
3/1/2020     aaa            false
3/1/2020     bbb            true
4/1/2020     aaa            false
4/1/2020     bbb            false
5/1/2020     aaa            false
5/1/2020     bbb            true
6/1/2020     aaa            true
6/1/2020     bbb            true

horseyride horseyride · Accepted Answer · 2021-08-05T13:54:36

Easiest way to do this is add a custom column that returns date-1

= Date.AddDays([Date],-1)

then merge the table on itself, matching Yesterday<-->Date and Process<-->Process

Expand the IsOnline column to become IsOnlineYesterday

You can then compare IsOnline to IsOnlineYest to do whatever you want with another custom column

Sample code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process", type text}, {"IsOnline", type logical}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "yesterday", each Date.AddDays([Date],-1), type date),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Process", "yesterday"},#"Added Custom",{"Process", "Date"},"Added Custom",JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"IsOnline"}, {"IsOnlineYest"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"yesterday"})
in #"Removed Columns"

Time series data in Excel power query. I wish to return a value from the previous day

1 Answers