topic Re: Get Data from cURL with Power Query in Power Query

Get Data from cURL with Power Query

Komi — Fri, 08 Apr 2022 12:43:29 GMT

Hi, I need help - I have a cURL and I need to transform it into a power query.

Can someone please help me?

curl --request POST \ --url https://api.abcd.com/2/ \ --header 'accept: application/json' \ --header 'content-type: application/json' \ --header 'authorization: Bearer xyz' \ --data '{"query":"query ($period: PeriodFreeInput!) { workspace (id: \"2ihQOg3IwTBE\") { shifts ( period: $period ) { userName, userId, workTime, period { start, end }, user { email } } } }\n\n","variables":{"period":{"start":"2022-03-31T22:00:00.000Z","end":"2022-04-30T21:59:59.999Z"}}}'

Re: Get Data from cURL with Power Query

lbendlin — Sun, 10 Apr 2022 22:19:52 GMT

Web.Contents - PowerQuery M | Microsoft Docs

Example 2

Re: Get Data from cURL with Power Query

Komi — Mon, 11 Apr 2022 07:54:18 GMT

Thanks, I've been looking at it, unfortunately I don't know how to write the query part. 😞

Re: Get Data from cURL with Power Query

lbendlin — Mon, 11 Apr 2022 11:26:43 GMT

Without access to your API it is not possible to help you further. You can use Postman or similar for developing the required format.

Re: Get Data from cURL with Power Query

Syndicate_Admin — Wed, 20 Jul 2022 04:54:22 GMT

Hello

I have an intranet website that has a button to download a file in CSV.

Can I use power BI to trigger this function in the website to fetch the CSV file and transform it into a table I can manipulate later? If it is doable what info you might need from me to help me? thank you

Re: Get Data from cURL with Power Query

lbendlin — Wed, 20 Jul 2022 11:37:28 GMT

Please do not hijack posts. Create a new post.

Re: Get Data from cURL with Power Query

Syndicate_Admin — Wed, 20 Jul 2022 15:31:11 GMT

sorry I didn't know that. I will create a new post.

Re: Get Data from cURL with Power Query

Syndicate_Admin — Sun, 02 Jul 2023 07:19:09 GMT

I recently had the same question which I have found the answer to.

Step 1: In Excel select Data\New Query\From Other Sources\From Web

Step 2: Select the "Advanced" radio button

Step 3: Paste the url into the "URL parts" text box

Step 4: Under the heading "HTTP request header parameters (optional) do the following:

- type in the word Authorization into the 1st dropdown box. Next to this type in the word Bearer followed by your required parameter. It's important you include the word Bearer as well as the parameter that follows it. Eg. Bearer xyz

Make sure there are no quotes around it.

- type in the words Content-Type into the 2nd dropdown box. Next to this type in the words application/json

Make sure there are no quotes around it.

Step 5: Click the OK button

Assuming that was successful this will take you into Power Query

Step 6: Click on the "Source" step & then click on the "To Tables" button seen in the next screenshot & Click the "Insert" button

Step 7: Then Click "Ok" as there are no delimiters.

Given that your question was only to do with how do you fetch data using a Curl api request using Power Query I will stop there as I will assume that with the access to the data now achieved that you know how to use Power Query to proceed further.

I hope this helps. My problem now is finding out how to run many Curl api's automatically one after another.

Re: Get Data from cURL with Power Query

simcla — Sun, 30 Jul 2023 11:22:40 GMT

Thank you for this solution!

I tried to use it to access HomeAssistant using the API interface.

The Curl-Statement is as follows and works fine:

curl \ -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9....." \ -H "Content-Type: application/json" \ http://homeassistant:8123/api/services

I did translate it in PowerQuery to this statement:

let url = "http://homeassistant:8123/api/services", headers = [Authorization="Bearer eyJhbGciOiJIUzI1NiIsInR5cC....", #"Content-Type"="application/json"], response = Web.Contents( url, [ Headers = headers ] ), jsonResponse = Json.Document(response) in jsonResponse

but only got this message:

Unerwarteter Fehler.: Die Blocklänge stimmt nicht mit ihrem Komplement überein. Details: Microsoft.Mashup.Evaluator.Interface.ErrorException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> System.IO.InvalidDataException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. ---> System.IO.InvalidDataException: Die Blocklänge stimmt nicht mit ihrem Komplement überein. bei System.IO.Mashup.Compression.Inflater.DecodeUncompressedBlock(Boolean& end_of_block) bei System.IO.Mashup.Compression.Inflater.Decode() bei System.IO.Mashup.Compression.Inflater.Inflate(Byte[] bytes, Int32 offset, Int32 length) .........................

Sorry, the error message is in german. It reads something like

"Unexpected Error. The block length does not match its complement."

Any idea what is wrong with my code ?

Thanks in advance!

Re: Get Data from cURL with Power Query

simcla — Fri, 04 Aug 2023 17:08:55 GMT

In the meanwhile I found a solution. You have to add

#"Accept-Encoding" = "gzip"

to your code:

let url = "http://homeassistant:8123/api/history/period?filter_entity_id=sensor.xxxxx", headers = [Authorization="Bearer eyJhbGciOiJIUzI1NiIsI....", #"Content-Type"="application/json", #"Accept-Encoding" = "gzip"], response = Web.Contents( url, [ Headers = headers ] ), ............................

Thanks to Chris Webb!

https://blog.crossjoin.co.uk/2023/02/05/the-block-length-does-not-match-with-its-complement-error-in-power-query-in-power-bi-and-excel/#:~:text=Error%3A%20Block%20length%20does%20not%20match%20with%20its%20complement.&text=The%20problem%20is%20caused%20by,NET%20functionality.

Re: Get Data from cURL with Power Query

kanchalwar — Thu, 20 Feb 2025 02:40:53 GMT

Thank you for this solution. I was looking for running multiline curl to fetch Problems data from Dynatrace using Excel. It worked!