Forum Discussion
Helper IIIhow to import website data including additional table pages under the same web address
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
Helper IIIYes the same url as i previously mentioned. Here it is again http://www.electionguide.org/elections/upcoming/
Most Valuable ProfessionalSorry 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
Hi DataChant,
I am in my 2nd week entering into the Power BI world. I am trying to extract data from a website to monitor the housing market in our city. The following code is where I have got to so far, however I just couldn't quite understand your solution for to handle the multiple page on the website. I want to extract all the property listings on the website. I know nothing about JSON apart from the name. 2 questions following:
Is there another way in Power BI apart from using JSON?
Is there any chance you could explain in a bit more detail about the JSON approach?
Also from my code, what should I do next to make it a table? Tried Transpose and it's not what I want. I only want the header once.
Thanks a lot.
let Source =Table.FromColumns({Lines.FromBinary(Web.Contents("http://www.realestate.co.nz/residential/search/districts/237/property_types/1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%2C50"),null,null,1252)}), #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "HTML"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "FindListingHeader", each if Text.Contains([HTML], "id=""listing-") then "ListingNo" else if Text.Contains([HTML], "itemprop=""name""") then "Slogan" else if Text.Contains([HTML], "itemprop=""description""") then "Description" else if Text.Contains([HTML], "itemprop=""streetAddress""") then "StreetAddress" else if Text.Contains([HTML], "itemprop=""addressLocality""") then "Suburb" else if Text.Contains([HTML], "class=""price""") then "Price" else if Text.Contains([HTML], "Bedrooms") then "Bedrooms" else if Text.Contains([HTML], "Bathrooms") then "Bathrooms" else null ), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "FindListingDetails", each if Text.Contains([HTML], "id=""listing-") then [HTML] else if Text.Contains([HTML], "itemprop=""name""") then [HTML] else if Text.Contains([HTML], "itemprop=""description""") then [HTML] else if Text.Contains([HTML], "itemprop=""addressLocality""") then [HTML] else if Text.Contains([HTML], "itemprop=""streetAddress""") then [HTML] else if Text.Contains([HTML], "class=""price""") then [HTML] else if Text.Contains([HTML], "Bedrooms</h6></li>") then [HTML] else if Text.Contains([HTML], "Bathrooms</h6></li>") then [HTML] else null ), #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"FindListingDetails", type text}, {"FindListingHeader", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"FindListingDetails", Text.Trim}}), #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","<span itemprop=""name"">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<p itemprop=""description"">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","<span class=""location"" itemprop=""addressLocality"">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","<span itemprop=""streetAddress"">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","<div class=""price"">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","<li><h6>","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","</h6></li>","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","</span>","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","</div>","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","</p","",Replacer.ReplaceText,{"HTML"}), #"Remove """ = Table.ReplaceValue(#"Replaced Value9","""","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value12" = Table.ReplaceValue(#"Remove ""","<div id=listing-","",Replacer.ReplaceText,{"FindListingDetails"}), #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12"," class=listing featuredListing itemscope itemtype=http://schema.org/Residence data-gtm={","",Replacer.ReplaceText,{"FindListingDetails"}), #"Remove ," = Table.ReplaceValue(#"Replaced Value13",",","",Replacer.ReplaceText,{"FindListingDetails"}), #"Remove }" = Table.ReplaceValue(#"Remove ,","}","",Replacer.ReplaceText,{"FindListingDetails"}), #"Remove >" = Table.ReplaceValue(#"Remove }",">","",Replacer.ReplaceText,{"FindListingDetails"}), #"Filtered Rows1" = Table.SelectRows(#"Remove >", each [FindListingDetails] <> null and [FindListingDetails] <> ""), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"HTML"}) in #"Removed Columns"
