none
Date and Value Stuck in Same Column - Need to Separate & Move Up

    Question

  • For whatever reason, data that I'm retrieving from this site: http://www.eia.gov/beta/api/qb.cfm?category=472142&sdid=NG.RES_EPG0_R5302_SAL_BCF.A comes back to PowerQuery with the Value's Date & the Value itself in one column (custom).  You can see the date (left justified) and value (right justified) need to be split into two columns in which they meet/match up.

    I've tried several UI based approaches - Group by (using max), Pivot/Unpivot, merge and others - no joy.

    I think I need to have a routine in the query that indexs the custom column and does some logic that takes every other entry and somehow moves it up a row.

    But, my lack of programming skills has me stymied.

    Am I missing a UI trick that can perform this feat of magic?

    Thanks in advance.

    |Custom|

    2002

        0



    Sunday, June 08, 2014 2:10 PM

Answers

  • When I go to http://api.eia.gov/series/?api_key={api_key}&series_id=NG.RES_EPG0_R5302_SAL_BCF.A, Power Query identifies the data as JSON and gives me a complex object as a result. When I think click on the "series" member, expand the only record and then click "data", I get a list of values, each of which is itself a list consisting of the year and the value. At this point, the Power Query editor doesn't seem to give me the choice I want, which is to be able to expand the list of lists into a table. So I go to View | Advanced Editor and add one extra line of code:

    table = Table.FromRows(data, {"Year", "Value"})

    I think this produces what you want. The complete program looks like this:

    let
        Source = Json.Document(Web.Contents("http://api.eia.gov/series/?api_key={api_key}&series_id=NG.RES_EPG0_R5302_SAL_BCF.A")),
        series = Source[series],
        series1 = series{0},
        data = series1[data],
        table = Table.FromRows(data, {"Year", "Value"})
    in
        table

    If this doesn't answer your question, then please supply the M code you've generated in the editor (being sure to redact the API key).

    Sunday, June 08, 2014 8:47 PM

All replies

  • When I go to http://api.eia.gov/series/?api_key={api_key}&series_id=NG.RES_EPG0_R5302_SAL_BCF.A, Power Query identifies the data as JSON and gives me a complex object as a result. When I think click on the "series" member, expand the only record and then click "data", I get a list of values, each of which is itself a list consisting of the year and the value. At this point, the Power Query editor doesn't seem to give me the choice I want, which is to be able to expand the list of lists into a table. So I go to View | Advanced Editor and add one extra line of code:

    table = Table.FromRows(data, {"Year", "Value"})

    I think this produces what you want. The complete program looks like this:

    let
        Source = Json.Document(Web.Contents("http://api.eia.gov/series/?api_key={api_key}&series_id=NG.RES_EPG0_R5302_SAL_BCF.A")),
        series = Source[series],
        series1 = series{0},
        data = series1[data],
        table = Table.FromRows(data, {"Year", "Value"})
    in
        table

    If this doesn't answer your question, then please supply the M code you've generated in the editor (being sure to redact the API key).

    Sunday, June 08, 2014 8:47 PM
  • That worked.  Thanks Curt.
    Monday, June 09, 2014 1:44 AM