Identify values before, close to and after today

[billybazinga]

Hi clever people,

I am hoping you can help me with a powerquery issue where I can't find a logical solution, let alone formulas.

I have a dirbusement schedule that looks like the image below. I would like to have an additional column that would identify the most recent payment run based on today's date, the current one and the future one, with values 1, 2 and 3 (or anything else that can help with filtering)

For example, assuming today is 16 March, I woud have the following values attributed

Value 1 -> Weekly & IB [10-Mar-22] (most recent)
Value 2 -> Weekly [16-Mar-22] (current)
Value 3 -> Weekly [23-Mar-22] (future)

If we switch to today as 24 March, I would have the following values attributed:
Value 1 -> Weekly [23-Mar-22]
Value 2 -> EOM [29-Mar-22]
Value 3 -> EOM [06-Apr-22]

Does any of you have a clever way to figure this out?

Thanks in advance,
BBK

[billybazinga]

Athough I can't share the data in a pbi file, I can at least upload in a table format

Date	Pay Run	Processed On	Processed On Order	Pay Run Date
29/03/2022	EOM	EOM [29-Mar-22]	167	29/03/2022
8/04/2022	IB	IB [08-Apr-22]	169	8/04/2022
30/03/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
31/03/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
1/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
2/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
3/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
4/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
5/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
1/03/2022	Out of Cycle	EOMv1 [25-Feb-22]	162	25/02/2022
9/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
10/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
11/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
12/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
11/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
12/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
13/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
14/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
15/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
3/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
4/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
5/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
6/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
7/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
8/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
9/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
7/04/2022	Out of Cycle	Weekly [06-Apr-22]	168	6/04/2022
14/04/2022	Out of Cycle	Weekly [13-Apr-22]	170	13/04/2022
15/04/2022	Out of Cycle	Weekly [13-Apr-22]	170	13/04/2022
17/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
18/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
19/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
20/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
21/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
22/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
24/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
25/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
26/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
27/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
28/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
2/03/2022	Weekly	Weekly [02-Mar-22]	163	2/03/2022
6/04/2022	Weekly	Weekly [06-Apr-22]	168	6/04/2022
13/04/2022	Weekly	Weekly [13-Apr-22]	170	13/04/2022
16/03/2022	Weekly	Weekly [16-Mar-22]	165	16/03/2022
23/03/2022	Weekly	Weekly [23-Mar-22]	166	23/03/2022
10/03/2022	Weekly & IB	Weekly & IB [10-Mar-22]	164	10/03/2022

[billybazinga]

Hi @BA_Pete 

Thank you for offering to help. Do you have a suggestion to share the file? I have tried to attach it but it doesn't seem to work and I dont have access to a dropbox type solution. Is there a feature I can't see?

[BA_Pete]

Hi @billybazinga ,

You could filter your table down to around 500 rows, copy the whole table, then paste it into Enter Data. This should be under the cell limit as 500 rows * 5 columns is only 2,500 cells. Try and keep rows that are around today's date if you can.

Once you have that table, just copy the M out of Advanced Editor and paste it in a code window here ( </> button).

Hope that makes sense.

Pete

[billybazinga]

Hi @BA_Pete 

Thank you for the guidance.

Hopefully the below works

let
    Source = #"Payment Dates",
    #"Removed Columns" = Table.RemoveColumns(Source,{"Index"})
in
    #"Removed Columns"

Date	Pay Run	Processed On	Processed On Order	Pay Run Date
1/03/2022	Out of Cycle	EOMv1 [25-Feb-22]	162	25/02/2022
2/03/2022	Weekly	Weekly [02-Mar-22]	163	2/03/2022
3/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
4/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
5/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
6/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
7/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
8/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
9/03/2022	Out of Cycle	Weekly [02-Mar-22]	163	2/03/2022
10/03/2022	Weekly & IB	Weekly & IB [10-Mar-22]	164	10/03/2022
11/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
12/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
13/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
14/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
15/03/2022	Out of Cycle	Weekly & IB [10-Mar-22]	164	10/03/2022
16/03/2022	Weekly	Weekly [16-Mar-22]	165	16/03/2022
17/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
18/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
19/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
20/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
21/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
22/03/2022	Out of Cycle	Weekly [16-Mar-22]	165	16/03/2022
23/03/2022	Weekly	Weekly [23-Mar-22]	166	23/03/2022
24/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
25/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
26/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
27/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
28/03/2022	Out of Cycle	Weekly [23-Mar-22]	166	23/03/2022
29/03/2022	EOM	EOM [29-Mar-22]	167	29/03/2022
30/03/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
31/03/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
1/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
2/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
3/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
4/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
5/04/2022	Out of Cycle	EOM [29-Mar-22]	167	29/03/2022
6/04/2022	Weekly	Weekly [06-Apr-22]	168	6/04/2022
7/04/2022	Out of Cycle	Weekly [06-Apr-22]	168	6/04/2022
8/04/2022	IB	IB [08-Apr-22]	169	8/04/2022
9/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
10/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
11/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
12/04/2022	Out of Cycle	IB [08-Apr-22]	169	8/04/2022
13/04/2022	Weekly	Weekly [13-Apr-22]	170	13/04/2022
14/04/2022	Out of Cycle	Weekly [13-Apr-22]	170	13/04/2022
15/04/2022	Out of Cycle	Weekly [13-Apr-22]	170	13/04/2022

