Forum Discussion
Connect to API in Power Query - Error: Block length does not match with its complement.
Hi,
I am trying to connect to Free Open-Source Weather API | Open-Meteo.com through a new query. I can see the json file on https://api.open-meteo.com/v1/forecast?latitude=52.52&longitude=13.41&hourly=temperature_2m
I have looked at the existing topics about this, this is the code I tried:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Web.Contents("https://api.open-meteo.com/v1/forecast?latitude=52.52&longitude=13.41&hourly=temperature_2m"),
Compression.Deflate)))
in
Source
On postman I get the json without any problems
Any help would be much appreciated. Thanks
This is the error I get:
DataFormat.Error: Block length does not match with its complement.
I wrote a blog post with two solutions to this problem here:
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/Chris
10 Replies
I wrote a blog post with two solutions to this problem here:
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/Chris
Thank you!
Both options worked really well. Really appreciate it.
Hi Chris,
For zip data of site below (csv/xml are in zip): I read your blog, but couldn't get it to work. I am probably doing it wrong. Any suggestion would be greatly appreicated!
Even if the error is the same, this is a slightly different problem: the links on this page actually return zip files, and what's more the zip files themselves contain multiple files. You should use the API to return the data in directly, for example in JSON format, as described in the section "Output format" here. Here's a quick example:
let Source = Json.Document(Web.Contents("http://api.worldbank.org/v2/country/all/indicator/SP.POP.TOTL?format=json")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type any}}), Column1 = #"Changed Type"{1}[Column1], #"Converted to Table1" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"indicator", "country", "countryiso3code", "date", "value", "unit", "obs_status", "decimal"}, {"Column1.indicator", "Column1.country", "Column1.countryiso3code", "Column1.date", "Column1.value", "Column1.unit", "Column1.obs_status", "Column1.decimal"}), #"Expanded Column1.country" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.country", {"id", "value"}, {"Column1.country.id", "Column1.country.value"}), #"Expanded Column1.indicator" = Table.ExpandRecordColumn(#"Expanded Column1.country", "Column1.indicator", {"id", "value"}, {"Column1.indicator.id", "Column1.indicator.value"}) in #"Expanded Column1.indicator"
The above error is caused by that the data from your API is stored as a compressed JSON document, which is recognized as invalid format in Power BI Desktop.
In your scenario, you can use Power Query to retrieve data from your API and use the Binary.Decompress() function to extract the data.
For more details about how to retrieve data from API in Power Query, please review this similar thread.
For more details about how to use Binary.Decompress() function, please review this blog: Working With Compression In Power Query And Power BI Desktop.
