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] <= D...
Need Help
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

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.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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)

     

     

     

     

Related content