Excel Power Query - from web with dynamic worksheet cell value

1
votes

We have a spreadsheet that gets updated monthly, which queries some data from our server.

The query url looks like this:

http://example.com/?2016-01-31

The returned data is in a json format, like below:

{"CID":"1160","date":"2016-01-31","rate":{"USD":1.22}}

We only need the value of 1.22 from the above and I can get that inserted into the worksheet with no problem.

My questions: 1. How to use a cell value [contain the date] to pass the date parameter [2016-01-31] in the query and displays the result in the cell next to it. 2. There's a long list of dates in a column, can this query be filled down automatically per each date? 3. When I load the query result to the worksheet, it always load in pairs. [taking up two cells, one says "Value", the other contains the value which is "1.22" in my case]. Ideally I would only need "1.22", not the title, can this be removed? [Del won't work, will give you a "Column 1" instead, or you have to hide the entire row which will mess up with the layout].

I know this is a lot to ask but I've tried a lot of search and reading in the last few days and I have to say the M language beats me.

Thanks in advance.

2
excelpowerquery

2 Answers

1
votes

  1. Convert your Web.Contents() request into a function:

    let
  myFunct = ( param as date ) => let
   x = Web.Contents(.... & Date.ToText(date) & ....)
  in
   x
 in
  myFunct

  2. Reference your data request function from a new query, include any transformations you need (in this case JSON.Document, table expansions, remove extraneous data. Feel free to delete all the extra data here, including columns that just contain the label 'value'.

  3. (assuming your table of domain values already exists) add a custom column like

    =Expand(myFunct( [someparameter] ))

edit: got home and got into my bookmarks. Here is a more detailed reference for what you are looking to do: http://datachix.com/2014/05/22/power-query-functions-some-scenarios/

0
votes

For a table - Add column where you get data and parse JSON

let
    tt=#table(
        {"date"},{
        {"2017-01-01"},
        {"2017-01-02"},
        {"2017-01-03"}
    }),
    add_col = Table.AddColumn(tt, "USD", each Json.Document(Web.Contents("http://example.com/?date="&[date]))[rate][USD])
in
    add_col

If you need only one value

Json.Document(Web.Contents("http://example.com/?date="&YOUR_DATE_STRING))[rate][USD]