Extracting data from web and creating vizulalization

[Sreesudha_OC]

Hi All,

I would like to extract the data that you see in the below link & create some visuals like " case trend over time /daily forecast etc in a line chart or line & Clustered chart or in something else.

https://www.covid19india.org

Is there a way to pull this data from this website?

I have tried using Query Editor - Web option ( in navigator) however when i click on Web view, i see some message like "Table highlighting is disabled because this page uses Internet explorer compatibility mode.  How can i fix this.?"

& 

How can i share some screenshots here. Kindly note, i am new to this forum so i would sincerely request your guidance to scale up my skills.

Thanks

Sudha

[ibarrau]

Hi. You can't take data from the web site because of the way it was created. You have to be a wizard of power query scrapping html.table to find something. You can go at the end of the link and check the data in other sites like github, api json, etc.

I have tried to use the "Live" json of the https://api.covid19india.org/ and works perfect. Check the data to see if they have all rows or if you have to load the 6 jsons and append them together.

let
    Source = Json.Document(Web.Contents("api.covid19india.org/raw_data6.json")),
    raw_data = Source[raw_data],
    #"Converted to Table" = Table.FromList(raw_data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"agebracket", "contractedfromwhichpatientsuspected", "currentstatus", "dateannounced", "detectedcity", "detecteddistrict", "detectedstate", "entryid", "gender", "nationality", "notes", "numcases", "patientnumber", "source1", "source2", "source3", "statecode", "statepatientnumber", "statuschangedate", "typeoftransmission"}, {"Column1.agebracket", "Column1.contractedfromwhichpatientsuspected", "Column1.currentstatus", "Column1.dateannounced", "Column1.detectedcity", "Column1.detecteddistrict", "Column1.detectedstate", "Column1.entryid", "Column1.gender", "Column1.nationality", "Column1.notes", "Column1.numcases", "Column1.patientnumber", "Column1.source1", "Column1.source2", "Column1.source3", "Column1.statecode", "Column1.statepatientnumber", "Column1.statuschangedate", "Column1.typeoftransmission"})
in
    #"Expanded Column1"

Hope this helps,

Regards,

[Sreesudha_OC]

Hi,

Looks like your solution is too awesome.  Unfortunately, i dont have a deep technical background and i am not strong with json.

If you can help me out a little bit more, that would be really helpful.  The data which you see in the website that i have shared,  keep refreshing almost 4-5 times in a day.

In Query Editor navigator, how do i need to call this? In the api link you have shared i saw many csv files and that does not have latest dates. So i am little confused.  

Thanks

Sudha

[ibarrau]

Go to the "Query Editor" or "Transform Data". Then under Advanced Editor copy and paste the code I have sent you. That will read the json file from the api. Then if you check that you need more data change the name of the json as the website. You can see all the jsons on this page:

https://api.covid19india.org/

Hope this helps

[Sreesudha_OC · Accepted solution]

Hi,

The link that you have provided is accepted by power bi however i have not got the desired ouput.

I am trying to develop something like below. That is my end objective.

http://travvise.com/COVID19.html

For this i need some data sets from the previous link i have shared. As of now when i connect,  i am getting only some link. 

Is there a way to share screenshot over here so that we can disucss more.

[Sreesudha_OC]

Wonderful!!..Thankyou so much and i am accepting this as a solution.

I am not sure if i will be able to make the desired output however let me try this.  

I just need one guidance for my future reference.  How have you figured out the API link for the initial link i have shared?

Incase if i want to check that for other links,  i can take a call basis this input.

Thanks

Sudha

[ibarrau]

The data I'm sharing is the data your initial page is using. You can check the complete code of how they made this on github or check the data they have used. I have taken the examples with the data they have written there.

CovidIndia

I'm pretty sure that they have build all the dashboards mixing that json's data.

Regards,

[Sreesudha_OC]

Hi,

I have extracted data using the api link https://covidtracking.com/api/v1/states/current.json

However in the query editor, format of the date is "20200618".  I just want to change this in a readable format.

I have tried chaning all data types, modeling etc. The format remains the same.

Is there any other way?

Thanks

Sudha

[ibarrau]

I'm sorry I can't help more. The web is design to avoid the data taken like that. You may try to take it by examples.

I'm pretty sure that here is what you are looking for: https://api.covid19india.org/

There are like 20 or more datasets. One of them or the combination of them should work. I will show you in a gif how to connect to one of the files so you can try with all of them:

scrappindiacovidjson

Hope this helps,