GraphQL mutation from Power Query

[Hap76]

Working in Excel Power Query. I can query data just fine in Power Query from a GraphQL API. But now I'm trying send to an API with a GraphQL mutation. Something causes it to duplicate the record on the endpoint:

let
    Source = Web.Contents(
	"https://api.######.com/v2",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"=APIKey
		],
		Content=Text.ToBinary("{""query"": ""mutation { create_item (board_id:#####, group_id:#####, item_name:wazoo) { id } }""}")
	]
    ),
    #"JSON" = Json.Document(Source)
in
   #"JSON"

The #JSON line is from a version where I'm actually fetching the JSON. I don't really need it since I'm not parsing here, only sending/updating. So I tried to just returned Source in the let statement. This seems to fire the mutation query only once (yay!) but it throws an error in Excel, because Excel wants something to do.

The M:

let
    Source = Web.Contents(
	"https://api.######.com/v2",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"=APIKey
	],
     Content=Text.ToBinary("{""query"": ""mutation { create_item (board_id:#####, group_id:new_group51930, item_name:wazoo) { id } }""}")
	]
    )
 in
       Source

I'm trying to learn how to work with APIs within Power Query. Info out there is sparse, appreciate any help and resources!

[lbendlin]

Your line 

#"Method"="POST",

will be ignored.  Web.Contents switches from GET to POST in the presence of  a Content payload.

I would recommend you read this article. Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? Chris Webb's BI Blog (crossjoin.co...

It talks about SQL but the general idea is the same.  Power Query cannot guarantee to run requests only once. That's not what it is designed for.

[Hap76]

Interesting, thanks. When you say "That's not what it is designed for", are you suggesting the M language is not suitable for working with APIs? Or SQL Inserts/Updates for that matter? That's suprising- and quite a blow to my future plans. Surely these issues could be handled in a custom connector, no?

[lbendlin · Accepted solution]

The design goal of Power Query M is to take data from a source and transform it into a tabular format that can be consumed by Excel or Power BI, or a Parquet-style dataflow.  Nowhere in the design specifications is there a place for "talking back to the data source" (the available options are more like unintended consequences of some of the design decisions).  Unlike SSIS and Informatica  Power Query is more of a one-way street.

[Hap76]

Truthfully, I've already been working on a VBA solution for this component of the process (ie the outbound traffic). I am consistently surprised by the power of M, and was just hoping to hear some magic. Thanks for chiming in on it, much appreicated.

[lbendlin]

The more I learn about M the more I like it. It is indeed very powerful. In my opinion it would be easy to add the outbound part and make Power Query a true ETL tool. But I suspect that the designers have no intentions to actually do that, sadly.