Need help with JSON in Power Query

cleverclog

New Member
Joined
Dec 27, 2017
Messages
7
Hello all,

Please forgive me if my post does not sound very professiona as I am quite proficient in Power Query when I extract data from local sources, but this time I have faced a REST API and got a bit lost.

I have connected through a REST API and receive data in JSON format. Surprisingly, I have a separate list of headers and a separate list of rows. A list of rows contains 26 lists (which is the number of items in my database for a selected period) and this time every list contains 87 records (which corresponds to the number of columns in my table).

My issue is that I do not know how to combine all those records and transpose them before I expand the column with lists. Otherwise, I get all the values stacked in a column view instead of 26 rows of 87 columns.

Screen capture

I will sppreciate if you could guide me a little bit with this issue.
Thanks
Lukas
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help with JSON format data pulled through API
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help with JSON format data pulled through API
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
OK, sorry. I was desperate to find the solution but I managed to do it myself.
 
Upvote 0
OK, sorry. I was desperate to find the solution but I managed to do it myself.
As we said, it is OK to Cross-Post here, provided that you follow the guidelines for doing so.

Also, if you have a solution to your problem, could you post it back here to the thread?
We like this forum to be a searchable database, so if others have a similar issue and come across this thread, they can find the answer.

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top