Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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
Proud to be a Super User!