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
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?
- cpwebb3 years agoMicrosoft Employee
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/)
- QC3 years agoKudo Kingpin
Thank you!
- QC3 years agoKudo Kingpin
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.