Forum Discussion
Dynamic Power BI parameters into MongoDB Atlas SQL
I was able to get this resolved after returning from unexpected leave. We made several changes to it in order to resolve it. I have an example query below of how we were able to set it up and run it. Hopefully the additional information below helps others else in the future.
<insert connection string here> should be replaced with the information provided from mongo db, it will start with mongodb://. only one mongodb:// is needed.
<DatabaseName> should be the database you want to connect to on MongoDB.
InputNumberParameter is set up as a parameter to be provided.
let NumberParameter = InputNumberParameter, Query = "SELECT `_id` AS `Id`, COALESCE(`Identifier`, '') AS `Identifier` FROM `Collection` WHERE `Number`='" & NumberParameter & "'", Source = Value.NativeQuery(MongoDBAtlasODBC.Contents("mongodb://<insert connection string here>", "<DatabaseName>", []){[Name="<DatabaseName>",Kind="Database"]}[Data], Query, null, [EnableFolding=true]) in Source
Hi programmerdavid,
Thank you for following up with the reply. Dynamic parameters won't work in this case. However, you can create a parameter and pass it to your SQL query. But consider these points:
* Instead of "?", replace it with the actual parameter name.
* If you're working with a number parameter, make sure to concatenate it properly after "=".
* First, create a variable for your query and then pass that variable to the query parameter. When entering source make sure that after "Kind = Database", there should be a "query = parameter".
You can use the connector and pass the query, then generate the m-code and update your m-code from the auto generated code.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Hi programmerdavid ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi programmerdavid ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi programmerdavid ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.I was able to get this resolved after returning from unexpected leave. We made several changes to it in order to resolve it. I have an example query below of how we were able to set it up and run it. Hopefully the additional information below helps others else in the future.
<insert connection string here> should be replaced with the information provided from mongo db, it will start with mongodb://. only one mongodb:// is needed.
<DatabaseName> should be the database you want to connect to on MongoDB.
InputNumberParameter is set up as a parameter to be provided.
let NumberParameter = InputNumberParameter, Query = "SELECT `_id` AS `Id`, COALESCE(`Identifier`, '') AS `Identifier` FROM `Collection` WHERE `Number`='" & NumberParameter & "'", Source = Value.NativeQuery(MongoDBAtlasODBC.Contents("mongodb://<insert connection string here>", "<DatabaseName>", []){[Name="<DatabaseName>",Kind="Database"]}[Data], Query, null, [EnableFolding=true]) in Source