Forum Discussion
how to import website data including additional table pages under the same web address
I can import website data through the power bi desktop app but the following web address http://www.electionguide.org/elections/past/ has more data when you click next within the table using the same web address. How do i access all the data in the table?
Sorry for the late response. On my computer this query works fine:
let Source = Json.Document(Web.Contents("http://www.electionguide.org/ajax/election/past/?sEcho=3&iColumns=5&sColumns=&iDisplayStart=0&iDisplayLength=100000&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&iSortCol_0=3&sSortDir_0=desc&iSortingCols=1&bSortable_0=false&bSortable_1=true&bSortable_2=false&bSortable_3=true&bSortable_4=true&_=1461528463674")), aaData = Source[aaData], #"Converted to Table" = Table.FromList(aaData, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Inserted Modulo", each Number.Mod([Index], 9), type number), #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if [Inserted Modulo] = 1 then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Inserted Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Inserted Modulo", type text}}, "en-US")[#"Inserted Modulo"]), "Inserted Modulo", "Column1"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Flag URL"}, {"4", "Date"}, {"5", "Status"}, {"7", "Election For"}, {"8", "Election Type"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"2", "3", "6", "0", "Custom"}) in #"Removed Columns1"Can you confirm that you used the same query?
Can you copy and paste the URL below and open it in your browser?
If you get a valid json response in your browser, it means that the query should work well in Power BI. If you don't get the response, it means that the URL above only works from my computer. If this is the case you will need to install Fiddler and follow the steps I recommended above to extract the URL that was used from your browser when you clicked on of the pages. Then you can use that URL in the query above. I marked the URL to replace in bold red.
Hope it helps.
Gil
DataChant.com
15 Replies
Assuming you are allowed to extract the data, you can run Fiddler and perform reverese engineering on the AJAX that was used on that site.
Below is the M query that will pull 100 records from the site you have mentioned, and transform it into a table - My code is quick and dirty, but it works :)
The parameters iDisplayStart and iDisplayLength control the offset and count of the results you can fetch
iDisplayStart=0&iDisplayLength=100
let Source = Json.Document(Web.Contents("http://www.electionguide.org/ajax/election/past/?sEcho=3&iColumns=5&sColumns=&iDisplayStart=0&iDisplayLength=100&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&iSortCol_0=3&sSortDir_0=desc&iSortingCols=1&bSortable_0=false&bSortable_1=true&bSortable_2=false&bSortable_3=true&bSortable_4=true&_=1461528463674")), aaData = Source[aaData], #"Converted to Table" = Table.FromList(aaData, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Inserted Modulo", each Number.Mod([Index], 9), type number), #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if [Inserted Modulo] = 1 then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Inserted Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Inserted Modulo", type text}}, "en-US")[#"Inserted Modulo"]), "Inserted Modulo", "Column1"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Flag URL"}, {"4", "Date"}, {"5", "Status"}, {"7", "Election For"}, {"8", "Election Type"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"2", "3", "6", "0", "Custom"}) in #"Removed Columns1"Thats very cool!
I'd be very happy if you could provide a link where a rookie like me could start to learn that AJAX reverse engineering.
You might be interested in the power of "Record.FromList" that I've just recently discovered to elegantly open JSON-files or other stuff that comes in list/record format. Add this as a step after #"Converted to Table", delete the rest and expand the results. Just some cleanup and you're done :-)
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Record.FromList([Column1], type [FlagURL=text, Del1=any, Del2=any, Date=date, Status=text, x=number,ElectionFor=text, ElectionType=text, y=number])),
Thank you Imke,
I like your propsal to use Record.FromList.
Regarding the reverse engineering -
You can install Fiddler (here) and run it. Then go to the website and click on one of the page numbers.
In the Fiddler window you should be able to see the relevant HTTP request and copy the URL.
Now, if we are lucky the URL will contain meanginful parameters that represent the page offset and number of results to display, so you can later modify these parameters to get more results, or run a query function to paginate over all the results.
Im not sure what i am doing wrong but i have copied and pasted your code and it brings up an error for me. Help please.
- As Inke pointed out, the last parameter is the UNIX time when you access the website, so you can try adding this code instead of the parameter value to dynamically provide the correct value:
FixedLocalNow(), -1) - #datetime(1970, 1, 1, 0, 0, 0))
Can't think of a way from looking at the page source. Looks like a lot of code behind and bootstrap doing the interface magic.
