Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredJoin us at FabCon Vienna from September 15-18, 2025, for the ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM. Get registered
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')")
Finally, we get the results we want and can achieve the needs of the user.
Author: Kelly Yang
Reviewer: Ula Huang, Kerry Wang
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.