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
ShashankRao99
Frequent Visitor

Convert the SQL Query to M-Query for Data Flow.

We are new to Dataflow and M query.  We are converting the SQL code to M query for Dataflows and we ran into  a challenge. 

We have two tables, enrollment and Degrees and we are not able to convert the join in M language.

 

The query looks like below:

 

Select  Enrl.*,  Deg.*

From Enrollment Enrl

Left Join Degree Deg on Enrl.student_id = Deg.student_id and Deg.degree_term between Enrl.Enrollment_Start_term and Enrl.(enrollment_start_term +4) and Enrl.Adjustment_term < Deg.degree_term and Enrl.Adjust_ind in ('1', '2');

 

The degree table has the student_id as well as the term when Degree was granted degree_term

The Enrollment table has the  the student_id, enrollment_start_term, adjust_term, and adjust_ind.

 

We want to find out how many students got degree in 4 years, hence the "AND" condition has (enrollment_start_term +4).

Please help me convert the above query into M-query.

1 REPLY 1
cassidy
Power Participant
Power Participant

First off, Dataflows accept native SQL statements if that's easier for you to work with.
https://learn.microsoft.com/en-us/power-query/native-database-query

 

Purely M Query, which may be less efficient, would look like this I imagine:

Load Enrollment Enrl table

Load Degree Deg table

 

Merge Enrollment Enrl to Degree Deg on student_id

Expand degree_term

Add calculated column with your conditional statements where if all is True then "Keep"

Filter on calculated column for "Keep"

 

If you get it right, query folding will still be functioning and it will write back a query similar to what you started with.

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