cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: How to add subquery to dataset with Power Query editor

Hi @JasonYeung,

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

View solution in original post

5 REPLIES 5
Highlighted
Resolver III
Resolver III

Re: How to add subquery to dataset with Power Query editor

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

Highlighted
Frequent Visitor

Re: How to add subquery to dataset with Power Query editor

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

Highlighted
Frequent Visitor

Re: How to add subquery to dataset with Power Query editor

Hi @JasonYeung,

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

View solution in original post

Highlighted
Frequent Visitor

Re: How to add subquery to dataset with Power Query editor

@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

 

Highlighted
Frequent Visitor

Re: How to add subquery to dataset with Power Query editor

Hi @JasonYeung ,

 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors