Forum Discussion
Why is there no option to import Json into Excel?
Don't try text/csv or anything else like that you waste your time
From your screenshot the Blank Query option is probably in the From Other Sources sub-option
I am looking at the article at https://learn.microsoft.com/en-us/power-query/connectors/json.
Again, this article references a "Get Data" option that does not exist in this version of Power Query.
PLEASE save your and my time following below steps:
#1 Restart from scratch (close the Power Query Editor discarding all) to come back to Excel
#2 Go to Data tab > From Other Sources > Blank Query (the Power Query Editor opens & auto. creates a new query)
#3 In the Power Query Editor formula bar enter exactly (Power Query is case-sentive), replacing X:\PathToYourFile\YourJsonFile.xyz with your file path:
= Json.Document(File.Contents("X:\PathToYourFile\YourJsonFile.xyz"))then Validate. If the Path to your file is ok and it's valid Json file have fun parsing it...
Some pointers just in case:
- https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/
- https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/
Thanks for all the help. To keep the complexity and additional support requirements at a minimum, we are writing a .NET application using EPPlus and Newtonsoft.
Since this is something our users would be doing on a regular basis, we feel that a simple application would save a great deal of time and effort and not require end-users (who are not power users) to learn Power Query and the M language. The support requirement for Power Query is a non-starter.
Glad I could help a bit & Thanks for posting back
Since this is something our users would be doing on a regular basis, we feel that a simple application would save a great deal of time and effort and not require end-users (who are not power users) to learn Power Query and the M language. The support requirement for Power Query is a non-starter
Totally makes sense to me
All the best...
If I use:
= Json.Document(File.Contents("file:///C:/Projects/Workspace/Json/Roster.json"))
as my source, it loads the following. Each record below contains 171 fields. If I remember correctly, I need to create columns for each of the 171 fields.
However, all options on the "Add Column" tab are disabled.
Each record below contains 171 fields. If I remember correctly, I need to create columns for each of the 171 fields
It's all about parsing a Json. Can be easy or not. It depends on the Json and what you expect to get out of it. Have a look to the pointers in my previous reply
And if you need help parsing it please create a new discussion/thread, sharing your Json and the expected result
This discussion/thread was about getting access to a Json file with Excel 2016 where the option is obviously not available in the Excel User Interface - topic closed from my perspective
Hope this all makes sense & Thanks in advance to Mark the solution to help others who Search (topics marked appear first when you Search)