Direct query connection to Mongo DB using ODBC

[JHob]

Hi

I would be grateful for some help.

I have a Gateway setup that allows me to connect to Mongo DB using ODBC.  I have setup a Power BI Dataflow to connect to the Datasource and connect to the Power BI Dataflows in Desktop to access the necessary information.

Is it possible to also use Direct query with this for some collections?  I would like to have some information updated more frequently.

When I try switch to DirectQuery I get the message "This query contains transformations that can't be used for direct query".  All I have done in the Power BI Dataflow is connect to the MongoDB collection. I haven't added any transformations.

Many thanks in advance for your help

[HamidBee]

I was able to fix the following by firstly using Import mode instead of DirectQuery. Here are the full steps I followed:

No I need to use the EnableFolding=true command and this is only available when using the Value.NativeQuery function. I created a new step and typed the following:

= Value.NativeQuery( Source, "
SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID", null, [EnableFolding=true])

Now if I apply any parameter based filters to the dataset it will work without any errors.

[v-yingjl]

Hi @JHob ,

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li

[v-yingjl]

Hi @JHob ,

It is a warning message that remind your it is not import type. 

Direct Query doesn't support all Power Query transformations. If you try to use one that is not allowed you get this message. Please remove the query step causing  issue and try to look for alternate for that step.

Please refer this document about limitations of DirectQuery:

Implications of using DirectQuery 

Best Regards,
Yingjie Li

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

[amitchandak]

@JHob , Is it listed under direct query source? If yes then you can.

Second, if you have configured in import mode, there many transformations which can do, which are not allowed in direct query mode.

refer :https://radacad.com/directquery-live-connection-or-import-data-tough-decision

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

[JHob]

Thanks @amitchandak

If I cange the data connectivity mode to direct query I get the following message "This step results in a query that isnot supported in DirectQuery Model