Forum Discussion

SaliyaWeera's avatar
SaliyaWeera
Frequent Visitor
2 years ago
Solved

Convert SQL query to M language query

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

Need Help
  • ChiragGarg2512's avatar
    ChiragGarg2512
    2 years ago

    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's avatar
    SaliyaWeera
    2 years ago

    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

2 Replies

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Solution Sage
    2 years ago

    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's avatar
      SaliyaWeera
      Frequent Visitor
      2 years ago

      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