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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors