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:

Expected result:

 

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)

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

 

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

And you will see:

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:

 

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”:

2.  Paste  below M codes to the formula bar:

= 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:

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

 

 

Author: Kelly Yang 

Reviewer: Ula Huang, Kerry Wang