Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have been struggling with this for awhile, I am writting a report looking for stale checks. I dont want to pull all the checks in an issued state, only ones that are 45 days or older.
What is the correct syntax, this isnt working:
""TABLE"".""DATE"" <= Date.AddDays(DateTime.From(DateTime.LocalNow(),-45))
Thanks,
RJ
@rjs2 , You can create a column in M like
if [Date] < Date.AddDays(DateTime.Date(DateTime.LocalNow()),-45) then 1 else 0
I want it in M Query as part of the data pull. I am loading data, and I do not want to load data that are 45 days or sooner. I am trying to keep data volume in my pull as low as possible.
Hi @rjs2 ,
Using below M codes:
"Filtered Rows"== Table.SelectRows(#"Changed Type", each [Date] < =Date.AddDays(DateTime.From(DateTime.LocalNow()),-45))
The complete M codes are as below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RdTBbQMxDETRXnw2kCUpSutaDPffRhKYf+b6T4/CQO/3I37iJ6+Mx/MRj8/zPyQhJxShJizCmtCEnrAJe8IhnAk34Z7wIrwmxCXZRTIWbYgbeEPgQBwiB+YQOlCH2IE7BA/kIXpgD+EDfUqf6FP61Fv7sdGn9Ik+pU/0KX2iT+kTfUqf6FP6RJ/SJ/qSvtCX9PXVp6dTOUXnVE3xeNYUHVM9RbfUnqJT6kzRJXVP0SH1mqI71oVQdyyhpV6ovaEF2xtauL2hBdwbWsi9oQXdG1rYvaEF3htq9N5Qo/eGWm8ufaP3hhq9N9TovaFG7w01em+o0XtD/dWXp7CvKVLtmCLUziky7Zoi0l5T/JX0FIH2niLPPlP0mPtGqMfcQkt9UHsKB7ancHB7Cge4p3CQewoHuqdwsHsKB7yncNB7Cge9p3D/6T+/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Value", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.AddDays(DateTime.From(DateTime.LocalNow()),-45))
in
#"Filtered Rows"
Then you will only load the value which are 45 days or older.
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
But doesnt that still pull any record that is less than 45 days old?
I will repeat. I DO NOT want to pull a record that less than 45 days. I only want to pull (select) the records that are 45 days or greater. I dont want that many records going thru the pipe and then being filtered on. I am pulling from a production database and I am trying to limit how much data I am pulling. I want to add it to the "WHERE" part of the query, not after the data is pulled and loaded.
Also, this is causing refresh to fail thru personal gateway. I want to keep the dataset as small as possible with the smallest data pull i can do.
Am I missing something? All the suggestions are to filter, after the data is pulled.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |