Imported CSV only read the first line

[uruhara99]

Hello everyone,

I'm currently working on importing EPSS data into my PowerBI dashboard. The data, which is in CSV format within a gzip file from a web source, needs to be decompressed before loading. I've encountered an issue where, during the import process, only the first line of the CSV file is read. This line contains a comment with metadata about the date and model version, and the rest of the data below it is ignored.

However, when I manually decompress the file first and then load it as a file, the entire document reads successfully.

Please help. Thank you.

[RossEdwards]

Looking purely at your Power Query code, you are not using the same method in both queries.

In the query with the decompress, you are using the function Csv.Document() on its own.  In the method without the decompress you are using Csv.Document() that calls File.Contents().  Have you tried adding File.Contents around the Source entry on that #"Imported CSV" line of code?

i.e.

Csv.Document(File.Contents(Source),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])

[uruhara99]

Hi Ross, 

I'm reaching out to extract the CSV from the web content, which is why I used the first query. Here's how it looks: 

let
    Source = Binary.Decompress(Web.Contents("https://epss.cyentia.com/epss_scores-current.csv.gz"), Compression.GZip),
    #"Imported CSV" = Csv.Document(Source, [Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv])
in
    #"Imported CSV"

The purpose of the second query, which calls a local file, is to demonstrate the difference in results between using a file and web content, even though it's the same file. However, following your suggestion led to an expression error: We cannot convert a value of type Binary to type Text.

Thanks for the suggestion.

[RossEdwards]

Yeah you are right. The File.Contents needs a path, not a binary to function.  Its output is binary.

Binary.Decompress accepts a Nullable Binary and returns a Nullable Binary.

My next thought would be to try this function:
Lines.FromBinary - PowerQuery M | Microsoft Learn

[uruhara99]

This function easily worked with local file:

Unofortunately, for web content still giving the same result : only the first line

[RossEdwards]

Are you able to download the Compressed file from the Web and run the same decompression code using File.Contents?  It seems like there are 2 possible variables.  Is the problem with Web.Contents or the problem with Binary.Decompress?

I'd expect its more likely that Decompress is doing something to the CSV file which is causing it to see an EOF marker and stop.

Could you do the Decompress in its own step, then look at the binary data differently and see whats actually in the uncompressed binary?  I.e. Conver it to a straight Text file and see what spits out?

[uruhara99 · Accepted solution]

After conducting a thorough debugging process, it has been determined that the error is not caused by the decompression process in Power Query. It seems likely that the issue may stem from a different format or setting used during the initial compression with gzip, leading to an EOF reading error. However, re-compressing and decompressing the CSV file with 7zip did not result in any issues, whether the file was decompress using file/web.content. 

I've switched to using the API method, thank you for your assistance.

[RossEdwards]

Glad to hear you figured it out!  Things like that can be so frustrating!