PowerBI get data why doesn't get correct table?

user_8314628 0
Viewed 23

I'm trying to get the Best and worst states for retirees table from this site.

This is what the table looks like.

enter image description here

But after I input the url to get data, it found 3 suggested tables and none of them is the table that I want.

enter image description here

enter image description here

It is supposed to look like the one in the video tutorial.

enter image description here

How can I fix it?

powerbi-desktop powerbi
1 Answers

This is a bit tricky because the data is actually contained in a table that is generated inside an iframe. You can see this by pressing F12 in your browser (or however else you activate developer tooling) and selecting any element inside the table, then going up until you run into the iframe.

While Power BI supports loading dynamically rendered data, it still can't do this from inside an iframe hosted on another page -- you'd have to load the iframe source directly (in this case https://e.infogram.com/459b1298-00b7-4083-9375-c7a38b98d5c4). However, even specifying this URL directly will not get you what you want, because you need to tell Power BI to wait until the table has loaded -- and if you do that, you lose the ability to use the table extract wizard.

I recommend the following steps instead:

  1. First we find the iframe as described above, and open it in your browser. Wait until it has fully loaded, then save the page to your local machine using the "Web Page - Complete" type. Note the full path of the file.
  2. In Power BI, use "Get data > Web". Instead of the original URL, use "file://" followed by the full path of the file you saved in step 1 (for example, file://C:\Users\jeroen.mostert\Desktop\Best and worst states for retirees - ranking table - Infogram.html -- obviously it should be different on your machine, unless you've stolen my identity).
  3. With the full HTML available, Power BI will now give you the option to extract the table directly with no further fiddling or manual entry needed, so do that.
  4. If this is a one-time thing, you're done! Enjoy. If you need to import the data dynamically, go back to the "Source" step and replace the File.Contents step it generated with the following:
Web.BrowserContents("https://e.infogram.com/459b1298-00b7-4083-9375-c7a38b98d5c4", [WaitFor = [Selector = "table.igc-table"]])

This instructs Power BI to wait until the table has fully loaded. Again, you need some familiarity with web tools and CSS to find the proper selector (look for the table element and note the class property).

For one-time data import, it may actually be easier to use a browser extension to extract the table contents (I won't recommend any, but they exist for most browsers) and then load the data from a local text file/CSV, rather than using Power BI's capabilities for extracting HTML -- which is easier depends a bit on the page and the extension's capabilities. For dynamic import you'll still need the above.

user_41379160
Related