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
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
Icon for Super User rankSuper 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