Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to add subquery to dataset with Power Query editor

Hi, I'm new to Power BI and I have the following SQL query. It has 3 fields: one from a table and 2 generated via a subquery. select c.CaseID ,(select top 1 CaseAdmit.BeginDate from ReadModel.Cas...
Need Help
  • Miralon's avatar
    Miralon
    6 years ago

    Hi Anonymous,

    I tried to solve your request based on some test tables I created in SQL. In order to obtain same output I applied some steps(using Power BI Desktop):

     

    1. Imported and transform Cases table to keep only CaseId column :

    let
    Source = Sql.Database("servername", "dbname"),
    dbo_Cases = Source{[Schema="dbo",Item="Cases"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Cases,{"Name"})
    in
    #"Removed Columns"

    2. Imported CaseAdmit table and transform:

    let
    Source = Sql.Database("servername", "dbname"),
    dbo_CaseAdmit = Source{[Schema="dbo",Item="CaseAdmit"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_CaseAdmit,{"Id"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"CaseId"}, {{"FirstAdmitDate", each List.Min([BeginDate]), type date}, {"LastAdmitDate", each List.Max([BeginDate]), type date}})
    in
    #"Grouped Rows"

    3. Imported CaseReferral table and transform:

    let
    Source = Sql.Database("servername", "dbname"),
    dbo_CaseReferral = Source{[Schema="dbo",Item="CaseReferral"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_CaseReferral,{"Id"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"CaseId"}, {{"FirstReferralDate", each List.Min([BeginDate]), type date}, {"LastReferralDate", each List.Max([BeginDate]), type date}})
    in
    #"Grouped Rows"

    4. Merge all 3 queries into consolidated one:

    let
    Source = Table.NestedJoin(Cases, {"CaseID"}, CaseAdmit, {"CaseId"}, "CaseAdmit", JoinKind.LeftOuter),
    #"Merged Queries" = Table.NestedJoin(Source, {"CaseID"}, CaseReferral, {"CaseId"}, "CaseReferral", JoinKind.LeftOuter),
    #"Expanded CaseAdmit" = Table.ExpandTableColumn(#"Merged Queries", "CaseAdmit", {"FirstAdmitDate", "LastAdmitDate"}, {"FirstAdmitDate", "LastAdmitDate"}),
    #"Expanded CaseReferral" = Table.ExpandTableColumn(#"Expanded CaseAdmit", "CaseReferral", {"FirstReferralDate", "LastReferralDate"}, {"FirstReferralDate", "LastReferralDate"})
    in
    #"Expanded CaseReferral"

    5. After Close andApply, switch to Data View, right click on Cases, CaseAdmit, CaseReferral and select 'Hide from report view' from pop-up menu.

     

    I generated all this from the graphic interface, I can share with you if needed.

     

    Hope this helps!

    Regards,

    Mira

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I was wondering if I could do this within the power query editor? I want to avoid using as much SQL as possible.

Miralon's avatar
Miralon
Frequent Visitor
6 years ago

Hi Anonymous,

I tried to solve your request based on some test tables I created in SQL. In order to obtain same output I applied some steps(using Power BI Desktop):

 

1. Imported and transform Cases table to keep only CaseId column :

let
Source = Sql.Database("servername", "dbname"),
dbo_Cases = Source{[Schema="dbo",Item="Cases"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_Cases,{"Name"})
in
#"Removed Columns"

2. Imported CaseAdmit table and transform:

let
Source = Sql.Database("servername", "dbname"),
dbo_CaseAdmit = Source{[Schema="dbo",Item="CaseAdmit"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_CaseAdmit,{"Id"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"CaseId"}, {{"FirstAdmitDate", each List.Min([BeginDate]), type date}, {"LastAdmitDate", each List.Max([BeginDate]), type date}})
in
#"Grouped Rows"

3. Imported CaseReferral table and transform:

let
Source = Sql.Database("servername", "dbname"),
dbo_CaseReferral = Source{[Schema="dbo",Item="CaseReferral"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_CaseReferral,{"Id"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"CaseId"}, {{"FirstReferralDate", each List.Min([BeginDate]), type date}, {"LastReferralDate", each List.Max([BeginDate]), type date}})
in
#"Grouped Rows"

4. Merge all 3 queries into consolidated one:

let
Source = Table.NestedJoin(Cases, {"CaseID"}, CaseAdmit, {"CaseId"}, "CaseAdmit", JoinKind.LeftOuter),
#"Merged Queries" = Table.NestedJoin(Source, {"CaseID"}, CaseReferral, {"CaseId"}, "CaseReferral", JoinKind.LeftOuter),
#"Expanded CaseAdmit" = Table.ExpandTableColumn(#"Merged Queries", "CaseAdmit", {"FirstAdmitDate", "LastAdmitDate"}, {"FirstAdmitDate", "LastAdmitDate"}),
#"Expanded CaseReferral" = Table.ExpandTableColumn(#"Expanded CaseAdmit", "CaseReferral", {"FirstReferralDate", "LastReferralDate"}, {"FirstReferralDate", "LastReferralDate"})
in
#"Expanded CaseReferral"

5. After Close andApply, switch to Data View, right click on Cases, CaseAdmit, CaseReferral and select 'Hide from report view' from pop-up menu.

 

I generated all this from the graphic interface, I can share with you if needed.

 

Hope this helps!

Regards,

Mira

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Miralon ,

     

    Thanks for your help! It looks like this did the trick. I was able to get the latest date if there are multiple records with the case ID.

     

    Sincerely,

     

    Jason

     

    • Miralon's avatar
      Miralon
      Frequent Visitor
      6 years ago

      Hi Anonymous ,

       

      I'm glad I could help.

      I started Power BI as a SQL Server specialist. It was easy for me to understand the way Power Query incapsulate data: every table from Power Query is a graphical representation of a subquery (you work with queries instead of tables). With this in mind, it will be much easier to work in Power Query if you are familiar to T-SQL.

      Good luck in the future and see you around.

      Sincerely,

      Mira