Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 weight | Material reception.Magnetic unloading equipment | Shell preparation.Plate pre-blasting capability |
20 | 1 | 0 |
Any ideas on the right M method, some sort of function/loop?
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?
Hey Stachu,
Many thanks for your quick reply!
Recursive function seems like an appropriate way to proceed.
Answer to your questions:
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 😞
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 reception | Material reception.Max single material handling weight | Material reception.Magnetic unloading equipment | Shell preparation.Plate pre-blasting capability |
0 | 20 | 1 | 0 |
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:
GroupPath | GroupName | DatapointName | DataPointValue |
Level1Group1 | Level1Group1 | DataPoint1 | A |
Level1Group2 | Level1Group2 | DataPoint2 | B |
Level1Group2.Level2Group3 | Level2Group3 | DataPoint3 | C |
Level1Group2.Level2Group3.Level3Group4 | Level3Group4 | DataPoint4 | D |
Does it make better sense?
Thanks again!!!
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
Path | IsGroup | Name | Children | DataPointValue |
|Level0Group0|Level1Group1 | FALSE | DataPoint1 | null | A |
|Level0Group0|Level1Group2 | FALSE | DataPoint2 | null | B |
|Level0Group0|Level1Group2|Level2Group3 | FALSE | DataPoint3 | null | C |
|Level0Group0|Level1Group2|Level2Group3|Level3Group4 | FALSE | DataPoint4 | null | D |
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |