Need Help with import if JSON with seperate keys and values

[Anonymous]

Hello community, 

I have a slight problem with importing an JSON over a web API (I am fairly new to power Bi).  My issue is that I get a separate keys list and a value list looking like this: 

In the keys list there are the header information like date, name for example. And in the values list there are the data that matches the headings. When I convert the values list into a table and then expand the table into new lines it puts all the data in one column like this, everything is written in a column one below the other.

How do I manage that I get the keys list as the headers and the values corresponding to the headers? I tried to extract the values and separate them with an ; and then split them. But then I have to manually do all the headers by hand.

I hope this is somewhat understandable 🙂

[lbendlin]

Expand both lists to tables

Add Index column to both tables

merge the tables based on the index column

[Anonymous]

Hey lbendlin,

first thank you for your reply.

I am running into a problem here. My keys table has only 11 columns and my values table has 400 columns . 

How do I merge 2 tables on that kind of index?

[lbendlin · Accepted solution]

Do the values come in groups of 11? In that case you can change their index to a modulo (is repeated groups of 0..10

[Anonymous]

Hey Ibendlin,

I tried your modulu Version and it worked also. I also found this Solution to the Problem. I just had to rename some of the Columns.

https://community.powerbi.com/t5/Power-Query/Multi-Dimensional-Json-file-to-Table/td-p/113667

Thanks a lot<3