A bit more advanced JSON to Power BI

Hi SimonKibsgaard

try:

let
    Source = "HERE_COMES_JSON_STRING", // insert you source string,
    #"Parsed JSON" = Json.Document(Source), // or here you can refer to the JSON as the Source
    toTable = Record.ToTable(#"Parsed JSON")[[Value]],
    #"Expanded {0}" = Table.ExpandRecordColumn(toTable, "Value", {"organizationname", "teams"}, {"Organization", "teams"}),
    Custom1 = Table.TransformColumns(#"Expanded {0}",{{"teams", Record.ToList}}),
    #"Expanded {0}1" = Table.ExpandListColumn(Custom1, "teams"),
    #"Expanded {0}2" = Table.ExpandRecordColumn(#"Expanded {0}1", "teams", {"teamname","respondents"}, {"Team", "respondents"}),
    #"Added Custom" = Table.AddColumn(#"Expanded {0}2", "R", each List.Transform(Record.FieldNames([respondents]), each [Resp = _])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "C", each List.Zip({[R],Record.FieldValues([respondents])})),
    #"Added Custom2" = Table.TransformColumns(#"Added Custom1", {{"C", each List.Transform(_, each Record.Combine(_))}}),
    #"Expanded {0}3" = Table.ExpandListColumn(#"Added Custom2", "C"),
    #"Expanded {0}4" = Table.ExpandRecordColumn(#"Expanded {0}3", "C", {"Resp", "created", "m"}, {"Respondent", "Created", "M"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}4",{"respondents", "R"})
in
    #"Removed Columns"

not optimal, but works