Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
How to dynamically expand the following nsted API output:
"byOrganization": {
"47": [
{
"clusterId": 6796,
"G_ClusterId": 7,
"requirements": 1059610
}
],
"53": [
{
"clusterId": 6796,
"G_ClusterId": 7,
"requirements": 1150826
},
{
"clusterId": 6802,
"G_ClusterId": 11,
"requirements": 2278103
},
{
"clusterId": 6795,
"G_ClusterId": 6,
"requirements": 2228481
}
],
"173": [
{
"clusterId": 6795,
"G_ClusterId": 6,
"requirements": 1200000
},
returned into records, such as in this image:
then expanding it will return the below lists , converting orgs IDs into columns like this
my two issues are:
1- The APi will return different IDs evertime i call, so i need the table to dynamically fetch the ids with the statement "Table.ExpandRecordColumn(previousStep, "byOrganization", {"3", "8", "164"})"
2- How to expand all lists in one step, dynamically
Solved! Go to Solution.
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.
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
which even won't work becaue i will then have to expand the Orgs lists manually
{
"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"}
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.
let
Source = "
{""byOrganization"": {
""47"": [
{
""clusterId"": 6796,
""G_ClusterId"": 7,
""requirements"": 1059610
}
],
""53"": [
{
""clusterId"": 6796,
""G_ClusterId"": 7,
""requirements"": 1150826
},
{
""clusterId"": 6802,
""G_ClusterId"": 11,
""requirements"": 2278103
},
{
""clusterId"": 6795,
""G_ClusterId"": 6,
""requirements"": 2228481
}
],
""173"": [
{
""clusterId"": 6795,
""G_ClusterId"": 6,
""requirements"": 1200000
} ]
}
}
",
JS = Json.Document(Source),
byOrganization = JS[byOrganization],
#"Converted to Table" = Record.ToTable(byOrganization),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"clusterId", "G_ClusterId", "requirements"}, {"clusterId", "G_ClusterId", "requirements"})
in
#"Expanded Value1"
This is really really cool and work exactly as expected, thanks so much @lbendlin , wish you a lovely evening
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |