Power Query Date Filtering M Language

[Anonymous]

The spreadsheet I am working on is to transform data about products and prices based on dates in the next 10 years give or take.

The highlighted text in the M language is the part that is wrong. Specifically, "dLimit". In the leftmost column of my spreadsheet dates are listed (Y-Axis) with products on the X-Axis, and prices listed based on the date and product.

I want a code to return values from the EARLIEST DATE LISTED until 5 years from then. The start date could be June 2021, March 2021, or any date, so in each case, the dates should go until June 2026, March 2026, or other. I have tried using the date filter "InTheNextNDays" but that skips the current day or any ones before it, so this won't work if the data being used is old.

Is there a correction to this formula, or a different formula that would return dates for 5 years after the starting date in the spreadsheet?

The spreadsheet's format is:

Y-Axis: Dates

X-Axis: Products

Values: Prices and Net Change from the last price

https://imgur.com/gallery/VgJG5J5

Error message "Token Then expected" is appearing under "dLimit". Code may be wrong otherwise too, and any suggestions are appreciated.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First create a query parameter as start date;

Then create a funtion as below:

let
    Source = (number as number) => let
    
        #"End date"=Date.AddYears(startdate,number)
    in
        #"End date"
in
    Source
in
    Source

Then use below M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DYNQFATRXogtwd4PcGtB9N+GHTxLs+FokvM8m3btceje3s+zBSMZxWjGybgYN2MYOqzMIEOsNeQNeUPekDfkDXlD3pA3xhvjjfEGvDjAW5GMYjTjZFyMmzGMxfuXGWSItUSeyBN5Ik/kiTyRJ/JkPBlPxhN5QV6QF+QFeUFekBfkBXlhvDBeGC/IS/KSvCQvyUvykrwkL8lL46Xx0nhJXpFX5BV5RV6RV+QVeUVeGa+MV8Yr8pq8Jq/Ja/KavCavyWvy2nhtvDber94v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date]<=dateadd(5) and [Date]>=startdate then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

Filter out the results which are null and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Nathaniel_C]

Hi @Anonymous just glancing at the m language code, I see you have an If(  - so you must include a then before the end.  That would be the value if the if evaluates to true.  Having a result for false is optional.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel