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.

[Anonymous]

Hi @ShashankRao99 ,

Try it.

let
    SourceEnrollment = ... // Load the Enrollment table
    SourceDegree = ... // Load the Degree table
    JoinTables = Table.NestedJoin(
        SourceEnrollment,
        "student_id",
        SourceDegree,
        "student_id",
        "Degree",
        JoinKind.LeftOuter
    ),
    ExpandDegree = Table.ExpandTableColumn(
        JoinTables,
        "Degree",
        {"degree_term", "adjust_term", "adjust_ind"},
        {"Degree[degree_term]", "Degree[adjust_term]", "Degree[adjust_ind]"}
    ),
    FilteredRows = Table.SelectRows(
        ExpandDegree,
        each
            [Degree[degree_term]] >= [Enrollment_Start_term] and
            [Degree[degree_term]] <= [Enrollment_Start_term] + 4 and
            [Degree[adjust_term]] < [Degree[degree_term]] and
            List.Contains({"1", "2"}, [Degree[adjust_ind]])
    )
in
    FilteredRows

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.