Nested JSON from PostMan due to Subselect Query

sny23vpb · ‎07-05-2022

Hello - I was wondering if I could get some assistance to an issue I'm having. I'm very new to Power BI but trying to solve an issue where I can write a report showing nested JSON output from Postman that essentially breaks out a main query and subquery together in excel or comparable report..

Doing some research - I found this issue and kind of thought it might help or is at least close, but I can't figure out next steps with the advanced editor -

Solved: Re: Nested JSON and never end Records - Microsoft Power BI Community

I tried creating two separate queries for each nested array and joining them together in Power BI but there was only one record in the second query with expanded JSON no matter how I did the relationships. I think the solution will be similar to the above link , but I'm trying to get up to speed on the code in the advanced editor and what to change.

Mine Looks like :

let
Source = Json.Document(File.Contents("C:\Users\BR1\OneDrive - \GreenQuery.json")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "assignment_details__vr"}, {"Column1.id", "Column1.assignment_details__vr"})
in
#"Expanded Column1"

A very simple example with just one field looks like this :

Not every ID would have a record in the nested field.

I can keep expanding but I only get down to one record ; I can never get the rows together to show

Ultimately what I'm trying to recreate is something like this :

Column 1	Column 2	Column3 from nested array	Column 4 from nested array
value	value	Null because no value in subselect on this column 1 record	Null because no value in subselect on this column 1 record
value	value	value	value
value	value	value	value
value	value	Null because no value in subselect on this column 1 record	Null because no value in subselect on this column 1 record