Forum Discussion

joelcochran's avatar
joelcochran
Microsoft Employee
10 years ago
Solved

Get data from web not showing table

Using the following URL: https://beta.fec.gov/data/elections/president/2016/   There are two tables on this page, but neither shows as a data source (but Document does):   I confirmed in the...
Need Help
  • Rémi's avatar
    Rémi
    10 years ago

    Yeah it was the Edit button sorry, wrong translation from me.

     

    It seems that you have to find your table into the html code.

    The steps are saved on the right ribbon, if the navigation step don't increment when you navigate, you can add a random step after each click (rename columns for example). Then you can back to the previous step easily. Otherwise, you can reveal the formula bar and edit them.

     

MihirSanghvi's avatar
MihirSanghvi
Regular Visitor
9 years ago

https://beta.fec.gov/data/elections/president/2016/

 

From the above URL, I have copied contents of the first table (changed table display settings to 100 results per page and copied all rows including header).

 

Solution

 

paste it in Power BI (click on Home Menu -> Enter Data). After pasting it click on Load button. After Loading table, edit query and then add following R script (click on Transform Menu -> Run R Script).

 

 

# 'dataset' holds the input data for this script
output <- dataset[1 : (nrow(dataset)/3),]
j=1
for (i in seq (from=1, to= nrow(dataset), by=3)){
output$Candidate[j]<- as.character(dataset$Candidate[i])
output$Party[j]<- as.character(dataset$Party[i])
output$TotalReceipts[j]<- as.character(dataset$TotalReceipts[i])
output$TotalDisbursements[j]<- as.character(dataset$TotalDisbursements[i])
output$CashOnHand[j]<- as.character(dataset$Candidate[i+1])
j <- j+1
}
output[output=="--"] <- "$0.00"