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.
Hello,
In Power Query I need to filter my table by the last 2 dates that are available. For example, I have a column "Date of End" with 3 dates :
18/08/2021
04/08/2021
03/08/2021
And I need to see only the rows for the last 2 dates that are :
18/08/2021
04/08/2021
My table will be growing everyday but the column "Date of end" is not regular (it's not necessarily every 2 weeks).
Thank you for your help!
Solved! Go to Solution.
Hi @Anonymous ,
In power query,you could use below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31TcyMDJUitUBcQwtkHkmyBxjKCcWAA==", 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}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"End of date", each List.Max([Date]), type nullable date}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.MaxN(#"Grouped Rows","End of date",2)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"End of date"}, {"Custom.End of date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Date]=[Custom.End of date] then [Date] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Custom] <> null and [Custom] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"End of date", "Custom.End of date"})
in
#"Removed Columns"
And you will see:
You could also realize it using dax expression:
Create a column as below:
rank = IF( RANKX('Table (2)','Table (2)'[Date],,DESC,Dense)<=2,'Table (2)'[Date],BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Anonymous ,
In power query,you could use below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31TcyMDJUitUBcQwtkHkmyBxjKCcWAA==", 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}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"End of date", each List.Max([Date]), type nullable date}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.MaxN(#"Grouped Rows","End of date",2)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"End of date"}, {"Custom.End of date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Date]=[Custom.End of date] then [Date] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Custom] <> null and [Custom] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"End of date", "Custom.End of date"})
in
#"Removed Columns"
And you will see:
You could also realize it using dax expression:
Create a column as below:
rank = IF( RANKX('Table (2)','Table (2)'[Date],,DESC,Dense)<=2,'Table (2)'[Date],BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
This saved me so much time - thanks.
Simply Table.MaxN() does the trick,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00Dew0DcyMDJUitWJVkpJgYghCSUBRUyQBdLQBZycQSYZIQsVg4yBC8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Other = _t, #"Date of end" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of end", type date}}, "de"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date of end"}, {{"ar", each _, type table [Other=nullable text, Date of end=nullable date]}}),
MaxN = Table.MaxN(#"Grouped Rows", "Date of end", 2),
#"Expanded ar" = Table.ExpandTableColumn(MaxN, "ar", {"Other"}, {"Other"})
in
#"Expanded ar"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous , based on what I got, create a column like this and filter for 1
if [Date of End] <= List.Max([Date of End]) && [Date of End] >= (List.Max([Date of End]) -#duration(2,0,0,0)) then 1 else 0
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |