Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

View solution in original post

5 REPLIES 5
JirkaZ
Solution Specialist
Solution Specialist

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

Anonymous
Not applicable

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

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
Not applicable

@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

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors