Forum Discussion

Martin Kimzey's avatar
Martin Kimzey
Copper Contributor
Jun 19, 2023

Why is there no option to import Json into Excel?

I am using Microsoft Excel 2016.  (16.0.5400.1000).   My Data Ribbon has no options to open a Json file. Much of the documentation for 2016 looks nothing like my Data Ribbon.     There is ...
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 22, 2023

Martin Kimzey 

 

I have a Query Tab in my spreadsheet - but every item is grayed out...They are enabled in your "PQIsJasonDocumentAvailable.xlsx". How did you enable the Query tab items?
Not sure at all as I don't see what you see but your Query tab (and its options) is probably only available (not grayed out) when at least one query exist in your workbook. The fact that the Query tab is active with the file I shared seems to confirm this assumption

 

Looking at the initial picture you posted, in the Data tab there's a New Query option:

 

 

If you natigate in the sub-options you should find something like Blank Query. If you find this select that option and the Power Query Editor will be lauched to create a new query from scratch

 

From there I can get your started...

Martin Kimzey's avatar
Martin Kimzey
Copper Contributor
Jun 22, 2023

Lorenzo There is nothing like "Blank Query" under New Query.  

 

 

I have tried the "From Text" item. But this launches the CSV text import wizard. 

 

I also tried the "From Web" item in the "From Other Sources".  When I specify the Json file instead of a URL, it opens the Json file in Visual Studio.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 22, 2023

    Martin Kimzey 

     

    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

    • Martin Kimzey's avatar
      Martin Kimzey
      Copper Contributor
      Jun 22, 2023
      I have opened the Power Query Editor from the "From Other Sources". I don't see any Json options in the "New Source".

      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.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 22, 2023

        Martin Kimzey 

         

        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/

Resources