WilberBeast

I was recently stuck trying to load a JSON source into PowerBI as the source itself appeared to be at its core a list of lists as per the below example.

{
   "error":[],
   "result":{
      "XETHZUSD":[
         [
            1438905600,
            "3.00000",
            "3.00001",
            "3.00000",
            "3.00000",
            "3.00000",
            "123.93056831",
            4
         ],
         [
            1438992000,
            "3.00000",
            "3.00000",
            "0.15000",
            "1.19999",
            "0.59918",
            "1942.88814725",
            20
         ],
         [
            1439164800,
            "1.19999",
            "1.19999",
            "1.19999",
            "1.19999",
            "0.00000",
            "0.00000000",
            0
         ],
         [
            1439251200,
            "0.79969",
            "1.15000",
            "0.65038",
            "0.99000",
            "0.77715",
            "9234.56870480",
            84
         ],
         [
            1439337600,
            "1.00000",
            "1.28800",
            "0.90500",
            "1.28800",
            "1.08120",
            "1604.20230300",
            44
         ]
      ],
      "last":1462752000
   }
}

This is actually an extract of the result from the following public API call to Kraken. https://api.kraken.com/0/public/OHLC?pair=XETHZUSD&interval=1440

When trying to load this as a Web source it was initially presented as expected as shown below.

Drilling down on the ‘result’ Record produced the following where XETHZUSD was presented as a list. No problem so far.

Drilling down further on the XETHZUSD list produced the following, a list of lists.

There is not much that can be done at this point without transforming the list to a table, however this is only presented the list of lists as a column of lists as follows.

Expanding the columns does not create a result that we would be looking for as all the content from each individual list is merely expanded into the same list as shown below.

The secret is to add columns that reference specific items within the List stored in ‘Column1’.

To do this I added a new column with the following formula:

List.First([Column1])

This statement returns the first item in the list stored in ‘Column1’.

This is great – it allows us to create our first column by dipping into the list. However, there is not a direct way to access individual elements of a list given their position.

Therefore to get the second item we need to skip over the first item. Thus, the formula for the second column is as follows:

List.First(List.Skip([Column1],1))

This can then be repeated as required, e.g.

List.First(List.Skip([Column1],1))

List.First(List.Skip([Column1],2))

List.First(List.Skip([Column1],3))

etc.

Once you have all the elements of the list required in their own separate columns the original list column, ‘Column1’ in our example here, can be removed. The final result is as shown below.