question

MouradBENKADOUR-8650 avatar image
0 Votes"
MouradBENKADOUR-8650 asked YnakiZumalacarregui-5668 published

Unable to import data from web in Power query in Excel

Hi,
I try to import data from site web :
https://www.forbes.com/global2000/#28adc35a335d

or

https://mvp.microsoft.com/en-us/MvpSearch?ex=Business+Applications

I can Import table from those web sites by using Power BI Desktop without any problem (as in the image below), but I'm unable to Importing Data from those Websites by using Power Query in Excel (as in the image below).

in other hand, I don't know the Best way to export data from Power Bi Desktop to Excel.

@MiguelEscobar-3907 @ChrisWebb-7726 @Lz-3068

Thanks in advance,
Mourad
84262-excel-forbes.png
84166-forbes.png
84225-excel-mvp.png
84200-mvp.png


power-query-not-supported
excel-forbes.png (42.4 KiB)
forbes.png (73.0 KiB)
excel-mvp.png (39.9 KiB)
mvp.png (80.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Mourad. One note on Q&A protocol: please don't tag people in your questions/comments unless they've already replied to the question. Thanks!
Ehren

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered Ehren commented

Hi @MouradBENKADOUR-8650

Power BI Desktop provides more funtionalities and functions than Power Query for Excel...
Tables inside Web pages can be defined with HMTL tags <div> and <table>

As of today, Excel Power Query only "recognizes" tables defined with the <table> tag. So, when your target Web page only contains tables defined with <div> tags the PQ Navigator doesn't list any Table but HMTL document(s) instead (as shown in your 1st picture)

Things are different in Power BI Desktop as it recognizes a wider variety of tables, thanks to (and probably not only) function Hmtl.Table that isn't currently available in Excel PQ

Hope this clarifies things

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Does this mean that to import data from webpages that identify their tables with <div> I would need to install an additional program?

0 Votes 0 ·

Right now, you would either need to use Power BI Desktop or write some custom M in Excel to extract the data. Eventually, however, the new web connector will appear in Excel as well.

0 Votes 0 ·
MiguelEscobar-3907 avatar image
0 Votes"
MiguelEscobar-3907 answered MouradBENKADOUR-8650 commented

Hey!
What happens when you copy the query that was created in Power BI Desktop into Excel? does the query still work?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I did that, but it doesn't recognize Html.Table function, and show the error below :

 Expression.Error: The name 'Html.Table' wasn't recognized.  Make sure it's spelled correctly.

The M Code in Power Bi Desktop below84285-htmlerror.png84229-pbi-code.png


0 Votes 0 ·
pbi-code.png (19.4 KiB)
htmlerror.png (18.7 KiB)
MouradBENKADOUR-8650 avatar image
0 Votes"
MouradBENKADOUR-8650 answered MouradBENKADOUR-8650 published

I did that, but it doesn't recognize Html.Table function, and show the error below :

  Expression.Error: The name 'Html.Table' wasn't recognized.  Make sure it's spelled correctly.

The M Code in Power Bi Desktop below :

 let
     Source = Web.BrowserContents("https://www.forbes.com/global2000/#17a97667335d"),
     #"Extracted Table From Html" = Html.Table(Source, {
                 {"Column1", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column5", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column6", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column7", "TABLE.fbs-table.organization > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(1), TABLE.fbs-table.organization > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(1), TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column8", "TABLE.fbs-table.organization > * > TR > TD[colspan=""8""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE.fbs-table.organization > * > TR"]),
     #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
     #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
                 {"Rank", Int64.Type}, {"Company", type text}, {"Country/Territory", type text}, {"Sales", type text}, {"Profits", type text}, {"Assets", type text}, {"Market Value", type text}, {"Column8", type text}}),
     #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
 in
     #"Removed Blank Rows"


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YnakiZumalacarregui-5668 avatar image
0 Votes"
YnakiZumalacarregui-5668 answered YnakiZumalacarregui-5668 published

Have exact same problem and have not been able to solve it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.