Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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
User | Count |
---|---|
5 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |