none
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
    Moderator

All replies