cancel
Showing results for 
Search instead for 
Did you mean: 
yingyinr

Using Python to get data from a CSV file of a web source

Scenario:

A user wants to get the data from a CSV file of a web source for analyzing, and he doesn’t want to download the CSV file. But when he uses the Web connector of Power BI Desktop to connect the CSV link, it returns an error. How to resolve it?

 

CSV file link as below:

https://www.cdc.gov/coronavirus/2019-ncov/map-data-cases.CSV

 

Error as below:

yingyinr_0-1632285012412.png

Expected result:

yingyinr_1-1632285053565.png

 

Detailed steps:

Analyze:

Since we can’t directly access the CSV file via Web connector, let us try to use Python to realize it.

Preparation:

1. Install Python and enable Python visuals in Power BI Desktop (Please refer to this official documentation)

yingyinr_2-1632285090376.png

2. Choose “Get data” -> ”More” -> ”Other” -> ”Python script”:

 yingyinr_3-1632285150474.png

3. Click “Connect” and paste the below codes to the window:

yingyinr_4-1632285150475.png

And you will see:

yingyinr_5-1632285150479.png

The CSV file has been connected successfully.

4. Using below M codes to replace all the “None” to 0:

let

Source = Python.Execute("import pandas as pd#(lf)#(lf)#read CSV file from CD's website#(lf)#(lf)df=pd.read_CSV('https://www.cdc.gov/coronavirus/2019-ncov/map-data-cases.CSV',encoding='latin1')"),

df1 = Source{[Name="df"]}[Value],



#"Changed Type" = Table.TransformColumnTypes(df1,{{"Jurisdiction", type text}, {"Range", type text}, {"Cases Reported", type text}, {"Community Transmission#(0086)", type text}, {"URL", type text}, {"Unnamed: 5", Int64.Type}, {"Unnamed: 6", Int64.Type}, {"Unnamed: 7", type text}}),



#"ReplacedValue" = Table.ReplaceValue(#"Changed Type","None","0",Replacer.ReplaceText,Table.ColumnNames(#"Changed Type"))

in

 #"Replaced Value"

Then you will get a transformed table as requested  for analyzing:

yingyinr_6-1632285290127.png

 

Besides, you could also simply create a blank query using the below M codes in Power Query Editor to connect to the CSV file:

The steps are as below:

1. Go to query editor -> Click “New Source” under “Home” -> choose “Blank Query”:

yingyinr_7-1632285444301.png

2.  Paste  below M codes to the formula bar:

yingyinr_8-1632285479134.png

= Python.Execute("import pandas as pd#(lf)df=pd.read_CSV('https://www.cdc.gov/coronavirus/2019-ncov/map-data-cases.CSV',encoding='latin1')")
  • Press “Enter” and click “Expanded values”, you could also get connected to the CSV file.
  • Result is as below:

yingyinr_9-1632285479140.png

Finally, we get the results we want and can achieve the needs of the user.

 

 

Author: Kelly Yang 

Reviewer: Ula Huang, Kerry Wang