msdotnetclr/import-gzip-csv-from-web-to-power-bi.md

## import-gzip-csv-from-web-to-power-bi.md

      
    Raw
  

              import-gzip-csv-from-web-to-power-bi.md
            
          
Get Data -> Web, choose "Basic", enter URL. E.g.
https://myteststorage.blob.core.windows.net/pmdogy/20180129/csv/shrmyd/shrmyd.csv.gz?st=2018-02-05T08%3A57%3A00Z&se=2018-02-05T16%3A57%3A00Z&sp=r&sv=2017-04-17&sr=b&sig=PbFVzUv%2FBwrOCR0RcuzgVw%2F9nVFFmbo%2BXOJusO1yo9E%3D
Power BI Query Editor will display a single icon for the web blob with the source domain name and content size.
Go to View -> Advanced Editor. The script will be like this:

let
    Source = Web.Contents("https://myteststorage.blob.core.windows.net/pmdogy/20180129/csv/shrmyd/shrmyd.csv.gz?st=2018-02-05T08%3A57%3A00Z&se=2018-02-05T16%3A57%3A00Z&sp=r&sv=2017-04-17&sr=b&sig=PbFVzUv%2FBwrOCR0RcuzgVw%2F9nVFFmbo%2BXOJusO1yo9E%3D")
in
    Source


Add GZip decompression:

let
    Source = Web.Contents("https://myteststorage.blob.core.windows.net/pmdogy/20180129/csv/shrmyd/shrmyd.csv.gz?st=2018-02-05T08%3A57%3A00Z&se=2018-02-05T16%3A57%3A00Z&sp=r&sv=2017-04-17&sr=b&sig=PbFVzUv%2FBwrOCR0RcuzgVw%2F9nVFFmbo%2BXOJusO1yo9E%3D"),
    Decompressed = Binary.Decompress(Source, Compression.GZip)
in
    Decompressed


Power BI Query Editor will display a "CSV" icon. Right click the icon and choose "CSV" from the popup menu. Power BI will extract metadata from the CSV data stream and generate columns and change data types of each column to the best it can. The final outcome looks like this:

let
    Source = Web.Contents("https://myteststorage.blob.core.windows.net/pmdogy/20180129/csv/shrmyd/shrmyd.csv.gz?st=2018-02-05T08%3A57%3A00Z&se=2018-02-05T16%3A57%3A00Z&sp=r&sv=2017-04-17&sr=b&sig=PbFVzUv%2FBwrOCR0RcuzgVw%2F9nVFFmbo%2BXOJusO1yo9E%3D"),
    Decompressed = Binary.Decompress(Source, Compression.GZip),
    #"Imported CSV" = Csv.Document(Decompressed,[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}})
in
    #"Changed Type"


Now you can add additional transformation as needed.