Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need Help with import if JSON with seperate keys and values

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's avatar
    lbendlin
    5 years ago

    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

4 Replies

  • Expand both lists to tables

    Add Index column to both tables

    merge the tables based on the index column

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey 

       

      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's avatar
        lbendlin
        Icon for Super User rankSuper User

        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