cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mussol_
Helper II
Helper II

DIFFERENT BEHAVIOR EXCEL-POWER BI IMPORTING WEB LINKS

Hello, 

I found a different behavior between Excel PowerQuery and PBI PowerQuery if you are importing data from web.

The link I am importing is:

https://es.wikipedia.org/wiki/Anexo:Pa%C3%ADses_por_veh%C3%ADculos_per_c%C3%A1pita

and after importing data, in Excel looks like this:

4.JPG

 

But doing exactly the same steps in Power BI, looks like this:

5.JPG

 

Note that country names are duplicated and command line is different.

I understand the correct import way is the one with excel, but I cannot understand the behavior in PBI.

 

Can somebody explain it?

 

Thanks a lot.

1 ACCEPTED SOLUTION

Hello @mussol_ 

 

this hasn't to do anything with the version of Excel/BI/PowerQuery, but for the functions used by the GUI. Just change the function back to the old version meaning Web.Page(Web.Contents()) and it reads your data as it supposed to be.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

7 REPLIES 7
mussol_
Helper II
Helper II

Microsoft... Is it possible to return to the older version of PowerQuery?

 

It work better.

Hello @mussol_ 

 

this hasn't to do anything with the version of Excel/BI/PowerQuery, but for the functions used by the GUI. Just change the function back to the old version meaning Web.Page(Web.Contents()) and it reads your data as it supposed to be.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

mussol_
Helper II
Helper II

Hello,

I understand the method how to copy and paste, but this is not the question.

The question is that if anything worked fine before, why to modify it?

According the solution, for other queries, should I test both in PBI and Excel and then deliver which one works fine?

I know there should be no answer...

Thanks a lot!!

Josep.

Hello @mussol_ 

 

this is because the two different functions are reading the html in different way. Just check the html-code and you will see that for malta (twice in the column) the code is different then for australia

cell for australia

Jimmy801_0-1613112391578.png

cell of malta

Jimmy801_1-1613112421419.png

this first cell in the malta-cell is not even existing for australia and this makes the difference

 

Hope this helps

 

BR

 

Jimmy

 

 

mussol_
Helper II
Helper II

Hello Jimmy801.

Thanks for the answer, but seems that the new version is not working properly.

You can see at the first image (with Excel), the result is correct and with the new version (with PBI) is not (names duplicated) and several steps are needed to obtain the correct result.

Copy paste is not the solution, this means I have to check for all the queries if is better solution with older or newer version.

Any other solution?

Thanks a lot!!

Josep.

Hello @mussol_ 

 

just as I wrote, register the query in Excel and copy paste the code from the advanced editor to Power BI. 

This is the code that Excel registers

let
    Quelle = Web.Page(Web.Contents("https://es.wikipedia.org/wiki/Anexo:Pa%C3%ADses_por_veh%C3%ADculos_per_c%C3%A1pita")),
    Data0 = Quelle{0}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}})
in
    #"Geänderter Typ"

the result in power bi is then this

Jimmy801_0-1612940066460.png

 

and so it's exactly this what you need


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Jimmy801
Super User III
Super User III

Hello @mussol_ 

 

the reason for this is, that Excel has some older version of the power query engine and therefore missing some functions. Excel uses Web.Page for reading this and Power BI is using Web.BrowserContents with Html.Table.

You can copy paste the code from your Excel to Power BI and you will have the same result


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors