SOLVED

Power Query Editor unpack JSON

%3CLINGO-SUB%20id%3D%22lingo-sub-2898588%22%20slang%3D%22en-US%22%3EPower%20Query%20Editor%20unpack%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2898588%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%26nbsp%3B%20I%20have%20a%20spreadsheet%20with%205%20columns.%26nbsp%3B%20One%20of%20those%20columns%20contains%20a%20json%20array%20(the%20array%20also%20has%20nested%20arrays).%26nbsp%3B%20Is%20it%20possible%20to%20use%20Power%20Query%20Editor%20to%20parse%20the%20json%20array%20in%20each%20row%3F%26nbsp%3B%20So%20that%26nbsp%3B%20each%20row%20has%20the%20four%20original%20columns%20plus%20the%20new%20columns%20for%20each%20value%20in%20the%20Json%20array%20and%20sub%20arrays%3F%26nbsp%3B%20While%20the%20data%20in%20each%20JSON%20array%20may%20be%26nbsp%3B%20unique%2C%20the%20structure%20is%20always%20the%20same%2C%20though%20the%20number%20of%20elements%2Fsub%20arrays%20fluctuates.%20%26nbsp%3B%20I%20have%20uploaded%20an%20example%20spread%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2898588%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2898876%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Editor%20unpack%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2898876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199348%22%20target%3D%22_blank%22%3E%40DreamDelerium%3C%2FA%3E%26nbsp%3BYes%20you%20can.%20Connect%20to%20the%20table%20with%20PQ%2C%20Select%20the%20column%20with%20the%20JSON%20code.%20Tra%2Cnsform%2C%20Parse%2C%20JSON.%20Expande%20fields%20to%20new%20rows.%20Expend%20records%2C%20OK.%20Repeat%20expanding%20fields%20and%20records%20until%20all%20columns%20contain%20data%20only.%20No%20M-coding%20required%2C%20just%20pushing%20buttons%20in%20the%20correct%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2900474%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Editor%20unpack%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2900474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you!%26nbsp%3B%20Your%20steps%20have%20gotten%20me%20part%20way%20there.%26nbsp%3B%20But%2C%20I%20can't%20seem%20to%20follow%20your%20steps%20on%20the%20sub%20arrays.%26nbsp%3B%20These%20are%20the%20steps%20I%20have%20taken%3A%3C%2FP%3E%3CP%3E1.%26nbsp%3B%20Open%20Excel%20and%20Go%20To%20Data%20Tab%3C%2FP%3E%3CP%3E2.%26nbsp%3B%20Select%20Get%20Data-%26gt%3BFrom%20File-%26gt%3BFrom%20Workbook%20(and%20select%20the%20workbook%20with%20the%20raw%20data)%3C%2FP%3E%3CP%3E3.%26nbsp%3B%20In%20the%20Navigator%20window%2C%20select%20the%20Excel%20tab%20with%20the%20data%20and%20click%20the%20%22Transform%20Data%22%20button.%26nbsp%3B%20This%20opens%20the%20Power%20Query%20Editor%3C%2FP%3E%3CP%3E4.%26nbsp%3B%20Select%20the%20column%20with%20JSON%20data%20and%20right%20click%2C%20selecting%20Transform-%26gt%3BJson.%26nbsp%3B%20This%20converts%20each%20record%20as%20a%20%22list%22%3C%2FP%3E%3CP%3E5.%26nbsp%3B%20Click%20the%20double%20arrow%20icon%20on%20the%20top%20right%20corner%20of%20the%20column%20header%20and%20select%20%22Expand%20to%20new%20rows%22.%26nbsp%3B%20This%20converts%20the%20fields%20from%20%22list%22%20to%20%22Record%22%3C%2FP%3E%3CP%3E6.%26nbsp%3B%20Click%20the%20double%20arrow%20icon%20again%2C%20which%20opens%20a%20dialog%20with%20all%20the%20fields%20in%20the%20json%20array%2C%20and%20make%20sure%20all%20columns%20are%20selected%20an%20click%20OK.%3C%2FP%3E%3CP%3E7.%26nbsp%3B%20This%20expands%20all%20the%20json%20data%20into%20new%20columns.%26nbsp%3B%20Find%20the%20column%20with%20the%20second%20layer%20of%20json%20data%20and%20select%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E8.%26nbsp%3B%20Each%20item%20in%20this%20column%20says%20%22List%22.%26nbsp%3B%20But%2C%20when%20I%20right%20click%2C%20there%20is%20no%20%22Transform%22%20option.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20how%20do%20I%20do%20this%20for%20the%20second%20layer%20of%20JSON%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2901168%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Editor%20unpack%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901168%22%20slang%3D%22en-US%22%3EI%20think%20I%20found%20the%20issue.%20On%20occasion%2C%20the%20sub%20array%20will%20have%20sublist%3Afalse%20instead%20of%20sublist%3A%5B%5D.%20Is%20there%20a%20way%20to%20skip%20the%20json%20formatting%20process%20for%20the%20sub%20array%20if%20it%20is%20%22False%22%3F%20Or%2C%20is%20this%20something%20I%20would%20need%20to%20do%20before%20bringing%20into%20Excel%20PQ%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2902099%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Editor%20unpack%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2902099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20To%20make%20things%20a%20little%20easier%20to%20reproduce%20the%20error%2C%20I%20have%20attached%20a%20more%20concise%20example%20of%20the%20issue.%26nbsp%3B%20In%20this%20example%2C%20the%20field%20%22sublist%22%20can%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3Esublist%3A%5B%5D%0Asublist%3A%5B%7B%22option_1%22%3A1%2C%20%22option_2%3A%20%22aaa%22%2C%20option_3%3A%22%22%7D%5D%0Asublist%3Afalse%3C%2FPRE%3E%3CP%3EIn%20this%20example%2C%20if%20the%20sublist%20is%20%22false%22%20then%20it%20does%20not%20appear%20that%20I%20can%20parse%20the%20entire%20column.%20%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture_ExcelEx.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321619i6E13D07A7E8B1BA3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture_ExcelEx.PNG%22%20alt%3D%22Capture_ExcelEx.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello.  I have a spreadsheet with 5 columns.  One of those columns contains a json array (the array also has nested arrays).  Is it possible to use Power Query Editor to parse the json array in each row?  So that  each row has the four original columns plus the new columns for each value in the Json array and sub arrays?  While the data in each JSON array may be  unique, the structure is always the same, though the number of elements/sub arrays fluctuates.   I have uploaded an example spread sheet.

