Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
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
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
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
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