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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrLasse
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
Stachu
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 🙂

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 😞

Stachu
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 🙂

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!!!

Stachu
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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.