Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 !
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"))
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.
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)
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.
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!
What are you trying to accomplish with that query?
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!
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
its sample, thanks.
in a usable format please, not as a screenshot
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
41 | |
23 | |
20 | |
20 | |
13 |
User | Count |
---|---|
157 | |
61 | |
60 | |
28 | |
20 |