29

OK yes I figured they were using 2 different versions. The Windows server has the newer version that supports the native query differently. This is in preparation of the upcoming Direct Query support (Should land sometime next year, Quarter 1). Please make sure the ODBC Driver on the Windows Server is up to date as well.

For this latest version, the “.query” in the M Code it is problematic.Try this instead:

Instructions (selecting the connector):

  1. From Power BI App, navigate to Workspaces (must have a workspace other than “My Workspace)
  2. Select New->More options
  3. Select Dataflow Gen2 from the “Data Factory” group
  4. At the top of the screen, select “Get data”
  5. Select “More” then enter in the Search bar “MongoDB”
  6. Find and paste in your Atlas SQL MongoDB URI, then enter in the Database Name
  7. At the bottom of the connection window, make sure your On-Premise Data Gateway is selected from the drop down
  8. Select “Next” (you may need to edit/add in user credentials if they are not saved already)
  9. You will be presented with the navigation menu to select tables from your virtual database.
  10. Once you select a table(s), you can then transform the data using Power Query Online.

Instructions (selecting blank query):

From Power BI App, navigate to Workspaces (must have a workspace other than “My Workspace)

  1. Select New->More options
  2. Select Dataflow Gen2 from the “Data Factory” group
  3. At the top of the screen, select “Get data”
  4. Select “More” then enter in the Search bar “Blank Query”
  5. In the query window, make sure your query syntax is as follows:
    Let
    Source = Value.NativeQuery(MongoDBAtlasODBC.Contents(“mongodb://atlassqlsandbox- rotpc.a.query.mongodb.net/Supplies?ssl=true&authSource=admin”, “Supplies”, ). {[Name=“Supplies”,Kind=“Database”]}[Data], “select * from Sales limit 10”, null, [EnableFolding=false])
    in
    Source
  6. At the bottom of the connection window, make sure your On-Premise Data Gateway is selected from the drop down
  7. Select “Next” (you may need to edit/add in user credentials if they are not saved already)
  8. Power Query Online will open and present you with your data per your sql statement.