How to add subquery to dataset with Power Query editor

[Anonymous]

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.CaseAdmit where CaseAdmit.CaseID = c.CaseID) as StartDate_Admit
,(select top 1 CaseReferral.BeginDate from ReadModel.CaseReferral where CaseReferral.CaseID = c.CaseID) as StartDate_Referral
from ReadModel.[Case] as C 

 I want to use Power Query Editor to create a dataset based on this query. I'm able to import "CaseID", but I'm not sure how to deal with the 2 subqueries. I tried some table functions like Table.FirstN and Table.SelectRows and adding the name of the table in the subquery (e.g. "ReadModel.CaseAdmit") as the first parameter, but it didn't work. I was wondering if you know how to get values from subqueries? Thanks!

Jason

[JirkaZ]

@Anonymous Can't you just use the custom SQL command to achieve the same?

[Anonymous]

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

[Miralon · Accepted solution]

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]

@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]

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