Forum Discussion

Helper I

2 years ago

Solved

dynamically expand API output

How to dynamically expand the following nsted API output: "byOrganization": { "47": [ { "clusterId": 6796, "G_ClusterId": 7, "requirements": 1059610 } ], "53": [ { "clusterId": 6796, "G_...

Need Help

lbendlin

2 years ago

Your sample JSON is malformed (extra ] at position 1375). Please provide properly formed JSON.

let
    Source = Json.Document("
    
{
""data"": {
""planData"": [
{
""planId"": 123,
""name"": ""test1"",
""planType"": ""plan"",
""planCostingType"": ""with projects"",
""planYear"": ""2023"",
""planEndDate"": ""30/09/2023"",

""financialData"": {
""requirements"": {
""totalRequirements"": 376729201,
""breakdown"": {
""byOrganization"": {
""3"": [
{
""clusterId"": 6760,
""globalClusterId"": 26480,
""requirements"": 700000
}
],
""8"": [
{
""clusterId"": 6760,
""globalClusterId"": 26480,
""requirements"": 1065690
}
],
""164"": [
{
""clusterId"": 6766,
""globalClusterId"": 11,
""requirements"": 1511500
},
{
""clusterId"": 6765,
""globalClusterId"": 4,
""requirements"": 620082
}
],
""165"": [
{
""clusterId"": 6766,
""globalClusterId"": 11,
""requirements"": 1777350
}
],
""758"": [
{
""clusterId"": 6764,
""globalClusterId"": 13,
""requirements"": 162000
},
{
""clusterId"": 6764,
""globalClusterId"": 12,
""requirements"": 30000
},
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 40000
}
],
""845"": [
{
""clusterId"": 6763,
""globalClusterId"": 7,
""requirements"": 1182703
}
],
""880"": [
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 127207
},
{
""clusterId"": 6764,
""globalClusterId"": 12,
""requirements"": 123466
},
{
""clusterId"": 6764,
""globalClusterId"": 13,
""requirements"": 123467
}
],
""928"": [
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 1775615
}
]

}
}
}
}
},
{
""planId"": 789,
""name"": ""test2"",
""planType"": ""plan"",
""financialData"": {
""requirements"": {
""totalRequirements"": 1311953516,
""breakdown"": {

""byOrganization"": {
""173"": [
{
""clusterId"": 6775,
""globalClusterId"": 13,
""requirements"": 296940
},
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 692860
}
],
""1207"": [
{
""clusterId"": 6775,
""globalClusterId"": 12,
""requirements"": 400000
},
{
""clusterId"": 6775,
""globalClusterId"": 10,
""requirements"": 400000
}
],
""1472"": [
{
""clusterId"": 6779,
""globalClusterId"": 26480,
""requirements"": 2035000
}
],
""2178"": [
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 4700000
},
{
""clusterId"": 6770,
""globalClusterId"": 3,
""requirements"": 1583400
},
{
""clusterId"": 6775,
""globalClusterId"": 14,
""requirements"": 2500000
},
{
""clusterId"": 6775,
""globalClusterId"": 10,
""requirements"": 1000000
},
{
""clusterId"": 6772,
""globalClusterId"": 6,
""requirements"": 875176
},
{
""clusterId"": 6769,
""globalClusterId"": 2,
""requirements"": 103600
}
],
""2297"": [
{
""clusterId"": 6778,
""globalClusterId"": 8,
""requirements"": 23993118
}
],
""2404"": [
{
""clusterId"": 6775,
""globalClusterId"": 15,
""requirements"": 372932
}
],
""2849"": [
{
""clusterId"": 6775,
""globalClusterId"": 12,
""requirements"": 582367
},
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 1985276
},
{
""clusterId"": 6772,
""globalClusterId"": 6,
""requirements"": 20000000
}
],
""12882"": [
{
""clusterId"": 6773,
""globalClusterId"": 7,
""requirements"": 107489
}
],
""12884"": [
{
""clusterId"": 6775,
""globalClusterId"": 15,
""requirements"": 100000
}
],
""12898"": [
{
""clusterId"": 6769,
""globalClusterId"": 2,
""requirements"": 51000
}
]
}
}
}
}
}
],
""meta"":{""language"":""en""}},
""status"":""ok""}
    
    "),
    data = Source[data],
    planData = data[planData],
    #"Converted to Table" = Table.FromList(planData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"planId", "name", "planType", "planCostingType", "planYear", "planEndDate", "financialData"}, {"planId", "name", "planType", "planCostingType", "planYear", "planEndDate", "financialData"}),
    #"Expanded financialData" = Table.ExpandRecordColumn(#"Expanded Column1", "financialData", {"requirements"}, {"requirements"}),
    #"Expanded requirements" = Table.ExpandRecordColumn(#"Expanded financialData", "requirements", {"totalRequirements", "breakdown"}, {"totalRequirements", "breakdown"}),
    #"Expanded breakdown" = Table.ExpandRecordColumn(#"Expanded requirements", "breakdown", {"byOrganization"}, {"byOrganization"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded breakdown",each [byOrganization],each Record.ToTable([byOrganization]),Replacer.ReplaceValue,{"byOrganization"}),
    #"Expanded byOrganization" = Table.ExpandTableColumn(#"Replaced Value", "byOrganization", {"Name", "Value"}, {"Name.1", "Value"}),
    #"Expanded Value" = Table.ExpandListColumn(#"Expanded byOrganization", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"clusterId", "globalClusterId", "requirements"}, {"clusterId", "globalClusterId", "requirements"})
