Excel can Convert JSON data to Table in Excel

Question

Hi,&nbsp;Actually i am getting data from external source through API by using "From Web" option in excel &amp; getting JSON data. But after getting data in excel, trying to transform that JSON data into Table by using power query. But, it is not working. JSON data is not transforming into proper table.&nbsp;I have tried pivot, transpose options in power query, still not working. Attached Excel for reference that what is required &amp; what i am getting.&nbsp;Let me know if any solutions are available for this. Any insights are highly appreciated.&nbsp;ThanksSridhar Dasari

riny_van_eekelen · Answer

SridharDasari&nbsp;"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.&nbsp;In summar,y all that the query does is:&nbsp;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" characters3. Add an index column and filter out rows that have no data ("null") in Column14. Pivot Column1 with values from from Column2 (Don't aggregate in Advance options)5. Fill up the six last columns6. Filter "null" from the CommentID column7. Remove the index8. Change data types&nbsp;Close and load to a table in Excel.&nbsp;&nbsp;Does this answer your questions?

riny_van_eekelen · Answer

SridharDasari&nbsp;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.&nbsp;&nbsp;

sridhardasari · Answer

Hi&nbsp;Riny_van_Eekelen&nbsp;,&nbsp;I have got that JSON data by using an API, nothing more than that.&nbsp;Thanks for your solution, the solution you provided is exactly what i needed. But, i need to know why you copied the columns A &amp; B to columns E &amp; F. Why don't you do on the columns i have given.&nbsp;I have tried in original columns it is not working. Will you please elaborate how can i proceed with that.&nbsp;Let me know if any issues if we do on the same columns.&nbsp;ThanksSridhar Dasari&nbsp;

riny_van_eekelen · Answer

SridharDasari&nbsp;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.&nbsp;Now, I had a chance to apply the same steps to your original data, called "Table5".

sridhardasari · Answer

Thanks&nbsp;Riny_van_Eekelen&nbsp;&nbsp;Will you please update the steps that you have done now. Because query looks same as original.&nbsp;letSource = 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"&nbsp;In the above code it is showing "Table1_2" not "Table5"(Highlighted). So please give me the steps what you done.&nbsp;Thanks

Forum Discussion

Excel can Convert JSON data to Table in Excel

6 Replies

Resources