cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KyleD87
New Member

Power Query Date Filtering M Language

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.

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @KyleD87 ,

 

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:

v-kelly-msft_0-1623128962804.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @KyleD87 ,

 

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:

v-kelly-msft_0-1623128962804.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

Nathaniel_C
Super User I
Super User I

Hi @KyleD87 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





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

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors