I need to convert percentages to decimal in a table that takes data from the internet

Excel automatically converts the data into percentages and I would need them in decimal otherwise I can't multiply them. Which is the problem

No, it isn't.

And Excel has no problem with text in arithmetic expressions, if Excel can convert the text to a numeric value. For example:

="8,77%" * "0,24%"

correctly returns 0,00021048, which appears as 0,021048% when formatted as Percentage.

The primary problem is a language difference: you use comma as the decimal separator; your data uses period.

So, Excel treats that as text only because it cannot convert it to a numeric value.

That applies to the data in column F (Price) and G (Chg), as well as the percentage data in columns E (%Portfolio) and H (%Chg).

My basic use of Power Query does not correct that.

Perhaps Andreas can explain how to use PQ to fix the language differences, as well as the improper use of "accounting" notation, namely: (0,24%) for positive (!) 0,24%.

Otherwise, I would do the following:

1. Copy the following formula and paste it into I2:L102:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2; ","; ""); "."; ","); "("; ""); ")"; "")

2. Copy I2:L102 and paste-value into E2:H102. Then delete I2:L102.
3. Format E2:E102 and H2:H102 as Percentage.

The formula in I2 does the following, by example (the order matters):

1. Removes the comma thousands separator (e.g. 5,220.98 in F23).
2. Replaces the period decimal separator with comma.
3. Replaces the eimproper parentheses ( "(" and ")" ) around percentages in column H.
4. Returns a numeric value, because of the double unary minus ( "--" ).

Additional issues to consider correcting.

1. The %Portfolio values in column E do not sum to 100%, due to decimal rounding errors.

Correction: Enter =1 - SUM(E3:E102) into E2. Then copy E2, and paste-value back into E2.

Caveat: If the rounding error in the original sum exceeds 0.005%, you might want to distribute the rounding error to several of the largest %Portfolio values.

2. The %Chg values in column H are inconsistent with Chg/Price, again due to decimal rounding errors.

Technically, we cannot correct that because part of the problem might be decimal rounding errors in columns F and G, which we cannot correct.

But I prefer the data to be consistent. A personal choice. If you agree....

Correction: Enter =G2/F2 into I2, and copy I2 into I3:I102. Then copy I2:I102, and paste-value in H2:H102. Then delete I2:I102.

Aside.... This obviates the need to correct the original misuse of "accounting" notation in column H.

Excel automatically converts the data into percentages and I would need them in decimal otherwise I can't multiply them.

Nonsense. A percentage is a number and Excel can calculate with it, reproduce this in a new file:

Again: The reason for your error is that you have plain text.

If I import the data from that website, I got text:

You have to change the data type in PQ, see code below.

Andreas.

// Table 0

let

    Source = Web.Page(Web.Contents("https://www.slickcharts.com/nasdaq100")),

    Data0 = Source{0}[Data],

    #"Extracted Text Between Delimiters" = Table.TransformColumns(Data0, {{"% Chg", each Text.BetweenDelimiters(_, "(", ")"), type text}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Between Delimiters",{{"Portfolio%", Percentage.Type}, {"Price", type number}, {"Chg", type number}, {"% Chg", Percentage.Type}})

in

    #"Changed Type"