Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DavFount90
Regular Visitor

Trouble with Mongo Document that has an Array

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.

error.jpg

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!

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@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.

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@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.

@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"

powerbi.jpg

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors