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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'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