Connect to API in Power Query - Error: Block length does not match with its complement.

[TMA]

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.

[cpwebb · Accepted solution]

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...

Chris

[QC]

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!

https://data.worldbank.org/indicator/NV.IND.TOTL.CD

[cpwebb]

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"

[QC]

Thank you so much Chris!! I struggled half day yesterday, you made it look so effortless 😄 

I am beginning to learn Power Query, I have been reading MS documents, and recently your blog, any recommendations?

[cpwebb]

Apart from the obvious names, many of whom aren't active these days, I really like Rick de Groot's content (https://gorilla.bi/power-query/) and I learn a lot from Ben Gribaudo's blog (https://bengribaudo.com/)

[QC]

Hi Chris, I just read your 2 blogs RE "dynamic what-if", excellent as usual. My question: is there a direct option in PQ to switch Import/DQ, instead of going back to PBI? On several occassions I meant for DQ, but ended up importing, there is no way to go back once it's imported in PBI. Sorry I meant to post on your blog, but Akismet kept blocking me.

[QC]

Thank you!

[TMA]

Thank you!

Both options worked really well. Really appreciate it.

[Anonymous]

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.

[TMA]

Thank you for your response but I had seen the thread you mentioned and this is exactly what I have tried. 

I had also seen the blog post about the Binary.Decompress() function which I tried to incorporate but it still didn't work.