Need to capture state with min change date for every state as shown from table1 to table 2

[Pravdba]

Hi, 

Help me on this, I have table1 having historical data i need to achieve Table 2,  Need to capture min change date of every state if it was repeated state after state it should also capture.

Table 1

ID	State	ChangeDate
1	Proposed	08-03-2024 00:00
1	Proposed	09-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Authoring	27-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Blocked	07-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Blocked	07-02-2025 00:00
2	Authoring	08-02-2025 00:00

Table 2

Id	State	MinChangeDate
1	Proposed	08-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Authoring	08-02-2025 00:00

Thanks

Praveen Kumar

[Ashish_Mathur · Accepted solution]

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"State", type text}, {"ChangeDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "State"}, {{"Count", each List.Min([ChangeDate]), type nullable date}},GroupKind.Local)
in
    #"Grouped Rows"

Hope this helps.

[v-aatheeque]

Hi @Pravdba ,

If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Pravdba ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @Pravdba ,

If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!