4
Import GZipped CSV file from the web to Power BI
source link: https://gist.github.com/msdotnetclr/9477258a703468578445d6f029fa64a3
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Import GZipped CSV file from the web to Power BI · GitHub
Instantly share code, notes, and snippets.
Import GZipped CSV file from the web to Power BI
- 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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK