Power Query (web page to Excel) - when there is no table?

[GabrielAIK]

Hi,

I would like to fetch data from an Internet page to Excel, but the problem is that the web page in question does not have a table that is importable.

Is it still possible to fetch the data, or is there no hope?

I'd like to fetch information from a website called Avanza.

E.g. this stock: https://www.avanza.se/aktier/om-aktien.html/82987/nokia-oyj

If you scroll down you see something called "Mäklarstatistik" (Broker statistics). I want to sort this by "Köpt" and then fetch the top 5 rows to Excel.

Is this possible?

Thanks!

 

[Norie]

Do you have a Get & Transform section on the Data tab?

Or a New Query button?

 

[GabrielAIK]

Do you have a Get & Transform section on the Data tab?

Or a New Query button?

Oh!

I had just went from Data straight to pressing "From Web"

But there is also an alternative route, going from Data -> New Query -> From other sources -> Web

There, I enter the URL, select the "Basic" version then press "OK".

Then it connects to the site, and thereafter there is a Navigator window that pops up.

To the left, under "Display Options", I can see all the tables.

As you said, Table 0 is the one I was looking for. So I simply pressed on it, and then on the "Load" button, and it was sent to Excel.

Now, I'm wondering, is it possible to automatically sort it from largest to smallest under column C ("Köpt")?

Or do I need to update Excel sheet using alt+A+R+A

And then manually select the numbers shown in Column C and sort from Largest to smallest?

 

[Norie]

Not sure what you mean, in the Power Query editor you can sort the Köpt using the arrow next to the header and you can do the same when you return the table to Excel.

 

[GabrielAIK]

Not sure what you mean, in the Power Query editor you can sort the Köpt using the arrow next to the header and you can do the same when you return the table to Excel.

I see, I opened the Query editor, and under "Köpt" I wanted to sort it by Descending to get the highest values at the top.

However, the website uses a spacing which the Query Editor doesn't recognize. Is is possible to fix this in some way?

Like transforming all " " to "", enabling it to sort the numbers?

 

[Norie]

So what should a value like 9 521 be?

Is it 9,521?

Also, in the other columns should 5071 actually be 50.71 (50,71)?

 

[GabrielAIK]

So what should a value like 9 521 be?

Is it 9,521?

Also, in the other columns should 5071 actually be 50.71 (50,71)?

Yes exactly!

I received some help yesterday on the forum regarding this matter, using substitution formulas, e.g.: =IFERROR(SUBSTITUTE(SUBSTITUTE(W48,",",".")," ","")+0,"-")

 

[Norie]

In Power query select all the columns except Mäklare.

Right click one of the columns and select Change Type>Using Locale.

In the Change Type with Locale select a suitable data type, e.g. Currency, Decimal Number, from the first drop down.

In the Locale drop down select the relevant locale, which I think is Swedish (Sweden).

Click OK.

You should now have a table with sortable columns.

 

[GabrielAIK]

In Power query select all the columns except Mäklare.

Right click one of the columns and select Change Type>Using Locale.

In the Change Type with Locale select a suitable data type, e.g. Currency, Decimal Number, from the first drop down.

In the Locale drop down select the relevant locale, which I think is Swedish (Sweden).

Click OK.

You should now have a table with sortable columns.

Thank you very much!!

I chose Decimal numbers for the share prices, and then "Whole numbers" for the volumes which seems to solve the problem.

I'll give it a try tonight when I need to update my Excel sheet.

Thank you very much for your patience and help, appreciate it a lot!