Convert SQL query to M language query

[SaliyaWeera]

Can someone help me to convert the following SQL code to M query to run in Power BI Desktop please

SELECT top 100 f.fileidentifier as FID,

[Next Event] = (SELECT distinct MIN(n1.[Start Date])

            FROM LEXQueryCRM.Event n1

            WHERE n1.[Event Type]

            AND n1.FileIdentifier=f.FileIdentifier

            And CAST(n1.[Start Date] AS date) > GETDATE()),

[Last Event] = (SELECT distinct MAX(l1.[Start Date])

            FROM LEXQueryCRM.Event l1

            WHERE l1.[Event Type] <> 'Event Reminder -'

            and l1.FileIdentifier=f.FileIdentifier

            And CAST(l1.[Start Date] AS date) <= GETDATE())

  FROM [LEXDEV_MSCRM].[LEXQueryCRM].[File] f

ORDER by f.[FileIdentifier]

Thanks

Saliya

[ChiragGarg2512 · Accepted solution]

@SaliyaWeera , Try this query,

let
    Source = Sql.Database("YourServerName", "YourDatabaseName"),
    Query = Source{[Schema="LEXDEV_MSCRM",Item="LEXQueryCRM_File"]}[Data],
    Top100Rows = Table.FirstN(Query, 100),
    FindNextEvent = (FileIdentifier) =>
        let
            FilteredEvents = Table.SelectRows(Query, each [FileIdentifier] = FileIdentifier and Date.From([Start Date]) > Date.From(DateTime.LocalNow())),
            NextEvent = if Table.IsEmpty(FilteredEvents) then null else List.Min(FilteredEvents[Start Date])
        in
            NextEvent,

    FindLastEvent = (FileIdentifier) =>
        let
            FilteredEvents = Table.SelectRows(Query, each [FileIdentifier] = FileIdentifier and [Event Type] <> "Event Reminder -" and Date.From([Start Date]) <= Date.From(DateTime.LocalNow())),
            LastEvent = if Table.IsEmpty(FilteredEvents) then null else List.Max(FilteredEvents[Start Date])
        in
            LastEvent,

    AddedNextEvent = Table.AddColumn(Top100Rows, "Next Event", each FindNextEvent([FileIdentifier]), type datetime),
    AddedLastEvent = Table.AddColumn(AddedNextEvent, "Last Event", each FindLastEvent([FileIdentifier]), type datetime),
    ReorderedColumns = Table.ReorderColumns(AddedLastEvent, {"FileIdentifier", "Next Event", "Last Event"}),

    SortedTable = Table.Sort(ReorderedColumns, {{"FileIdentifier", Order.Ascending}})
in
    SortedTable

[ChiragGarg2512 · Accepted solution]

@SaliyaWeera , Try this query,

let
    Source = Sql.Database("YourServerName", "YourDatabaseName"),
    Query = Source{[Schema="LEXDEV_MSCRM",Item="LEXQueryCRM_File"]}[Data],
    Top100Rows = Table.FirstN(Query, 100),
    FindNextEvent = (FileIdentifier) =>
        let
            FilteredEvents = Table.SelectRows(Query, each [FileIdentifier] = FileIdentifier and Date.From([Start Date]) > Date.From(DateTime.LocalNow())),
            NextEvent = if Table.IsEmpty(FilteredEvents) then null else List.Min(FilteredEvents[Start Date])
        in
            NextEvent,

    FindLastEvent = (FileIdentifier) =>
        let
            FilteredEvents = Table.SelectRows(Query, each [FileIdentifier] = FileIdentifier and [Event Type] <> "Event Reminder -" and Date.From([Start Date]) <= Date.From(DateTime.LocalNow())),
            LastEvent = if Table.IsEmpty(FilteredEvents) then null else List.Max(FilteredEvents[Start Date])
        in
            LastEvent,

    AddedNextEvent = Table.AddColumn(Top100Rows, "Next Event", each FindNextEvent([FileIdentifier]), type datetime),
    AddedLastEvent = Table.AddColumn(AddedNextEvent, "Last Event", each FindLastEvent([FileIdentifier]), type datetime),
    ReorderedColumns = Table.ReorderColumns(AddedLastEvent, {"FileIdentifier", "Next Event", "Last Event"}),

    SortedTable = Table.Sort(ReorderedColumns, {{"FileIdentifier", Order.Ascending}})
in
    SortedTable

[SaliyaWeera · Accepted solution]

Thanks. Unfortunately this DAX query did not work. When ran it in PBI Desktop it ended up with an error message. Error message is showb below.
The syntax for ',' is incorrect. (DAX(Sql.Database("JUVLEX12", "LEXDEVReports_MSCRM"), Query = Source{[Schema="LEXDEV_MSCRM",Item="LEXQueryCRM_File"]}[Data], Top100Rows