Forum Discussion
Connect to API in Power Query - 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
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"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?
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/)
Thank you!