How to get Max date for each group of data for each fiscal year using Power Query ( M )

[Possibilitis]

Hi folks, 

I am quite new to powerQuery, and I would appreciate your assistance. 

I have a table with data from different fiscal year, including the fiscal year - which is not at its end. 

I would like to get the maxdate, at any time, for each fiscal year, using data for each fiscal year. For instance, If today is January 20, then, for this fiscal year, the max date would be 2024-01-20, for fiscal year 2024.  The max date for fiscal year would be whatever last run date for fiscal year 2023. Assuming reg calendar, I can assume it is 2023-12-31. 

The following is the DAX code that works.

MaxDate =

'Table_Name'[Report Run Date] = CALCULATE(MAX('Table_Name'[Report Run Date]),ALLEXCEPT('Table_Name','Table_Name'[Fiscal year]))

I tried the following code -which gives me the table with the max date. The problem is, when I try to load, it adds more data and I don't know why. For instance, if, I have 10 rows in the data, it will load 1000 rows. I don't understand where the other rows come from. 

let
Source = #”Source”,
    // Group by 'Fiscal year' and calculate the maximum 'Report Run Date' within each group
   GroupedTable = Table.Group(Source, {"Fiscal year"}, {{"MaxDate", each List.Max([Report Run Date]), type datetime}}),

   
    // Merge the calculated maximum date back to the original table
    MergedTable = Table.Join(Source, "Fiscal year", GroupedTable, "Fiscal year"),
   
    // Extract the 'MaxDate' column from the merged table
    ResultTable = Table.SelectColumns(MergedTable, {"MaxDate"})
in
    ResultTable

I also tried  just using List.max( #"source" Report Run Date), it gives the max date for ALL THE FISCAL YEARS. NOT for each fiscal year. 

Can someone help with this? Thanks in advance!

Thanks

[Fowmy · Accepted solution]

@Possibilitis 

I created a dummy table to represent your data.

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc6xDcAgDAXRXaixZJsEwyyI/deIi0TE8u9Or7q1irJqqUVvEiXPXY91YAZsAJvZGgOTaO28tGAdmAEbwGa29yWaRLvcpBMLef7NgA1gM5vyZ/sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fiscal Year" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fiscal Year", Int64.Type}, {"Date", type date}},"en-gb"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Fiscal Year"}, {{"Last Date", (t)=>  Table.AddColumn(t,"Latest Date", each List.Max(t[Date] )) }}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Fiscal Year"}),
    #"Expanded Last Date" = Table.ExpandTableColumn(#"Removed Columns", "Last Date", {"Fiscal Year", "Date", "Latest Date"} , {"Fiscal Year", "Date", "Latest Date"})
in
    #"Expanded Last Date"

Desired results:

[Possibilitis]

I don't know. All I want is the exact number of records, with just a new column added. I don't understand why I have additional records. How do I get rid of that.  Or what would be the best way to get calcuate the maxdate for each fiscal year ? How would you do it ?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

For instance, if, I have 10 rows in the data, it will load 1000 rows. I don't understand where the other rows come from. 

From the Table.Join step.  Why do you have that?