locked
Work out week number between list of dates RRS feed

  • Question

  • Hi,

    I have a list of dates ranging from 06-07-17 to 06-07-18 and I want too add a field for number for each week starting at 1 for 06-07-17 and ending with 53 on 06/07/18.

    Any advice would be appreciated.

    Cheers,

    Jonathon

    Friday, July 6, 2018 3:26 PM

Answers

All replies

    • Marked as answer by j_dewhirst Tuesday, July 10, 2018 3:25 PM
    Friday, July 6, 2018 9:54 PM
  • Thanks Herbert.

    How would I get the first and last date from movement_date field in the following query to act as inputs into the Lims query?

    let
        Source = Sql.Database("xxxxxxx", "xxxxxx"),
        scheme_stkhstm = Source{[Schema="scheme",Item="stkhstm"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(scheme_stkhstm,{{"dated", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([dated], 365)),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([product], "F") or Text.StartsWith([product], "C")),
        #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"movement_date", type date}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"warehouse", "product", "transaction_type", "movement_date", "movement_quantity", "movement_cost"}),
        #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"warehouse", Text.Trim, type text}, {"product", Text.Trim, type text}, {"transaction_type", Text.Trim, type text}}),
        #"Added Custom" = Table.AddColumn(#"Trimmed Text", "ext_cost", each [movement_quantity]*[movement_cost]),
        #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"ext_cost", type number}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"movement_cost"}),
        #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([transaction_type] = "ADJ" or [transaction_type] = "DKIT" or [transaction_type] = "SALE" or [transaction_type] = "W/O"))
    in
        #"Filtered Rows2"
    

    Tuesday, July 10, 2018 3:34 PM