In a dire need for help - result based on a subquery (max)

Hi Ronnie7,

When you connect to the SQL Database in desktop, you can write the T-SQL Query in this window:

Sample data:

Also you can get data from the table MS_Workflow and duplicate this query as MS_Workflow (2) in Query Editor, then follow below steps:

1. In query MS_Workflow, Goup by ProjectId, and return maximum StageEntryDate.

2. Merge this MS_Workflow with MS_Workflow (2).

3. Expand the column "projectname", "StageName".

4. Uncheck Enable Load for MS_Workflow (2), this table MS_Workflow (2) will not display in the report.

Backend Power Query is below:

let
    Source = Sql.Database("sql2016ga", "qiuyun"),
    dbo_MS_Workflow = Source{[Schema="dbo",Item="MS_Workflow"]}[Data],
    #"Grouped Rows" = Table.Group(dbo_MS_Workflow, {"projectid"}, {{"MAX", each List.Max([StageEntryDate]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"MAX"},#"MS_Workflow (2)",{"StageEntryDate"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"projectname", "StageName"}, {"projectname", "StageName"})
in
    #"Expanded NewColumn"

Best Regards,
Qiuyun Yu