Error opening .csv: DataFormat.Error: Block length does not match its plugin.

[nsalvay]

@Microsoft @artemus 

Hello everyone, I have this use case where I want to connect to a csv file. I obtain this file via API, and it comes compressed in .zip.

I was able to make the connection and managed to unzip the ZIP file from Power Query by following different forums, such as https://community.fabric.microsoft.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API -in-to-po....

The problem I have is that I cannot see the .csv file, it gives me the following error:

If you can guide me what error I have, I pass the code used:

let
Origen = Json.Document(Web.Contents("https:xxx", [Headers=[Accept="application/json", apikey="xxx"]])),
report = Origen[report],
sections = report[sections],
#"Estado de la red" = sections[Estado de la red],
#"Estado de la red1" = #"Estado de la red"{0},
fields = #"Estado de la red1"[fields],
Reporte = fields[Reporte],
#"Convertido en tabla" = Record.ToTable(Reporte),
#"Tabla transpuesta" = Table.Transpose(#"Convertido en tabla"),
#"Columnas quitadas" = Table.RemoveColumns(#"Tabla transpuesta",{"Column1"}),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Columnas quitadas", [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"value", type text}, {"filename", type text}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Personalizado", each Binary.FromText([value], BinaryEncoding.Base64)),
#"Función personalizada invocada" = Table.AddColumn(#"Personalizada agregada", "Personalizado.1", each Unzip1([Personalizado])),
#"Se expandió Personalizado.1" = Table.ExpandListColumn(#"Función personalizada invocada", "Personalizado.1"),
#"Personalizado 1" = #"Se expandió Personalizado.1"{0}[Personalizado.1],
#"Convertido en tabla1" = Record.ToTable(#"Personalizado 1"),
Value = #"Convertido en tabla1"{1}[Value]
in
Value

 

[Anonymous]

Hi @nsalvay ,

According to the M function you provided, I think your problem should be that the data from your REST API is stored as a compressed JSON document, which is recognized as invalid format in Power BI Desktop.
Maybe you can try to use Binary.Decompress() function to extract the data, or use the Web.BrowserContents function instead of Web.Contents.

And here I find a blog with two solutions to this problem:
"Block length does not match with its complement" Error In Power Query (crossjoin.co.uk)
Solved: Connect to API in Power Query - Error: Block lengt... - Microsoft Fabric Community

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nsalvay]

Hi @Anonymous, thanks for the answer. I tried what the blog indicates, but I couldn't get it to work.

1st I added the code #"Accept-Encoding" = "gzip" inside the Header in Web.Contents, but the error remained the same.

2nd I tried using Web.BrowserContents instead of Web.Contents, but I get an error with the header, like it doesn't support it:

3rd I tried decompressing the binary content using the Binary.Decompress() function, and all the decompression formats but the error remained the same.

Greetings

[nsalvay]

Hi @Anonymous , There I managed to see the .csv file, but apparently it has bzip2 compression. I couldn't find information on how to unzip it, do you know if this is possible from power query?

Greetings and thanks!