Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am looking for a way to use Power Query to identify the Max (or Latest) Date in a column that has dates from multiple years. I will use this for end of year reporting year-over-year in a dynamic fashion (new max dates are added each new year). I've provided an image below of what I would like to have as a result; I currently have Columns A, B and C, and I am looking for how to create Column D. 🙂
Thanks so much for your help!
Michael
Solved! Go to Solution.
Hi @basel777 ,
You can use the below 2 methods to achieve it:
1. Use "Group by" function to group the data based on the field DataCollectedYear and get the max date of per year in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczJDcAgDETRXnxG8hIlztRi0X8b4UBguFnfo1cltzQJ81ddfZ7SW8nz9zx7rn3odXymhC2BJWwJLIElkBS2pDCSRk/q/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HeadCount = _t, DateCollected = _t, DateCollectedYear = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"HeadCount", Int64.Type}, {"DateCollected", type date}, {"DateCollectedYear", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DateCollectedYear"}, {{"MaxDateByYear", each List.Max([DateCollected]), type date}, {"Details", each _, type table [HeadCount=number, DateCollected=date, DateCollectedYear=number]}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Details", "DateCollectedYear", "MaxDateByYear"}),
#"Expanded Details" = Table.ExpandTableColumn(#"Reordered Columns", "Details", {"HeadCount", "DateCollected"}, {"HeadCount", "DateCollected"})
in
#"Expanded Details"
2. Create a measure as below with DAX
Measure =
CALCULATE (
MAX ( 'Table'[DateCollected] ),
FILTER (
ALL ( 'Table' ),
'Table'[DateCollectedYear] = MAX ( 'Table'[DateCollectedYear] )
)
)
Best Regards
Rena
Hi @basel777 ,
You can use the below 2 methods to achieve it:
1. Use "Group by" function to group the data based on the field DataCollectedYear and get the max date of per year in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczJDcAgDETRXnxG8hIlztRi0X8b4UBguFnfo1cltzQJ81ddfZ7SW8nz9zx7rn3odXymhC2BJWwJLIElkBS2pDCSRk/q/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HeadCount = _t, DateCollected = _t, DateCollectedYear = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"HeadCount", Int64.Type}, {"DateCollected", type date}, {"DateCollectedYear", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DateCollectedYear"}, {{"MaxDateByYear", each List.Max([DateCollected]), type date}, {"Details", each _, type table [HeadCount=number, DateCollected=date, DateCollectedYear=number]}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Details", "DateCollectedYear", "MaxDateByYear"}),
#"Expanded Details" = Table.ExpandTableColumn(#"Reordered Columns", "Details", {"HeadCount", "DateCollected"}, {"HeadCount", "DateCollected"})
in
#"Expanded Details"
2. Create a measure as below with DAX
Measure =
CALCULATE (
MAX ( 'Table'[DateCollected] ),
FILTER (
ALL ( 'Table' ),
'Table'[DateCollectedYear] = MAX ( 'Table'[DateCollectedYear] )
)
)
Best Regards
Rena
Post data not a picture for faster answers.
Looks like you can 'Group By' -advanced on DateCollectedYear.
Use 2 aggregations:
Choose Max on DateCollected.
Use All Rows for the next one.
Expand 'All' returning HeadCount and DateCollected
Will do. Was my first post, so still working out the process. Thank you!
@v-yiruan-msft and @HotChilli, Thank you for your help! Between your two answers, I was able to identify the max dates for each year! 🙂
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |