Forum Discussion

difat's avatar
difat
Copper Contributor
Sep 10, 2019

How to convert JSON data into Excel table in Power Query

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.

 

 

excel
json
Power Query

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 11, 2019

    difat 

    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.

Resources