locked
Generating serial number for dates table in PowerQuery RRS feed

  • Question

  • Hi,

    I'm trying to generate a serial number for a sorted unique dates table, using PowerQuery(M).

    The number of the first date after the start of financial year (1/7) should be zero, and then increase until the end of the financial year.then it should be zeroed again.

    The dates are weeks. We tried to solve this using Date.WeekOfYear function, but it's not good enough because we have dates that fall in the same week and then get the same week index.

    I've started to write the solution in M and it's preatty comlicated...Does anyone has other suggestions?

    This is how the index should be:

    ...  
    8/08/2014 12:00:00 AM 6
    31/07/2014 12:00:00  5
    25/07/2014 12:00:00  4
    18/07/2014 12:00:00  3
    11/07/2014 12:00:00  2
    4/07/2014 12:00:00 AM 1
    27/06/2014 12:00:00  53
    20/06/2014 12:00:00  52
    13/06/2014 12:00:00  51
    6/06/2014 12:00:00 50

    Thank you!!!



    • Edited by MorinRod Friday, February 19, 2016 1:19 AM
    Friday, February 19, 2016 1:17 AM

Answers

  • Thanks bozza, so this is one way to go then:

    let
        Source = Excel.CurrentWorkbook(){[Name="ModifiedDates"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "FiscalYear", each if Date.Month([Value])<7 then Date.Year([Value]) else Date.Year([Value])+1),
        #"Grouped Rows" = Table.Group(#"Added Custom", {"FiscalYear"}, {{"All", each Table.AddIndexColumn(_, "week", 1,1), type table}}),
        #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "week"}, {"Value", "week"})
    in
        #"Expanded All"

    It starts with the week number of 1 (which meets the example given but not the worded request). So if you want to start at 0, just change the index start in the #"Grouped Rows"-step to: (_, "week", 0,1).


    Imke Feldmann TheBIccountant.com

    • Marked as answer by MorinRod Monday, February 22, 2016 1:29 AM
    Sunday, February 21, 2016 1:22 PM

All replies

  • If this should start with 0 at the 1st July, wouldn't the 8th July be the first day with a 1? In your exemple the 4th already has a 1.

    For how long should the 0 stay?


    Imke Feldmann TheBIccountant.com

    Friday, February 19, 2016 7:41 AM
  • <I have removed my submission. I had not read the question properly. When OP has  answered Imke's question, let's revisit. >
    Saturday, February 20, 2016 7:05 AM
  • Saturday, February 20, 2016 8:08 AM
  • Hi Imke,

    The question is about setting index for a sorted list of dates BUT in a context of Financial Year (starts on 1st of Julay in Australia)

    3/7/14 => 1

    4/7/14 => 2

    8/7/14 => 3

    2/7/15 => 1

    13/7/15 => 2

    Sunday, February 21, 2016 10:42 AM
  • Thanks bozza, so this is one way to go then:

    let
        Source = Excel.CurrentWorkbook(){[Name="ModifiedDates"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "FiscalYear", each if Date.Month([Value])<7 then Date.Year([Value]) else Date.Year([Value])+1),
        #"Grouped Rows" = Table.Group(#"Added Custom", {"FiscalYear"}, {{"All", each Table.AddIndexColumn(_, "week", 1,1), type table}}),
        #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "week"}, {"Value", "week"})
    in
        #"Expanded All"

    It starts with the week number of 1 (which meets the example given but not the worded request). So if you want to start at 0, just change the index start in the #"Grouped Rows"-step to: (_, "week", 0,1).


    Imke Feldmann TheBIccountant.com

    • Marked as answer by MorinRod Monday, February 22, 2016 1:29 AM
    Sunday, February 21, 2016 1:22 PM
  •  Thanks Imke, it works!
    • Edited by MorinRod Monday, February 22, 2016 1:29 AM
    Sunday, February 21, 2016 11:34 PM