5 Replies

@DreamDelerium Yes you can. Connect to the table with PQ, Select the column with the JSON code. Tra,nsform, Parse, JSON. Expande fields to new rows. Expend records, OK. Repeat expanding fields and records until all columns contain data only. No M-coding required, just pushing buttons in the correct order.

 

See attached.

@Riny_van_EekelenThank you!  Your steps have gotten me part way there.  But, I can't seem to follow your steps on the sub arrays.  These are the steps I have taken:

1.  Open Excel and Go To Data Tab

2.  Select Get Data->From File->From Workbook (and select the workbook with the raw data)

3.  In the Navigator window, select the Excel tab with the data and click the "Transform Data" button.  This opens the Power Query Editor

4.  Select the column with JSON data and right click, selecting Transform->Json.  This converts each record as a "list"

5.  Click the double arrow icon on the top right corner of the column header and select "Expand to new rows".  This converts the fields from "list" to "Record"

6.  Click the double arrow icon again, which opens a dialog with all the fields in the json array, and make sure all columns are selected an click OK.

7.  This expands all the json data into new columns.  Find the column with the second layer of json data and select it. 

8.  Each item in this column says "List".  But, when I right click, there is no "Transform" option. 

 

So, how do I do this for the second layer of JSON data?

 

 

I think I found the issue. On occasion, the sub array will have sublist:false instead of sublist:[]. Is there a way to skip the json formatting process for the sub array if it is "False"? Or, is this something I would need to do before bringing into Excel PQ?

@Riny_van_Eekelen  To make things a little easier to reproduce the error, I have attached a more concise example of the issue.  In this example, the field "sublist" can be:

 

sublist:[]
sublist:[{"option_1":1, "option_2: "aaa", option_3:""}]
sublist:false

In this example, if the sublist is "false" then it does not appear that I can parse the entire column.  Capture_ExcelEx.PNG

best response confirmed by allyreckerman (Microsoft)
Solution

I found this worked.  I first created a custom column.  Then added this as the custom forumla

 

 

=if [response_json.sublist] <> false then
Table.ToRecords(Table.FromRecords([response_json.sublist]))
else
Table.ToRecords(
Table.FromRecords({
[option_1 = "null", option_2= "null", option_3= "null"]
})
)