Forum Discussion
Connect to API in Power Query - Error: Block length does not match with its complement.
- 3 years ago
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
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
- TMA3 years agoFrequent Visitor
Thank you!
Both options worked really well. Really appreciate it.
- QC3 years agoKudo Kingpin
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!
- cpwebb3 years agoMicrosoft Employee
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"- QC3 years agoKudo Kingpin
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?