Forum Discussion
Helper IIExtracting data from web and creating vizulalization
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.
Super UserHi. 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,
Helper IIHi,
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:
Hope this helps
- Sreesudha_OC
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.
- 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,