Hyperlinks pulling as 0 in Power Query

[ferlvovi]

I have an excel file where one of the columns are hyperlinks, but when I pull the table into Power Query the whole column just shows zeros. I would like to get the text from the original file

An example of the formula used in the original file is 

=HYPERLINK("#"&CELL("address";OFFSET(Analítico!$B$1;MATCH(104658;Analítico!$B:$B;0)-1;3));104658)

And this is what I get in Power Query

[v-pgoloju]

Hi @ferlvovi,

Great to hear that it's working as expected on your end!. I would suggest accepting your approach as the solution so that it can benefit others as well. It would be really helpful for others in the community who might be facing similar issues and can address them quickly.

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @ferlvovi,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[ferlvovi · Accepted solution]

Hi, I couldn't use the solutions provided, because the original file is not suposed to be edited in any way. I ended up creating a macro to copy the sheet (just values) I needed from the original file to the one I'm working on, and start the query from there. It's a little bit of a nuisance, but it works.

[v-pgoloju]

Hi @ferlvovi,

Thank you for reaching out to the Microsoft Fabric Forum Community.

This happens because Power Query doesn't evaluate formulas it only reads the raw value stored in the cell. When a hyperlink is created using a formula, Excel may not store the display text (like "104658") as the actual value, so Power Query ends up seeing it as 0 or blank. To fix this, you can try creating a helper column in Excel that extracts the display text as plain text. This makes it readable by Power Query.

Best regards,
Prasanna Kumar

[Omid_Motamedise]

Power Query imports the value stored in the cell, and if the cell’s formula results in something that Power Query interprets as zero, it will show zero. Also, Power Query does not evaluate Excel formulas — it only reads the result that Excel stores.

In your case, the HYPERLINK() function returns a clickable link in Excel, but Power Query reads the underlying value, which might be numeric or an empty string converted to zero. So you need to add a helper column manualy or using VBA

[ferlvovi]

My confusion is how it's interpreting it as zero? Because all the texts in the hyperlinks are different numbers. The one I used in the example was 104658, but it still pulls as zero...

[Chanty4u]

Hi 

Use a Helper Column in Excel

Before loading into Power Query, create a helper column that converts the formula result into plain text

=TEXT(104658, "0")

=IF(ISFORMULA(A2), A2, TEXT(A2, "0"))

Then load helper column in power query 

[ferlvovi]

Yes, that would work, but I don't want to have to alter the original file, mainly because I'm not always using the same one. In power query, I get the lastest file from a folder, and I would prefer to not have to create a column every time a new file is uploaded.