Home

How to convert JSON data into Excel table in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-847828%22%20slang%3D%22en-US%22%3EHow%20to%20convert%20JSON%20data%20into%20Excel%20table%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847828%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20import%20the%20JSON%20file%20into%20Excel%2C%20I%20am%20using%20Power%20Query%20as%20I'm%20not%20versed%20in%20VBA.%20I%20would%20like%20the%20output%20to%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131040i8FE7459EF2DDB248%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20issue%20I'm%20having%20is%20figuring%20out%20how%20to%20to%20transform%20the%20data%20based%20on%20my%20data%20I%20have.%20Below%20is%20sample%20data%3A%3C%2FP%3E%3CP%3E%7B%3CBR%20%2F%3E%22items%22%3A%20%5B%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22tableName%22%3A%20%22tableA%22%2C%3CBR%20%2F%3E%22count%22%3A%201729%2C%3CBR%20%2F%3E%22columnNames%22%3A%20%5B%3CBR%20%2F%3E%22id%22%2C%3CBR%20%2F%3E%22createdTime%22%2C%3CBR%20%2F%3E%22updatedTime%22%2C%3CBR%20%2F%3E%22name%22%2C%3CBR%20%2F%3E%22category%22%2C%3CBR%20%2F%3E%22id2%22%2C%3CBR%20%2F%3E%22subject%22%2C%3CBR%20%2F%3E%22text%22%3CBR%20%2F%3E%5D%2C%3CBR%20%2F%3E%22rows%22%3A%20%5B%3CBR%20%2F%3E%5B%3CBR%20%2F%3E%221234567%22%2C%3CBR%20%2F%3E%222019-02-02T14%3A54%3A02Z%22%2C%3CBR%20%2F%3E%222019-02-02T20%3A57%3A32Z%22%2C%3CBR%20%2F%3E%22John%20Smith%22%2C%3CBR%20%2F%3E%22123%22%2C%3CBR%20%2F%3E%221234%22%2C%3CBR%20%2F%3E%22Product%20A%20-%20need%20assistance%20%22%2C%3CBR%20%2F%3E%22%3CEM%3Etext%20field%3C%2FEM%3E%22%3CBR%20%2F%3E%5D%2C%3CBR%20%2F%3E%5B%3CBR%20%2F%3E%222345678%22%2C%3CBR%20%2F%3E%222019-02-03T14%3A54%3A02Z%22%2C%3CBR%20%2F%3E%222019-02-03T20%3A57%3A32Z%22%2C%3CBR%20%2F%3E%22John%20Smith%22%2C%3CBR%20%2F%3E%22123%22%2C%3CBR%20%2F%3E%221234%22%2C%3CBR%20%2F%3E%22Product%20B%20help%22%2C%3CBR%20%2F%3E%22%3CEM%3EText%20field%3C%2FEM%3E%22%3CBR%20%2F%3E%5D%2C%20etc%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20step%20was%20converting%20the%20json%20file%20to%20a%20table%2C%20afterwards%2C%20I%20expanded%20'value'%20cell%20to%20new%20rows%20and%20did%20so%20another%20time%2C%20to%20get%20thisimage%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131041i245E2FEA2E57129C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20there%20I%20further%20expanded%20and%20to%20get%20all%20the%20values.%20I%20tried%20removing%20duplicates%20and%20transpose%20but%20those%20options%20are%20leading%20to%20the%20tabular%20data%20I%20am%20looking%20for.%20How%20do%20I%20go%20about%20solving%20this%20issue%3F%3C%2FP%3E%3CP%3EThanks%20for%20your%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-847828%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EJson%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-849227%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%20JSON%20data%20into%20Excel%20table%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-849227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406880%22%20target%3D%22_blank%22%3E%40difat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20733px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131192iCE73F0BFFCD4CF4A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eas%20next%20step%20from%20your%20second%20screenshort%20you%20may%20expand%20last%20column%20%22rows%22%20to%20rows.%20After%20that%20add%20custom%20column%20to%20create%20records%20from%20two%20last%20columns%20using%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3Eeach%20Record.FromList(%5Brows%5D%2C%5BcolumnNames%5D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EHere%20keep%20only%20this%20column%20and%20expand%20it.%20Generated%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Parsed%20JSON%22%20%3D%20Table.TransformColumns(Source%2C%7B%7D%2CJson.Document)%2C%0A%20%20%20%20%23%22Expanded%20JSON%22%20%3D%20Table.ExpandRecordColumn(%0A%20%20%20%20%20%20%20%20%23%22Parsed%20JSON%22%2C%0A%20%20%20%20%20%20%20%20%22JSON%22%2C%0A%20%20%20%20%20%20%20%20%7B%22items%22%7D%2C%20%7B%22items%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20items%22%20%3D%20Table.ExpandListColumn(%23%22Expanded%20JSON%22%2C%20%22items%22)%2C%0A%20%20%20%20ExpandedRecords%20%3D%20Table.ExpandRecordColumn(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20items%22%2C%0A%20%20%20%20%20%20%20%20%22items%22%2C%0A%20%20%20%20%20%20%20%20%7B%22tableName%22%2C%20%22count%22%2C%20%22columnNames%22%2C%20%22rows%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22tableName%22%2C%20%22count%22%2C%20%22columnNames%22%2C%20%22rows%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20rows%22%20%3D%20Table.ExpandListColumn(ExpandedRecords%2C%20%22rows%22)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20rows%22%2C%0A%20%20%20%20%20%20%20%20%22Records%22%2C%0A%20%20%20%20%20%20%20%20each%20Record.FromList(%5Brows%5D%2C%5BcolumnNames%5D)%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Records%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20Records%22%20%3D%20Table.ExpandRecordColumn(%0A%20%20%20%20%20%20%20%20%23%22Removed%20Other%20Columns%22%2C%0A%20%20%20%20%20%20%20%20%22Records%22%2C%0A%20%20%20%20%20%20%20%20%7B%22id%22%2C%20%22createdTime%22%2C%20%22updatedTime%22%2C%20%22name%22%2C%20%22category%22%2C%20%22id2%22%2C%20%22subject%22%2C%20%22text%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22id%22%2C%20%22createdTime%22%2C%20%22updatedTime%22%2C%20%22name%22%2C%20%22category%22%2C%20%22id2%22%2C%20%22subject%22%2C%20%22text%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Expanded%20Records%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20sample%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863879%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%20JSON%20data%20into%20Excel%20table%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-864088%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%20JSON%20data%20into%20Excel%20table%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406880%22%20target%3D%22_blank%22%3E%40difat%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
difat
New Contributor

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:

clipboard_image_0.png

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: 

clipboard_image_1.png

 

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.

 

 

3 Replies

@difat 

For such result

image.png

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies