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.

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper 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