Forum Discussion
Help creating a table from some awkward api results - aka square pegs and round holes
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"
}
]
}
]
}
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"
2 Replies
- AlexisOlson
Super User
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"