Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Identify values before, close to and after today

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....
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

Hi Anonymous ,

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"

 

 

DatePay RunProcessed OnProcessed On OrderPay Run Date
1/03/2022Out of CycleEOMv1 [25-Feb-22]16225/02/2022
2/03/2022WeeklyWeekly [02-Mar-22]1632/03/2022
3/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
4/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
5/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
6/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
7/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
8/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
9/03/2022Out of CycleWeekly [02-Mar-22]1632/03/2022
10/03/2022Weekly & IBWeekly & IB [10-Mar-22]16410/03/2022
11/03/2022Out of CycleWeekly & IB [10-Mar-22]16410/03/2022
12/03/2022Out of CycleWeekly & IB [10-Mar-22]16410/03/2022
13/03/2022Out of CycleWeekly & IB [10-Mar-22]16410/03/2022
14/03/2022Out of CycleWeekly & IB [10-Mar-22]16410/03/2022
15/03/2022Out of CycleWeekly & IB [10-Mar-22]16410/03/2022
16/03/2022WeeklyWeekly [16-Mar-22]16516/03/2022
17/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
18/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
19/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
20/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
21/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
22/03/2022Out of CycleWeekly [16-Mar-22]16516/03/2022
23/03/2022WeeklyWeekly [23-Mar-22]16623/03/2022
24/03/2022Out of CycleWeekly [23-Mar-22]16623/03/2022
25/03/2022Out of CycleWeekly [23-Mar-22]16623/03/2022
26/03/2022Out of CycleWeekly [23-Mar-22]16623/03/2022
27/03/2022Out of CycleWeekly [23-Mar-22]16623/03/2022
28/03/2022Out of CycleWeekly [23-Mar-22]16623/03/2022
29/03/2022EOMEOM [29-Mar-22]16729/03/2022
30/03/2022Out of CycleEOM [29-Mar-22]16729/03/2022
31/03/2022Out of CycleEOM [29-Mar-22]16729/03/2022
1/04/2022Out of CycleEOM [29-Mar-22]16729/03/2022
2/04/2022Out of CycleEOM [29-Mar-22]16729/03/2022
3/04/2022Out of CycleEOM [29-Mar-22]16729/03/2022
4/04/2022Out of CycleEOM [29-Mar-22]16729/03/2022
5/04/2022Out of CycleEOM [29-Mar-22]16729/03/2022
6/04/2022WeeklyWeekly [06-Apr-22]1686/04/2022
7/04/2022Out of CycleWeekly [06-Apr-22]1686/04/2022
8/04/2022IBIB [08-Apr-22]1698/04/2022
9/04/2022Out of CycleIB [08-Apr-22]1698/04/2022
10/04/2022Out of CycleIB [08-Apr-22]1698/04/2022
11/04/2022Out of CycleIB [08-Apr-22]1698/04/2022
12/04/2022Out of CycleIB [08-Apr-22]1698/04/2022
13/04/2022WeeklyWeekly [13-Apr-22]17013/04/2022
14/04/2022Out of CycleWeekly [13-Apr-22]17013/04/2022
15/04/2022Out of CycleWeekly [13-Apr-22]17013/04/2022
  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    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's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

      Hi Anonymous ,

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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?