Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

the date operation failed because the resulting value falls outside the range of allowed values

Hello, 

 

I used the follow power query not issue:

 

= Table.SelectRows(Intercept, each [Name]>= #date( Date.Year(Date.From("2023/12/31")),Date.Month(Date.From("2023/12/31"))-2,1) and [Name] <= Date.From("2023/12/31") ) 

 

but show 'Expression,Error: the date operation failed because the resulting value falls outside the range of allowed values' if change from '2023/12/31' to '2024/01/01' .

 

= Table.SelectRows(Intercept, each [Name]>= #date( Date.Year(Date.From("2024/01/01")),Date.Month(Date.From("2024/01/01"))-2,1) and [Name] <= Date.From("2024/01/01") )

 

How can I fix it?

 

Thanks !

Need Help

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      thanks for your reply.

      let me explain the whole picture.

      my user share lots of excel file in sharepoint, the file name format like ' ****************_YYYYMMDD.xlsx'

      I need to consolidate the last 3 month's.

      for example,

      For January, include October, November and December 's file.

      For Febuary, include November ,December and January 's file. thanks!

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Please provide sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Your problem is here:

    Date.Month(Date.From("2024/01/01"))-2

     

    If the month is January, the month number is 1. 1-2 = -1 and -1 is not a valid month argument for #date(y,m,d).

     

    You can correct this using Number.Mod.

     

    But your logic makes no sense to me either. It looks like you are testing for [Name] >= "2024/11/01"  and [Name] <= "2024/01/01" which will always return nothing.

     

    Possibly you mean [Name] >= "2023/11/01" in which case you can create that date using Date.AddMonths

     

    Perhaps, (and I show using two ways of defining a date -- use either)

     

      [Name]>= Date.AddMonths(#date(2024,1,1),-2) and [Name]<=Date.From("2024/01/01"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      thanks for your reply.

      let me explain the whole picture.

      my user share lots of excel file in sharepoint, the file name format like ' ****************_YYYYMMDD.xlsx'

      I need to consolidate the last 3 month's.

      for example,

      For January, include October, November and December 's file.

      For Febuary, include November ,December and January 's file. thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      for example, there are lots excel file in share point, format is ***_YYYYMMDD.xlsx.

      ABC_20230901.xlsx

      ABC_20230911.xlsx

      ABC_20230921.xlsx

      ABC_20230923.xlsx

      ABC_20231001.xlsx

      ABC_20231011.xlsx

      ABC_20231021.xlsx

      ABC_20231024.xlsx

      ABC_20231031.xlsx

      ABC_20231104.xlsx

      ABC_20231114.xlsx

      ABC_20231124.xlsx

      ABC_20231128.xlsx

      ABC_20231204.xlsx

      ABC_20231214.xlsx

      ABC_20231224.xlsx

      ABC_20240104.xlsx

      ABC_20240114.xlsx

      ABC_20240124.xlsx

      ABC_20240131.xlsx

      I need to consolidate the last 3 month's file.

      for January, need consolidate from ABC_20231001.xlsx to ABC_20231224.xlsx (from October to December);

      for Febuary, need consolidate from ABC_20231104.xlsx to ABC_20240131.xlsx (from November to January)c.

      thanks.

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Files:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xCoAwEETRu6QWmdmk0FI9RghewM4mxzeVCP72MXx2a03bfpyhyFrluV93T236qlGDNf/Voq5FXYu6Qwtppq1FWxsVu44FNLAb2A3oFtFlQ6EwlAvvx+0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [File = _t])
in
    Source

         

        Months:

         

        let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjTSN9Q3MjAyVorVAfIgHBMwxwiZYwznxAIA", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Month = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Month", type date}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    (k) =>
      Table.SelectRows(
        Files, 
        each Text.Range([File], 4, 8)
          >= Date.ToText(Date.AddMonths(k[Month], - 3), [Format = "yyyyMMdd"])
            and Text.Range([File], 4, 8)
          < Date.ToText(k[Month], [Format = "yyyyMMdd"])
      )
  ), 
  #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"File"}, {"File"})
in
  #"Expanded Custom"

         

        How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Related content