SOLVED

How do I load and filter JSON with Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-1688252%22%20slang%3D%22en-US%22%3EHow%20do%20I%20load%20and%20filter%20JSON%20with%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1688252%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fs.natalian.org%2F2020-09-20%2Ffilterme.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fs.natalian.org%2F2020-09-20%2Ffilterme.json%3C%2FA%3E%20I%20was%20to%20filter%20by%20the%20color%20Red%2C%20i.e.%20just%20see%20the%20rows%20%22foo%22%20and%20%22bar%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20that%20in%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20own%20attempt%20was%20%3CA%20href%3D%22https%3A%2F%2Fs.natalian.org%2F2020-09-20%2Fred.mp4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fs.natalian.org%2F2020-09-20%2Fred.mp4%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1688252%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1688322%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20load%20and%20filter%20JSON%20with%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1688322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712071%22%20target%3D%22_blank%22%3E%40kaihendry%3C%2FA%3E%26nbsp%3BAfter%20converting%20the%20list%20to%20a%20table%2C%20click%20on%20the%20button%20with%20the%20two%20arrows%20to%20expand%20Column1%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-20%20at%2007.17.12.png%22%20style%3D%22width%3A%20215px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219902i74E2F5B9987FDD10%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-09-20%20at%2007.17.12.png%22%20alt%3D%22Screenshot%202020-09-20%20at%2007.17.12.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-20%20at%2007.17.27.png%22%20style%3D%22width%3A%20368px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219903iD56728DAC5124AD8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-09-20%20at%2007.17.27.png%22%20alt%3D%22Screenshot%202020-09-20%20at%2007.17.27.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDo%20the%20same%20again%20for%20the%20color%20column%2C%20choose%20%22Expand%20to%20new%20rows%22%3C%2FP%3E%3CP%3EEnd%20result%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-20%20at%2007.18.05.png%22%20style%3D%22width%3A%20353px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219904i040C5369F48D80C8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-09-20%20at%2007.18.05.png%22%20alt%3D%22Screenshot%202020-09-20%20at%2007.18.05.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20that%20what%20you%20had%20in%20mind%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

 

https://s.natalian.org/2020-09-20/filterme.json I was to filter by the color Red, i.e. just see the rows "foo" and "bar".

 

How do I do that in Excel?

 

 

My own attempt was https://s.natalian.org/2020-09-20/red.mp4

2 Replies
Highlighted
Best Response confirmed by kaihendry (Occasional Contributor)
Solution

@kaihendry After converting the list to a table, click on the button with the two arrows to expand Column1

Screenshot 2020-09-20 at 07.17.12.png

Screenshot 2020-09-20 at 07.17.27.png

Do the same again for the color column, choose "Expand to new rows"

End result:

Screenshot 2020-09-20 at 07.18.05.png

Is that what you had in mind?

 

 

Highlighted

@Riny_van_EekelenThanks!

 

I was hoping that it wouldn't mean splitting into rows, since my JSON has many embedded lists, so there is effectively a LOT of repeating rows (besides one column).