Import JSON into Excel 2019

[sfluegge]

I have a Powershell script that pulls all of the Outlook Email Rules in our Office 365 Domain. I would like to output it to JSON as CSV drops columns. I have the export working fine but cannot seem to get the data to load into Excel.

I have followed several videos on how to go to Data --> Get Data --> From File --> JSON and select my datafile. This part works fine. Using the attached sample JSON file I open it, select List then Convert to Table --> Close and Load. It runs and the output shows no usable results...

Below is the content of my JSON file... (didn't see a way to attach it here).

Any help is greatly appreciated!

{"Rules":[ {"Danielle":[
{"priority":1,"user":"danielle@mycompany.com","name":"Auto-Receipt (3)","condition1":"the message was received from \u0027Auto-Receipt\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
,{"priority":2,"user":"danielle@mycompany.com","name":"Auto-Receipt (2)","condition1":"the message was received from \u0027Auto-Receipt\u0027","condition2":"and the message includes specific words in the subject \u0027Merchant Email Receipt\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
,{"priority":3,"user":"danielle@mycompany.com","name":"Auto-Receipt (1)","condition1":"the message was received from \u0027Auto-Receipt\u0027","condition2":"and the message includes specific words in the subject \u0027Successful Report.\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
]},
{"Scott":[
{"priority":1,"user":"scott@mycompany.com","name":"Synchronization with your iPhone (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone\u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
,{"priority":2,"user":"scott@mycompany.com","name":"Synchronization with your iPhone failed (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone failed\u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
,{"priority":3,"user":"scott@mycompany.com","name":"888VoIP: New Order (Migrated)","condition1":"the message includes specific words in the subject \u0027888VoIP: New Order \u0027","action1":"forward the message to \u0027Purchasing\u0027"}
,{"priority":4,"user":"scott@mycompany.com","name":"Synchronization with your iPhone failed (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone failed \u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
]}
]}

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]priority[/td][td=bgcolor:#70AD47]user[/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]condition1[/td][td=bgcolor:#70AD47]action1[/td][td=bgcolor:#70AD47]action2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]danielle@mycompany.com[/td][td=bgcolor:#E2EFDA]Auto-Receipt (3)[/td][td=bgcolor:#E2EFDA]the message was received from 'Auto-Receipt'[/td][td=bgcolor:#E2EFDA]move the message to folder 'Company.net'[/td][td=bgcolor:#E2EFDA]and stop processing more rules on this message[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]scott@mycompany.com[/td][td]Synchronization with your iPhone (Migrated)[/td][td]the message includes specific words in the subject 'Synchronization with your iPhone'[/td][td]move the message to folder 'Junk Email'[/td][td]and stop processing more rules on this message[/td][/tr]
[/table]

 

[sfluegge]

OMG Yes! That is exactly what I WANT to see but am somehow UNABLE to... It is likely user error but I SWEAR I have tried every combination of buttons. In fact, I felt like a monkey banging on the keyboard in the end just HOPING to get it to work! Please tell me how you did it! (and clearly I need STEP BY STEP instruction as I am not a SKILLED monkey...)

 

[sandy666]

first: change your file.json to file.json.txt
then: from file - txt:

[SIZE=1]// json
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("[I][COLOR="#FF0000"]path to the file[/COLOR][/I]\[COLOR="#0000FF"]file.json.txt[/COLOR]"), null, null, 1252)}),
    Parse = Table.TransformColumns(Source,{},Json.Document),
    RemError = Table.RemoveRowsWithErrors(Parse, {"Column1"}),
    Expand = Table.ExpandRecordColumn(RemError, "Column1", {"priority", "user", "name", "condition1", "action1", "action2"}, {"priority", "user", "name", "condition1", "action1", "action2"})
in
    Expand[/SIZE]

 

[sfluegge]

OK, now I get to embarrass myself further and tell you that your answer confuses me... Changing the file name is easy (though the purpose is mysterious). Then you lose me. Are you advocating that I add VM Code to an empty spreadsheet to load in the information? If so, then this greatly reduces the usefulness of using Excel as a solution... In your example you explicitly stated the number of rows in your Expand statement. I will have a variable number in a real use scenario and it will be in the hundreds of entries... I was hoping I could use the data import which calls the Power Query Editor. Will that NOT work?

 

[sandy666]

too many words for too little action

Data - New Query - From File - From Text
you will get new query then open PQ Editor and Parse this column as JSON, next Remove Errors
you will get records then Expand.

that's all​

edit:
based on your example from the first post

btw. layout is a little different 2016 vs 2019

 

[sfluegge]

Hmm... Maybe my issue is using Excel 2019? I don't HAVE that option (New Query). When I expand the Data --> Get Data I see the following: https://snag.gy/lx60wK.jpg

In my previous attempts I selected From JSON.

Following your suggestions I tried From Text/CSV and open the newly named JSON (now O365_Review_Rules - Copy.json.txt). This is what I get: https://snag.gy/RtYbNA.jpg

It is either a versioning issue OR I am missing something REALLY basic...

 

[sandy666]

second snap ! that's it !
now

Parse = Table.TransformColumns(Source,{},Json.Document),

then Expand

 

[sfluegge]

Sorry. Still no love...

Loading as specified (Data --> Get Data --> From Text
I am at a screen similar to what you show above (https://snag.gy/0mreRg.jpg)

From here I selected Edit --> Transform --> Parse --> JSON (https://snag.gy/TGguCB.jpg)

That is where Excel craps the bed and turns out nothing but errors... (https://snag.gy/7vKpkY.jpg)

I am sure you are regretting offering assistance byt this point, but it IS appreciated!!

 

[sandy666]

you did these steps:

1. Source = Table.FromColumns({Lines.FromBinary(File.Contents("json.txt"), null, null, 1252)}),
2. Parse = Table.TransformColumns(Source,{},Json.Document),

but where are the next?
should be two more :

• 
  [3]Remove Errors
  [4]Expand

RemError = Table.RemoveRowsWithErrors(Parse, {"Column1"}),
Expand = Table.ExpandRecordColumn(RemError, "Column1", {"priority", "user", "name", "condition1", "action1", "action2"}, {"priority", "user", "name", "condition1", "action1", "action2"})