[BA_Pete]

Hi @billybazinga ,

Yes, I should be able to work with that.

For future reference, I was referring to copying your table and pasting it in here:

Which generates code like this in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdZBS8MwGAbgvxJ6XumXL23aHlUUPIwdPcQdVOrFgSJO2L832aRrmE3emlw+tpL36aDvkhpTyIpUxcRcrIrN/ku8v4qbw8tusF9vN+tvKQw35d3wXDJv7TWp3UJuKuJTarsyBU+Mh2F42x3GD8IQl+unzzGuXHxc79Jq/hfARp3BaDIYOoPRZjC6DEafwZB0UQ3xuCdiLe6v/7gkjCSPrN0k3wxU9l8eZ/bihV7mxcu9zIsXfZmnQ39/qb18c5x+Hig8oACVBxSg9HGFKYsS7zmixNuNKCr0jFl5ee22AeXngQ0aUIAtGlCATRpQgNYCCtBaQJm21p7ap2mTvZdsXbL3kirQVJQIv0EghBXqNIGTBZUs1MlCkyzoiXD5JqbLq49zvLPzvP73zWPu/rDRTYzjEe8OEeq8VG9n56X6+TtDeXckpQGBDmJAoIIYoELPTqppvCU3lZ8P9G+BEuggqGx/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Pay Run" = _t, #"Processed On" = _t, #"Processed On Order" = _t, #"Pay Run Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}})
in
    chgTypes

Which can be pasted straight back into anyone else's Avanced Editor to reproduce the table perfectly.

The Enter Data function accepts 3,000 cells of data as standard (or 3,000 rows if you're smart with CSV data), so you can share pretty large tables on here this way quicky and tidily.

Pete

[BA_Pete]

Hi @billybazinga ,

Paste this over the default code in a new blank query to see if this works for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdZBS8MwGAbgvxJ6XumXL23aHlUUPIwdPcQdVOrFgSJO2L832aRrmE3emlw+tpL36aDvkhpTyIpUxcRcrIrN/ku8v4qbw8tusF9vN+tvKQw35d3wXDJv7TWp3UJuKuJTarsyBU+Mh2F42x3GD8IQl+unzzGuXHxc79Jq/hfARp3BaDIYOoPRZjC6DEafwZB0UQ3xuCdiLe6v/7gkjCSPrN0k3wxU9l8eZ/bihV7mxcu9zIsXfZmnQ39/qb18c5x+Hig8oACVBxSg9HGFKYsS7zmixNuNKCr0jFl5ee22AeXngQ0aUIAtGlCATRpQgNYCCtBaQJm21p7ap2mTvZdsXbL3kirQVJQIv0EghBXqNIGTBZUs1MlCkyzoiXD5JqbLq49zvLPzvP73zWPu/rDRTYzjEe8OEeq8VG9n56X6+TtDeXckpQGBDmJAoIIYoELPTqppvCU3lZ8P9G+BEuggqGx/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Pay Run" = _t, #"Processed On" = _t, #"Processed On Order" = _t, #"Pay Run Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}}),
    Date.Today = Date.From(DateTime.LocalNow()),
    Order.Today = Table.SelectRows(chgTypes, each [Date] = Date.Today){0}[Processed On Order],
    addOutput =
    Table.AddColumn(chgTypes, "output", each
        if [Processed On Order] = Order.Today then "Current"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) - 1 then "Most Recent"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) + 1 then "Future"
        else null
    )
in
    addOutput

