DIFFERENT BEHAVIOR EXCEL-POWER BI IMPORTING WEB LINKS

[mussol_]

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:

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

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.

[mussol_]

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

It work better.

[Jimmy801 · 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

[mussol_]

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.

[Jimmy801]

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

cell of malta

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

Hope this helps

BR

Jimmy

[mussol_]

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.

[Jimmy801]

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

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]

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