Trouble with Mongo Document that has an Array

[DavFount90]

Here is my Query

let
    Source = R.Execute("library(mongolite)#(lf)m3 <- mongo(collection = ""CAD"", url = ""mongodb://:@/?authSource=admin"")#(lf)cad<-m3$find(query = ""{}"")"),
    cad1 = Source{[Name="cad"]}[Value]
in
    cad1

Here is my Document Export

{
    "_id":{"$oid":"5dc224aa33288336bcb0e208"},
    "Pat Name":"LastName,FirstName",
    "Birth Date":"2019-01-01",
    "Charges": [
            {
                "Code":"R09.81",
                "Desc":"Nasal Congestion"
            },
            {
                "Code":"J33.9",
                "Desc":"Nasal Polyp, unspecified"
            },
            {
                "Code":"E84.9",
                "Desc":"Cystic Fibrosis, unspecified (CMS/HCC)"
            }]
}

When this Query runs the data returned to Power BI receives an error in the Charges column.

Is there a way for me to query this data from my Mongo Database and access it as it is intended inside of Power BI? So far the only solution I've had working is removing the array of data and storing them with unique Keys. So far the data that is sent to us has been limited to 6 charges so I've been able to hardcode 6 different keys. However, I have yet to receive word whether this will always be the case or not.

Any Help is appreciated thank you!

[v-yuta-msft · Accepted solution]

@DavFount90 ,

I would suggest you connect to Mongo DB using ODBC connector and then using R script in power query to do data transform. About how to use ODBC connector, you may refer to doc below:

https://docs.mongodb.com/bi-connector/master/connect/powerbi/

Community Support Team _ Jimmy Tao

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

[DavFount90]

@v-yuta-msft  Thank you for your reply I was originally using the ODBC connector however I'm not sure why but it only sees one of the collections from that database. I haven't found much else on that issue. Most of the comments about ODBC and Mongo people were saying stop using that and use R to query the data.

Edit: I found my problem I think with ODBC. I needed to restart my MongoSQL service and now they are all showing up. Must have created the first collection prior to the first start of the connector. Thanks. I'll work on the power query to get the data I want.

Is there any documentation you'd recommend for figuring out the power query for the data that is inside the "table" field type. I am not very familiar with R script or Power BI. (Currently searching the site)

Edit2:

So this query seems to get me the values I want from that column However it duplicates each entry for as many charges as there are and adds one charge per column.

let
    Source = Odbc.DataSource("dsn=MongoDB", [HierarchicalNavigation=true]),
    SCC_Database = Source{[Name="SCC",Kind="Database"]}[Data],
    CAD_Table = SCC_Database{[Name="CAD",Kind="Table"]}[Data],
    TransformColumnType = Table.TransformColumns(CAD_Table, {{"CAD_Charges", each {_}{0}, Value.Type(Table.Combine(CAD_Table[CAD_Charges]))}}),
    #"Expanded Activities" = Table.ExpandTableColumn(TransformColumnType, "CAD_Charges", {"Charges.Code", "Charges.Desc"})
in
    #"Expanded Activities"

[Jyo27]

Can you please explain how did you fetch array data from mongo?