Connecting Mongo with PowerBi without using a flattened database

[mmmarkin]

Hi, I'm an employee at a thin film fabrication firm. The tldr overall question of mine is whether there's a way to handle deeply nested /hierarchical/ragged data directly in PowerBi. I've tried a few free widgets for hierarchical data but our data seems too complicated for the widgets. Especially since there is no one schema that truly represents the data, as different clients may require machinery with different combinations of components.

To give some background, I'm working with a Mongo DB instance which contains deeply nested hierarchical data. I've provided an example of the data with the field and values randomized below. So far, to connect to PowerBi I've used the Atlas SQL Bi Connector for Mongo, but I've had to make a "copy" database which contains the flattened version of my original database.

The problem with this approach is that there is this intermediary step of flattening the data every time we update the database. I'm in touch with Mongo support about this as well. 
Example:

{
  "_id": {
    "$oid": "f1e2d3c4b5a697887766554433221100"
  },
  "jeff": "abcde",
  "sprocket": [
    {
      "wibble": "12345678-90ab-cdef-1234-567890abcdef",
      "foo": false,
      "bar": "xyzzy",
      "qux": "plugh"
    }
  ],
  "flibber": [],
  "gizmo": [],
  "wobble": [
    {
      "blorp": "qwerty",
      "snarf": [
        {
          "spaz": "asdfgh",
          "whiz": "zxcvbn"
        }
      ],
      "thingamajig": [],
      "doodad": [
        {
          "foobar": "foobar",
          "frobnicate": "frobnicate",
          "bloop": "bloop",
          "blarp": "blarp",
          "widget": "widget",
          "doohickey": [
            [
              {
                "whatzit": "whatzit",
                "splork": "splork",
                "zibble": [
                  "zibble"
                ],
                "frob": "frob",
                "zork": false,
                "gorp": false
              },
              {
                "whatzit": "whatzit",
                "splork": "splork",
                "zibble": [
                  "zibble"
                ],
                "frob": "frob",
                "zork": false,
                "gorp": false
              }
            ]
          ],
          "bliz": [
            {
              "yarp": "yarp",
              "glorp": "glorp"
            }
          ],
          "zoink": true,
          "flurb": [
            {
              "snizzle": "snizzle",
              "crizzle": false,
              "yibble": "yibble"
            },
            {
              "snizzle": "snizzle",
              "crizzle": false,
              "yibble": "yibble"
            }
          ]
        }
      ]
    }
  ]
}

[v-junyant-msft · Accepted solution]

Hi @mmmarkin ,

I'm sorry I may not understand what you mean, could you provide sample data (not code) of numerical type and what your expected result is? 

Here are some suggestions I can offer you:
For dealing with complex structural hierarchies in your data, Power BI can handle a certain level of hierarchy with its built-in grouping and drill-down functions. 
Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn
Drill mode in the Power BI service - Power BI | Microsoft Learn

You can also filter the entire hierarchy by putting all the fields it contains into the slicer. For example:

If your hierarchy is too complex, you can try using this custom visual:

It allows you to filter by searching directly for keywords

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

[mmmarkin]

The goal is to use slicers to create a search tool.