Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI! I'm in uncharted waters here, but hoping to find some help. I'm trying to create an end-user tool to run HTTP POST requests for entering new records in REST API over PowerQuery. The general usage is Excel-centric and HTTP GET request already runs fine over PQ, so I was hoping to keep using the same toolset.
I found a nice video for POST requests over PowerQuery here: Making a POST Request Using Power Query - YouTube
Unfortunately it didn't help me far along as it just passes a single value to the API (Json.FromValue). Meanwhile I need to pass an entire JSON body. Not an enormous bunch of data, but still a handful of elements. I'd expect data to reside in Excel table or separate ranges, but not really sure how to go about converting it all into a single JSON body and pass onwards with POST request.
Would Python be a must here? Any thoughts on how to go about it? JSON body is something like this (I have run it via POSTMAN and it works fine).
{
"account": "https://myapi.com/id1/",
"contact": {
"name": "MyContact",
"phone": ""
},
"address": {
"raw_address": "Myaddress"
},
"description": "",
"complete_after": "2021-03-01T02:39:04.234330+02:00",
"state": "unassigned",
"metafields": {
"account:id1": "123",
"account:id2": "ABC",
}
}
Solved! Go to Solution.
Hello @crossover ,
you have to use the M-syntax if you want to use Json.FromValue like so:
let
Source = Json.Document(Web.Contents(
"https://myservice.com/api/tasks/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]])
]
))
in
Source
Alterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:
Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF and others!
Thanks for the input. Unfortunately I'm still struggling to get it to work. I have teared my POST request JSON to a bare minimum that API accepts via Postman and I'm trying to build on that in PowerQuery:
{
"account": "https://myservice.com/api/tasks/accounts/123/",
"address": {
"raw_address": "10 greetings from Estonia"
}
}
This is what I came up with in PowerQuery, however PQ doesn't even launch the query and points towards the colon in front the "account": (comma expected). When replacing all colons with commas, I end up with Error 400 (Bad request). Am I missing something very minor here or is much more extensive nesting needed? In practice, I'd need to compile JSON maybe 3-4x as long.
let
Source = Json.Document(Web.Contents(
"https://myservice.com/api/tasks/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content=Json.FromValue({"account":"https://myservice.com/api/tasks/accounts/123/","address":{"raw_address":"10 greetings from Estonia"}})
]
))
in
Source
Hello @crossover ,
you have to use the M-syntax if you want to use Json.FromValue like so:
let
Source = Json.Document(Web.Contents(
"https://myservice.com/api/tasks/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]])
]
))
in
Source
Alterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:
Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF thank you so much! Both options work fine in basic form, but looks like M-syntax is easier to read. I also had problems adding parameter values (address etc) into the code with Text.ToBinary and found this to be easier with native M, since the query ultimately needs parameterized values.
I really hate bugging you more, but I ran into some more trouble when building up from the working base. How should I handle JSON objects with a colon in M? This is a snippet of JSON that works over Postman:
{
"account": "https://myservice.com/api/tasks/accounts/123/",
"contact": {
"name": "task Name",
"phone": "555-123"
},
"address": {
"raw_address": "10 greetings from Estonia"
},
"metafields": {
"account:sn": "123",
"account:project": "PR1",
"account:phase": "1"
}
}
I have an error in metafields section and apparently problem is the colon sign, e.g account:sn, account:project, account:phase objects. Do I somehow need to hide the colon since I'm getting an "invalid identifier" error message about "account:sn"
Source = Json.Document(Web.Contents(
"https://myservice.com/api/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/",
contact = [name = "taskName", phone = "555-123"],
address = [raw_address = API_address],
metafields = [account:sn = "123", account:project = "PR1", account:phase = "1"]
])
]
)),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
Hi @crossover ,
I believe you have to escape strings with colons like so: #"Text1:Text2"
Source = Json.Document(Web.Contents(
"https://myservice.com/api/",
[
Headers = [#"Authorization"= MyToken,
#"Content-Type"="application/json"],
Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/",
contact = [name = "taskName", phone = "555-123"],
address = [raw_address = API_address],
metafields = [#"account:sn" = "123", #"account:project" = "PR1", #"account:phase" = "1"]
])
]
)),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @crossover ,
What you're typing inside Json.FromValue() is a what M calls a record. Records, as much as almost everything in PQ syntax, use identifiers.
So, on the left of each record element, a valid Identifier should be present.
account:sn is not valid because it contains a colon. Just turn that into #"account:sn" .
In case you'd like to learn almost everything about identifiers, here's a thorough explanation.
Cheers,
Hi @crossover ,
just create the nested object in Power Query and then use the Json.FromValue function to transform it into the format for your call: Easy POST requests with Power BI and Power Query using Json.FromValue – The BIccountant
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.