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

[ShashankRao99]

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.

[cassidy]

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.