in
    #"Expanded Value1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Bassil74

Helper I

2 years ago

Thanks lbendlin i tried but didn't work, maybe it's me who didn't know how to incopraote the code you shared in my query, so iam sharing sample dataset in case you can help me out. so basically i need to fetch the

byOrganization data under financialData ---> requirements --->breakdown --->byOrganization

without having to expand it manually as below screenshot

which even won't work becaue i will then have to expand the Orgs lists manually

Appreciate your insight on this!

{
"data": {
"planData": [
{
"planId": 123,
"name": "test1",
"planType": "plan",
"planCostingType": "with projects",
"planYear": "2023",
"planEndDate": "30/09/2023",

"financialData": {
"requirements": {
"totalRequirements": 376729201,
"breakdown": {
"byOrganization": {
"3": [
{
"clusterId": 6760,
"globalClusterId": 26480,
"requirements": 700000
}
],
"8": [
{
"clusterId": 6760,
"globalClusterId": 26480,
"requirements": 1065690
}
],
"164": [
{
"clusterId": 6766,
"globalClusterId": 11,
"requirements": 1511500
},
{
"clusterId": 6765,
"globalClusterId": 4,
"requirements": 620082
}
],
"165": [
{
"clusterId": 6766,
"globalClusterId": 11,
"requirements": 1777350
}
],
"758": [
{
"clusterId": 6764,
"globalClusterId": 13,
"requirements": 162000
},
{
"clusterId": 6764,
"globalClusterId": 12,
"requirements": 30000
},
{
"clusterId": 6764,
"globalClusterId": 10,
"requirements": 40000
}
],
"845": [
{
"clusterId": 6763,
"globalClusterId": 7,
"requirements": 1182703
}
],
"880": [
{
"clusterId": 6764,
"globalClusterId": 10,
"requirements": 127207
},
{
"clusterId": 6764,
"globalClusterId": 12,
"requirements": 123466
},
{
"clusterId": 6764,
"globalClusterId": 13,
"requirements": 123467
}
],
"928": [
{
"clusterId": 6764,
"globalClusterId": 10,
"requirements": 1775615
}
]
]
}
}
}
]
}
}
}
},
{
"planId": 789,
"name": "test2",
"planType": "plan",
"financialData": {
"requirements": {
"totalRequirements": 1311953516,
"breakdown": {

"byOrganization": {
"173": [
{
"clusterId": 6775,
"globalClusterId": 13,
"requirements": 296940
},
{
"clusterId": 6776,
"globalClusterId": 11,
"requirements": 692860
}
],
"1207": [
{
"clusterId": 6775,
"globalClusterId": 12,
"requirements": 400000
},
{
"clusterId": 6775,
"globalClusterId": 10,
"requirements": 400000
}
],
"1472": [
{
"clusterId": 6779,
"globalClusterId": 26480,
"requirements": 2035000
}
],
"2178": [
{
"clusterId": 6776,
"globalClusterId": 11,
"requirements": 4700000
},
{
"clusterId": 6770,
"globalClusterId": 3,
"requirements": 1583400
},
{
"clusterId": 6775,
"globalClusterId": 14,
"requirements": 2500000
},
{
"clusterId": 6775,
"globalClusterId": 10,
"requirements": 1000000
},
{
"clusterId": 6772,
"globalClusterId": 6,
"requirements": 875176
},
{
"clusterId": 6769,
"globalClusterId": 2,
"requirements": 103600
}
],
"2297": [
{
"clusterId": 6778,
"globalClusterId": 8,
"requirements": 23993118
}
],
"2404": [
{
"clusterId": 6775,
"globalClusterId": 15,
"requirements": 372932
}
],
"2849": [
{
"clusterId": 6775,
"globalClusterId": 12,
"requirements": 582367
},
{
"clusterId": 6776,
"globalClusterId": 11,
"requirements": 1985276
},
{
"clusterId": 6772,
"globalClusterId": 6,
"requirements": 20000000
}
],
"12882": [
{
"clusterId": 6773,
"globalClusterId": 7,
"requirements": 107489
}
],
"12884": [
{
"clusterId": 6775,
"globalClusterId": 15,
"requirements": 100000
}
],
"12898": [
{
"clusterId": 6769,
"globalClusterId": 2,
"requirements": 51000
}
]
}
}
}
]
}
}
}
}
],
"meta":{"language":"en"}},
"status":"ok"}

