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

[Anonymous]

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 !

[ronrsnfld]

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]

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.

[ronrsnfld]

You can filter that table using this code snippet:

//#"Previous Step" refers to whatever step in your code contains all of those files

today=DateTime.FixedLocalNow(),
#"Three Months" = Date.From(Date.AddMonths(Date.StartOfMonth(today),-3)),
#"Last Month" = Date.From(Date.EndOfMonth(Date.AddMonths(today,-1))),

#"Last Three Months" = Table.SelectRows(#"Previous Step", each Date.From(Text.SplitAny([Files],"_."){1}) >=#"Three Months" and 
        Date.From(Text.SplitAny([Files],"_."){1}) <=#"Last Month")

List of Files

Filtered List (Nov-Dec-Jan since this is February)

[lbendlin]

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.

[Anonymous]

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]

What are you trying to accomplish with that query?

[Anonymous]

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]

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

[Anonymous]

its sample, thanks.

[lbendlin]

in a usable format please, not as a screenshot

[Anonymous]

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.