Help creating a table from some awkward api results - aka square pegs and round holes

[mkredpoint]

Hi gang!

Newbie here - tasked with building a PowerBI custom connector to one of our APIs for loading some data. I have a lot of the pieces of the connector working (OAuth2, polling for job results using wait/for pattern), but when I get the final record set from the API, it's in a very unfriendly structure. Unfortunately, changing the API results is not an option, so I'm stuck with working with this format, which I need to manipulate into a Power Query table object. I've included a simplified version of the response payload below.

Essentially, we have a 'viewAttributes' array of records containing what I want to be the column names, and then a 'resultsRows' array of records that each has a 'values' array that corresponds to the values for the columns. These results can vary, but the number of values records will always line up with the # of column headers. So I want to dynamically create the following table. Is this doable using Power Query M language?

Education	Gender	Income
Graduate Degree likely	F	$125,000-$149,999
Less than High School Diploma	M	$25,000-$49,999
High School Diploma	UNKNOWN	$50,000-$74,999

{
  "id": "abc123",
  "viewAttributes": [
    {
      "id": "69c07576-060f-448b-bd42-c365f8f46986",
      "name": "Education"
    },
    {
      "id": "7217d3ce-f6e8-4c3e-bd07-2cb8783043aa",
      "name": "Gender"
    },
    {
      "id": "90b4cea3-4d54-471a-9051-dac918af6252",
      "name": "Income"
    }
  ],
  "resultRows": [
    {
      "values": [
        {
          "id": "69c07576-060f-448b-bd42-c365f8f46986",
          "value": "Graduate Degree likely"
        },
        {
          "id": "7217d3ce-f6e8-4c3e-bd07-2cb8783043aa",
          "value": "F"
        },
        {
          "id": "90b4cea3-4d54-471a-9051-dac918af6252",
          "value": "$125,000-$149,999"
        }
      ]
    },
    {
      "values": [
        {
          "id": "69c07576-060f-448b-bd42-c365f8f46986",
          "value": "Less than High School Diploma Extremely likely"
        },
        {
          "id": "7217d3ce-f6e8-4c3e-bd07-2cb8783043aa",
          "value": "M"
        },
        {
          "id": "90b4cea3-4d54-471a-9051-dac918af6252",
          "value": "$25,000-$49,999"
        }
      ]
    },
    {
      "values": [
        {
          "id": "69c07576-060f-448b-bd42-c365f8f46986",
          "value": "High School Diploma likely"
        },
        {
          "id": "7217d3ce-f6e8-4c3e-bd07-2cb8783043aa",
          "value": "UNKNOWN"
        },
        {
          "id": "90b4cea3-4d54-471a-9051-dac918af6252",
          "value": "$50,000-$99,999"
        }
      ]
    }
  ]
}

[AlexisOlson · Accepted solution]

Ugh. Power Query can parse JSON fine but there's still a good bit of work left to do (joining on id and pivoting).

Try this:

let
    JSON = "
    {
      ""id"": ""abc123"",
      ""viewAttributes"": [
        {
          ""id"": ""69c07576-060f-448b-bd42-c365f8f46986"",
          ""name"": ""Education""
        },
        {
          ""id"": ""7217d3ce-f6e8-4c3e-bd07-2cb8783043aa"",
          ""name"": ""Gender""
        },
        {
          ""id"": ""90b4cea3-4d54-471a-9051-dac918af6252"",
          ""name"": ""Income""
        }
      ],
      ""resultRows"": [
        {
          ""values"": [
            {
              ""id"": ""69c07576-060f-448b-bd42-c365f8f46986"",
              ""value"": ""Graduate Degree likely""
            },
            {
              ""id"": ""7217d3ce-f6e8-4c3e-bd07-2cb8783043aa"",
              ""value"": ""F""
            },
            {
              ""id"": ""90b4cea3-4d54-471a-9051-dac918af6252"",
              ""value"": ""$125,000-$149,999""
            }
          ]
        },
        {
          ""values"": [
            {
              ""id"": ""69c07576-060f-448b-bd42-c365f8f46986"",
              ""value"": ""Less than High School Diploma Extremely likely""
            },
            {
              ""id"": ""7217d3ce-f6e8-4c3e-bd07-2cb8783043aa"",
              ""value"": ""M""
            },
            {
              ""id"": ""90b4cea3-4d54-471a-9051-dac918af6252"",
              ""value"": ""$25,000-$49,999""
            }
          ]
        },
        {
          ""values"": [
            {
              ""id"": ""69c07576-060f-448b-bd42-c365f8f46986"",
              ""value"": ""High School Diploma likely""
            },
            {
              ""id"": ""7217d3ce-f6e8-4c3e-bd07-2cb8783043aa"",
              ""value"": ""UNKNOWN""
            },
            {
              ""id"": ""90b4cea3-4d54-471a-9051-dac918af6252"",
              ""value"": ""$50,000-$99,999""
            }
          ]
        }
      ]
    }
    ",
    #"Parsed JSON" = Json.Document(JSON),
    resultRows = List.Transform(#"Parsed JSON"[resultRows], each [values]),
    #"Converted to Table" = Table.FromList(resultRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
    #"Expanded Lists" = Table.ExpandListColumn(#"Added Index", "Column1"),
    #"Expanded Records" = Table.ExpandRecordColumn(#"Expanded Lists", "Column1", {"id", "value"}, {"id", "value"}),
    viewAttributes = Table.FromRecords(#"Parsed JSON"[viewAttributes]),
    #"Merged Queries" = Table.NestedJoin(viewAttributes, {"id"}, #"Expanded Records", {"id"}, "viewAttributes", JoinKind.LeftOuter),
    #"Expanded values" = Table.ExpandTableColumn(#"Merged Queries", "viewAttributes", {"value", "Index"}, {"value", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded values",{"id"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[name]), "name", "value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Index", Int64.Type}, {"Education", type text}, {"Gender", type text}, {"Income", type text}})
in
    #"Changed Type"

[mkredpoint]

Thanks - this is exactly what I needed. I hope you gain some satisfaction knowing that you saved an internet stranger hours of frustration and futile trial and error attempts.