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.
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"
}
]
}
]
}
Solved! Go to 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"
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"
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.