Decompress .gz file on Sharepoint to Power BI

[j_martinho]

Hi!

I tried to open the csv from a .gz file allocated in Sharepoint, but I couldn't. Can you help me?

I used the function below and the query:

(zip) =>
let
Source =
        Binary.Decompress(
            File.Contents(
                zip),
                Compression.GZip
        ),
#"Imported" =
        Csv.Document(Source,[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),

#"Promoted Headers" = Table.PromoteHeaders(#"Imported", [PromoteAllScalars=true])
in
#"Promoted Headers"

Query:

let
    Fonte = SharePoint.Files("https://xxx.sharepoint.com/sites/GSP/", [ApiVersion = 15]),
    #"Linhas Filtradas2" = Table.SelectRows(Fonte, each ([Extension] = ".gz")),
    UnpackGzips = Table.AddColumn(#"Linhas Filtradas2", "Custom", each fnUnpackGzipLinesFromBinary([Folder Path]&[Name])),
    #"Expanded Custom" = Table.ExpandTableColumn(UnpackGzips)
in
    #"Expanded Custom"

Error:

Expression.Error: 1 arguments were passed to a function, which expected between 3 and 4.
Detalhes:
Pattern=
Arguments=[List]

[parry2k]

@j_martinho are you getting error on expand step? Can you confirm? or let us know on which step you are getting this error.

[j_martinho]

@parry2k 

[pranit828 · Accepted solution]

Hi @j_martinho 

I used the below code and it worked for me.

let
    Source = File.Contents("zip"),
    Decompressed = Binary.Decompress(Source, Compression.GZip),
    #"Imported CSV" = Csv.Document(Decompressed,[Delimiter="|",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV")
in
    #"Promoted Headers"

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

[j_martinho]

Works! 

I adjusted it so that Power BI downloads the file directly from the web instead of allocating it to Sharepoint. And I used the code from colleague @pranit828. Thank you!

let
    Fonte = Web.Contents("https://data.brasil.io/dataset/covid19/caso_full.csv.gz"),
    Decompress = Binary.Decompress(Fonte, Compression.GZip),
    #"CSV Importado" = Csv.Document(Decompress,[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"CSV Importado", [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"city", type text}, {"city_ibge_code", Int64.Type}, {"date", type date}, {"epidemiological_week", Int64.Type}, {"estimated_population_2019", Int64.Type}, {"is_last", type logical}, {"is_repeated", type logical}, {"last_available_confirmed", Int64.Type}, {"last_available_confirmed_per_100k_inhabitants", Int64.Type}, {"last_available_date", type date}, {"last_available_death_rate", Int64.Type}, {"last_available_deaths", Int64.Type}, {"order_for_place", Int64.Type}, {"place_type", type text}, {"state", type text}, {"new_confirmed", Int64.Type}, {"new_deaths", Int64.Type}})
in
    #"Tipo Alterado"