none
Can't figure out how to parse JSON list of lists into a table RRS feed

  • Question

  • JSON returned (from google analytics) has an entry like:

    "rows": [
        [
            "(none)",
            "148"
        ],
        [
            "email",
            "2"
        ],
        [
            "organic",
            "183"
        ],
        [
            "referral",
            "140"
        ]
    ]

    In Power Query the column heading is "List" and each row has "List" which I can drill into. But I need to instead expand this into a table with 4 rows and 2 columns.

    Saturday, February 8, 2014 2:31 AM

Answers

  • If your data actually looks like that and is contained in some expression X, then you can say Table.FromRows(X) to convert it to a table. You can assign column names by saying Table.FromRows(X, {"Column1", "Column2"}). If you omit the names, they'll be assigned default values.

    There may be a UI way to do this as well, but I'm an "M" kind of guy...

    Monday, February 10, 2014 9:14 PM

All replies

  • I thought to transform this JSON into XML Spreadsheet format, but I just did a test and Power Query cannot parse Excel XML Spreadsheet files. Frankly I was surprised that it couldn't read it since it is a native Excel format. 

    If I were to transform my JSON into a format that Power Query can understand and that allows me to specify data types and ideally also formats, what text file formats should I be considering.  XML Spreadsheet is the only text file format I know that allows you to specify data types.  But I have a feeling that I am overlooking some other options.

    OData would be great except that Power Query insists on also asking for the metadata in a separate call. It would be great if Power Query could RESTify the OData request by either not making the $metadata request (another argument to the OData function?) or just continuing on if that HTTP call failed.

    Saturday, February 8, 2014 5:20 AM
  • Hi,

    Did you try turning the list into a table by using "To Table" in the List ribbon tab or list header context menu? This should turn the list into a single column table which you can then expand to promote the nested lists into table rows.

    Thanks,
    M.

    Saturday, February 8, 2014 8:59 PM
  • Hi Miguel,

    Yes, I did try that. But because the JSON is in the form of an array of arrays, the "To Table" expands the nested list into a table with one column and 8 rows instead of 2 columns and 4 rows that I desire. I expect that if it were an array of objects instead that Power Query would expand the four rows horizontally instead of vertically. But I was wondering if there's a way to force it to expand horizontally even with the JSON I have.

     - Chris

    Saturday, February 8, 2014 10:20 PM
  • If your data actually looks like that and is contained in some expression X, then you can say Table.FromRows(X) to convert it to a table. You can assign column names by saying Table.FromRows(X, {"Column1", "Column2"}). If you omit the names, they'll be assigned default values.

    There may be a UI way to do this as well, but I'm an "M" kind of guy...

    Monday, February 10, 2014 9:14 PM
  • As Curt mentioned Table.FromRows is the easiest way to do this. If you want to go through the UI, you can also add custom columns that get the first and second elements from the list. After converting the list to a table, you would add a custom column using this as the first custom formula:

    = [Column1]{0}

    and this as the second custom formula:

    = [Column1]{1}

    You can then remove the first column to get the final table.

    Monday, February 10, 2014 9:36 PM
  • I'm going to post-process the original feed to make it easier for Power Query to consume.

    Thank you both for the suggestions. I'm trying to become more of an "M" kind of guy ;)

    - Chris

    Monday, February 10, 2014 11:54 PM
  • This is exactly what I need to do, but I'm very newbie to all this.... 

    where do I apply Table.FromRows when all I have is...

    let

    source = json.document(.....) //the List column comes from this web query

    in 

    Source


    Richard

    Thursday, February 12, 2015 1:15 AM
  • In the advanced editor, edit the query to say

    let
        Source = Json.Document(...),
        Table = Table.FromRows(Source),
    in
        Table

    Thursday, February 12, 2015 1:29 PM