I would like to import the JSON file into Excel, I am using Power Query as I'm not versed in VBA. I would like the output to look like this:
The issue I'm having is figuring out how to to transform the data based on my data I have. Below is sample data:
{
"items": [
{
"tableName": "tableA",
"count": 1729,
"columnNames": [
"id",
"createdTime",
"updatedTime",
"name",
"category",
"id2",
"subject",
"text"
],
"rows": [
[
"1234567",
"2019-02-02T14:54:02Z",
"2019-02-02T20:57:32Z",
"John Smith",
"123",
"1234",
"Product A - need assistance ",
"text field"
],
[
"2345678",
"2019-02-03T14:54:02Z",
"2019-02-03T20:57:32Z",
"John Smith",
"123",
"1234",
"Product B help",
"Text field"
], etc
}
The first step was converting the json file to a table, afterwards, I expanded 'value' cell to new rows and did so another time, to get thisimage:
From there I further expanded and to get all the values. I tried removing duplicates and transpose but those options are leading to the tabular data I am looking for. How do I go about solving this issue?
Thanks for your time.
For such result
as next step from your second screenshort you may expand last column "rows" to rows. After that add custom column to create records from two last columns using
each Record.FromList([rows],[columnNames]
Here keep only this column and expand it. Generated script is
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
#"Expanded JSON" = Table.ExpandRecordColumn(
#"Parsed JSON",
"JSON",
{"items"}, {"items"}
),
#"Expanded items" = Table.ExpandListColumn(#"Expanded JSON", "items"),
ExpandedRecords = Table.ExpandRecordColumn(
#"Expanded items",
"items",
{"tableName", "count", "columnNames", "rows"},
{"tableName", "count", "columnNames", "rows"}
),
#"Expanded rows" = Table.ExpandListColumn(ExpandedRecords, "rows"),
#"Added Custom" = Table.AddColumn(
#"Expanded rows",
"Records",
each Record.FromList([rows],[columnNames])
),
#"Removed Other Columns" = Table.SelectColumns(
#"Added Custom",
{"Records"}
),
#"Expanded Records" = Table.ExpandRecordColumn(
#"Removed Other Columns",
"Records",
{"id", "createdTime", "updatedTime", "name", "category", "id2", "subject", "text"},
{"id", "createdTime", "updatedTime", "name", "category", "id2", "subject", "text"}
)
in
#"Expanded Records"Please check sample in attached.