If you want to test how it works with different dates, just change the 'Date.Today' step to something like this (don't forget to change it back!):

Date.Today = #date(2022, 03, 24)

Pete

[billybazinga]

Hi @BA_Pete 

Thank you very much for this. I have to admit I don't quite understand what you did, especially the piece around 

Order.Today = Table.SelectRows(chgTypes, each [Date] = Date.Today){0}[Processed On Order],

which seems to be key here!!

So I probably misexplained something which makes the data not quite line up. With your example and today being the 17th of March, I have:

Most Recent: Weekly & IB [10-Mar-22]

Current: Weekly [16-Mar-22]

Future: Weekly [23-Mar-22]

It should actually be:

Most recent: Weekly [16-Mar-22]

Current: Weekly [23-Mar-22]

Future: EOM [29-Mar-22]

Any adjustment you suggest, as well as a spot where I can read and understand the functions you used?

[BA_Pete · Accepted solution]

Hi @billybazinga ,

Try this updated code and see if it works better for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdZBS8MwGAbgvxJ6XumXL23aHlUUPIwdPcQdVOrFgSJO2L832aRrmE3emlw+tpL36aDvkhpTyIpUxcRcrIrN/ku8v4qbw8tusF9vN+tvKQw35d3wXDJv7TWp3UJuKuJTarsyBU+Mh2F42x3GD8IQl+unzzGuXHxc79Jq/hfARp3BaDIYOoPRZjC6DEafwZB0UQ3xuCdiLe6v/7gkjCSPrN0k3wxU9l8eZ/bihV7mxcu9zIsXfZmnQ39/qb18c5x+Hig8oACVBxSg9HGFKYsS7zmixNuNKCr0jFl5ee22AeXngQ0aUIAtGlCATRpQgNYCCtBaQJm21p7ap2mTvZdsXbL3kirQVJQIv0EghBXqNIGTBZUs1MlCkyzoiXD5JqbLq49zvLPzvP73zWPu/rDRTYzjEe8OEeq8VG9n56X6+TtDeXckpQGBDmJAoIIYoELPTqppvCU3lZ8P9G+BEuggqGx/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Pay Run" = _t, #"Processed On" = _t, #"Processed On Order" = _t, #"Pay Run Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}}),
    Date.Today = Date.From(DateTime.LocalNow()),
    Order.Today = Table.SelectRows(chgTypes, each [Date] = Date.Today){0}[Processed On Order],
    addOutput =
    Table.AddColumn(chgTypes, "output", each
        if [Processed On Order] = Order.Today then "Most Recent"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) + 1 then "Current"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) + 2 then "Future"
        else null
    )
in
    addOutput

The Date.Today and Order.Today steps are just standalone steps that perform specific functions outside of changing the main table.

Date.Today = Declares the value of today's date to make subsequent references quicker and tidier to write, so I can just write 'Date.Today' in code, instead of 'Date.From(DateTime.LocalNow())' every time I want to use today's date.

Order.Today = Selects the value of [Processed On Order] at today's date to use as a comparison to help allocate the three different ouput statuses. The Table.SelectRows bit gets any rows that have today's [Date] on them, the '{0}' bit gets the first row out of these rows, and the [Processed On Order] bit at the end selects just that column, so it zeroes in on just a single cell value that can be used in other calculations.

Pete

[billybazinga]

Hi @BA_Pete 

Thank you so much for this, it works brilliantly and your explanation tought me a few things. One final question: when I try to copy your code in my actual model, the let _t doesn't seem to be recognised (red underlined - I assume because the source is different to the one provided to you). Any thoughts on what I need to adjust?

let
    Source = #"Payment Dates",
    
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Pay Run" = _t, #"Processed On" = _t, #"Processed On Order" = _t, #"Pay Run Date" = _t]), 

chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}}), Date.Today = Date.From(DateTime.LocalNow()), 

Order.Today = Table.SelectRows(chgTypes, each [Date] = Date.Today){0}[Processed On Order], 

addOutput = Table.AddColumn(chgTypes, "output", each if [Processed On Order] = Order.Today then "Most Recent" else if Number.From([Processed On Order]) = Number.From(Order.Today) + 1 then "Current" else if Number.From([Processed On Order]) = Number.From(Order.Today) + 2 then "Future" else null ) 

in addOutput

[BA_Pete]

Hi @billybazinga ,

This part of the code is just defining the JSON table characterstics. Swap everything before my 'chgTypes' step for your source code, something like this:

let
    Source = #"Payment Dates",
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}}),
...
...
in
    addOutput

Pete

[BA_Pete]

Hi @billybazinga ,

Are you able to share a copyable version of your example data please?

I think this should be doable using combinations of List.Min/List.Max on [Pay Run Date] while excluding dates before/after today, but I'd like to ba able to test first.

Pete