cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Power Query - Dynamic JSON ETL - multiple children

Dear experts,

 

I am looking for the right method to ETL a "dynamic" JSON document in the sense that there can be a indefinite level of "children".

 

Exract of JSON Structure:

 

{
  "Children": [
    {
      "IsGroup": true,
      "Name": "Material reception",
      "Children": [
        {
          "IsGroup": false,
          "Name": "Max single material handling weight",
          "InputValue": {
            "Value": "20",
          }
        },
        {
          "IsGroup": false,
          "Name": "Magnetic unloading equipment",
          "InputValue": {
            "Value": "1",
          }
        },
      ]
    },
    {
      "IsGroup": true,
      "Name": "Shell preparation",
      "Children": [
        {
          "IsGroup": false,
          "Name": "Plate pre-blasting capability",
          "InputValue": {
            "Value": "0",
          }
        },
      ]
    },
  ],
}

 

We have

  • an attribute (IsGroup) which tells us if the current level has children or no, I am thinking this attribute can be used in a loop method to tell us if we should continue any further.
  • name on each level, I am thinking to name the "column" name.name.name to keep track of the levels until the level where we have a value
  • InputValue: {"Value":} value is the value of the lowest level

 

Due to the nature of the JSON document there can be different levels of children, but the IsGroup, Name, InputValue: {"Value":} is the same for all documents.

 

From given example I am looking for a output like:

Material reception.Max single material handling weightMaterial reception.Magnetic unloading equipmentShell preparation.Plate pre-blasting capability
2010

 

Any ideas on the right M method, some sort of function/loop?

5 REPLIES 5
Community Champion
Community Champion

you can use recursion in Power Query/M (page 91)
https://docs.microsoft.com/en-gb/powerquery-m/power-query-m-language-specification
do you know have many nested groups can be there? maybe it's just easier to expand X times
Also in which format do you need the output to be?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Hey Stachu,

 

Many thanks for your quick reply!

 

Recursive function seems like an appropriate way to proceed.

 

Answer to your questions: 

  1. For each group (child) there can be a different quantity of nested groups (levels), and the document is developing over time as the business is adding more groups and levels.
  2. Output format is as demonstrated in the table from the first post. Table and value of the value records, with column name = Name.Name.Name from Name of each nested group level.

 

What define if a group has more levels is the attribute called IsGroup true/false. Value is always stored in the last level group within InputValue: Value:

 

I'll read up on the recursive function, not very strong at the M syntax though 😞

Community Champion
Community Champion

but if that';s the case you will have "infinite" number of columns, right?
e.g. if some of the children would end at Material Reception, how would it look like? something like this comes to  mind, but that means you will have set of columns for each different level of nesting, it will blow up really fast

Material receptionMaterial reception.Max single material handling weightMaterial reception.Magnetic unloading equipmentShell preparation.Plate pre-blasting capability
02010

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Hey Stachu,

 

You are so right, I beg for forgiveness. It is more logical to fix first group level and make a column with multiple lines per nested category.

 

New example:

{
  "Children": [
    {
      "IsGroup": true,
      "Name": "Level1Group1",
      "Children": [
        {
          "IsGroup": false,
          "Name": "DataPoint1",
          "InputValue": {
            "Value": "A"
          }
        }
      ]
    },
    {
      "IsGroup": true,
      "Name": "Level1Group2",
      "Children": [
        {
          "IsGroup": false,
          "Name": "DataPoint2",
          "InputValue": {
            "Value": "B"
          }
        },
        {
          "IsGroup": true,
          "Name": "Level2Group3",
          "Children": [
            {
              "IsGroup": false,
              "Name": "DataPoint3",
              "InputValue": {
                "Value": "C"
              }
            },
            {
              "IsGroup": true,
              "Name": "Level3Group4",
              "Children": [
                {
                  "IsGroup": false,
                  "Name": "DataPoint4",
                  "InputValue": {
                    "Value": "D"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

 

New expected result:

GroupPathGroupNameDatapointNameDataPointValue
Level1Group1Level1Group1DataPoint1A
Level1Group2Level1Group2DataPoint2B
Level1Group2.Level2Group3Level2Group3DataPoint3C
Level1Group2.Level2Group3.Level3Group4Level3Group4DataPoint4D

 

Does it make better sense?

 

Thanks again!!!

Community Champion
Community Champion

the thing with recurrence is that it has to transform object into same type of object, so I midified your JSON to a table so in the end there would be this format

PathIsGroupNameChildrenDataPointValue
|Level0Group0|Level1Group1FALSEDataPoint1nullA
|Level0Group0|Level1Group2FALSEDataPoint2nullB
|Level0Group0|Level1Group2|Level2Group3FALSEDataPoint3nullC
|Level0Group0|Level1Group2|Level2Group3|Level3Group4FALSEDataPoint4nullD

have a look here
https://1drv.ms/u/s!AjxUGXgGNzCEiVwCAgctdbe4oA7X

I've also made change to JSON so it's more "recurrent", it now starts as

{
"IsGroup": true,
"Name": "Level0Group0",
"Children": [
{
"IsGroup": true,
"Name": "Level1Group1",
"Children": [
{

etc.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors