Forum Discussion
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.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
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
5 Replies
Anonymous Can't you just use the custom SQL command to achieve the same?
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
