Forum Discussion

Ronnie7's avatar
Ronnie7
Icon for Helper II rankHelper II
9 years ago
Solved

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

Hello,

 

I'm using the PowerBI desktop version and trying to convert a code from SQL to Power Query.

I Need to filder the result of a table in the query editor the SQL query is:

select wf.projectname,wf.StageName
  from MS_Workflow wf
  where
  wf.StageEntryDate = (select max(wf2.StageEntryDate) from MS_Workflow wf2 where wf.projectid = wf2.ProjectId)
  group by wf.ProjectName,StageName
  order by StageName desc
I need to be able to het the latest entry date for each projectID.

I have merged the table to iteslf to create a self-join but not sure how to convert this sub-query.

 

Thanks in Advance!

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    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

    • Ronnie7's avatar
      Ronnie7
      Icon for Helper II rankHelper II
      9 years ago

      Thanks v-qiuyu-msft, this was really helpful.

       

      My data source is actually an ODATA feed from Project Online to i can't use SQL and hence my struggle. Different table names, different language,  nonetheless, you solution with the grouping and expanding worked well.

       

      Thanks,

      Ran

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago

    Please post the sample file or data for recreating a solution.

     

    What you would like to do as per my understanding,

     

    1. Firstly find wf.projectid = wf2.ProjectId in your table from MS_Workflow wf2.

    2. Secondly find the max date in wf2.StageEntryDate ( As the table is filtered because of first step). This would be your desired wf.StageEntryDate.

    3. Remove unneccessary columns 

    4. Group the results by Grouping By Project Name and Stage Name.

    • Ronnie7's avatar
      Ronnie7
      Icon for Helper II rankHelper II
      9 years ago

      Hi Bhavesh,

       

      Is there a way i can write that SQL query in Power Query?

      I'm not so sure how to implement those steps in Power BI.

       

      Thanks again.

       

       

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        You just need to use Query editor ribbon interface to achieve this. PowerQuery is using "M" and It has its own syntex. It is powerful enough to convert your SQL into M code. Ribbon interface would create a code for you. As I said before, Please post the sample data to provide you a exact solution.