Forum Discussion

SridharDasari's avatar
SridharDasari
Copper Contributor
Sep 12, 2020

Excel can Convert JSON data to Table in Excel

Hi,   Actually i am getting data from external source through API by using "From Web" option in excel & getting JSON data. But after getting data in excel, trying to transform that JSON data into T...
developer
excel
Excel for web
Macros and VBA
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 12, 2020

SridharDasari Not sure how you connected to the JSON data, but I copied the table in columns A and B on the "Actual Data" sheet to columns E and F. then I created a fresh query on that table and loaded the end result into Sheet1. Looks like what you asked for. I hope this is helpful and trust you will be able to figure out what I did to create the output.

 

 

SridharDasari's avatar
SridharDasari
Copper Contributor
Sep 13, 2020

Hi Riny_van_Eekelen ,

 

I have got that JSON data by using an API, nothing more than that.

 

Thanks for your solution, the solution you provided is exactly what i needed. But, i need to know why you copied the columns A & B to columns E & F. Why don't you do on the columns i have given.

 

I have tried in original columns it is not working. Will you please elaborate how can i proceed with that. Let me know if any issues if we do on the same columns.

 

Thanks

Sridhar Dasari

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 13, 2020

    SridharDasari When I worked on my initial answer I had some error messages form the original data. Can't remember exactly what. Didn't have much time so decided to just copy it to get a clean start, assuming you would be able to adopt my query to your own file.

     

    Now, I had a chance to apply the same steps to your original data, called "Table5".

    • SridharDasari's avatar
      SridharDasari
      Copper Contributor
      Sep 13, 2020

      Thanks Riny_van_Eekelen 

       

      Will you please update the steps that you have done now. Because query looks same as original.

       

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"CommitmentID", type text}, {"CommitmentName", type text}, {"Type", type text}, {"Hours", Int64.Type}, {"CommittedCost", Int64.Type}, {"ActualCost", Int64.Type}, {"OpenCommitment", Int64.Type}})
      in
      #"Changed Type"

       

      In the above code it is showing "Table1_2" not "Table5"(Highlighted). So please give me the steps what you done.

       

      Thanks

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Sep 13, 2020

        SridharDasari "Table1_2" is the name of the table that gets loaded back into Excel. If you want to change the data types, just add a step at the end of the query that generates the output table. Done that in the attached file.

         

        In summar,y all that the query does is:

         

        1. Connect to the data table called "Table5"

        2. Trim and clean both columns to get rid of trailing and/or leading spaces and possible other "strange" characters

        3. Add an index column and filter out rows that have no data ("null") in Column1

        4. Pivot Column1 with values from from Column2 (Don't aggregate in Advance options)

        5. Fill up the six last columns

        6. Filter "null" from the CommentID column

        7. Remove the index

        8. Change data types

         

        Close and load to a table in Excel.

         

         Does this answer your questions?