lbendlin

Super User

2 years ago

Your sample JSON is malformed (extra ] at position 1375). Please provide properly formed JSON.

let
    Source = Json.Document("
    
{
""data"": {
""planData"": [
{
""planId"": 123,
""name"": ""test1"",
""planType"": ""plan"",
""planCostingType"": ""with projects"",
""planYear"": ""2023"",
""planEndDate"": ""30/09/2023"",

""financialData"": {
""requirements"": {
""totalRequirements"": 376729201,
""breakdown"": {
""byOrganization"": {
""3"": [
{
""clusterId"": 6760,
""globalClusterId"": 26480,
""requirements"": 700000
}
],
""8"": [
{
""clusterId"": 6760,
""globalClusterId"": 26480,
""requirements"": 1065690
}
],
""164"": [
{
""clusterId"": 6766,
""globalClusterId"": 11,
""requirements"": 1511500
},
{
""clusterId"": 6765,
""globalClusterId"": 4,
""requirements"": 620082
}
],
""165"": [
{
""clusterId"": 6766,
""globalClusterId"": 11,
""requirements"": 1777350
}
],
""758"": [
{
""clusterId"": 6764,
""globalClusterId"": 13,
""requirements"": 162000
},
{
""clusterId"": 6764,
""globalClusterId"": 12,
""requirements"": 30000
},
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 40000
}
],
""845"": [
{
""clusterId"": 6763,
""globalClusterId"": 7,
""requirements"": 1182703
}
],
""880"": [
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 127207
},
{
""clusterId"": 6764,
""globalClusterId"": 12,
""requirements"": 123466
},
{
""clusterId"": 6764,
""globalClusterId"": 13,
""requirements"": 123467
}
],
""928"": [
{
""clusterId"": 6764,
""globalClusterId"": 10,
""requirements"": 1775615
}
]

}
}
}
}
},
{
""planId"": 789,
""name"": ""test2"",
""planType"": ""plan"",
""financialData"": {
""requirements"": {
""totalRequirements"": 1311953516,
""breakdown"": {

""byOrganization"": {
""173"": [
{
""clusterId"": 6775,
""globalClusterId"": 13,
""requirements"": 296940
},
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 692860
}
],
""1207"": [
{
""clusterId"": 6775,
""globalClusterId"": 12,
""requirements"": 400000
},
{
""clusterId"": 6775,
""globalClusterId"": 10,
""requirements"": 400000
}
],
""1472"": [
{
""clusterId"": 6779,
""globalClusterId"": 26480,
""requirements"": 2035000
}
],
""2178"": [
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 4700000
},
{
""clusterId"": 6770,
""globalClusterId"": 3,
""requirements"": 1583400
},
{
""clusterId"": 6775,
""globalClusterId"": 14,
""requirements"": 2500000
},
{
""clusterId"": 6775,
""globalClusterId"": 10,
""requirements"": 1000000
},
{
""clusterId"": 6772,
""globalClusterId"": 6,
""requirements"": 875176
},
{
""clusterId"": 6769,
""globalClusterId"": 2,
""requirements"": 103600
}
],
""2297"": [
{
""clusterId"": 6778,
""globalClusterId"": 8,
""requirements"": 23993118
}
],
""2404"": [
{
""clusterId"": 6775,
""globalClusterId"": 15,
""requirements"": 372932
}
],
""2849"": [
{
""clusterId"": 6775,
""globalClusterId"": 12,
""requirements"": 582367
},
{
""clusterId"": 6776,
""globalClusterId"": 11,
""requirements"": 1985276
},
{
""clusterId"": 6772,
""globalClusterId"": 6,
""requirements"": 20000000
}
],
""12882"": [
{
""clusterId"": 6773,
""globalClusterId"": 7,
""requirements"": 107489
}
],
""12884"": [
{
""clusterId"": 6775,
""globalClusterId"": 15,
""requirements"": 100000
}
],
""12898"": [
{
""clusterId"": 6769,
""globalClusterId"": 2,
""requirements"": 51000
}
]
}
}
}
}
}
],
""meta"":{""language"":""en""}},
""status"":""ok""}
    
    "),
    data = Source[data],
    planData = data[planData],
    #"Converted to Table" = Table.FromList(planData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"planId", "name", "planType", "planCostingType", "planYear", "planEndDate", "financialData"}, {"planId", "name", "planType", "planCostingType", "planYear", "planEndDate", "financialData"}),
    #"Expanded financialData" = Table.ExpandRecordColumn(#"Expanded Column1", "financialData", {"requirements"}, {"requirements"}),
    #"Expanded requirements" = Table.ExpandRecordColumn(#"Expanded financialData", "requirements", {"totalRequirements", "breakdown"}, {"totalRequirements", "breakdown"}),
    #"Expanded breakdown" = Table.ExpandRecordColumn(#"Expanded requirements", "breakdown", {"byOrganization"}, {"byOrganization"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded breakdown",each [byOrganization],each Record.ToTable([byOrganization]),Replacer.ReplaceValue,{"byOrganization"}),
    #"Expanded byOrganization" = Table.ExpandTableColumn(#"Replaced Value", "byOrganization", {"Name", "Value"}, {"Name.1", "Value"}),
    #"Expanded Value" = Table.ExpandListColumn(#"Expanded byOrganization", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"clusterId", "globalClusterId", "requirements"}, {"clusterId", "globalClusterId", "requirements"})
in
    #"Expanded Value1"

Forum Discussion

dynamically expand API output

Related content

Dynamically Expand Table Records

Dynamically merge specific columns without null and output as text

Dynamically limit output columns in matrix

Dynamic column expand

expanded table