Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
basel777
Advocate II
Advocate II

Find The Max Date by Year in a Column Containing Multiple Years in Power Query

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

 

Desired Data Structure.png

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

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"

Use group by function in Power Query EditorUse group by function in Power Query Editor

2. Create a measure as below with DAX

Measure = 
CALCULATE (
    MAX ( 'Table'[DateCollected] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[DateCollectedYear] = MAX ( 'Table'[DateCollectedYear] )
    )
)

Create measure with DAXCreate measure with DAX

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

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"

Use group by function in Power Query EditorUse group by function in Power Query Editor

2. Create a measure as below with DAX

Measure = 
CALCULATE (
    MAX ( 'Table'[DateCollected] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[DateCollectedYear] = MAX ( 'Table'[DateCollectedYear] )
    )
)

Create measure with DAXCreate measure with DAX

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HotChilli
Super User
Super User

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! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.