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"}
]}
]}

 

[sfluegge]

Getting closer. I was indeed missing both Remove Errors and Expand. I found Expand but for the LIFE of me cannot find Remove Errors. I found REPLACE Errors and set that to blank but that didn't help. I am ALMOST there now... https://snag.gy/EPkxiK.jpg

 

[sandy666]

when you start with PowerQuery ?

see this

 

[sfluegge]

Getting closer! I found the Expand command and that brought in the data. It still shows errors on all the "rows" from the text file that didn't have data. I also had to delete the leading coma's from the input lines of the file else Excel only saw the first line under each user.

So, where the heck is the Remove Errors option??? https://snag.gy/opEXQB.jpg

 

[sandy666]

post#12 - wait for animation

 

[sfluegge]

Winner Winner, Chicken Dinner!! Woo Hoo! Thanks a ton! I finally got through all the required steps and have output I can use. I will write myself a cheat sheet so as not to need to bother you again!!

 

[sandy666]

I think you should learn where/what/how in PQ Editor

now you can click Like/Thanks buttons on the left bottom corner in post(s) which helped you

have a nice day