Use another query result as text parameter

vilmarci — Wed, 13 Mar 2019 09:28:11 GMT

I need to pass a json document as body to another Graph API query.

The source where I want to get the parameter data is:

rooms = Json.Document(Web.Contents("https://graph.microsoft.com/beta/me/findRooms",[ Headers = [#"Content-Type"="application/json"] ] ))

The source data that comes from Graph API like:

{
  "@odata.context": "https://graph.microsoft.com/beta/$metadata#Collection(microsoft.graph.emailAddress)",
  "value": [
    {
      "name": "Room1",
      "address": "Room1@my.com"
    },
    {
      "name": "Room2",
      "address": "Room2@my.com"
    }
  ]
}

The desired result is the following JSON is. What I want from another query is the first, "schedules" part:

{
"schedules": [
"room1@my.com",
"room1@my.com"
],
"startTime": {
"dateTime": "2019-03-01T09:00:00",
"timeZone": "Central European Time"
},
"endTime": {
"dateTime": "2019-04-01T09:00:00",
"timeZone": "Central European Time"
},
"availabilityViewInterval": "15"
}

I can extract the column I need:

  let
    rooms = Json.Document(Web.Contents(roomsurl,[ Headers = [#"Content-Type"="application/json"] ] )),
    roomsvalue = rooms[value],
    #"roomstable" = Table.FromList(roomsvalue, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"roomaddress" = Table.ExpandRecordColumn(#"roomstable", "Column1", {"address"}, {"schedules"})
  in
    #"roomaddress"

But I couldn't find how can I convert this list back to a json text. If I try this:

    jsaddress = Json.FromValue(#"roomaddress"),
    tx = Binary.ToText(jsaddress)

, that returns a binary object as text, not the json text. Can somebody please help me out?

Finally, I need to put this JSON text in the following query in place of "params"

schedules = Json.Document(Web.Contents("https://graph.microsoft.com/beta/me/calendar/getschedule",[ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(params) ] ))

Thank you for the help.

Re: Use another query result as text parameter

Anonymous — Wed, 18 Dec 2019 18:59:21 GMT

Hi @vilmarci , if you want the output to be the text representation of a JSON document; have you tried to use the Text.FromBinary function? Try to edit the query to the following:

jsaddress = Json.FromValue(#"roomaddress"), tx = Text.FromBinary( Json.FromValue(jsaddress) )

Example of Text.FromBinary in use on JSON formatted data in the docs

topic Use another query result as text parameter in Developer

Use another query result as text parameter

Re: Use another query result as text parameter